脚本作用:实时拉取指定数据库实例上的binlog日志到本地进行存储
应用场景:数据增量备份、MHA binlog server
#!/bin/sh
BACKUP_BIN=/usr/local/mysql/bin/mysqlbinlog
LOCAL_BACKUP_DIR=/data/mysql_binlog_backup/public/binlog
BACKUP_LOG=/data/mysql_binlog_backup/public/backuplog
REMOTE_HOST=10.3.0.221
REMOTE_PORT=3306
REMOTE_USER='binlog_backup'
REMOTE_PASS='binlog_backup'
SERVER_ID='302341'
FIRST_BINLOG=mysql-bin.012237
#time to wait before reconnecting after failure
SLEEP_SECONDS=30
##create local_backup_dir if necessary
mkdir -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} | grep -v backuplog |tail -n 1 |awk '{print $9}'`
fi
${REMOTE_USER} --password=${REMOTE_PASS} ${LAST_FILE}
${BACKUP_BIN} --raw --read-from-remote-server --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} --stop-never --stop-never-slave-server-id=${SERVER_ID} ${FIRST_BINLOG}
echo "公共实例binlog实时同步出错 $(date +%F\ %T),${SLEEP_SECONDS}秒后再次连接并继续备份" | mail -s "公共实例binlog实时同步出错 $(date +%F\ %T)" ops@huize.com,lome@huize.com
#echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog停止,返回代码:$?" | tee -a ${BACKUP_LOG}
#echo "${SLEEP_SECONDS}秒后再次连接并继续备份" | tee -a ${BACKUP_LOG}
sleep ${SLEEP_SECONDS}
done
脚本mysqlbinlog所用选项解释:
--read-from-remote-server:用于备份远程服务器的binlog。如果不指定该选项,则会查找本地的binlog。
--raw:binlog日志会以二进制格式存储在磁盘中,如果不指定该选项,则会以文本形式保存。
--user:用哪个MySQL用户进行binlog的复制,该用户只需要授予REPLICATION SLAVE权限。
--stop-never:mysqlbinlog可以只从远程服务器获取指定的几个binlog,也可将不断生成的binlog保存到本地。指定此选项则代表远程服务器不关闭或者连接未断开就持续进行binlog的复制
--to-last-log:mysqlbinlog工具不仅能够获取指定的binlog,还能持续获取后面更新的binlog。该选项在指定--stop-never后会自动打开
mysql-bin.000001:代表从哪个binlog开始复制