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  , $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