mysql二進制日志增量備份腳步

本文章給大家分享一個mysql5.5的二進制日志增量備份腳步代碼，這個我只是寫了代碼，如果要定時備份計劃任務來操作。  代碼如下 復制代碼

#!/bin/bash
#定義用戶名及密碼
host=
user=
userPWD=
#定義數據庫
database=

mysqladmin -h$host -u$user  flush-logs
daily_databakDir=/backdir
#定義MYSQL數據目錄
mysqlDataDir=/mysql5.5/data

eMailFile=$daily_databakDir/email.txt
#eMail=

DATE=`date +%Y%m%d%H%M%S`
logFile=$daily_databakDir/mysql$DATE.log
#echo "       " > $eMailFile
#echo "-----------------------" >> $eMailFile
#echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile
#echo "-------
#
#------------------" >> $eMailFile
TIME=$(date "-d 40 day ago" +%Y%m%d%H%M%S)
StartTime=$(date -d "-60 minute" +"%Y-%m-%d %H:%M:%S")
echo “Delete 40 days before the log” >>$eMailFile
mysql -h$host -u$user  -e "purge master logs before ${TIME}" && echo "delete 40 days before log" |tee -a $eMailFile #刪除10天前的2進制文件

filename=`cat $mysqlDataDir/mysql-bin.index |awk -F "/" '{print $2}'` # 2進制文件
for i in $filename
do
#echo "$StartTime start backup binlog" >> $eMailFile
mysqlbinlog -h$host -u$user -d $database --start-datetime="$StartTime" $mysqlDataDir/$i >> $daily_databakDir/daily$DATE.sql |tee -a $eMailFile
done
if [ $? = 0 ]
then

# 刪除mtime>32的增量日志備份文件
find $daily_databakDir -name "*.log" -type f -mtime +32 -exec rm {} ; > /dev/null 2>&1

cd $daily_databakDir
tar -cvzf $daily_databakDir/$database$DATE.tar.gz daily$DATE.sql
# >>/dev/null 2&1
#scp 至另一台服務器
echo "Daily backup succeed" >>$eMailFile
else
echo "Daily backup fail"  >>$eMailFile
#mail -s "MySQL Backup" $eMail < $eMailFile #備份失敗之後發送郵件通知
fi
cat $eMailFile > $logFile

# 刪除mtime>32的增量日志備份文件
find $daily_databakDir -name "*.log" -type f -mtime +32 -exec rm {} ; > /dev/null 2>&1