mysqlbinlog备份_mysql数据库binlog日志的异地备份

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

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

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

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

脚本如下&＃xff1a;#!/bin/sh

######脚本功能&＃xff1a;本地定时备份生产目录的binlog到备份目录。#####

user&＃61;"root"

password&＃61;"linzj"

port&＃61;"3306"

host&＃61;"localhost"

name&＃61;&＃96;hostname&＃96;

last_binlog_dir&＃61;"/home/mysql/chkpoint"

last_binlog_pos&＃61;"$last_binlog_dir/last_binlog_pos.txt"  ###上一次备份的位置点

binlog_backup_dir&＃61;"/tmp/logbak/$name"                       ###binlog异地存放目录

mysqlcommand&＃61;"mysql -u$user -p$password -h$host -P$port -N --protocol&＃61;tcp -e "

logdir&＃61;"/home/mysql/log"

binlogfile&＃61;"$logdir/binlog_bak.log"

###脚本运行日志存放的目录必须先行存在&＃xff0c;否则后续写日志会报日志文件不存在的问题

if [ ! -d $logdir ]

then

mkdir -p $logdir

fi

function create_timestamps()

{

text&＃61;$1

echo "$(date &＃43;%Y%m%d-%H:%M:%S):$text" >>$binlogfile

}

function init_binlog_backup_dir()

{

###判断存放上一次备份位置点的目录是否存在&＃xff0c;不存在就创建

if [ ! -d $last_binlog_dir ]

then

#echo "$(date &＃43;%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

###判断备份目录是否存在&＃xff0c;不存在就创建

if [ ! -d $binlog_backup_dir ]

then

#echo "$(date &＃43;%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&＃61;&＃96;$mysqlcommand "show variables like &＃39;log_bin_index&＃39;;" 2>/dev/null|awk &＃39;{print "dirname "$2}&＃39;|sh&＃96;

###获取binlog日志的index文件名

binlog_index&＃61;&＃96;$mysqlcommand "show variables like &＃39;log_bin_index&＃39;;" 2>/dev/null|awk &＃39;{print $2}&＃39;&＃96;

###获取binlog日志的个数信息

binlog_num&＃61;&＃96;wc -l $binlog_index|awk &＃39;{print $1}&＃39;&＃96;

###如果是首次备份&＃xff0c;偏移量binlog_start为1;如果非首次备份&＃xff0c;偏移量binlog_start为上次偏移量&＃43;1。

if [ ! -f "$last_binlog_pos" ]

then

binlog_start&＃61;"1"

else

binlog_last_file&＃61;&＃96;cat $last_binlog_pos|awk -F \/ &＃39;{print $NF}&＃39;&＃96;

binlog_last&＃61;&＃96;grep -n $binlog_last_file $binlog_index|awk -F \: &＃39;{print $1}&＃39;&＃96;

binlog_start&＃61;&＃96;expr ${binlog_last} &＃43; 1 &＃96;

fi

#echo "binlog_start is $binlog_start"

#flush logs,强制切换到新的binlog文件,避免备份当前最新的binlog文件时&＃xff0c;mysql仍对其进行写操作###

$mysqlcommand "flush logs" 2>/dev/null

for (( i&＃61;$binlog_start;i<&＃61;$binlog_num;i&＃43;&＃43; ))

do

if [ $i &＃61;&＃61; $binlog_num ]

then

##记录当次备份的最后一个binlog文件,作为本次备份的位置点信息

sed -n "${i}p" $binlog_index > $last_binlog_pos

fi

cd $binlog_dir

logfile&＃61;&＃96;sed -n "${i}p" $binlog_index|awk &＃39;{print "basename "$1}&＃39;|sh&＃96;

num&＃61;5       ###重传次数限制

###如果拷贝的binlog文件md5值对应不上&＃xff0c;尝试重传$num次&＃xff0c;md5值依然对不上&＃xff0c;放弃备份binlog并记录日志。

for(( j&＃61;1;j<&＃61;$num;j&＃43;&＃43; ))

do

cp $logfile $binlog_backup_dir

md5_source&＃61;&＃96;md5sum $logfile|awk &＃39;{print $1}&＃39;&＃96;

md5_backup&＃61;&＃96;md5sum $binlog_backup_dir/$logfile|awk &＃39;{print $1}&＃39;&＃96;

if [ "$md5_source" &＃61; "$md5_backup" ]

then

gzip $binlog_backup_dir/$logfile

echo "$(date &＃43;%Y%m%d-%H:%M:%S):$logfile backup to the $binlog_backup_dir sucessfully." >> $binlogfile

break

fi

if [ "$j" &＃61;&＃61; "$num" ]

then

rm -fr $binlog_backup_dir/$logfile

echo "$(date &＃43;%Y%m%d-%H:%M:%S):$logfile can not backup to the $binlog_backup_dir sucessfully,please check !!!"  >> $binlogfile

fi

done

done

}

