使用mysqlbinlog实时备份MySQL二进制日志

备份二进制日志是创建良好备份基础架构的重要部分，因为它为您提供了即时恢复的可能性。在MySQL 5.6中，mysqlbinlog增加了一项新功能，支持连接到远程MySQL实例并将二进制日志数据转储到本地磁盘。下面我们就使用这个特性来实时备份MySQL的二进制日志文件。例如可以连接阿里云的rds开始备份。

创建配置文件

# cat /root/scripts/mysql/.livebackup.conf
MBL=/usr/local/mysql/bin/mysqlbinlog
MD=/usr/local/mysql/bin/mysqldump
MYSQLHOST=192.168.1.100
MYSQLPORT=3306
MYSQLUSER=root
MYSQLPASS=123456
BACKUPDIR=/data/backup/mysql
# time to wait before reconnecting after failure
RESPAWN=10

备份脚本

具体如下：

# cat livebinlog.sh 
#!/bin/sh

source $1

BACKUPDIR="$BACKUPDIR/binlogs"

cd $BACKUPDIR
echo "Backup dir: $BACKUPDIR "
while :
do
  LASTFILE=`ls -1 $BACKUPDIR|grep -v orig|tail -n 1`
  TIMESTAMP=`date +%s`
  FILESIZE=$(stat -c%s "$LASTFILE")
  if [ $FILESIZE -gt 0 ]; then
    echo "Backing up last binlog"
    mv $LASTFILE $LASTFILE.orig$TIMESTAMP
  fi
  touch $LASTFILE
  echo "Starting live binlog backup"
  $MBL --raw --read-from-remote-server --stop-never --host $MYSQLHOST --port $MYSQLPORT -u $MYSQLUSER -p$MYSQLPASS $LASTFILE

  echo "mysqlbinlog exited with $? trying to reconnect in $RESPAWN seconds."
  sleep $RESPAWN
done

这个脚本运行之前，在backupdir中创建一个开始备份的二进制的文件，例如mysql-bin.000020 。

运行

nohup /root/scripts/mysql/livebinlog.sh /root/scripts/mysql/.livebackup.conf 2>&1 > /root/scripts/mysql/livebinlog.log &

运行后确认备份路径/data/backup/mysql是否有二进制文件产生。

参考：https://www.percona.com/blog/2012/01/18/backing-up-binary-log-files-with-mysqlbinlog/