Shulou(Shulou.com)06/01 Report--

Parameter description:

Refer to the official website address:

Https://github.com/danfengcao/binlog2sql

Download the software package directly from the official website, according to the method given on github, although the installation can be successful, but execute the following life to report an error, because the source code of this tool is written by someone else, it is not found which part of the code caused the syntax error at present. Netizens who know can kindly remind me, thank you.

[root@git-server binlog2sql] # python binlog2sql.py-h227.0.0.1-P3306-uadmin-pendant 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] #

So using the binlog2sql code package downloaded on https://github.com last year, the installation is normal, and there is no error.

For a description of the parameters of this tool, please refer to:

Https://github.com/danfengcao/binlog2sql

As a result, there are the following tests:

View the current binlog file logged to the log

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

To facilitate testing, refresh the binlog and generate a new binlog log file to record the sql of the mysql operation

MySQL [zixun3] > flush logs Query OK 0 rows affected (0.07 sec) MySQL [zixun3] > show master status\ gateway * 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 | Private | 1 | 0 | 1 | | 3 | | monitor | 2 | 1000 | 1 | | 4 | second lieutenant | 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 General | 9 | 14000 | 1 | | 11 | Lieutenant General | 10 | 19000 | 1 | 12 | Zhang San | 11 | 24000 | 1 | 15 | Senior General | 12 | 29000 | 1 | + | -+ 12 rows in set (0.00 sec) python binlog2sql.py-h227.0.0.1-P3306-uadmin-pawnadmin`-dzixun3-tzx_scores-- start-file='mysql-bin.000006'-B | moreUPDATE `zixun3`.zx _ scores`SET `titles` = 'Admiral' `integral` = 24000, `id` = 12, `isdefault` = 1, `icon` = 11 WHERE `titles` = 'Zhang San' 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 simply described as follows:

[root@git-server vhost] # mysqlbinlog-- no-defaults-- base64-output=decode-rows-v / dataUniverse mysqlqlbin.000006 SET "50530 SET @ SESSION.PSEUDOSLAVESLAVEMODE1Thread / session" 40019 SET @ session.Max.insertdelayedthreadsThreads0Universe SET @ @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * / # at 4 session 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 144CRC32 0x84ca9830 Query thread_id=77 exec_time=0 error_code=0SET timestamp 1527058523 pick-me-up set @ @ session.pseudoplastics thread readership idols 77 sessions SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlchocolate modewords 1075838976, @ @ session.autoincrementalization offsetbacks @ session.auto_increment_increment=1, @ @ session.autoincrementalization offsetbacks @ @ session. Session utf8 * / / *! * /; SET @ @ session.session. SET @ @ session.lcdating timetables namespace 0 license plansSet @ @ session.collationalization databases database where session. Collationalization database contains default timetables. # at 1941.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=' Admiral'# @ 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 End of log fileROLLBACK / * added by mysqlbinlog * /; / *! 50003 SET completion TYPENETOLYPERATION BUBG: 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

Location-based recovery:

The one between BEGIN and COMMIT is the sql statement that needs to be rolled back. The location of the rollback is: 194Muhami 328

Begin _ blank # at 1941.180523 14:55:23 server id 1 end_log_pos 254mm 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" 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 " Com _ MIT _ blank /

Just log in to MySQL and execute the rollback sql statement above.

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-psamadmin`-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 ('Senior General', 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 ('Admiral', 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 ('Lieutenant General', 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 ('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`, `icon`) 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`, `icon`) 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`, `icon`) VALUES ('Captain', 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 ('Lieutenant', 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 ('second lieutenant', 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 ('monitor', 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 cannot be directly used to restore to the database, and requires simple processing:

[root@git-server binlog2sql] # python binlog2sql.py-h227.0.0.1-P3306-uadmin-psamadmin'- 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 (' Senior General', 29000, 15, 1, 12) INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Admiral', 24000, 12, 1, 11); INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Lieutenant General', 19000, 11, 1, 10); INSERT INTO `zixun3`.zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Major General', 14000, 10, 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', 5000, 7, 1, 6) INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Captain', 4000, 6, 1, 5); INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Lieutenant', 3000, 5, 1, 4); INSERT INTO `zixun3`.zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Lieutenant', 2000, 4, 1, 3) INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('monitor', 1000, 3, 1, 2); INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('private', 0, 2, 1, 1)

Introduction to point-in-time recovery:

Extract the sql log according to the time point: delete the generation time point of the binlog log of the sql statement delete from zx_scores; to the next DML operation delete from zx_vote; record to the time point of the binlog log. Only at this point in time can you find the sql statement that needs to be restored.

[root@git-server binlog2sql] # python binlog2sql.py-h227.0.0.1-P3306-uadmin-pawnadmin'- dzixun3-tzx_scores-- start-file='mysql-bin.000006'-- start-datetime='2018-05-23 15Frey 35dzixun3 59'-- stop-datetime='2018-05-23 16purl 09lv 59'-B | awk-F'[;]'{print $1 " "} 'INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES (' Senior General', 29000, 15, 1, 12); INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Admiral', 24000, 12, 1, 11) VALUES ('Admiral', 24000, 12, 1, 11); INSERT INTO `zixun3.zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Lieutenant General', 19000, 11, 1, 10) INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Major General', 14000, 10, 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', 5000, 7, 1, 6); INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Captain', 4000, 6, 1, 5); INSERT INTO `zixun3`.zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Lieutenant', 3000, 5, 1, 4) INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('second lieutenant', 2000, 4, 1, 3); INSERT INTO `zixun3`.`zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('monitor', 1000, 3, 1, 2); INSERT INTO `zixun3`.zx _ scores` (`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('Private', 0, 2, 1, 1)

Just restore it in mysql directly.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.