MySQL回滚工具binlog2sql使用介绍

[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]#

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)

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: 0 MySQL [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|more UPDATE `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 LIM IT 1; #start 4 end 328 time 2018-05-23 14:55:23

[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=0 SET 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=0 SET TIMESTAMP=1527058523/*!*/; COMMIT /*!*/; DELIMITER ; #End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

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=0 SET TIMESTAMP=1527058523/*!*/; COMMIT /*!*/;

[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 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('大将', 29000, 15, 1, 12); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上将', 24000, 12, 1, 11); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中将', 19000, 11, 1, 10); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少将', 14000, 10, 1, 9); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上校', 9000, 9, 1, 8); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中校', 6000, 8, 1, 7); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少校', 5000, 7, 1, 6); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上尉', 4000, 6, 1, 5); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中尉', 3000, 5, 1, 4); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少尉', 2000, 4, 1, 3); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('班长', 1000, 3, 1, 2); #start 760 end 1083 time 2018-05-23 15:35:59 INSERT 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

[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);

[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);

相关阅读