Offsite backup of MySQL database Binlog logs

The binary log binlog of the MySQL database records the full amount of DDL and DML operations on the database, which is an irreplaceable key to the point-to-point disaster recovery of the database. Therefore, based on such considerations, it is necessary to make appropriate backup measures for the Binlog produced by the production environment.

Here, we mainly talk about 2 kinds of backup methods, a kind of scheduling through the script, a forced switch binlog, incremental backup binary binlog. The other is to implement Binlog backup by Mysqlbinlog remote real-time backup.

1, based on the flush logs way to achieve Binlog file switching

Rationale: The location point information for the last backup is recorded through the Last_binlog_pos.txt file, and the next backup is based on the location point information for incremental backups. If it is the first backup (the Last_binlog_pos.txt file does not exist, the full amount of backup binlog), and the flush logs to forcibly switch Binlog files (only back to the new Binlog file), to avoid the backup binlog process, MySQL still writes to it, backs up each binlog file to its production side and backup side of the Binlog file MD5 value to verify that the checksum does not pass through the configuration retransmission number $num, exceeds the retransmission number still MD5 value check does not pass, discards the Binlog backup and logs to the log.

The script is as follows:

#!/bin/sh##### #脚本功能: Locally scheduled backup of the Binlog of the production directory to the backup directory. # # # # #user = "root" password= "Linzj" port= "3306" host= "localhost" name= ' hostname ' last_binlog_dir= "/home/mysql/ Chkpoint "last_binlog_pos=" $last _binlog_dir/last_binlog_pos.txt "  ## #上一次备份的位置点binlog_backup_dir ="/tmp /logbak/$name "                        ## #binlog异地存放目录mysqlcommand = "Mysql -u$user -p$password  -h$host -P$port -N --protocol=tcp -e  "logdir="/home/mysql/log "binlogfile=" $ Logdir/binlog_bak.log "# # #脚本运行日志存放的目录必须先行存在, or the subsequent write log will report that the log file does not exist if [ ! -d  $logdir  ]then     mkdir -p  $logdirfifunction  create_timestamps () {     text=$1    echo  "$ (date +%y%m%d-%h:%m:%s): $text"  >> $binlogfile} Function init_binlog_backup_dir () {    ## #判断存放上一次备份位置点的目录是否存在, does not exist to create     if [ ! -d  $last _binlog_dir ]    then          #echo   "$ (date +%y%m%d-%h:%m:%s): Last binlog save  dir is not existed, now create it !!! " >> $binlogfile         create_timestamps  "Last binlog  save dir is not existed, now create it !!! "         mkdir -p  $last _binlog_dir    fi     ## #判断备份目录是否存在, do not exist create     if [ ! -d  $binlog _ backup_dir ]    then         #echo   "$ ( date +%y%m%d-%h:%m:%s): Binlog backup dir is not existed, now create  it !!! " >> $binlogfile &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp;create_timestamps  "Binlog backup dir is not existed, now create  it !!! "         mkdir -p  $binlog _backup_dir     Fi}function binlog_backup () {    ## #获取存放binlog日志的目录     binlog_dir= ' $mysqlcommand   ' show variables like  ' log_bin_index '; "  2>/dev/null|awk  ' {print  ' dirname  ' $ |sh '     ### Gets the index file name of the Binlog log     binlog_index= ' $mysqlcommand   ' show variables like   ' Log_bin_index '; "  2>/dev/null|awk  ' {print $2} '         ### Get the number of Binlog logs     binlog_num= ' wc -l  $binlog _index|awk  ' {print $1} ' The     ## #如果是首次备份, the offset Binlog_start is 1, and if not the first backup, the offset is binlog_start to the last offset of +1.     if [ ! -f  "$last _binlog_pOS " ]      then        binlog_start = "1"     else        binlog_last_file= ' cat $ last_binlog_pos|awk -f \/  ' {print  $NF} '          Binlog_last= ' grep -n  $binlog _last_file  $binlog _index|awk -f \:  ' {print $1} ' '         binlog_start= ' expr ${binlog_last} + 1  '     fi         #echo   "Binlog_start is   $binlog _start "     #flush  logs, forced to switch to a new Binlog file, to avoid backing up the current Binlog file, MySQL still writes it # ##     $mysqlcommand   "Flush logs"  2>/dev/null        for  (( i= $binlog _start;i<= $binlog _num;i++ ))     do         if [  $i  ==  $binlog _num ]         then            # #记录当次备份的最后一个binlog文件, Location point information for this backup             sed -n  "${i}p"   $binlog _index >  $last _binlog_pos        fi         cd  $binlog _dir         Logfile= ' sed -n  "${i}p"   $binlog _index|awk  ' {print  "basename " $ ' |sh '                  num=5        ## #重传次数限制         ### If the copy of the Binlog file MD5 value does not correspond, try to retransmit $num times, the MD5 value is still not up, discard the backup binlog and log.         for (( j=1;j<= $num;j++ ))   &nbsP;     do            cp   $logfile   $binlog _backup_dir             Md5_source= ' md5sum  $logfile |awk  ' {print $1} '              md5_backup= ' md5sum  $binlog _backup_dir/$logfile |awk  ' {print $1} '              if [  "$md 5_source"  =  "$ Md5_backup " ]            then                 gzip  $binlog _backup_ dir/$logfile                  echo  "$ (date +%y%m%d-%h:%m:%s): $logfile  backup to the  $binlog _backup_dir  sucessfully. "  >>  $binlogfile                  break            fi             if [  "$j"  ==  "$num"  ]             then                 rm -fr  $binlog _backup_dir/$logfile                  echo  "$ (date +%y %m%d-%h:%m:%s): $logfile  can not backup to the  $binlog _backup_dir  sucessfully,please check !!! "   >>  $binlogfile              Fi        done    done}create_timestamps&nBSP; " ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ " create_timestamps " the  binlog backup start now !!! "                                       init_binlog_backup_dir                                                                    binlog_backup                                                                              create_timestamps  "the binlog backup end    now !!! "                                       create_timestamps  "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"

