Binlog2sql usage summary, binlog2sql Summary

Binlog2sql usage summary, binlog2sql Summary

Binlog2sql is an open-source tool for parsing binlog. It has been tried in the test environment and is not bad.

 

It has the following functions

1. Extract SQL

2. Generate a rollback SQL statement

 

For how to use this tool, refer to github operation documentation: https://github.com/danfengcao/binlog2sql

I personally think this document is quite simple and clear.

 

Prerequisites for using this tool

1. binlog_format is ROW and binlog_row_image is full or noblog. The default value is full.

2. MySQL Server must be enabled for the following reasons:

1> it obtains binlog content based on the BINLOG_DUMP protocol.

2> you need to read the information_schema.COLUMNS table on the server side, obtain the metadata of the table structure, and splice it into a visual SQL statement.

The permissions required for this tool are as follows:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 

You do not need to have the read permission on binlog.

 

SQL extraction example

# 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:27INSERT INTO `test`.`test`(`id`, `name`) VALUES (11, 'b'); #start 4 end 376 time 2017-02-17 15:36:31UPDATE `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:40DELETE FROM `test`.`test` WHERE `id`=11 AND `name`='c' LIMIT 1; #start 4 end 568 time 2017-02-17 15:36:48INSERT INTO `test`.`test`(`id`, `name`) VALUES (12, 'd'); #start 599 end 763 time 2017-02-17 15:37:09INSERT INTO `test`.`test`(`id`, `name`) VALUES (13, 'c'); #start 794 end 958 time 2017-02-17 15:37:15UPDATE `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:23DELETE FROM `test`.`test` WHERE `id`=13 AND `name`='d' LIMIT 1; #start 1192 end 1356 time 2017-02-17 15:37:30

 

Example of generating a rollback SQL statement

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

 

Summary

1. After reading the source code, the core code is relatively small, mainly based on pymysqlreplication for secondary development.

Pymysqlreplication implements the MySQL replication protocol to capture different types of EVENT Events.

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

2. Personally, parsing the binlog in text format is not a good solution.

The reasons are as follows:

1> binlog2sql is strongly dependent on the MySQL replication protocol. If the replication protocol changes, the tool will be unavailable.

Although the replication protocol is unlikely to change (usually forward-compatible), the built-in mysqlbinlog must be more familiar with binlog and can be processed based on the parsed results of mysqlbinlog.

Completely shield the bottom layer details such as the replication protocol.

2> using python to parse binlogs in text format is not difficult.

For example, the text of the update statement in binlog

When the table structure is obtained, it can 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 */