Binlog2sql Flash Back recovery data

Use:

1 , fast data rollback 2. Generate standard SQL from Binlog

Usage Restrictions:

1, must be setbinlog_format= ROW2, binlog_row_image= full, this parameter defaults tofull3, Restore the minimum set of permissions that the user has:select, super/replication client, replication slave
*. * to permission description  Select: The server-side INFORMATION_SCHEMA needs to be read. Columns table, get meta-information of table structure, mosaic into visual SQL statement Super/replication client: Two permissions are allowed, need to execute 'SHOW MASTER STATUS '  ', get the server-side binlog list replication slave: Get Binlog Content permissions through the Binlog_dump protocol

1. Download and install

git clone https://github.com/danfengcao/binlog2sql.gitwget https://  bootstrap.pypa.io/get-pip.py--no-check-certificatepython get-install –r requirements.txt

2. Instructions for use

MySQL Connection Configuration-H Host: Database Address-P Port: Database Port-u User: Database connection user-p Password: Database connection password parsing mode --stop-never continues to parse Binlog. Optional. Default False to synchronize to the latest Binlog position when executing the command. -K,--no-primary-key removes the primary key from the INSERT statement. Optional. False by default. -B,--flashback generates rollback SQL, resolves large files, and is not subject to memory limitations. Optional. False by default. With Stop-never or no-primary-key cannot be added at the same time. In--back-interval-b mode, the SQL is rolled back every 1000 lines, add a sleep how many seconds, if do not want to add sleep, please set to 0. Optional. Default 1.0. resolution range Control --start-filestart parsing the file, just the file name, without the full path. Have to. --start-position/--start-pos the starting parse location. Optional. Default is start-the starting position of the file. --stop-file/--end-fileTerminates parsing of the file. Optional. The default is start-file the same file. If the parsing mode is stop-never, this option is invalid. --stop-position/--end-pos terminates the parsing location. Optional. The default is the last position of the stop-file; if the parse mode is stop-never, this option is invalid. --start-datetime start parsing time, format'%y-%m-%d%h:%m:%s'. Optional. The default is not filtered. --stop-datetime terminate parsing time, format'%y-%m-%d%h:%m:%s'. Optional. The default is not filtered. Object Filtering -D,--databases only resolves the SQL for the target DB, and multiple libraries are separated by spaces, such as-D db1 DB2. Optional. The default is empty. -T,--tables only parses the SQL of the target table, and multiple tables are separated by spaces, such as-T tbl1 tbl2. Optional. The default is empty. --only-DML only resolves DML, ignoring DDL. Optional. Default true. --sql-type only resolves the specified type and supports INSERT, UPDATE, DELETE. Multiple types are separated by spaces, such as--sql-type INSERT DELETE. Optional. The default is to delete the changes are resolved. With this parameter but without any type, none of the three is parsed.

3. Advantages and Disadvantages
3.1, limit (contrast mysqlbinlog)

MySQL server must be turned on, offline mode cannot parse parameter binlog_row_image must be full, not support minimal parsing speed is not as Mysqlbinlog

3.2. Advantages (comparison Mysqlbinlog)

Pure Python development, installation and use are very simple to bring flashback, no-primary-key parsing mode, no need to install patches Flashback mode, more suitable for flash back to the actual combat resolution to standard SQL, easy to understand, Easy-to-change filter code to support more personalized parsing

4. Use reference

Python binlog2sql.py-hlocalhost-p3306-uroot-p-dowms-hh--start-file='master-bin.000011   '--start-datetime='2017-12-25 11:47:51' --stop-datetime='  2017-12-25 11:47:58' -B >/data/t_rollback.sql

Binlog2sql Flash Back recovery data