Shulou(Shulou.com)06/01 Report--

The binary log binlog of MySQL database records all the DDL and DML operations on the database, which plays an irreplaceable key role in the point to point disaster recovery of the database. Therefore, based on such considerations, it is necessary to take corresponding backup measures for the binlog generated by the production environment.

This paper mainly talks about two backup methods, one is to forcibly switch binlog through script scheduling, and the other is to backup binary binlog incrementally. The other is to realize binlog backup by remote real-time backup of mysqlbinlog.

1. Switch binlog files based on flush logs.

Basic principle: the location point information of 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 backup. If it is the first backup (if the last_binlog_pos.txt file does not exist, then full backup binlog); forcibly switch the binlog file through flush logs (only back up to the next new binlog file) to avoid MySQL still writing to it during the backup binlog process Backup each binlog file to check the MD5 value of the binlog file on its production side and backup side. If the check fails to pass the configuration retransmission number of $num, and if the md5 value check fails if the number of retransmissions exceeds, discard the binlog backup and record it to the log.

The script is as follows:

#! / bin/sh# script function: locally regularly back up 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" # last backup location binlog_backup_dir= "/ tmp/logbak/$name" # # binlog remote storage directory mysqlcommand= "mysql-u$user-p$password-h$host-P$port-N-protocol=tcp-e "logdir=" / home/mysql/log "binlogfile=" $logdir/binlog_bak.log "# the directory where the script running log is stored must exist first Otherwise, the subsequent log writing will report the problem 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 () {# determine whether the directory where the last backup location is stored exists 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!" if it doesn't exist. Mkdir-p $last_binlog_dir fi # determine whether the backup directory exists. If it does 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 create_timestamps "binlog backup dir is not existed, now create it!" Mkdir-p $binlog_backup_dir fi} function binlog_backup () {# obtain the directory binlog_dir= `$ mysqlcommand "show variables like 'log_bin_index';" 2 > / dev/null | awk' {print "dirname" $2}'| sh` # obtain the index file name binlog_index= `$ mysqlcommand "show variables like 'log_bin_index' of binlog log "2 > / dev/null | awk'{print $2}'`# obtain the number of binlog logs binlog_num= `wc-l $binlog_index | awk'{print $1}'` # if it is the first backup, the offset binlog_start is 1; if it is not the first backup, the offset is + 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 Force a switch to a new binlog file to avoid backing up the latest binlog file Mysql still writes to it # $mysqlcommand "flush logs" 2 > / dev/null for ((i=$binlog_start I $last_binlog_pos fi cd $binlog_dir logfile= `sed-n "${I} p" $binlog_index | awk'{print "basename" $1}'| limit on the number of retransmissions # if the md5 value of the copied binlog file does not match, try to retransmit $num times, and the MD5 value still does not match. Abandon the backup binlog and log. For ((jack1 J > $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" + "create_timestamps" the binlog backup start now!! " Init_binlog_backup_dir binlog_backup create_timestamps "the binlog backup end now!!" Create_timestamps "+"

2. Remote backup binlog is realized by mysqlbinlog.

Basic principle: realize real-time backup of remote binlog through-read-from-remote-server and-- stop-never parameters of mysqlbinlog. Through the way of while dead loop, the disconnection caused by network and other anomalies can be avoided.

The script is as follows:

#! / bin/shBACKUP_BIN= "mysqlbinlog" LOCAL_BACKUP_DIR= "/ mysqlbackup/logbak/mysql2/" # 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}') # get the oldest binlogif [! $FIRST_BINLOG] then echo in the current database" cannot get binlog information Please check the database account permissions and whether the current database opens the 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 cycle, wait for a specified time after the connection is disconnected, and reconnect while: do 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=$ {LAST_FILE} ${LAST_FILE} echo "`date +"% Yhand% mhand% D H:%M:%S "`mysqlbinlog stop Return code: $? "| tee-a ${BACKUP_LOG} echo" ${SLEEP_SECONDS} seconds later connect again and continue backup "| tee-a ${BACKUP_LOG} sleep ${SLEEP_SECONDS} done |

To sum up, these two ways of binlog backup have their own advantages and disadvantages:

Disadvantages:

In the first way, backup must be achieved through scheduled scheduling, and in extreme cases there is the possibility of losing binlog (in two scheduled window times, the MySQL is abnormal and all production binlog is not available, and the binlog generated by this window time cannot be backed up).

The second way, through the function of mysqlbinlog to achieve real-time backup, can not confirm the availability of backup, that is, it is impossible to compare the MD5 value of the file to determine whether the file is consistent with the production environment. In extreme cases, an exception will occur (disconnection caused by a network exception and misoperation of the binlog of the backup path), because the disconnection is reconnected through a while dead loop, and the location information of the reconnection is based on the latest binlog file under the backup path.

Advantages:

First, you can ensure that the backup is consistent with production by verifying the MD5 value. The logic of backup is simple and easy to understand.

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

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.