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到备份目录。#####userrootpasswordxxxxxport3306hostlocalhostnamehostnamelast_binlog_dir/home/mysql/chkpointlast_binlog_pos$last_binlog_dir/last_binlog_pos.txt ###上一次备份的位置点binlog_backup_dir/tmp/logbak/$name ###binlog异地存放目录mysqlcommandmysql -u$user -p$password -h$host -P$port -N --protocoltcp -e logdir/home/mysql/logbinlogfile$logdir/binlog_bak.log###脚本运行日志存放的目录必须先行存在否则后续写日志会报日志文件不存在的问题if [ ! -d $logdir ]thenmkdir -p $logdirfifunction create_timestamps(){text$1echo $(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 !!!$binlogfilecreate_timestamps last binlog save dir is not existed, now create it !!!mkdir -p $last_binlog_dirfi###判断备份目录是否存在不存在就创建if [ ! -d $binlog_backup_dir ]then#echo $(date %Y%m%d-%H:%M:%S):binlog backup dir is not existed, now create it !!!$binlogfilecreate_timestamps binlog backup dir is not existed, now create it !!!mkdir -p $binlog_backup_dirfi}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_numwc -l $binlog_index|awk {print $1}###如果是首次备份偏移量binlog_start为1;如果非首次备份偏移量binlog_start为上次偏移量1。if [ ! -f $last_binlog_pos ]thenbinlog_start1elsebinlog_last_filecat $last_binlog_pos|awk -F \/ {print $NF}binlog_lastgrep -n $binlog_last_file $binlog_index|awk -F \: {print $1}binlog_startexpr ${binlog_last} 1 fi#echo binlog_start is $binlog_start#flush logs,强制切换到新的binlog文件,避免备份当前最新的binlog文件时mysql仍对其进行写操作###$mysqlcommand flush logs 2/dev/nullfor (( i$binlog_start;i$binlog_num;i ))doif [ $i $binlog_num ]then##记录当次备份的最后一个binlog文件,作为本次备份的位置点信息sed -n ${i}p $binlog_index $last_binlog_posficd $binlog_dirlogfilesed -n ${i}p $binlog_index|awk {print basename $1}|shnum5 ###重传次数限制###如果拷贝的binlog文件md5值对应不上尝试重传$num次md5值依然对不上放弃备份binlog并记录日志。for(( j1;j$num;j ))docp $logfile $binlog_backup_dirmd5_sourcemd5sum $logfile|awk {print $1}md5_backupmd5sum $binlog_backup_dir/$logfile|awk {print $1}if [ $md5_source $md5_backup ]thengzip $binlog_backup_dir/$logfileecho $(date %Y%m%d-%H:%M:%S):$logfile backup to the $binlog_backup_dir sucessfully. $binlogfilebreakfiif [ $j $num ]thenrm -fr $binlog_backup_dir/$logfileecho $(date %Y%m%d-%H:%M:%S):$logfile can not backup to the $binlog_backup_dir sucessfully,please check !!! $binlogfilefidonedone}create_timestamps create_timestamps the binlog backup start now !!!init_binlog_backup_dirbinlog_backupcreate_timestamps the binlog backup end now !!!create_timestamps 一键获取完整项目代码2、通过mysqlbinlog方式实现远程异地备份binlog。基本原理通过mysqlbinlog的--read-from-remote-server、 --stop-never参数实现异地binlog实时备份。通过while死循环的方式避免由于网络等异常造成的断连。脚本如下#!/bin/shBACKUP_BINmysqlbinlogLOCAL_BACKUP_DIR/mysqlbackup/logbak/mysql2/ ###异地存放binlog的目录BACKUP_LOG/home/mysql/log/binlog_logREMOTE_HOSTxx.168.xx.132REMOTE_PORT3306REMOTE_USERrootREMOTE_PASSxxxxxxMYSQLCOMMANDmysql -u$REMOTE_USER -p$REMOTE_PASS -P$REMOTE_PORT --protocoltcp -N -e FIRST_BINLOG$($MYSQLCOMMAND show binary logs 2/dev/null|head -1|awk {print $1}) ###获取当前数据库最老的binlogif [ ! $FIRST_BINLOG ]thenecho 无法获取binlog信息请检查数据库帐号权限和当前数据库是否打开binlog日志exitfi#time to wait before reconnecting after failureSLEEP_SECONDS10##create local_backup_dir if necessarymkdir -p ${LOCAL_BACKUP_DIR}cd ${LOCAL_BACKUP_DIR}## 运行while循环连接断开后等待指定时间重新连接while :doif [ ls -A ${LOCAL_BACKUP_DIR} |wc -l -eq 0 ];thenLAST_FILE${FIRST_BINLOG}elseLAST_FILEls -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实时备份功能。所以基于以上的优缺点分析选择哪种备份策略仍需要根据生产环境的实际需要进行抉择。