MySQL rollback tool Binlog2sql usage Introduction

Parameter description:
Reference website Address:
Https://github.com/danfengcao/binlog2sql

Download the package directly from the official website, according to the method on GitHub, although it can be installed successfully, but the execution of the following error, because the tool source code is written by someone else, currently not found is the code where the syntax error caused. Have to know friends friend can remind of friendship, thank you .

[[email protected] binlog2sql]# python binlog2sql.py -h127.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:                                          

So the use of last year in https://github.com downloaded Binlog2sql code package, installation is normal, and there is no error.
See the parameter descriptions for this tool for more information:
Https://github.com/danfengcao/binlog2sql

the following test is then possible:
View the current record to log of the Binlog file

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)

To facilitate testing, refresh the Binlog, generate a new Binlog log file to record the MySQL operation of 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= ' Zhang San ' 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 |    Pvt |        1 |         0 |  1 | | 3 |    Monitor |     2 |         1000 |  1 | | 4 |    Ensign |     3 |         2000 |  1 | | 5 |    Lieutenant |     4 |         3000 |  1 | | 6 |    Captain |     5 |         4000 |  1 | | 7 |    Major |     6 |         5000 |  1 | | 8 |    Lieutenant Colonel |     7 |         6000 |  1 | | 9 |    Colonel |     8 |         9000 | 1 | | 10 | Major   |    9 |         14000 | 1 | | 11 |   Lieutenant |    10 |         19000 | 1 | | 12 |   Zhang San |    11 |         24000 | 1 | | 15 |   Generals |    12 |         29000 | 1 |+----+--------+------+----------+-----------+12 rows in Set (0.00 sec)

python binlog2sql.py -h127.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

The-b parameter uses a statement that generates a rollback

The Binlog content format is briefly described below:

[[email protected] 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 @[email protected]@ completion_type,completion_type=0*/;D elimiter/*!*/; #at 4#180523 14:51:19 Server ID 1 end_log_pos + CRC32 0xda6d72fe S Tart:binlog v 4, Server v 5.6.36-log created 180523 14:51:19#warning:this Binlog is either in use or be not closed prop erly. #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 t o number 70#at 254#180523 14:55:23 server ID 1 end_log_pos 328 CRC32 0x1f932ec9 update_rows:table ID flags:stmt_end _f## #UPDATE ' zixun3 '. ' Zx_scores ' # # #WHERE ###@1=12###@2= ' Admiral ' ###@3=11###@4=24000###@5=1## #SET ###@1=12###@2= ' Zhang San ' # # # @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/*!*/;D elimiter; #End of log filerollback/* Added by Mysqlbinlog */;/*!50003 SET [email protected]_completion _type*/;/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

Location-based recovery:

Between BEGIN and commit is the SQL statement that needs to be rolled back. The location of the rollback point is: 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/*!*/;

Log in directly to MySQL and execute the rollback SQL statement above.
MySQL [zixun3]> Delete from zx_scores;
Query OK, Rows Affected (0.00 sec)
MySQL [zixun3]> SELECT * from Zx_scores;
Empty Set (0.00 sec)

[[email protected] binlog2sql]# python binlog2sql.py-h127.0.0.1-p3306-uadmin-p ' admin '-dzixun3-tzx_scores--st Art-file= ' mysql-bin.000006 '--start-position=760--stop-position=1083-binsert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' General ', 29000, 15, 1, 12); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Admiral ', 24000, 12, 1, 11); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Will ', 19000, 11, 1, 10); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Major General ', 14000, 10, 1, 9); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Colonel ', 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 (' Lieutenant Colonel ', 6000, 8, 1, 7); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Major ', 5000, 7, 1, 6); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Capt ', 4000, 6, 1, 5); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Lieutenant ', 3000, 5, 1, 4); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Ensign ', 2000, 4, 1, 3); #start 760 End 1083 time 2018-05-23 15:35:59insert into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' ico n ') VALUES (' Squad Leader ', 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 (' Private ', 0, 2, 1, 1); #start 760 End 1083 Time 2018-05-23 15:35:59

This SQL format is not directly used to restore to the database, it needs to be handled simply:

[[email protected] binlog2sql]# python binlog2sql.py-h127.0.0.1-p3306-uadmin-p ' admin '-dzixun3-tzx_scores--st Art-file= ' mysql-bin.000006 '--start-position=760--stop-position=1083-b|awk-f ' [;] ' ' {print $ '; '} ' Insert INTO ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' General ', 29000, 1, +); INSERT I NTO ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Admiral ', 24000, 1, one); INSERT into ' Zix Un3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Will ', 19000, one, 1, ten); INSERT into ' zixun3 '. ' ZX _scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Major General ', 14000, 1, 9); INSERT into ' zixun3 '. ' Zx_scores ' ( ' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Colonel ', 9000, 9, 1, 8); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Lieutenant Colonel ', 6000, 8, 1, 7); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Major ', 50XX, 7, 1, 6); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Capt ', 4000, 6, 1, 5 Insert INTO ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Lieutenant ', 5, 1, 4); INSERT in To ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Ensign ', 4, 1, 3); INSERT into ' zixun3 ' . ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Squad Leader ', 3, 1, 2); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Private ', 0, 2, 1, 1);

Point-in-time recovery introduction:

Extract SQL log instructions by point in time: delete the SQL statement by mistake deleting from zx_scores; the Binlog log generation point to the next DML operation delete from Zx_vote; the point in time that the Binlog log was recorded. This time period of time to find the SQL statement that needs to be recovered

[[email protected] binlog2sql]# python binlog2sql.py-h127.0.0.1-p3306-uadmin-p ' admin '-dzixun3-tzx_scores--st Art-file= ' mysql-bin.000006 '--start-datetime= ' 2018-05-23 15:35:59 '--stop-datetime= ' 2018-05-23 16:09:59 '-b|awk-f ' [ ;]‘ ' {print $ '; '} ' Insert INTO ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' General ', 29000, 1, +); INSERT I NTO ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Admiral ', 24000, 1, one); INSERT into ' Zix Un3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Will ', 19000, one, 1, ten); INSERT into ' zixun3 '. ' ZX _scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Major General ', 14000, 1, 9); INSERT into ' zixun3 '. ' Zx_scores ' ( ' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Colonel ', 9000, 9, 1, 8); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Lieutenant Colonel ', 6000, 8, 1, 7); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isDefault ', ' Icon ') VALUES (' Major ', 7, 1, 6); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' Icon ') VALUES (' Capt ', 4000, 6, 1, 5); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALU ES (' Lieutenant ', 5, 1, 4); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Ensign ', 2 4, 1, 3); INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Squad Leader ', 1000, 3, 1, 2) INSERT into ' zixun3 '. ' Zx_scores ' (' titles ', ' Integral ', ' id ', ' isdefault ', ' icon ') VALUES (' Private ', 0, 2, 1, 1);

Just restore it directly from MySQL.

MySQL rollback tool Binlog2sql usage Introduction