Shulou(Shulou.com)06/01 Report--

This article mainly explains how to install and configure mysql flashback tool binlog2sql, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Overview

Binlog2sql is an open source MySQL Binlog parsing tool developed by Python, which can parse Binlog to original SQL, and also support parsing Binlog to rollback SQL, removing primary key INSERT SQL, which is a good helper for data recovery of DBA and operation and maintenance personnel.

I. installation and configuration

1.1 purpose

Fast data rollback (flashback) repair of lost data in the new master after master-slave switching generates standard SQL from binlog, and the derived function supports MySQL5.6,5.7

1.2 installation

Shell > git clone https://github.com/danfengcao/binlog2sql.git & & cd binlog2sqlshell > pip install-r requirements.txt

II. Usage

2.1 pre-use configuration

2.1.1 Parameter configuration

[mysqld] server_id = 1log_bin = / var/log/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = full

2.1.2 minimum set of permissions required by user

Select, super/replication client, replication slave

Recommended authorization

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

Permission description

Select: you 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: obtain the permission of binlog content through the BINLOG_DUMP protocol

2.2 basic usage

2.2.1 basic usage

Parse out the standard SQL

Shell > python binlog2sql.py-h227.0.0.1-P3306-uadmin-pumped admins'- dtest-t test3 test4-- start-file='mysql-bin.000002' ​ output: INSERT INTO `test`.`test3` (`addtime`, `data`, `id`) VALUES ('2016-12-10 1315 03dtest 38,' english', 4) # start 570 end 736UPDATE `test`.`test3` SET `addtime` = '2016-12-10 1212 WHERE 0000V, `data` =' Chinese', `id` = 3 WHERE `addtime` = '2016-12-10 13AND 03AND `data` =' Chinese 'AND `id` = 3 LIMIT 1; # start 763 end 954DELETE FROM `test`.`test3` WHERE `addtime` =' 2016-12-10 1314338 'AND `data` =' english' AND `id` = 4 LIMIT 1; # start 981 end 1147

Parse out rollback SQL

Shell > python binlog2sql.py-- flashback-h227.0.0.1-P3306-uadmin-pumped admins'- dtest-ttest3-- start-file='mysql-bin.000002'-- start-position=763-- stop-position=1147 ​ output: INSERT INTO `test`.`test3` (`addtime`, `data`, `id`) VALUES ('2016-12-10 1313 rifle,' english', 4) # start 981 end 1147UPDATE `test`.`test3` SET `addtime` = '2016-12-10 1313 WHERE 03AND 22mm, `data` =' Chinese', `id` = 3 WHERE `addtime` = '2016-12-10 12SET 00' AND `data` =' Chinese 'AND `id` = 3 LIMIT 1;

2.2.2 options

Mysql connection configuration

-h host;-P port;-u user;-p password

Analytic mode

-- stop-never continuously parses binlog. Optional. The default False, which synchronizes to the latest binlog location when the command is executed.

-K,-- no-primary-key removes the primary key from the INSERT statement. Optional. Default False

-B,-- flashback generates rollback SQL, which can parse large files without memory restrictions. Optional. Default False. Cannot be added at the same time as stop-never or no-primary-key.

In back-interval-B mode, roll back SQL for every thousand lines printed, add a word SLEEP for how many seconds. If you do not want to add SLEEP, please set it to 0. Optional. The default is 1.0.

Analytical range control

-- start-file starts parsing the file. You only need the file name, not the full path. Must.

-- the starting resolution position of start-position/--start-pos. Optional. The default is the starting position of start-file.

-- stop-file/--end-file terminates the resolution file. Optional. The default is start-file the same file. If the parsing mode is stop-never, this option has no effect.

-- stop-position/--end-pos terminates the resolution location. Optional. The default is the last location of stop-file; if the parsing mode is stop-never, this option has no effect.

-- the starting parsing time of start-datetime, in the format of'% Y-%m-%d% HRV% MRV% S'. Optional. Does not filter by default.

-- stop-datetime terminates the resolution time, in the format'% Y-%m-%d% HRV% MRV% S'. Optional. Does not filter by default.

Object filtering

-d,-- databases only parses the sql of 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 parses only dml and ignores ddl. Optional. Default False.

-- sql-type parses only specified types, and supports INSERT, UPDATE, and DELETE. Multiple types are separated by spaces, such as-- sql-type INSERT DELETE. Optional. The default is to resolve all additions, deletions and changes. If this parameter is used but no type is filled in, none of the three will be parsed.

2.3 Application cases

2.3.1 accidentally delete the entire table data, which needs to be rolled back urgently

