MySQL Database perfect Incremental backup script

Whether because MySQL is too big, back and forth backup waste resource bandwidth and worry, if you want to solve this problem requires incremental backup, the following is the Zhangxiao resource Network modified a MySQL incremental backup script, I have made the relevant comments, you can use the modified.

VI/ETC/MY.CNF open log and regular cleanup log

Log-bin=mysql-bin
Binlog_format=mixed
The number of days that the binary log is automatically deleted. The default value is 0, which means "No automatic deletion"
Expire_logs_days = 5
Full backup script:
#!/bin/bash
# NAME:QBK Full backup script
# MySQL QBK scripts
# by Zxsdw.com
# Last Modify:2015-01-21
#定义脚本存放路径
#scriptsDir =/usr/sbin
#定义用户名及密码
User=root
userpwd= Password
#定义要备份的数据库
database= Database
#定义完全备份文件存放路径
Bakdir=/backup/mysql
#eMailFile = $bakDir/email.txt
#eMail =admin@zxsdw.com
#定义日志文件
logfile= $bakDir/mysqlbak.log
Date= ' Date +%y%m%d '
echo "" >> $LogFile
echo "" >> $LogFile
echo "--------------------------" >> $LogFile
echo $ (date + "%y-%m-%d%h:%m:%s") >> $LogFile
echo "-----------------" >> $LogFile
CD $bakDir
dumpfile= $DATE. sql.gz
Mysqldump--flush-logs-u$user-p$userpwd--quick $database | gzip > $DumpFile
echo "Dump done" >> $LogFile
echo "[$DumpFile]backup success!" >> $LogFile
daily_databakdir= $bakDir/daily_backup
CD $bakDir/daily_backup
Find $daily _databakdir-name "daily*"-type f-mtime +35-exec rm {} \; >/dev/null 2>&1
Incremental backup script:
#!/bin/bash
# NAME:ZBK Incremental Backup
# MySQL ZBK scripts
# by Zxsdw.com
# Last Modify:2015-01-21

#定义数据库用户名及密码
User=root
userpwd= Password
#定义数据库
database= Database
#生成一个新的mysql the-bin.00000x file and automatically create a new one if the Err log is cleared.
/usr/local/mysql/bin/mysqladmin-u$user-p$userpwd Flush-logs
#定义增量备份位置
Daily_databakdir=/backup/mysql/daily_backup
#定义MYSQL数据日志目录
Mysqldatadir=/usr/local/mysql/var
#定义增量日志及目录
emailfile= $daily _databakdir/email.txt
#eMail =admin@zxsdw.com
#定义变量DATE格式为20150127
Date= ' Date +%y%m%d '
#定义一个总的logFile日志
logfile= $daily _databakdir/mysql$date.log

#美化日志模板
echo "" > $eMailFile
echo "-----------------------" >> $eMailFile
#时间格式为15-01-27 01:06:17
echo $ (date + "%y-%m-%d%h:%m:%s") >> $eMailFile
echo "-------------------------" >> $eMailFile

#定义删除bin日志的时间范围, formatted as 20150124010540
time=$ (date "D 3 day Ago" +%y%m%d%h%m%s)
#定义需要增量备份数据的时间范围, Format 2015-01-26 01:04:11
starttime=$ (date "D 1 day Ago" + "%y-%m-%d%h:%m:%s")

########## #开始删除操作美化日志标题 ##############
echo "Delete 3 days before the log" >> $eMailFile

#删除三天前的bin文件, and update index records in index, beautify the journal title
Mysql-u$user-p$userpwd-e "purge master logs before ${time}" && echo "Delete 3 days before log" |tee-a $eMailFi Le

#查找index索引里的bin 2 into the file and assign it to I.
Filename= ' cat $mysqlDataDir/mysql-bin.index |awk-f '/' {print $} '
For I in $filename
Todo
######## #开始增量备份操作, beautify the log title ###########
echo "$StartTime start Backup Binlog" >> $eMailFile

#利用mysqlbinlog备份1天前增加的数据, and gzip compressed to the incremental backup directory
/usr/local/mysql/bin/mysqlbinlog-u$user-p$userpwd-d $database--start-datetime= "$StartTime" $mysqlDataDir/$i |gzip >> $daily _databakdir/daily$date.sql.gz |tee-a $eMailFile

Done

#如果以上备份脚本执行成功, and then run the following delete script
If [$? = 0]
Then
# Delete mtime>32 Incremental log backup file
Find $daily _databakdir-name "*.log"-type f-mtime +32-exec rm {} \; >/dev/null 2>&1
CD $daily _databakdir
echo "Daily backup succeed" >> $eMailFile
Else
echo "Daily backup fail" >> $eMailFile
#mail-S "MySQL Backup" $eMail < $eMailFile #备份失败之后发送邮件通知
#fi结束IF判断
Fi

#把变量eMailFile的内容替换logFile内容

Cat $eMailFile > $logFile

#如果上面的IF判断失败, run the incremental log backup file that deletes mtime>32 again
Find $daily _databakdir-name "*.log"-type f-mtime +32-exec rm {} \; >/dev/null 2>&1
RSYNC-VZRTOPG--delete--progress--password-file=/usr/local/rsync/rsync.passwd root@ $ip:/zxs/allimg/$ (date-d -1day +%y%m%d)/zxs/allimg/
Gunzip </backup/mysql/daily_backup/ceshi.sql.gz | /usr/local/mysql/bin/mysql-u User name-p password database name--force
--force parameter ignores error