MySQL Binary incremental backup

I've been thinking about how to incrementally back up binary logs, so we can use mysqldump + binary full or take advantage of xtrabackup+ binary backup

Thought: Using Mysql-bin.index, if it is the first backup, copy all the files in the Mysql-bin.index to the backup directory and then save the sequence number of the binary log to a file after the backup backbinlogpostion
Incremental backup (non-first backup), the backbinlogpostion is used to compare with the current mysql_bin.index, if the sequence number is less than Mysql-bin.index, copy the binary file to the backup directory

#!/bin/bash
#################################################################
#name: INCREMENTALBACKDB
#function: incremental-backup for MySQL Everyday
#finish-time:2015-04-02 by [email protected]
#################################################################
Bindir=/usr/local/mysql/data
Backupdir=/usr/backupyzx
Binlogfile=/usr/local/mysql/data/mysql-bin.index
Backlogpostion=0
Backuplog=${backupdir}/backup.log
printn=
binlogpostion=$ (wc-l $BinLogFile |awk ' {print $} ')
#get binnary Log sequ
lastseqbinlog=$ (basename $ (tail-n 1 $BinLogFile))
LASTBINLOGPOSTION=${LASTSEQBINLOG##*0}
#before you backup,you should flush logs
/usr/local/mysql/bin/mysqladmin-uroot-p ' Tianqu ' flush-logs
echo "Begin Increment-backup,time is:" $ (date + '%y-%m-%d%h:%m:%s ') >> $BackupLog
If [-e ${backupdir}/backbinlogpostion];then
#取出backbinlogpostion中二进制的序号, such as mysql-bin.000002, the saved sequence number is 2.
backlogpostion=$ (Cat ${backupdir}/backbinlogpostion)
#如果当前二进制的序号与备份的二进制序号相同
If [${lastbinlogpostion}-eq $BackLogPostion];then
echo "Backup Skiped,backup success!" >>${backuplog}
Else
#取出序号的之间的差值
printn=$ ((${lastbinlogpostion}-${backlogpostion}))
Echo $printN
q=$ ((${backlogpostion}+1))
For file in $ (tail-n ${printn} ${binlogfile});d O
basefile=$ (basename ${file})
If [$q-ge ${lastbinlogpostion}];then
Break
Fi
CP-PD ${bindir}/${basefile} ${backupdir}
Echo ${basefile} "is copying ..." >>${backuplog}
((q++))
Done
Echo ${lastbinlogpostion} >${backupdir}/backbinlogpostion
Fi
Else
#如果是第一次备份, the backbinlogpostion is created to record the currently copied binary log, making it easier to back up only the new parts when a second backup is made
Touch ${backupdir}/backbinlogpostion
I=1
For file in $ (cat ${binlogfile});d O
basefile=$ (basename ${file})
If [$i-eq ${binlogpostion}];then
echo ${basefile# #0} >${backupdir}/backbinlogpostion
Fi
If [$i-eq $ ((${binlogpostion}+1))];then
Break
Fi
CP-PD ${bindir}/${basefile} ${backupdir}
Echo ${basefile} "is copying ..." >>${backuplog}
((i++))
Done
Fi
echo "Increment-backup Finished,time is:" $ (date + '%y-%m-%d%h:%m:%s ') >> $BackupLog
#Delete old File
#将当前备份的文件转移到远程机器, and then delete the local archive
#find ${backupdir}-type f-mtime +15-exec rm-rf {} \;
#/usr/bin/rsync-vzrtopg--progress ${backupdir} [email protected]::backup/backdb/--password-file=/usr/local/etc/ Rsyncd.sec

This article is from the "Wind Water" blog, please be sure to keep this source http://linuxybird.blog.51cto.com/5689151/1638035

MySQL Binary incremental backup