Test Library tbl Table original data mysql > select * from tbl +-+ | id | name | addtime | +-+ | 1 | Xiaozhao | 2016-12-10 00:04:33 | 2 | small coin | 2016-12-10 00:04:48 | 3 | Xiao Sun | 2016-12-12- 13 20:25:00 | | 4 | Xiao Li | 0-12-12 00:00:00 | +-+ rows in set (0.00 sec) ​ mysql > delete from tbl Query OK, 4 rows affected (0.00 sec) ​ 20: 28, tbl table misoperation is cleared mysql > select * from tbl;Empty set (0.00 sec)

Steps to restore data:

Log in to mysql to view the current binlog file

Mysql > show master status;+-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000051 | 967 | mysql-bin.000052 | 965 | +-+-+

The latest binlog file is mysql-bin.000052, and we relocate the binlog location of the misoperation SQL. The misoperator can only know the approximate misoperation time, and we filter the data according to the approximate time.

Shell > python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-pawnadmin'-dtest-ttbl-- start-file='mysql-bin.000052'-- start-datetime='2016-12-13 2020 uadmin 25RV 00'-stop-datetime='2016-12-13 20RV 30RV 00' output: INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-13 20RU 26Rou, 4,' Xiao Li') # start 317 end 487 time 2016-12-13 20:26:26UPDATE `test`.`tbl` SET `addtime` = '2016-12-12 0000 WHERE `addtime`, `name` =' 2016-12-13 20VR 2600' AND `id` = 4 AND `name` = 'Xiao Li' LIMIT 1; # start 514 end 701 time 2016-12-13 20:27:07DELETE FROM `test`.`tbl` WHERE `addtime` = '2016-12-10 00time 0415' AND id` = 1 AND `name` = 'Xiao Zhao' LIMIT 1 # start 728 end 938 time 2016-12-13 20:28:05DELETE FROM `test`.`tbl` WHERE `addtime` = '2016-12-10 00 time 04purl 48' AND `id` = 2 AND `name` =' small money 'LIMIT 1; # start 728 end 938 time 2016-12-13 20:28:05DELETE FROM `test`.`tbl`WHERE `addtime` =' 2016-12-13 2020 20:28:05DELETE FROM 2500' AND `id` = 3 AND `name` = 'Xiao Sun' LIMIT 1 # start 728 end 938 time 2016-12-13 20:28:05DELETE FROM `test`.`tbl` WHERE `addtime` = '2016-12-12 0000 WHERE 00' AND `id` = 4 AND `name` =' Xiao Li 'LIMIT 1; # start 728 end 938 time 2016-12-13 20:28:05

3. We get that the exact location of the misoperation sql is between 728 and 938, and then filter further according to the location, generate a rollback sql using flashback mode, and check whether the rollback sql is correct. (note: in the real world, this step often further filters out the required sql. Combine grep, editor, etc.)

Shell > python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-paired admins'- dtest-ttbl-- start-file='mysql-bin.000052'-- start-position=3346-- stop-position=3556-B > rollback.sql | cat output: INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-12 0000 INSERT INTO, 4, Xiao Li) # start 728 end 938 time 2016-12-13 20:28:05INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-13 20 25 time, 3,' Xiao Sun'); # start 728 end 938 time 2016-12-13 20:28:05INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES # start 728 end 938 time 2016-12-13 20:28:05INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-100 00 VALUES 04time 33, 1,' Xiao Zhao'); # start 728 end 938 time 2016-12-13 20:28:05

4. Verify that the rollback sql is correct and execute the rollback statement. Log in to mysql to confirm that the data was rolled back successfully.

Shell > mysql-h227.0.0.1-P3306-uadmin-pendant admin'

Select * from tbl +-+ | id | name | addtime | +-+ | 1 | Xiaozhao | 2016-12-10 00:04:33 | 2 | small coin | 2016-12-10 00:04:48 | 3 | Xiao Sun | 2016-12-12- 13 20:25:00 | | 4 | Xiao Li | 2016-12-12 00:00:00 | +-

III. Summary

3.1restrictions (compared to mysqlbinlog)

Mysql server must be enabled. The parameter binlog_row_image that cannot be parsed in offline mode must be FULL. It is not supported that MINIMAL parsing is not as fast as mysqlbinlog.

3.2 benefits (compared to mysqlbinlog)

Pure Python development, installation and use are very simple with flashback, no-primary-key parsing mode, no need to install patch flashback mode, more suitable for flashback actual combat parsing to standard SQL, easy to understand, filter code easy to modify, can support more personalized parsing after reading the above content, is not on how to install and configure mysql flashback tool binlog2sql have further understanding, if you want to learn more content, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.