MySQL Script for incremental backup

because the online database is a bit large, the disk does not have a raid, and the server is scarce. Originally wanted to use Xtrabackup to do incremental backup, but a variety of errors, I have lost confidence in it, so, write a script to achieve.

The script is divided into two parts, part is full backup, this is nothing good to introduce, is fully prepared. The other part is incremental backup, the incremental backup aspect is to be carried out in the smallest units of the day, each day the increment of content is independent of a SQL file, of course, can also be modified, appended to the same file, but I think it is better to separate.

Script just write out, simple test has no problem, interested classmates can help test, welcome report error, reported bug email: [Email protected]

Task Schedule:

0 3 * * 5/bin/bash/var/scripts/full-backup.sh &>/dev/null0 3 * * 1-4,6,7/bin/bash/var/scripts/inc-backup.sh &A Mp;>/dev/null

Incremental script:

#!/bin/bash##################################################################      mysql backup shell script part 1  (full backup) # #Completed  in sep  2014 22# #Author:  colin wong# #Email:  [email protected]########################## ############################################### base_info ###### #Date = ' date +%y%m%d ' BACK_DIR= "/ Usr/backup/full-backup "file_dir="/usr/backup "user=rootdbpw=123456mysqlbin=/usr/bin/mysqlmysqldumpbin=/usr/bin/ mysqldump## check whether dir exists[[ -d  $BACK _dir-$Date  ]] | |  mkdir -p  $BACK _dir-$Date ## start backup$mysqldumpbin -u$user -p "$DBPW"  --opt --triggers --routines --events --ignore-table=mysql.events --flush-logs  --hex-blob --master-data=1 --all-databases >  $BACK _dir-$Date/fullbackup.sql  ## get postionsed -n  ' 22p '   $BACK _dir-$Date/fullbackup.sql | awk -f "' |,|=|;"   ' {print $3,$6} '  >  $FILE _dir/fullbackpostion ## check backup fileif  [ ! -s  $BACK _dir-$Date/fullbackup.sql ]; then         echo  "Mysqldump backup file faild , please check"  |  tee -a /var/scripts/full-backup.log        exit  1fi

Incremental backup:

#!/bin/bash##################################################################      mysql backup shell scriptpart 2  (incremental backup) # #Completed  in sep  2014 22# #Author:  colin wong# #Email:  [email protected]########################## ################################################### increment backup ############# #Date = ' Date  +%y%m%d ' olddate= ' date --date= ' 1 days ago '  +%y%m%d ' mysqlbasedir= '/var/lib/mysql/' Backupdir= "/usr/backup" startbinlog= ' awk  ' {print $1} '   $BackupDir/fullbackpostion ' startpostion= ' awk  ' {print $2} '   $BackupDir/fullbackpostion ' mysqlbin=/usr/bin/mysqlmysqlbinlog=/usr/bin/ mysqlbinloguser=rootdbpw=tianqu## starting...if [ -s  $BackupDir/postion-$OldDate  ]; Then# get master info$mysqlbin -u$user -p "$DBPW"  -e  "show master " Status\g "|awk  ' {PRINT&NBsp;$2} '  >  $BackupDir/postion-$Date # get the first backup binlog and  postionlaststartbinlog= ' sed -n  ' 2p '   $BackupDir/postion-$OldDate ' laststartpos= ' sed -n   ' 3p '   $BackupDir/postion-$OldDate ' # get after the first backup binlog  and postionafterstartbinlog= ' sed -n  ' 2p '   $BackupDir/postion-$Date ' afterstartpos= ' sed  -n  ' 3p '   $BackupDir/postion-$Date ' # check  whether the binlog  switchif [  "$LastStartBinLog"  =  "$AfterStartBinLog"  ];then$mysqlbinlog -- start-position= $LastStartPos  --stop-position= $AfterStartPos   $MysqlBaseDir/$LastStartBinLog  > >  $BackupDir/inc_backup_$date.sqlelsestartline= ' awk  "/$LastStartBinLog/{print nr}"   "$ Mysqlbasedir "Mysql-bin.index '         stopline= ' wc -l $ mysqlbasedir/mysql-bin.index |awk  ' {print $1} '         for i in  ' seq   $StartLine   $StopLine '         do# Get binlog  Filename from line i        binlogfile= ' Sed -n   "$i" p  "$MysqlBaseDir" mysql-bin.index |sed  ' s/.\///g '                  case  "$BinLogFile"  in                  "$StartBinLog")                   $MysqlBinLog  -- start-position= $StartPostion   $MysqlBaseDir $binlogfile >>  $BackupDir/inc_backup_$date.sql                 ;;                  "$StopBinLog")                   $MysqlBinLog  --stop-position= $StopPostion   $MysqlBaseDir $binlogfile >>  $BackupDir/inc_backup_$date.sql                 ;;                 *) #  normally, program will not perform here                 echo -e  "$Date \n$startbinlog to $ Stopbinlog have other values " >> /var/log/MyBackup.log                 esac         donefielse## Get New Postion$MysqlBin -u$user -p "$DBPW"  -e  "show master status\g" |awk  ' {print $2} '  >   $BackupDir/postion-$Date ## get stop postion and binlogstopbinlog= ' sed -n   ' 2p '   $BackupDir/postion-$Date ' stoppostion= ' sed -n  ' 3p '   $BackupDir/postion-$Date ' # #if  [  "$StartBinLog"  =  "$StopBinLog"  ]; then$mysqlbinlog --start-position=$ startpostion --stop-position= $StopPostion   $MysqlBaseDir/$StartBinLog  >>  $BackupDir/ Inc_backup_$date.sqlelsestartline= ' awk  "/$StartBinLog/{print nr}"   "$MysqlBaseDir" Mysql-bin.index ' stopline= ' wc -l  $MysqlBaseDir/mysql-bin.index |awk  ' {print $1} ' for  i in  ' seq  $StartLine   $StopLine ' dobinlogfile= ' sed -n  "$i" p   "$ Mysqlbasedir "mysql-bin.index |sed  ' s/.\///g" case  "$BinLogFile"  in "$StartBinLog") $ mysqlbinlog --start-position= $StartPostion &nbsp, $MysqlBaseDir $binlogfile >>  $BackupDir/inc_backup_$date.sql;; " $StopBinLog ") $MysqlBinLog  --stop-position= $StopPostion   $MysqlBaseDir $binlogfile >> $ Backupdir/inc_backup_$date.sql;; *) echo -e  "$Date \n$startbinlog to  $StopBinLog  have other values"  > > /var/scripts/mybackup.logesacdonefifi############################  packing  #### ########################### #cd   $BackupDirif  [ -s inc_backup_$date.sql ];thengzip  inc_backup_$Date.sqlelse echo  "$Date  backup fail"  >> /var/scripts/ Inc-backup.logfi

This article is from "Hqy's Ops Life" blog, please make sure to keep this source http://ldhqy.blog.51cto.com/2788106/1557127

MySQL Script for incremental backup