2, through the Mysqlbinlog way to achieve remote offsite backup Binlog.

Basic principle: Binlog Real-time backup is realized by Mysqlbinlog--read-from-remote-server and--stop-never parameters. By the way of the while dead loop, avoid the network and other anomalies caused by the disconnection.

The script is as follows:

#!/bin/shbackup_bin= "Mysqlbinlog" local_backup_dir= "/mysqlbackup/logbak/mysql2/"                                              ## #异地存放binlog的目录BACKUP_LOG = "/home/mysql/log/binlog_log" remote_host= "192.168.124.132" remote_port= "3306" remote_user= "root" remote_pass= "Linzj" mysqlcommand= "mysql -u$remote_user -p$remote_pass -p$ remote_port --protocol=tcp -n -e  "first_binlog=$ ($MYSQLCOMMAND  " show binary  logs " 2>/dev/null|head -1|awk  ' {print $1} ')    ### Gets the oldest binlogif [ !  of the current database $FIRST _binlog ]then  echo  "Cannot get BINLOG information, Please check the database account permissions and whether the current database is open Binlog log "  exitfi#time to wait before reconnecting  after failuresleep_seconds=10# #create  local_backup_dir if necessarymkdir -p ${local_backup_dir}cd ${local_backup_dir}##  run while Loop, Wait for a specified time after the connection disconnects, reconnect while :d o  if [  ' ls -a  "${local_backup_dir}"  |wc  -L '  -eq 0 ];then     LAST_FILE=${FIRST_BINLOG}  else      last_file= ' ls -l ${local_backup_dir} | tail -n 1 | awk  ' {print $9} '   fi  ${backup_bin} --raw --read-from-remote-server  --stop-never --host=${remote_host} --port=${remote_port} --user=${remote_user} -- password=${remote_pass} ${last_file}   echo  "' date +"%Y/%m/%d %H:%M:%S "'  mysqlbinlog Stop, return code: $? "  | tee -a ${BACKUP_LOG}  echo  "${sleep_seconds} seconds to connect again and continue backup"  | tee  -a ${backup_log}    sleep ${sleep_seconds}done

To sum up, the two methods of Binlog backup have advantages and disadvantages:

Disadvantages:

In the first way, backups must be implemented in a timed manner, with the possibility of losing binlog in extreme cases (in the two scheduled window time, MySQL exception and all production Binlog unavailable, the binlog of this window time cannot be backed up to).

The second way, through the Mysqlbinlog function to achieve real-time backup, can not confirm the availability of the backup, that is, you cannot compare the file MD5 value to determine whether the file is consistent with the production environment. In extreme cases, an exception occurs (a disconnection caused by a network exception and the binlog of the backup path is mistakenly manipulated) because the disconnection is connected through a while dead loop, and the re-attached location point information is based on the latest Binlog file under the backup path.

Advantages:

In the first way, you can ensure that the backup is consistent with production by validating the MD5 value. The logic of backup is simple and easy to understand.

The second way, can realize binlog real-time backup function.

Therefore, based on the advantages and disadvantages of the above analysis, the choice of which backup strategy, still need to be based on the actual needs of the production environment to choose.

Offsite backup of MySQL database Binlog logs