MySQL回滚工具binlog2sql使用介绍

参数介绍：
参考官网地址：
https://github.com/danfengcao/binlog2sql

直接从官网下载软件包，照着github上给的方法，虽然可以安装成功，但是执行如下命了报错，由于此工具源代码是是别人所写，目前暂时没找到是代码的哪个地方导致的语法错误。有知道的网友朋友可以友情提醒下，谢谢。

[root@git-server binlog2sql]# python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006' -B  |awk -F '[;]' '{print $1 ";"}'|grep 'INSERT'  File "binlog2sql.py", line 73    with temp_open(tmp_file, "w") as f_tmp, self.connection as cursor:                                          ^SyntaxError: invalid syntax[root@git-server binlog2sql]# pwd/root/binlog2sql[root@git-server binlog2sql]# 

于是采用去年在https://github.com上下载的binlog2sql代码包，安装正常，而且也没有报错。
查看此工具的参数说明介绍请参考：
https://github.com/danfengcao/binlog2sql

于是才有下面的测试：
查看当前的记录到日志的binlog文件

MySQL [zixun3]> show master status\G*************************** 1. row ***************************             File: mysql-bin.000005         Position: 686     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

为了方便测试，刷新binlog，生成 新的binlog日志文件来记录mysql的操作的sql

MySQL [zixun3]> flush logs;Query OK, 0 rows affected (0.07 sec)MySQL [zixun3]> show master status\G*************************** 1. row ***************************             File: mysql-bin.000006         Position: 120     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)MySQL [zixun3]> MySQL [zixun3]> update zixun3.zx_scores set titles='张三' where id=12;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0MySQL [zixun3]> select * from zx_scores;+----+--------+------+----------+-----------+| id | titles | icon | integral | isdefault |+----+--------+------+----------+-----------+|  2 | 列兵   |    1 |        0 |         1 ||  3 | 班长   |    2 |     1000 |         1 ||  4 | 少尉   |    3 |     2000 |         1 ||  5 | 中尉   |    4 |     3000 |         1 ||  6 | 上尉   |    5 |     4000 |         1 ||  7 | 少校   |    6 |     5000 |         1 ||  8 | 中校   |    7 |     6000 |         1 ||  9 | 上校   |    8 |     9000 |         1 || 10 | 少将   |    9 |    14000 |         1 || 11 | 中将   |   10 |    19000 |         1 || 12 | 张三   |   11 |    24000 |         1 || 15 | 大将   |   12 |    29000 |         1 |+----+--------+------+----------+-----------+12 rows in set (0.00 sec)

python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006'  -B|moreUPDATE `zixun3`.`zx_scores` SET `titles`='上将', `integral`=24000, `id`=12, `isdefault`=1, `icon`=11 WHERE `titles`='张三' AND `integral`=24000 AND `id`=12 AND `isdefault`=1 AND `icon`=11 LIMIT 1; #start 4 end 328 time 2018-05-23 14:55:23

-B参数使用是生成回滚的语句

binlog内容格式简单说明如下：

[root@git-server vhost]# mysqlbinlog  --no-defaults  --base64-output=decode-rows  -v  /data/mysql/data/mysql-bin.000006/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;#at 4#180523 14:51:19 server id 1  end_log_pos 120 CRC32 0xda6d72fe  Start: binlog v 4, server v 5.6.36-log created 180523 14:51:19#Warning: this binlog is either in use or was not closed properly.#at 120#180523 14:55:23 server id 1  end_log_pos 194 CRC32 0x84ca9830  Query   thread_id=77    exec_time=0 error_code=0SET TIMESTAMP=1527058523/*!*/;SET @@session.pseudo_thread_id=77/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;#at 194#180523 14:55:23 server id 1  end_log_pos 254 CRC32 0xf289bf8a  Table_map: `zixun3`.`zx_scores` mapped to number 70#at 254#180523 14:55:23 server id 1  end_log_pos 328 CRC32 0x1f932ec9  Update_rows: table id 70 flags: STMT_END_F###UPDATE `zixun3`.`zx_scores`###WHERE###@1=12###@2='上将'###@3=11###@4=24000###@5=1###SET###@1=12###@2='张三'###@3=11###@4=24000###@5=1#at 328#180523 14:55:23 server id 1  end_log_pos 403 CRC32 0xab4af087  Query   thread_id=77    exec_time=0 error_code=0SET TIMESTAMP=1527058523/*!*/;COMMIT/*!*/;DELIMITER ;#End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

