Linux 服務器上的程序每天都在更新 MySQL 數據庫，于是就想起寫一個 shell 腳本，結合 crontab，定時備份數據庫。其實非常簡單，主要就是使用 MySQL 自帶的 mysqldump 命令。
#!/bin/bash
# Shell script to backup MySql database
# To backup Nysql databases file to /backup dir and later pick up by your
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
MyUSER="SET-MYSQL-USER-NAME" # USERNAME
MyPASS="SET-PASSWORD" # PASSWORD
MyHOST="localhost" # Hostname
# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
# Backup Dest directory, change this if you have someother location
DEST="/backup"
# Main directory where backup will be stored
MBD="$DEST/mysql"
# Get hostname
HOST="$(hostname)"
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"
# File to store current backup file
FILE=""
# Store list of databases
DBS=""
# DO NOT BACKUP these databases
IGGY="test"
[ ! -d $MBD ] mkdir -p $MBD || :
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
for db in $DBS
do
skipdb=-1
if [ "$IGGY" != "" ];
then
for i in $IGGY
do
[ "$db" == "$i" ] skipdb=1 || :
done
fi
if [ "$skipdb" == "-1" ] ; then
FILE="$MBD/$db.$HOST.$NOW.gz"
# do all inone job in pipe,
# connect to mysql using mysqldump for select mysql database
# and pipe it out to gz file in backup dir :)
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
fi
done
保存后將以上腳本加入crontab調度。如：每天早上四點半備份：30 4 * * * /data/backup-db.sh
如果你使用mysql5.1，可能會提示mysqldump 錯誤:
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist
mysqldump: Couldn't execute 'show create table `slow_log`': Table 'mysql.slow_log' doesn't exist
原因是mysql庫中沒有show_log表和general_log表，需要手動創建：
CREATE TABLE IF NOT EXISTS general_log (
event_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
user_host mediumtext NOT NULL,
thread_id int(11) NOT NULL,
server_id int(10) unsigned NOT NULL,
command_type varchar(64) NOT NULL,
argument mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
CREATE TABLE IF NOT EXISTS slow_log (
start_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
user_host mediumtext NOT NULL,
query_time time NOT NULL,
lock_time time NOT NULL,
rows_sent int(11) NOT NULL,
rows_examined int(11) NOT NULL,
db varchar(512) NOT NULL,
last_insert_id int(11) NOT NULL,
insert_id int(11) NOT NULL,
server_id int(10) unsigned NOT NULL,
sql_text mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
方法二：
注意：
DumpFile=db$(date +%y%m%d)如果設置為這樣一定要將此腳本放備份目錄下才行。
DumpFile="$BackupPath"db$(date +%y%m%d) 如果設置為這樣，日志中會有這樣的提示tar: Removing leading `/' from member names 是因為備份的目錄使用的是絕對路徑，不過這樣不影響數據，可以根據自己習慣而定。
-------------------------------------------------------------------start
#!/bin/bash
#This is a ShellScript For Auto DB Backup
#Powered by aspbiz
#2004-09
#Setting
#設置數據庫名，數據庫登錄名，密碼，備份路徑，日志路徑，數據文件位置，以及備份方式
#默認情況下備份方式是tar，還可以是mysqldump,mysqldotcopy
#默認情況下，用root(空)登錄mysql數據庫，備份至/root/dbxxxxx.tgz
DBName=mysql
DBUser=root
DBPasswd=
BackupPath=/root/
LogFile=/root/db.log
DBPath=/var/lib/mysql/
#BackupMethod=mysqldump
#BackupMethod=mysqlhotcopy
#BackupMethod=tar
#Setting End
NewFile="$BackupPath"db$(date +%y%m%d).tgz
DumpFile="$BackupPath"db$(date +%y%m%d)
OldFile="$BackupPath"db$(date +%y%m%d --date='5 days ago').tgz
echo "-------------------------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile
echo "--------------------------" >> $LogFile
#Delete Old File
if [ -f $OldFile ]
then
rm -f $OldFile >> $LogFile 2>1
echo "[$OldFile]Delete Old File Success!" >> $LogFile
else
echo "[$OldFile]No Old Backup File!" >> $LogFile
fi
if [ -f $NewFile ]
then
echo "[$NewFile]The Backup File is exists,Can't Backup!" >> $LogFile
else
case $BackupMethod in
mysqldump)
if [ -z $DBPasswd ]
then
mysqldump -u $DBUser --opt $DBName > $DumpFile
else
mysqldump -u $DBUser -p$DBPasswd --opt $DBName > $DumpFile
fi
tar czvf $NewFile $DumpFile >> $LogFile 2>1
echo "[$NewFile]Backup Success!" >> $LogFile
rm -rf $DumpFile
;;
mysqlhotcopy)
rm -rf $DumpFile
mkdir $DumpFile
if [ -z $DBPasswd ]
then
mysqlhotcopy -u $DBUser $DBName $DumpFile >> $LogFile 2>1
else
mysqlhotcopy -u $DBUser -p $DBPasswd $DBName $DumpFile >>$LogFile 2>1
fi
tar czvf $NewFile $DumpFile >> $LogFile 2>1
echo "[$NewFile]Backup Success!" >> $LogFile
rm -rf $DumpFile
;;
*)
/etc/init.d/mysqld stop >/dev/null 2>1
tar czvf $NewFile $DBPath$DBName >> $LogFile 2>1
/etc/init.d/mysqld start >/dev/null 2>1
echo "[$NewFile]Backup Success!" >> $LogFile
;;
esac
fi
echo "-------------------------------------------" >> $LogFile
---------------------------------------------------------------------------------------------end
以上內容就是本文給大家介紹的linux下mysql如何自動備份shell腳本，希望大家喜歡。
您可能感興趣的文章:- shell腳本定時備份MySQL數據庫數據并保留指定時間
- shell腳本實現mysql定時備份、刪除、恢復功能
- 使用shell腳本每天對MySQL多個數據庫自動備份的講解
- 用shell寫一個mysql數據備份腳本
- MySQL數據庫的shell腳本自動備份
- 一個簡單的MySQL備份Shell腳本
- CentOS下mysql定時備份Shell腳本分享
- Shell腳本自動備份MySQL到FTP并定期清理過期備份
- shell實現自動備份mysql、整站數據的兩個腳本分享
- mysql常用備份命令和shell備份腳本分享
