背景：
对于MySQL的binlog的查看都是用其自带的工具进行操作的，其实还有另一个方法来操作binlog，就是Replication中的去操作binlog，其实binlog和relaylog的格式是一样的。下面开始介绍如何用该方法进行对binlog的操作。
测试1：
---初始化：root@192.168.200.25 : aaa 03:51:38>reset master;Query OK, 0 rows affected (0.12 sec)root@192.168.200.25 : aaa 03:51:55>reset slave;Query OK, 0 rows affected (0.00 sec)root@192.168.200.25 : aaa 03:51:59>create table test_binlog(id int not null auto_increment,name varchar(10),primary key(id)) engine =innodb default charset utf8;Query OK, 0 rows affected (0.17 sec)root@192.168.200.25 : aaa 03:52:11>insert into test_binlog(name) values('a'),('b'),('c'),('d'),('e');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : aaa 03:52:20>insert into test_binlog(name) values('aa'),('bb'),('cc'),('dd'),('ee');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : aaa 03:52:24>insert into test_binlog(name) values('aaa'),('bbb'),('ccc'),('ddd'),('eee');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0---删除数据，为了恢复：root@192.168.200.25 : aaa 03:52:28>flush logs;Query OK, 0 rows affected (0.13 sec)root@192.168.200.25 : aaa 03:53:21>drop table test_binlog;Query OK, 0 rows affected (0.08 sec) 把Binlog复制到Relaylog 目录：
root@zhoujy:/var/lib/mysql# cp /var/log/mysql/mysql-bin.000001 /var/lib/mysql/relay-bin.000001root@zhoujy:/var/lib/mysql# chown -R mysql:mysql relay-bin.000001
修改my.cnf文件：
relay_log = slave_relay skip-slave-start server-id = 2 #测试例子是自己的binlog对自己恢复，根据Replication原理，需要修改其Server_id SQL线程才能进行操作
需要生成master.info，relay-log.info，slave_relay.index等Replication需要的文件。
root@192.168.200.25 : aaa 04:00:29>change master to master_host='192.168.220.25',master_user='rep',master_password='rep', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120,master_port=3306; #随便change 到一个地址，目的是为了生成SQL线程
关闭MySQL并修改Relaylog相关的文件：
vi relay-log.info---./slave_relay.0000014mysql-bin.000001120---修改成：---./relay-bin.000001 #复制过来的binlog文件4 #开始复制的postitionmysql-bin.000001120--- vi slave_relay.index---./slave_relay.000001---修改成：---./relay-bin.000001 #复制过来的binlog文件---
开启MySQL并查看复制的状态：
root@192.168.200.25 : aaa 04:08:04>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.220.25 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: relay-bin.000001 #Relaylog 已经被替换 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: aaa Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 1203 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:
最后启动SQL线程：
root@192.168.200.25 : aaa 04:14:58>select * from test_binlog;ERROR 1146 (42S02): Table 'aaa.test_binlog' doesn't exist
root@192.168.200.25 : aaa 04:15:00>start slave sql_thread;Query OK, 0 rows affected (0.00 sec) #已经恢复： root@192.168.200.25 : aaa 04:15:11>select * from test_binlog;+----+------+| id | name |+----+------+| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e || 6 | aa || 7 | bb || 8 | cc || 9 | dd || 10 | ee || 11 | aaa || 12 | bbb || 13 | ccc || 14 | ddd || 15 | eee |+----+------+15 rows in set (0.00 sec)
例1结束，上面测试说明通过binlog，把其当成relaylog进行处理，成功的用SQL线程进行恢复。
测试2：
上面是对一个binlog进行恢复的，那如何对多个binlog进行恢复呢？马上来测试下：
---log1中root@192.168.200.25 : aaa 04:57:39>create table test_binlog(id int not null auto_increment,name varchar(10),primary key(id)) engine =innodb default charset utf8;Query OK, 0 rows affected (0.17 sec)root@192.168.200.25 : aaa 04:57:47>insert into test_binlog(name) values('a'),('b'),('c'),('d'),('e');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : aaa 04:57:52>insert into test_binlog(name) values('aa'),('bb'),('cc'),('dd'),('ee');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : aaa 04:57:57>flush logs;Query OK, 0 rows affected (0.13 sec)---log2中root@192.168.200.25 : aaa 04:58:04>insert into test_binlog(name) values('aaaa'),('bbbb'),('cccc'),('dddd'),('eeee');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : aaa 04:58:10>insert into test_binlog(name) values('aaaaa'),('bbbbb'),('ccccc'),('ddddd'),('eeeee');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : aaa 04:58:15>flush logs;Query OK, 0 rows affected (0.12 sec)---log3中root@192.168.200.25 : aaa 04:58:19>insert into test_binlog(name) values('Aaaaaa'),('Bbbbbb'),('Cccccc'),('Dddddd'),('Eeeeee');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : aaa 04:58:23>use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A---切换当前数据库，test下面插入10行Database changedroot@192.168.200.25 : test 04:58:29>insert into aaa.test_binlog(name) values('A'),('B'),('C'),('D'),('E');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : test 04:58:33>insert into aaa.test_binlog(name) values('AA'),('BB'),('CC'),('DD'),('EE');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0---切换当前数据库root@192.168.200.25 : test 05:34:09>use aaaReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedroot@192.168.200.25 : aaa 05:34:19>insert into aaa.test_binlog(name) values('AAAAAA'),('BBBBBBB'),('CCCCCCC'),('DDDDDDD'),('EEEEEEE');Query OK, 5 rows affected (0.05 sec)Records: 5 Duplicates: 0 Warnings: 0root@192.168.200.25 : aaa 05:34:40>select * from test_binlog;+----+---------+| id | name |+----+---------+| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e || 6 | aa || 7 | bb || 8 | cc || 9 | dd || 10 | ee || 11 | aaaa || 12 | bbbb || 13 | cccc || 14 | dddd || 15 | eeee || 16 | aaaaa || 17 | bbbbb || 18 | ccccc || 19 | ddddd || 20 | eeeee || 21 | Aaaaaa || 22 | Bbbbbb || 23 | Cccccc || 24 | Dddddd || 25 | Eeeeee || 26 | A || 27 | B || 28 | C || 29 | D || 30 | E || 31 | AA || 32 | BB || 33 | CC || 34 | DD || 35 | EE || 36 | AAAAAA || 37 | BBBBBBB || 38 | CCCCCCC || 39 | DDDDDDD || 40 | EEEEEEE |+----+---------+40 rows in set (0.00 sec) 按照例1中的步骤操作，复制Binlog：
cp mysql-bin.000001 /var/lib/mysql/relay-bin.000001cp mysql-bin.000002 /var/lib/mysql/relay-bin.000002cp mysql-bin.000003 /var/lib/mysql/relay-bin.000003chown -R mysql:mysql relay-bin.00000*
修改my.cnf文件：
server-id = 12relay_log = slave_relay skip-slave-start replicate_do_db = aaa
需要生成master.info，relay-log.info，slave_relay.index等Replication需要的文件。
change master to master_host='192.168.220.25',master_user='rep',master_password='rep',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120,master_port=3306;
关闭MySQL并修改Relaylog相关的文件：
vi relay-log.info 修改成：---./relay-bin.0000014mysql-bin.000001120---vi slave_relay.index 修改成，这里和例1不同---./relay-bin.000001./relay-bin.000002./relay-bin.000003---
开启MySQL并查看复制的状态：
root@192.168.200.25 : aaa 05:38:04>show slave status\G;*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.220.25 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: relay-bin.000001 #Relaylog 已经被替换 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: aaa Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 2889 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.06 sec)
最后启动SQL线程：
root@192.168.200.25 : aaa 05:44:11>select * from test_binlog;ERROR 1146 (42S02): Table 'aaa.test_binlog' doesn't exist root@192.168.200.25 : aaa 05:44:18>start slave sql_thread;Query OK, 0 rows affected (0.00 sec)root@192.168.200.25 : aaa 05:44:29>select * from test_binlog;+----+---------+| id | name |+----+---------+| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e || 6 | aa || 7 | bb || 8 | cc || 9 | dd || 10 | ee || 11 | aaaa || 12 | bbbb || 13 | cccc || 14 | dddd || 15 | eeee || 16 | aaaaa || 17 | bbbbb || 18 | ccccc || 19 | ddddd || 20 | eeeee || 21 | Aaaaaa || 22 | Bbbbbb || 23 | Cccccc || 24 | Dddddd || 25 | Eeeeee || 36 | AAAAAA || 37 | BBBBBBB || 38 | CCCCCCC || 39 | DDDDDDD || 40 | EEEEEEE |+----+---------+30 rows in set (0.01 sec)
这里看到可以正常的恢复了，但是数据不对，少了10行（26~35行）。为什么少了？这里大家应该都很清楚了，因为是在初始化的时候有10行数据是在test数据库下面插入的，而在配置文件中只复制aaa数据库下的操作记录（replicate_do_db = aaa）。这也是通过Relaylog恢复的一个优点。
总结：
上面的测试在5.1到5.1 和 5.1到5.5的操作都没有问题，所以通过本文介绍的方法去操作Binlog，更有灵活性，可以随时进行stop、start slave操作；通过测试了解到，在用binlog进行增量备份的场景下，使用该方法更有效直接的进行恢复，比本身的mysqlbinlog 更有效；也可以解决mysqlbinlog的一些问题：(*) Max_allowed_packet问题 (*) 恼人的Blob/Binary/text字段问题 (*) 特殊字符的转义问题 (*) 没有"断点恢复"：执行出错后，没有足够的报错，也很难从失败的地方继续恢复
更多信息：