Ⅰ、bonlog server介绍
对于binlog的备份，之前文章里说的是有从机，一般不备份，那现在人家就是 要备份嘛，怎么办嘛，
写个脚本每天夜里去把前一天产生的binlog拷贝出来可以不？
行啊，没问题，你可以的，但你这个一下子整会不会有点累嘛
从5.6版本开始，我们有更好的办法了，用mysqlbinlog可以将远端的binlog时时地拉取到本地来
这可不是简单的拷贝哦，是通过Replication API去时时拉取产生的event，相当于做了一个主从。
Ⅱ、给我弄，操作起来
MySQL server
准备工作：创建rpl账号，授予replication权限
看下当前二进制文件是which one？
(root@localhost) [(none)]> show master status;
+------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+---------------------------------------------+
| bin.000022 | 194 | | | d565cde8-0573-11e8-89b2-525400a4dac1:1-1747 |
+------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)
binlog server
[root@VM_42_63_centos backup]# pwd
/data/backup
在备份目录下弄
[root@VM_42_63_centos backup]# mysqlbinlog --read-from-remote-server --raw --host=123.207.244.133 --port=3306 --user=rpl --password=123 --stop-never bin.000022 &
[1] 17292
[root@VM_42_63_centos backup]# mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
看下binlog拉过来了没？no problem!!!
[root@VM_42_63_centos backup]# ll
total 4
-rw-r----- 1 root root 194 Mar 21 16:38 bin.000022
MySQL server 刷一刷
(root@localhost) [(none)]> flush binary logs;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]> flush binary logs;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]> flush binary logs;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]> flush binary logs;
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [(none)]> show master status;
+------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+---------------------------------------------+
| bin.000026 | 194 | | | d565cde8-0573-11e8-89b2-525400a4dac1:1-1747 |
+------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)
binlog server
[root@VM_42_63_centos backup]# ll
total 20
-rw-r----- 1 root root 235 Mar 21 16:38 bin.000022
-rw-r----- 1 root root 235 Mar 21 16:38 bin.000023
-rw-r----- 1 root root 235 Mar 21 16:38 bin.000024
-rw-r----- 1 root root 235 Mar 21 16:38 bin.000025
-rw-r----- 1 root root 194 Mar 21 16:38 bin.000026
okay ! 没毛病，都同步过来了、
Ⅲ、重要参数参数
--read-from-remote-server：从远端拉binlog，不加会在本地找
--raw：本地以二进制方式保存binlog，不指定则以文本保存
--stop-never：一直同步不断
bin.000022：从这个binlog文件开始拉
- 指定为raw，数据不会时时落盘，而是先搞到内存里，然后每4k刷盘一次，一旦连接断开，内存中数据马上都刷到磁盘上
- 不指定raw，则需要用--result-file参数指定数据写入某个文本，不可指定为目录，此时数据会时时刷盘
Ⅳ、问题与解决
如果mysqlbinlog断了怎么办，并不会像主从一样去尝试重连
这里我抄袭一个陈老师脚本(陈老师博客请百度搜索ivictor)
思路：将mysqlbinlog写在一个死循环里，断了就发起重新执行
#!/bin/sh
BACKUP_BIN=/usr/bin/mysqlbinlog
LOCAL_BACKUP_DIR=/backup/binlog/
BACKUP_LOG=/backup/binlog/backuplog
REMOTE_HOST=192.168.244.145
REMOTE_PORT=3306
REMOTE_USER=repl
REMOTE_PASS=repl
FIRST_BINLOG=mysql-bin.000001
#time to wait before reconnecting after failure
SLEEP_SECONDS=10
##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
${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
网友评论