Ideas and methods for incremental backup of MySQL database

#function: MYSQL incremental backup #version: 1.0.0
#author:wangyanlin
#date: 2017/08/02
#-----------------------------------------------------------------------------------------------
#!/bin/sh
export LANG=en_US.UTF-8
#Set time DATE=`date +%Y%m%d`
#Set information USER=root
PASSWORD=withub
#Set the path cd /
/usr/bin/mkdir -p mysql_bak/daily 
/usr/bin/mkdir -p mysql_bak/logs
BakDir=/mysql_bak/daily
BinDir=/var/lib/mysql
LogFile=/mysql_bak/logs/Daily_$DATE.log
BinlogFile=/var/lib/mysql/mysql-bin.index
/usr/bin/mysqladmin -u$USER -p$PASSWORD flush-logs #Flush log Counter=`wc -l $BinlogFile | awk '{print $1}'`
NextNum=0
start_time=`date +'%Y-%m-%d %H:%M:%S'`
echo `date +"%Y year%m month%d day%H:%M:%S"` $Next1 Bakup start! >> $LogFile
#This for loop is used to compare the two values ​​of $Counter and $NextNum to determine whether the file exists or is the latest.
for file in `cat $BinlogFile`
do
  base=`basename $file`
  #basename is used to intercept the mysql-bin.00000* file name and remove the ./ in front of ./mysql-bin.000005
  NextNum=`expr $NextNum + 1`
  if [ $NextNum -eq $Counter ]
  then
    echo $base skip! >> $LogFile
  else
    dest=$BakDir/$base
    if(test -e $dest)
    #test -e is used to detect whether the target file exists. If it exists, write exist! to $LogFile.
    then
      echo $base exist! >> $LogFile
    else
      cp $BinDir/$base $BakDir
      echo $base copying >> $LogFile
    fi
  fi
done
echo `date +"%Y year%m month%d day%H:%M:%S"` $Next2 Bakup succ! >> $LogFile
end_time=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$start_time" +%s);
end_seconds=$(date --date="$end_time" +%s);
echo "This backup running time: "$((end_seconds-start_seconds))"s" >> $LogFile

[root@master leo]# cat DBfullBak.sh 
#!/bin/bash
#use mysqldump to fully backup mysql data
BakDir=/root/leo/full
LogFile=/root/leo/full/bak.log
Date=`date +%Y%m%d`
Begin=`date +"%Y year%m month%d day%H:%M:%S"`
cd $BakDir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
mysqldump -uroot -p123456 --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > $DumpFile
tar zcvf $GZDumpFile $DumpFile
rm $DumpFile
Last=`date +"%Y year%m month%d day%H:%M:%S"`
echo Start: $Begin End: $Last $GZDumpFile succ >> $LogFile

--all-databases #Backup all databases --lock-all-tables #Add read locks to all tables --routinge #Stored procedures and functions --triggers #Trigger --events #Record events --master-data=2 #Record the current binary log position in the backup file and annotate it. 1 is not commented out and is meaningful in master-slave replication --flush-logs #Roll the log once

[root@master full]# ls
20140728.sql.tgz bak.log
[root@master full]# cat bak.log 
Start: 2014-07-28 19:02:59 End: 2014-07-28 19:02:59 20140728.sql.tgz succ
Start: 2014-07-28 19:12:01 End: 2014-07-28 19:12:01 20140728.sql.tgz succ
[root@master full]#

[root@master leo]# cat DBDailyBak.sh 
#!/bin/bash
BakDir=/root/leo/binlog/
BinDir=/var/lib/mysql
LogFile=/root/leo/binlog/bak.log
BinFile=/var/lib/mysql/mysql-bin.index
mysqladmin -uroot -p123456 flush-logs
Counter=`wc -l $BinFile|awk '{print $1}'`
NextNum=0
for file in `cat $BinFile`
do 
 base=`basename $file`
 NextNum=`expr $NextNum + 1`
 if [ $NextNum -eq $Counter ]
 then
  echo $base skip! >> $LogFile
 else
  dest=$BakDir/$base
  if(test -e $dest)
  then
   echo $base exist! >> $LogFile
  else
   cp $BinDir/$base $BakDir/
   echo $base copying >> $LogFile
  fi
 fi
done
echo `date +"%Y year%m month%d day%H:%M:%S"` $Next Bakup succ~ >> $LogFile