Original tool Binlog2sql: Get the SQL you want from MySQL Binlog

Binlog2sql is the MySQL binlog parsing tool that I developed to help you get the SQL you want from Binlog. Depending on the settings, you can get raw SQL, rollback SQL, insert SQL to remove the primary key, and so on.

Use

• Data rollback
• Repair of inconsistent data after master-slave switching
• Generate standard SQL from Binlog, resulting in derivative functions

Installation

$ git clone https://github.com/danfengcao/binlog2sql.gitInstall -R requirements.txt

The following parameters must be set for using MySQL server:

[Mysqld]server-id = 1log_bin =/var/log/mysql/mysql-bin.logmax_binlog_size = 1000mbinlog-format = row

Basic usage

Parse out standard SQL

$ python binlog2sql.py-h127.0.0.1-p3306-uadmin-p'Admin'-D dbname-t table1 table2--start-file='mysql-bin.000002'--start-pos=1240output: INSERT into D (' do ', ' updatetime ', ' uid ') VALUES ( -,'2016-12-07 14:01:14',4INSERT into C ('ID', ' name ') VALUES (0,'b'); UPDATE d SET ' did '= -, ' updatetime ' ='2016-12-07 14:01:14', ' uid ' =4WHERE ' did ' = -and ' updatetime ' ='2016-12-07 14:01:14'and ' uid ' =4LIMIT1;D elete from C WHERE 'ID`=0and ' name ' ='b'LIMIT1;

Parse out rollback SQL

$ python binlog2sql.py--flashback-h127.0.0.1-p3306-uadmin-p'Admin'-D dbname-t table1 table2--start-file='mysql-bin.000002'--start-pos=1240output: INSERT into C ('ID', ' name ') VALUES (0,'b'); UPDATE d SET ' did '= -, ' updatetime ' ='2016-12-07 14:01:14', ' uid ' =4WHERE ' did ' = -and ' updatetime ' ='2016-12-07 14:01:14'and ' uid ' =4LIMIT1;D elete from C WHERE 'ID`=0and ' name ' ='b'LIMIT1;D elete from D WHERE ' did '= -and ' updatetime ' ='2016-12-07 14:01:14'and ' uid ' =4LIMIT1;

Options

MySQL Connection Configuration

-H host; -P Port; -u user; -p password

Parsing mode

--realtime Continuous Sync Binlog. Optional. Does not sync to the most recent binlog position when the command is executed.

--POPPK removes the primary key from the INSERT statement. Optional.

-B,--flashback generates a ROLLBACK statement. Optional. cannot be added with realtime or POPPK at the same time.

Resolution Range Control

--start-file start parsing file. Have to.

--start-pos the starting parse position of the start-file. Optional. The default is the starting position of Start-file;

Parse the file at the end of--end-file. Optional. The default is start-file the same file. If the parsing mode is realtime, this option fails.

--end-pos End-file the end of the parse position. Optional. The default is the last position of End-file, and if the parse mode is realtime, this option is invalidated.

Object filtering

-D,--databases only outputs SQL for Target db. Optional. The default is empty.

-T,--tables only outputs the SQL of the target tables. Optional. The default is empty.

Application case

Repair of inconsistent data after master/slave switchover, detailed description can be found in example/fixoldmasterextradata.md

1. Extract the old master's unsynchronized data and remove the primary key for the INSERT statement (in order to prevent primary key collisions in step 3)

$ python binlog2sql.py--poppk-h10. 1.1. 1 -p3306-uadmin-p'admin' --start-file='  mysql-bin.000040' --start-pos=125466 --end-file='  mysql-bin.000041' > Oldmaster.sql

2. Roll back the old master and turn on sync. synchronization is normal;

$ python binlog2sql.py--flashback-h10. 1.1. 1 -p3306-uadmin-p'admin' --start-file='  mysql-bin.mysql-bin.000040' --start-pos=125466 --end-file='  mysql-bin.000041' | Mysql-h10. 1.1. 1 -p3306-uadmin-p'admin'

3. Re-import the converted SQL in new Master;

$ mysql-h10. 1.1. 2 -p3306-uadmin-p'admin' < Oldmaster.sql

Limit

• MySQL server must be turned on, offline mode cannot parse Binlog
• Binlog format must be row mode
• Flashback mode only supports DML,DDL will not do output
• Flashback mode, one-time processing of the binlog should not be too large, can not exceed the memory size (to be optimized)
• Currently tested environment
  • Python 2.7
  • MySQL 5.6

Advantages (Contrast Mysqlbinlog)

• Pure Python development, installation and use are simple
• Comes with flashback, POPPK resolution mode, no need to install patches
• resolves to standard SQL for easy understanding and debugging
• Code is easy to retrofit, can support more personalized parsing

Contact me

If you have any questions, please contact me [email protected]

Welcome to Binlog2sql (Github.com/danfengcao/binlog2sql)

Resources

[1] Peng Lixun, the design idea of realizing flashback under MySQL

[2] __ Seven knives __, MySQL binlog format parsing

[3] Noplay, Pure Python Implementation of MySQL replication protocol build on top of Pymysql

Original tool Binlog2sql: Get the SQL you want from MySQL Binlog