基于位置点的恢复：

BEGIN 和COMMIT之间的才是需要回滚的sql语句。回滚的位置点是：194---328

BEGIN/*!*/;#at 194#180523 14:55:23 server id 1  end_log_pos 254 CRC32 0xf289bf8a  Table_map: `zixun3`.`zx_scores` mapped to number 70#at 254#180523 14:55:23 server id 1  end_log_pos 328 CRC32 0x1f932ec9  Update_rows: table id 70 flags: STMT_END_F### UPDATE `zixun3`.`zx_scores`### WHERE###   @1=12###   @2='上将'###   @3=11###   @4=24000###   @5=1### SET###   @1=12###   @2='张三'###   @3=11###   @4=24000###   @5=1#at 328#180523 14:55:23 server id 1  end_log_pos 403 CRC32 0xab4af087  Query   thread_id=77    exec_time=0 error_code=0SET TIMESTAMP=1527058523/*!*/;COMMIT/*!*/;

直接登陆MySQL执行 上面的回滚sql语句就可以了
MySQL [zixun3]> delete from zx_scores;
Query OK, 12 rows affected (0.00 sec)
MySQL [zixun3]> select * from zx_scores;
Empty set (0.00 sec)

[root@git-server binlog2sql]# python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006'  --start-position=760 --stop-position=1083  -BINSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('大将', 29000, 15, 1, 12); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上将', 24000, 12, 1, 11); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中将', 19000, 11, 1, 10); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少将', 14000, 10, 1, 9); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上校', 9000, 9, 1, 8); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中校', 6000, 8, 1, 7); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少校', 5000, 7, 1, 6); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上尉', 4000, 6, 1, 5); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中尉', 3000, 5, 1, 4); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少尉', 2000, 4, 1, 3); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('班长', 1000, 3, 1, 2); #start 760 end 1083 time 2018-05-23 15:35:59INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('列兵', 0, 2, 1, 1); #start 760 end 1083 time 2018-05-23 15:35:59

此sql格式还不能直接用来恢复到数据库中，需要简单的处理下：

[root@git-server binlog2sql]# python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006'  --start-position=760 --stop-position=1083  -B|awk -F '[;]' '{print $1 ";"}'INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('大将', 29000, 15, 1, 12);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上将', 24000, 12, 1, 11);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中将', 19000, 11, 1, 10);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少将', 14000, 10, 1, 9);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上校', 9000, 9, 1, 8);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中校', 6000, 8, 1, 7);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少校', 5000, 7, 1, 6);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上尉', 4000, 6, 1, 5);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中尉', 3000, 5, 1, 4);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少尉', 2000, 4, 1, 3);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('班长', 1000, 3, 1, 2);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('列兵', 0, 2, 1, 1);

基于时间点的恢复介绍：

按照时间点提取sql日志说明：误删除sql语句delete from zx_scores;的binlog日志的生成时间点到下一次DML操作 delete from zx_vote ;记录到binlog日志的时间点。这个时间段的时间点才可以找到需要恢复的sql语句

[root@git-server binlog2sql]# python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006' --start-datetime='2018-05-23 15:35:59' --stop-datetime='2018-05-23 16:09:59'  -B|awk -F '[;]' '{print $1 ";"}'INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('大将', 29000, 15, 1, 12);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上将', 24000, 12, 1, 11);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中将', 19000, 11, 1, 10);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少将', 14000, 10, 1, 9);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上校', 9000, 9, 1, 8);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中校', 6000, 8, 1, 7);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少校', 5000, 7, 1, 6);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上尉', 4000, 6, 1, 5);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中尉', 3000, 5, 1, 4);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少尉', 2000, 4, 1, 3);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('班长', 1000, 3, 1, 2);INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('列兵', 0, 2, 1, 1);

直接在mysql中恢复就可以了