linux mysqlDatabase Backup指令碼代碼

可以將這個指令碼放進crontab，他的配製檔案在 /etc/crontab中每天淩晨執行一次，自動備份 這個指令碼每天最多隻執行一次，而且只保留最近五天的備份在伺服器上。

代碼如下

複製代碼

#!/bin/bash #This is a ShellScript For Auto DB Backup #Powered by aspbiz #2004-09 #Setting #設定資料庫名，資料庫登入名稱，密碼，備份路徑，日誌路徑，資料檔案位置， #以及備份方式 #預設情況下備份方式是tar，還可以是mysqldump,mysqldotcopy #預設情況下，用root(空)登入mysql資料庫，備份至/root/namexxxxx.tgz DBName=mysql DBUser=root DBPasswd=password BackupPath=/root/ LogFile=/root/db.log DBPath=/var/local/mysql5/var/ #BackupMethod=mysqldump #BackupMethod=mysqlhotcopy #BackupMethod=tar #Setting End NewFile="$BackupPath""$DBName"$(date +%y%m%d).tgz DumpFile="$BackupPath""$DBName"$(date +%y%m%d) OldFile="$BackupPath""$DBName"$(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

例2.如果對上面指令碼不滿意可參考下面mysqlDatabase Backup指令碼哦

代碼如下

複製代碼

#!/bin/bash #Mysql autobackup shell#write by tuspark.cn #-------------------資料庫相關的使用者名稱、密碼、需要備份的資料庫名、備份目錄等 dbuser=root dbpasswd=XXXXX dbserver=localhost dbname=XXXXX dbopt=--opt backupdir=/dcbackup/ #-------------------是否開啟FTP遠程備份，0為否，1為是。 copytoftp=1 ftpserver=XXXXX ftpuser=XXXXX ftppasswd=XXXXX #-------------------以下參數 fileprefix=dcradiusdump filename=$backupdir`date +%F`.sql newfile=$fileprefix-`date +%F`.tar.gz keepdays=10 #-------------------以下為備份時的日誌 logfile=/var/log/mysqlbackup.log logtmp=/var/log/mybackup.tmp #=============================================== if [ ! -d $backupdir ] then echo "$backupdir is not exist, then make ..." >> $logfile mkdir -p $backupdir fi echo "start====================================>">>$logfile echo "Beginning backup `date '+%F %T'`" >>$logfile echo "Delete $keepdays days ago files ..." >>$logfile find $backupdir -name $fileprefix* -mtime +$keepdays -fls $logtmp -exec rm {} ; echo "Deleted Backup file is :">>$logfile cat $logtmp >>$logfile echo "Delete old file Success!" >>$logfile if [ -f $backupdir$newfile ] then echo "$newfile backup exist, backup stop ..." >>$logfile else if [ -z $dbpasswd ] then mysqldump -u$dbuser -h$dbserver $dbopt $dbname >$dumpfilename else mysqldump -u$dbuser -p$dbpasswd -h$dbserver $dbopt $dbname >$dumpfilename fi tar czvf $backupdir$newfile $dumpfilename >>$logfile 2>&1 echo "$backupdir$newfile Backup Success!" >>$logfile rm -fr $dumpfilename if [ $copytoftp = 1 ]; then if [ -z $ftpserver ];then echo "Ftp Server not set,Copy to Ftp Failed ..." >>$logfile exit 1 elif [ -z $ftpuser ];then echo "Ftp user not set, Copy to Ftp Failed ..." >>$logfile exit 2 elif [ -z $ftppasswd ]; then echo "Ftp password not set, Copy to Ftp Failed ..." >>$logfile exit 3 else echo "Start copy to Ftp server ...." >> $logfile ftp -n>$logfile fi

執行個體三

代碼如下

複製代碼

第一步：在伺服器上配置備份目錄代碼： -------------------------------------------------------------------------------- mkdir /var/lib/mysqlbackup cd /var/lib/mysqlbackup -------------------------------------------------------------------------------- 第二步：編寫備份指令碼代碼： -------------------------------------------------------------------------------- vi dbbackup.sh -------------------------------------------------------------------------------- 粘帖以下代碼，務必更改其中的username,password和dbname。 代碼： -------------------------------------------------------------------------------- #!/bin/sh #Username to access the MySQL server USERNAME="username" # Username to access the MySQL server PASSWORD="password" # List of DBNAMES for Backup DBNAME="dbname" #date timestamp for log message DATE=`/bin/date +%Y-%m-%d_%Hh%Mm` #output file OUTDIR="/var/lib/mysqlbackup/" OUTFILE="ip_bindass".$DATE."sql.gz" #working directory DIR="/var/lib/mysqlbackup/"#cd $DIR # MySQL Backup /usr/bin/mysqldump --database $DBNAME --opt --single-transaction -u$USERNAME -p$PASSWORD | /usr/bin/gzip -9 > $OUTDIR$OUTFILE -------------------------------------------------------------------------------- 更改備份指令碼許可權 代碼： -------------------------------------------------------------------------------- chmod +x dbbackup.sh -------------------------------------------------------------------------------- 第三步：用crontab定時執行備份指令碼代碼： -------------------------------------------------------------------------------- crontab -e -------------------------------------------------------------------------------- 若每天下午3點20備份，添加如下代碼， 代碼： -------------------------------------------------------------------------------- 20 15 * * * /var/lib/mysqlbackup/dbbackup.sh -------------------------------------------------------------------------------- 搞定！