Mysqlbinlog's flashback tool binlog2sql
Settings for binlog in my.cnf binlog_format = row binlog format must be row mode binlog_row_image = full must be full default is full preparation # yum intall git # yum install python-setuptools pip needs this # wget "https://pypi.python.org/packages/source/p/pip/pip-1.5.4.tar.gz#md5=834b2904f92d46aaa333267fb1c922bb"-- no-check-certificate # tar-xzvf pip-1.5.4. Tar.gz # cd pip-1.5.4 # python setup.py install install # git clone https://github.com/danfengcao/binlog2sql.git & & cd binlog2sql # pip install-r requirements.txt user Authorization (for binglog2sql use) GRANT SELECT REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO xiaomage@'localhost' identified by 'xiaomage' Select: need to read the server information _ schema.COLUMNS table, get the meta-information of the table structure, and assemble it into a visual sql statement super/replication client: both permissions can be obtained. You need to execute 'SHOW MASTER STATUS', to obtain the binlog list on the server side. Replication slave: permission test for obtaining binlog content through the BINLOG_DUMP protocol: mysql > select * from test +-+ | id | name | time | +-+ | 1 | lalal | 16:32:21 | | 2 | ababa | 16:32:34 | 3 | jgjgj | 16:32:43 | 4 | opopo | 16:32:52 | 5 | jljlj | 16: 33:06 | +-+ 5 rows in set (0.00 sec) mysql > delete from test -- emptying table-- Query OK, 5 rows affected (0.01 sec) mysql > show master status +-- + | File | Position | | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + -+ | mysql-bin.000013 | 2016 | f90ca153-b46d-11e6-ab68-0800279ecefa:1-35 | +- -generally speaking, you know an approximate time. Start from the approximate time to find [root@master binlog2sql] # python / opt/binlog2sql/binlog2sql/binlog2sql.py-uxiaomage-pxiaomage-dtest-ttest-start-file=mysql-bin.000013-- start-datetime='2017-05-05 16 root@master binlog2sql 30 USE test 00'-stop-datetime='2017-05-05 16 40 USE test Create table test (id int, name varchar (20), time time); INSERT INTO `test`.`test` (`time`, `id`, `name`) VALUES ('16 end 32 time 21, 1, 'lalal'); # start 478 end 659 Test` 2017-05-05 16:32:21 INSERT INTO `test`test` (`time`, `id`, `name`) VALUES (' 16 VALUES 32 time time, 2, 'ababa') # start 690 end 871 time 2017-05-05 16:32:34 INSERT INTO `test`.test` (`time`, `id`, `name`) VALUES (3, 'jgjgj'); # start end 1083 time 2017-05-05 16:32:43 INSERT INTO `test`test` (`time`, `id`, `name`) VALUES # start 1114 end 1295 time 2017-05-05 16:32:52 INSERT INTO `test`.`test` (`time`, `id`, `name`) VALUES ('16pur33 WHERE 06mm, 5,' jljlj'); # start 1326 end 1507 time 2017-05-05 16:33:06 USE test; alter table test modify time datetime; USE test; alter table test modify time time; DELETE FROM `test`.`test`test`time` = '1632 AND 21' AND `id` = 1 AND `name` =' lalal' LIMIT 1 # start 1756 end 1985 time 2017-05-05 16:37:27-this is the delete data-DELETE FROM `test`.`test`WHERE `time` = '16DELETE FROM 32delete 34' AND `id` = 2 AND `name` =' ababa' LIMIT 1; # start 1756 end 1985 time 2017-05-05 16:37:27 DELETE FROM `test`.`test`WHERE `time` = '16delete 3243' AND `id` = 3 AND `name` =' jgjgj' LIMIT 1 # start 1756 end 1985 time 2017-05-05 16:37:27 DELETE FROM `test`.`test` WHERE `time` = '16VV 32V 52' AND `id` = 4 AND `name` =' opopo' LIMIT 1; # start 1756 end 1985 time 2017-05-05 16:37:27 DELETE FROM `test`.`test`WHERE `time` = '16336' AND `id` = 5 AND `name` =' jljlj' LIMIT 1 # start 1756 end 1985 time 2017-05-05 16:37:27 generate a rollback statement-B, the specific location seen above is start 1756 end 1985 (must be filtered here, must be careful Match with grep) [root@master binlog2sql] # python / opt/binlog2sql/binlog2sql/binlog2sql.py-uxiaomage-pxiaomage-dtest-ttest-- start-file=mysql-bin.000013-- start-position=1756-- stop-position=1985-B INSERT INTO `test`.`test` (`time`, `id`, `name`) VALUES ('16JV 33 stop-position=1985 06mm, 5,' jljlj') # start 1756 end 1985 time 2017-05-05 16:37:27 INSERT INTO `test`.test` (`time`, `id`, `name`) VALUES ('16INSERT INTO 32INSERT INTO 52mm, 4,' opopo'); # start 1756 end 1985 time 2017-05-05 16:37:27 INSERT INTO `test`test` (`time`, `id`, `name`) VALUES # start 1756 end 1985 time 2017-05-05 16:37:27 INSERT INTO `test`.test` (`time`, `id`, `name`) VALUES ('16INSERT INTO 32time 34th, 2,' ababa'); # start 1756 end 1985 time 2017-05-05 16:37:27 INSERT INTO `test`.test` (`time`, `id`, `name`) VALUES ('16INSERT INTO 32INSERT INTO 21th, 1,' lalal') # start 1756 end 1985 time 2017-05-05 16:37:27 redirect to / opt/rollback.sql file to restore: mysql > select count (*) from test +-+ | count (*) | +-+ | 0 | +-+ 1 row in set (0.05 sec) mysql > source / opt/rollback.sql Query OK, 1 row affected (0.03 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.07 sec) Query OK, 1 row affected (0.04 sec) Query OK, 1 row affected (0.00 sec) mysql > select * from test +-+ | id | name | time | +-+ | 5 | jljlj | 16:33:06 | 4 | opopo | 16:32:52 | 3 | jgjgj | 16:32:43 | 2 | ababa | 16:32:34 | | 1 | Lalal | 16:32:21 | +-+ 5 rows in set (0.00 sec) ok recovery meeting is coming
Parsing mode-stop-never continuously synchronizes binlog. Optional. Add or synchronize to the latest binlog location when the command is executed. -K,-- no-primary-key removes the primary key from the INSERT statement. Optional. -B,-- flashback generates a rollback statement that parses large files without memory restrictions, adding a SLEEP SELECT (1) for every thousand lines printed. Optional. Cannot be added at the same time as stop-never or no-primary-key.
Parsing scope control-start-file starting parsing file. Must. -- the starting resolution position of the start-position/--start-pos start-file. Optional. The default is the starting position of start-file. -- parse the file at the end of stop-file/--end-file. Optional. The default is start-file the same file. If the parsing mode is stop-never, this option has no effect. -- the parsing position at the end of the stop-position/--end-pos stop-file. Optional. The default is the last location of stop-file; if the parsing mode is stop-never, this option has no effect. At which point in time does the start-datetime begin to be parsed by binlog, and the format must be datetime, such as' 2016-11-11-11-11-11-11-11-11-11-1-1-1-11-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 Optional. Does not filter by default. At which point in time does the binlog of stop-datetime stop parsing? the format must be datetime, such as' 2016-11-11-11-11-11-11-11-11-11-11-11-1-1-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11. Optional. Does not filter by default.
Object filtering-d,-- databases outputs only the sql of the target db. Optional. The default is empty. -t,-- tables outputs only the sql of the target tables. Optional. Default is empty
Reference: https://github.com/danfengcao/binlog2sql