create_timestamps "&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;"

create_timestamps "the binlog backup start now !!!"

init_binlog_backup_dir

binlog_backup

create_timestamps "the binlog backup end   now !!!"

create_timestamps "&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;&＃43;"

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

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

脚本如下&＃xff1a;#!/bin/sh

BACKUP_BIN&＃61;"mysqlbinlog"

LOCAL_BACKUP_DIR&＃61;"/mysqlbackup/logbak/mysql2/"                                          ###异地存放binlog的目录

BACKUP_LOG&＃61;"/home/mysql/log/binlog_log"

REMOTE_HOST&＃61;"192.168.124.132"

REMOTE_PORT&＃61;"3306"

REMOTE_USER&＃61;"root"

REMOTE_PASS&＃61;"linzj"

MYSQLCOMMAND&＃61;"mysql -u$REMOTE_USER -p$REMOTE_PASS -P$REMOTE_PORT --protocol&＃61;tcp -N -e "

FIRST_BINLOG&＃61;$($MYSQLCOMMAND "show binary logs" 2>/dev/null|head -1|awk &＃39;{print $1}&＃39;)   ###获取当前数据库最老的binlog

if [ ! $FIRST_BINLOG ]

then

echo "无法获取binlog信息&＃xff0c;请检查数据库帐号权限和当前数据库是否打开binlog日志"

exit

fi

#time to wait before reconnecting after failure

SLEEP_SECONDS&＃61;10

##create local_backup_dir if necessary

mkdir -p ${LOCAL_BACKUP_DIR}

cd ${LOCAL_BACKUP_DIR}

## 运行while循环&＃xff0c;连接断开后等待指定时间&＃xff0c;重新连接

while :

do

if [ &＃96;ls -A "${LOCAL_BACKUP_DIR}" |wc -l&＃96; -eq 0 ];then

LAST_FILE&＃61;${FIRST_BINLOG}

else

LAST_FILE&＃61;&＃96;ls -l ${LOCAL_BACKUP_DIR} | tail -n 1 |awk &＃39;{print $9}&＃39;&＃96;

fi

${BACKUP_BIN} --raw --read-from-remote-server --stop-never --host&＃61;${REMOTE_HOST} --port&＃61;${REMOTE_PORT} --user&＃61;${REMOTE_USER} --password&＃61;${REMOTE_PASS} ${LAST_FILE}

echo "&＃96;date &＃43;"%Y/%m/%d %H:%M:%S"&＃96; mysqlbinlog停止&＃xff0c;返回代码&＃xff1a;$?" | tee -a ${BACKUP_LOG}

echo "${SLEEP_SECONDS}秒后再次连接并继续备份" | tee -a ${BACKUP_LOG}

sleep ${SLEEP_SECONDS}

done

综上所述&＃xff0c;这两种binlog备份的方式各有优缺点&＃xff1a;

缺点&＃xff1a;

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

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

优点&＃xff1a;

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

第二种方式&＃xff0c;可以实现binlog实时备份功能。

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