Bilog2sql is an open source tool for public comment to parse binlog. It's good in the test environment.

It has the following functions

1. extract SQL

2. Generate rollback SQL

Refer to the github operating documentation for the use of the tool: https://github.com/danfengcao/binlog2sql

Personally, I feel that the document is quite simple and clear.

Prerequisites for using the tool

1. Bilog_format is ROW and binlog_row_image is full or noblog by default.

2. MySQL Server must be turned on for two reasons:

1 > It is based on BINLOG_DUMP protocol to obtain binlog content

2 > It is necessary to read the information_schema.COLUMNS table on server side, obtain meta-information of table structure, and stitch it into visual sql statements.

Extract the SQL sample

#  python binlog2sql.py -h192.168.244.10 -P3306 -uadmin -p123456 -dtest -ttest --start-file='mysql-bin.000028'

INSERT INTO `test`.`test`(`id`, `name`) VALUES (10, 'a'); #start 4 end 284 time 2017-02-17 15:36:27
INSERT INTO `test`.`test`(`id`, `name`) VALUES (11, 'b'); #start 4 end 376 time 2017-02-17 15:36:31
UPDATE `test`.`test` SET `id`=11, `name`='c' WHERE `id`=11 AND `name`='b' LIMIT 1; #start 4 end 476 time 2017-02-17 15:36:40
DELETE FROM `test`.`test` WHERE `id`=11 AND `name`='c' LIMIT 1; #start 4 end 568 time 2017-02-17 15:36:48
INSERT INTO `test`.`test`(`id`, `name`) VALUES (12, 'd'); #start 599 end 763 time 2017-02-17 15:37:09
INSERT INTO `test`.`test`(`id`, `name`) VALUES (13, 'c'); #start 794 end 958 time 2017-02-17 15:37:15
UPDATE `test`.`test` SET `id`=13, `name`='d' WHERE `id`=13 AND `name`='c' LIMIT 1; #start 989 end 1161 time 2017-02-17 15:37:23
DELETE FROM `test`.`test` WHERE `id`=13 AND `name`='d' LIMIT 1; #start 1192 end 1356 time 2017-02-17 15:37:30

Generate a rollback SQL example

# python binlog2sql.py --flashback -h192.168.244.10 -P3306 -uadmin -p123456 -dtest -ttest --start-file='mysql-bin.000028'

INSERT INTO `test`.`test`(`id`, `name`) VALUES (13, 'd'); #start 1192 end 1356 time 2017-02-17 15:37:30
UPDATE `test`.`test` SET `id`=13, `name`='c' WHERE `id`=13 AND `name`='d' LIMIT 1; #start 989 end 1161 time 2017-02-17 15:37:23
DELETE FROM `test`.`test` WHERE `id`=13 AND `name`='c' LIMIT 1; #start 794 end 958 time 2017-02-17 15:37:15
DELETE FROM `test`.`test` WHERE `id`=12 AND `name`='d' LIMIT 1; #start 599 end 763 time 2017-02-17 15:37:09
INSERT INTO `test`.`test`(`id`, `name`) VALUES (11, 'c'); #start 4 end 568 time 2017-02-17 15:36:48
UPDATE `test`.`test` SET `id`=11, `name`='b' WHERE `id`=11 AND `name`='c' LIMIT 1; #start 4 end 476 time 2017-02-17 15:36:40
DELETE FROM `test`.`test` WHERE `id`=11 AND `name`='b' LIMIT 1; #start 4 end 376 time 2017-02-17 15:36:31
DELETE FROM `test`.`test` WHERE `id`=10 AND `name`='a' LIMIT 1; #start 4 end 284 time 2017-02-17 15:36:27

summary

1. Looking at the following source code, its core code is relatively small, mainly on the basis of pymysql replication for secondary development.

Pymysql replication implements MySQL replication protocol to capture different types of EVENT events.

Specific reference: https://github.com/noplay/python-mysql-replication

2. Personally, it is not a good way to directly parse the binlog in text format.

The reasons are as follows:

Bilog2sql strongly relies on MySQL replication protocol, and if the replication protocol changes, the tool will not be available.

Although there is little possibility of a change in the replication protocol (generally keeping forward compatibility), the mysqlbinlog with it must be more knowledgeable about binlog and can be processed based on the parsed results of the mysqlbinlog.

Low-level details such as full-shielded replication protocol.

2 > Using python to parse binlog in text format is not a problem in itself.

For example, the corresponding text of the update statement in the binlog

In the case of table structure, it can basically be parsed offline.

### UPDATE `test`.`test`
### WHERE
###   @1=13 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### SET
###   @1=13 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */