mysql数据库binlog日志的异地备份

MySQL数据库的二进制日志binlog记录了对数据库的全量DDL和DML操作，对数据库的point to point灾难恢复起着无法替代的关键作用。因此，基于此类考虑，需要对生产环境产生的binlog做好相应的备份措施。

这里主要谈及2种备份方法，一种通过脚本定时调度的方式，强行切换binlog，增量备份二进制binlog。另一种则是通过mysqlbinlog的远程实时备份的方式实现binlog备份。

1、基于flush logs方式实现binlog文件切换

基本原理：通过last_binlog_pos.txt文件记录上一次备份的位置点信息，下一次备份基于该位置点信息进行增量备份。如果是首次备份（last_binlog_pos.txt文件不存在，则全量备份binlog）；通过flush logs的方式强行切换binlog文件（只备份到次新的binlog文件），避免备份binlog过程中，MySQL仍对其进行写入操作；备份每个binlog文件对其生产侧和备份侧的binlog文件md5值进行校验，校验不通过通过配置重传次数$num，超过重传次数仍md5值校验不通过的话，放弃该binlog备份并记录到日志。

脚本如下：

#!/bin/sh######脚本功能：本地定时备份生产目录的binlog到备份目录。#####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"###脚本运行日志存放的目录必须先行存在，否则后续写日志会报日志文件不存在的问题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(){    ###判断存放上一次备份位置点的目录是否存在，不存在就创建    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    ###判断备份目录是否存在，不存在就创建    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(){    ###获取存放binlog日志的目录    binlog_dir=`$mysqlcommand "show variables like 'log_bin_index';" 2>/dev/null|awk '{print "dirname "$2}'|sh`    ###获取binlog日志的index文件名    binlog_index=`$mysqlcommand "show variables like 'log_bin_index';" 2>/dev/null|awk '{print $2}'`        ###获取binlog日志的个数信息    binlog_num=`wc -l $binlog_index|awk '{print $1}'`    ###如果是首次备份，偏移量binlog_start为1;如果非首次备份，偏移量binlog_start为上次偏移量+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,强制切换到新的binlog文件,避免备份当前最新的binlog文件时，mysql仍对其进行写操作###    $mysqlcommand "flush logs" 2>/dev/null       for (( i=$binlog_start;i<=$binlog_num;i++ ))    do        if [ $i == $binlog_num ]        then            ##记录当次备份的最后一个binlog文件,作为本次备份的位置点信息            sed -n "${i}p" $binlog_index > $last_binlog_pos        fi        cd $binlog_dir        logfile=`sed -n "${i}p" $binlog_index|awk '{print "basename "$1}'|sh`                num=5       ###重传次数限制        ###如果拷贝的binlog文件md5值对应不上，尝试重传$num次，md5值依然对不上，放弃备份binlog并记录日志。        for(( j=1;j<=$num;j++ ))        do            cp $logfile $binlog_backup_dir            md5_source=`md5sum $logfile|awk '{print $1}'`            md5_backup=`md5sum $binlog_backup_dir/$logfile|awk '{print $1}'`            if [ "$md5_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 "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" create_timestamps "the binlog backup start now !!!"                                    init_binlog_backup_dir                                                                 binlog_backup                                                                          create_timestamps "the binlog backup end   now !!!"                                    create_timestamps "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"

2、通过mysqlbinlog方式实现远程异地备份binlog。

基本原理：通过mysqlbinlog的--read-from-remote-server、 --stop-never参数实现异地binlog实时备份。通过while死循环的方式，避免由于网络等异常造成的断连。

脚本如下：

#!/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}')   ###获取当前数据库最老的binlogif [ ! $FIRST_BINLOG ]then  echo "无法获取binlog信息，请检查数据库帐号权限和当前数据库是否打开binlog日志"  exitfi#time to wait before reconnecting after failureSLEEP_SECONDS=10##create local_backup_dir if necessarymkdir -p ${LOCAL_BACKUP_DIR}cd ${LOCAL_BACKUP_DIR}## 运行while循环，连接断开后等待指定时间，重新连接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=${REMOTE_PASS} ${LAST_FILE}   echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog停止，返回代码：$?" | tee -a ${BACKUP_LOG}  echo "${SLEEP_SECONDS}秒后再次连接并继续备份" | tee -a ${BACKUP_LOG}    sleep ${SLEEP_SECONDS}done

综上所述，这两种binlog备份的方式各有优缺点：

缺点：

第一种方式，必须通过定时调度的方式实现备份，极端情况下存在丢失binlog的可能（在两个定时调度的窗口时间，MySQL异常并且所有生产binlog不可用，这个窗口时间产生的binlog无法备份到）。

第二种方式，通过mysqlbinlog的功能去实现实时备份，无法确认备份的可用性，即无法通过对比文件md5值来判断文件是否同生产环境保持一致。极端情况下会出现异常（网络异常造成的断连并且备份路径的binlog被误操作），因为断连是通过while死循环去实现重连的，而重连的位置点信息是基于备份路径下的最新binlog文件。

优点：

第一种方式，可以通过验证md5值的方式确保备份同生产的一致性。备份的逻辑简单，便于理解。

第二种方式，可以实现binlog实时备份功能。

所以，基于以上的优缺点分析，选择哪种备份策略，仍需要根据生产环境的实际需要进行抉择。