如何通过mysqlbinlog 实现对mysql闪回的操作
一、mysqlbinlog使用限制
1. 本文测试用的mysqlbinlog命令，不是mysql自带的命令，是淘宝--彭立勋改进后的命令。补丁包下载地址：http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff
2. 对binlog格式限制。
2.1 binlog的格式需要设置为row；
2.2 binlog 格式设置方法：
首先查看，当前binglog的格式。结果为mixed。
mysql> show variables like 'binlog_format'; +---------------------+------------+ | Variable_name | Value | +---------------------+------------+ | binlog_format | MIXED | +----------------------+-----------+
然后，修改binlog的格式
mysql>set global binlog_format=row; mysql>show variables like 'binlog_format'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | binlog_format | ROW | +----------------------+---------+
注：binglog_format是动态参数，可在线修改。需注意，mysql参数分session(回话级)和global(全局级)，修改时，需注意。
官网文档连接：http://dev.mysql.com/doc/refman/5.5/en/binary-log.html
3. 闪回限制
mingbinlog的flashback功能，仅针对insert、update、delete做闪回
二、测试过程
1. 准备测试环境
1.1 检查binglog格式
mysql> show variables like 'binlog_format'; +---------------------+------------+ | Variable_name | Value | +---------------------+------------+ | binlog_format | MIXED | +----------------------+-----------+
1.2 查看当前使用的binlog日志
mysql>show binary logs; +--------------------+----------------+ | Log_name | File_size | +--------------------+----------------+ | binlog.000012 | 820833 | | binlog.000013 | 7811 | +---------------------+---------------+
最新的日志为binlog.000013
1.3 创建测试表
mysql>create table b (id int); Query OK, 0 rows affected (0.24 sec) mysql>insert into b values(1),(2),(3),(4); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0
测试环境创建完毕
2.测试insert的闪回
2.1 插入一条数据
mysql>insert into b values(5); Query OK, 1 row affected (0.04 sec)
2.2 查看binlog中的position，包括start position 和stop position
mysql>show binlog events in 'binlog.000013'; +---------------------+--------+------------------+--------------+-------------------+------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------------+--------+-------------------+-------------+-------------------+------------------------------------------------------------+ | binlog.000013 | 8575 | Xid | 11 | 8602 | COMMIT /* xid=3634 */ | | binlog.000013 | 8602 | Query | 11 | 8676 | BEGIN | | binlog.000013 | 8676 | Table_map | 11 | 8722 | table_id: 41 (help_table.b) | | binlog.000013 | 8722 | Write_rows | 11 | 8756 | table_id: 41 flags: STMT_END_F | | binlog.000013 | 8756 | Xid | 11 | 8783 | COMMIT /* xid=3635 */ | +---------------------+---------+------------------+-------------+---------------------+------------------------------------------------------------+ start position=8602;stop position=8783
2.3 使用mysqlbinlog命令对insert进行闪回
首先检查原数据
mysql>select * from b; +-----+ | id | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ mysqlbinlog -B -v --start-position=8602 --stop-position=8783 /home/mysql/binlog.000013|mysql test mysql>select * from b; +-----+ | id | +-----+ | 1 | | 2 | | 3 | | 4 | +------+
使用insert 命令插入的第5条数据被删除
3. binglog的解析差异
下面看一下，两个mysqlbinlog命令对binlog解析的不同之处；
3.1 mysql自带的mysqlbinlog命令
BEGIN /*!*/; # at 8676 # at 8722 #140607 17:59:30 server id 11 end_log_pos 8722 Table_map: `help_table`.`b` mapped to number 41 #140607 17:59:30 server id 11 end_log_pos 8756 Write_rows: table id 41 flags: STMT_END_F BINLOG ' guKSUxMLAAAALgAAABIiAAAAACkAAAAAAAEACmhlbHBfdGFibGUAAWIAAQMAAQ== guKSUxcLAAAAIgAAADQiAAAAACkAAAAAAAEAAf/+BQAAAA== '/*!*/; ### INSERT INTO help_table.b ### SET ### @1=5 # at 8756 #140607 17:59:30 server id 11 end_log_pos 8783 Xid = 3635 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
可见，正常的mysqlbinlog命令，解析出来的是正行执行的sql语句
3.2 可以闪回的mysqlbinlog命令
BINLOG ' guKSUxMLAAAALgAAABIiAAAAACkAAAAAAAEACmhlbHBfdGFibGUAAWIAAQMAAQ== guKSUxkLAAAAIgAAADQiAAAAACkAAAAAAAEAAf/+BQAAAA== '/*!*/; ### DELETE FROM help_table.b ### WHERE ### @1=5 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
可见，mysqlbinlog命令生成了delete的sql，用于实现闪回
同理，对update和delete的闪回，也是相同的实现方法
4.总结
通过mysqlbinlog的B参数，生成相反的sql语句，实现mysql的部分闪回，可以尽量减少对生产环境的影响。
闪回的过程，可以总结为：
1. 通过命令
show binlog events in 'binlog'
确定闪回的start position和stop position。如果不确定选择的区间是否正确，可以通过mysqlbinlog命令查看解析的sql是否是自己需要的
2. 闪回之前，最好对线上的环境进行备份，防止出现因闪回不当，造成的其他数据损失
3. 确认好闪回区间后，执行命令：
mysqlbinlog -v -B --start-position=0 --stop=position=1 /home/mysqll/binlog|mysql test
对数据库进行闪回
注：闪回功能只支持闪回insert update 和delete
