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 daemon
dbus      1056     1  0 May06 ?        00:00:00 dbus-daemon --system
root     24010 32696  0 10:58 pts/0    00:00:00 binlog_backup_daemon all    
root     24319 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.115':'3306' 
root     24330 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.116':'3306' 
root     24341 24010  0 10:59 pts/0    00: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 root  20839355 May 13 10:12 mysql-bin.000010
-rw-r--r-- 1 root root  50039255 May 13 10:12 mysql-bin.000011
-rw-r--r-- 1 root root    250816 May 13 11:00 mysql-bin.000012

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

Binlog server
Starting 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 password
  user=root
  password=mysqlpass
  # working directory on the manager
  manager_workdir=/var/log/masterha/app1
  # manager log file
  manager_log=/var/log/masterha/app1/app1.log
  # working directory on MySQL servers
  remote_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