news in mysqlbinlog – Back Up Master Binary Log Files_MySQL

news in mysqlbinlog – Back Up Master Binary Log Files

从mysql5.6开始 mysqlbinlog开始支持远程读取master主机的binlog写入本地,极大的加强了binlog的备份策略,由于在mysql cluster复制环境中,binlog的存在极大的决定的数据恢复的完整性，所以binlog的备份显得特别重要。在诸多HA方案中,例如MHA,使用主库的binlog去恢复主备库之间的数据差,在主库物理机器down机无法重启的情况下,binlog的备份可以直接用来recover slave.所以这一特性提升mysql 容灾级别,使得mysql的灾备方案显得不是那么的单调唯一。

使用”–raw”,”–read-from-remote-server” 选项可以直接控制读取方式与读取server,可以采用管理机器统一读取多master binlog。
Facebook 采用类似semi-sync的方式重构了mysqlbinlog用来替代semi-sync方式的slave机器,达到多份复制的目的。

"We extended mysqlbinlog to speak Semisync protocol. The reason of the enhancement is that we wanted to use "semisync mysqlbinlog" as a replacement of local semisync slaves. We usually run slaves on remote datacenters, and we don't always need local slaves to serve read requests / redundancy. On the other hand, as described at above "Requirements for Semisync Deployment" section, in practice at least two local semisync readers are needed to make semisync work. We didn't like to run additional two dedicated slaves per master just for semisync. So we invented semisync mysqlbinlog and use it instead of semisync slaves, as shown in the below figure."

我们采用mysqlbinlog的这种方式备份多台master的binlog.配合MHA的异地binlog复制,以达到最小的数据丢失。

[root@pajk-super-master /usr/local/dbadmin/backup]#nohup python binlog_backup_main.py ps -ef | grep -i daemondbus1056 10 May06 ?00:00:00 dbus-daemon --systemroot 24010 326960 10:58 pts/000:00:00 binlog_backup_daemon allroot 24319 240100 10:59 pts/000:00:00 binlog_backup_daemon '10.0.128.115':'3306' root 24330 240100 10:59 pts/000:00:00 binlog_backup_daemon '10.0.128.116':'3306' root 24341 240100 10:59 pts/000:00:00 binlog_backup_daemon '10.0.128.117':'3306' [root@pajk-super-master /usr/local/dbadmin/backup]#ls -ltr /tmp/backup/binlog_backup/10.0.128.115.3306/total 250908-rw-r--r-- 1 root root 27732 May 13 10:12 mysql-bin.000001-rw-r--r-- 1 root root 1063490 May 13 10:12 mysql-bin.000002-rw-r--r-- 1 root root 126 May 13 10:12 mysql-bin.000003-rw-r--r-- 1 root root 143 May 13 10:12 mysql-bin.000005-rw-r--r-- 1 root root 14000 May 13 10:12 mysql-bin.000004-rw-r--r-- 1 root root 64918 May 13 10:12 mysql-bin.000006-rw-r--r-- 1 root root 1216094 May 13 10:12 mysql-bin.000007-rw-r--r-- 1 root root 143 May 13 10:12 mysql-bin.000008-rw-r--r-- 1 root root 183388823 May 13 10:12 mysql-bin.000009-rw-r--r-- 1 root root20839355 May 13 10:12 mysql-bin.000010-rw-r--r-- 1 root root50039255 May 13 10:12 mysql-bin.000011-rw-r--r-- 1 root root250816 May 13 11:00 mysql-bin.000012

同时MHA 0.56 开始支持从binlog server上恢复日志：

Binlog serverStarting from MHA version 0.56, MHA supports new section [binlogN]. In binlog section, you can define mysqlbinlog streaming servers. When MHA does GTID based failover, MHA checks binlog servers, and if binlog servers are ahead of other slaves, MHA applies differential binlog events to the new master before recovery. When MHA does non-GTID based (traditional) failover, MHA ignores binlog servers.

Below is an example configuration.manager_host$ cat /etc/app1.cnf [server default]# mysql user and passworduser=rootpassword=mysqlpass# working directory on the managermanager_workdir=/var/log/masterha/app1# manager log filemanager_log=/var/log/masterha/app1/app1.log# working directory on MySQL serversremote_workdir=/var/log/masterha/app1[server1]hostname=host1[server2]hostname=host2[server3]hostname=host3[binlog1]hostname=binlog_host1[binlog2]hostname=binlog_host2

REF:semi-synchronous-replication-at-facebook
https://code.google.com/p/mysql-master-ha/wiki/Configuration#Binlog_server