背景：

对于MySQL的binlog的查看都是用其自带的工具mysqlbinlog进行操作的，其实还有另一个方法来操作binlog，就是Replication中的SQL线程去操作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: 0 root@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: 0 root@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.000001
root@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.000001
4
mysql-bin.000001
120 --- 修改成： --- ./relay-bin.000001 #复制过来的binlog文件 4 #开始复制的postition mysql-bin.000001
120 ---  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: NULL Master_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: 0 root@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: 0 root@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: 0 root@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: 0 root@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: 0 root@192.168.200.25 : aaa 04:58:23>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A ---切换当前数据库，test下面插入10行 Database changed
root@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: 0 root@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 aaa
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
root@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: 0 root@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.000001
cp mysql-bin.000002 /var/lib/mysql/relay-bin.000002
cp mysql-bin.000003 /var/lib/mysql/relay-bin.000003

chown -R mysql:mysql relay-bin.00000*

修改my.cnf文件：

server-id               = 12
relay_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.000001
4
mysql-bin.000001
120 --- 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: NULL Master_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.5版本中测试出现问题：重启MySQL实例的时候，复制线程一直读取自己设置的relaylog文件，即使修改relaylog文件的内容，在重启的时候也会被回写成本身设置的relaylog 文件，不知道是否是自己测试的问题。后续说明这点。

总结：

通过本文介绍的方法去操作Binlog，更有灵活性，可以随时进行stop、start slave操作；通过测试了解到，在用binlog进行增量备份的场景下，使用该方法更有效直接的进行恢复，比本身的mysqlbinlog 更有效；也可以解决mysqlbinlog的一些问题：
(*) Max_allowed_packet问题
(*) 恼人的Blob/Binary/text字段问题
(*) 特殊字符的转义问题
(*) 没有"断点恢复"：执行出错后，没有足够的报错，也很难从失败的地方继续恢复

原文链接:http://www.cnblogs.com/zhoujinyi/p/3459540.html

MySQL 通过SQL线程对Binlog的读取执行