Detailed installation and configuration tutorial of MySQL flashback tool binlog2sql

Overview

binlog2sql is an open source MySQL Binlog parsing tool developed in Python. It can parse Binlog into original SQL. It also supports parsing Binlog into rollback SQL and removing the INSERT SQL of the primary key. It is a good helper for DBA and operation and maintenance personnel to recover data.

1. Installation and Configuration

1.1 Purpose

• Fast data rollback (flashback)
• Repair of new master data loss after master-slave switching
• Generate standard SQL from binlog, bringing derivative functions
• Support MySQL 5.6, 5.7

1.2 Installation

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

2. Usage

2.1 Pre-use configuration

2.1.1 Parameter Configuration

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

2.1.2 Minimum set of permissions required by the user

select, super/replication client, replication slave

Recommended authorization

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

Permissions

• select: 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
• super/replication client: Both permissions are available. You need to execute 'SHOW MASTER STATUS' to obtain the binlog list on the server side.
• Replication slave: Get permission to binlog content through BINLOG_DUMP protocol

2.2 Basic Usage

2.2.1 Basic Usage

Parse standard SQL

shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 
--start-file='mysql-bin.000002'
​
Output:
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); 
#start 570 end 736
UPDATE `test`.`test3` SET `addtime`='2016-12-10 12:00:00', `data`='中文', 
`id`=3 WHERE `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954
DELETE FROM `test`.`test3` WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4
 LIMIT 1; #start 981 end 1147

Parse the rollback SQL

shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -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 13:03:38', 'english', 4); 
#start 981 end 1147
UPDATE `test`.`test3` SET `addtime`='2016-12-10 13:03:22', `data`='中文', 
`id`=3 WHERE `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954

2.2.2 Options

mysql connection configuration

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

Parsing Mode

--stop-never Continue parsing binlog. Optional. The default value is False, which means synchronization to the latest binlog position when the command is executed.

-K, --no-primary-key Remove primary key from INSERT statements. Optional. Default: False

-B, --flashback generates rollback SQL, can parse large files, and is not limited by memory. Optional. Default is False. It cannot be added at the same time as stop-never or no-primary-key.

--back-interval -B mode, every time a thousand rollback SQL lines are printed, add a SLEEP statement for a certain number of seconds. If you do not want to add SLEEP, please set it to 0. Optional. Default is 1.0.

Resolution range control

--start-file The starting parsing file only needs the file name, no full path is required. must.

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

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

--stop-position/--end-pos Stop parsing position. Optional. The default is the last position of the stop-file; if the parsing mode is stop-never, this option is invalid.

--start-datetime Start parsing time, format '%Y-%m-%d %H:%M:%S'. Optional. No filtering by default.

--stop-datetime Stop parsing time, format '%Y-%m-%d %H:%M:%S'. Optional. No filtering by default.

Object filtering

-d, --databases only parses the SQL of the target db. Multiple databases are separated by spaces, such as -d db1 db2. Optional. The default value is empty.

-t, --tables only parse the SQL of the target table. Multiple tables are separated by spaces, such as -t tbl1 tbl2. Optional. The default value is empty.

--only-dml Only parse dml, ignore ddl. Optional. Default is False.

--sql-type only parses the specified type, supporting INSERT, UPDATE, and DELETE. Multiple types are separated by spaces, such as --sql-type INSERT DELETE. Optional. The default is to parse all additions, deletions and modifications. 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 The entire table data is accidentally deleted and an emergency rollback is required

Original data of tbl table in test librarymysql> select * from tbl;
+----+--------+---------------------+
| id | name | addtime |
+----+--------+---------------------+
| 1 | Xiao Zhao | 2016-12-10 00:04:33 |
| 2 | Xiaoqian | 2016-12-10 00:04:48 |
| 3 | Xiaosun | 2016-12-13 20:25:00 |
| 4 | Xiao Li | 2016-12-12 00:00:00 |
+----+--------+---------------------+
rows in set (0.00 sec)
​
mysql> delete from tbl;
Query OK, 4 rows affected (0.00 sec)
​
At 20:28, the tbl table was cleared due to an error.mysql> select * from tbl;
Empty set (0.00 sec)

Steps to restore data:

Log in to mysql and 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. Let’s locate the binlog location of the incorrect SQL operation. The person who made the mistake can only know the approximate time of the mistake, so we filter the data based on the approximate time.

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl 
--start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13
 20:30:00'
Output:
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, 'Xiao Li'); 
#start 317 end 487 time 2016-12-13 20:26:26
UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='Xiao Li' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='Xiao Li' LIMIT 1; #start 514 end 701 time 2016-12-13 20:27:07
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='Xiao Li' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

3. We found that the exact location of the incorrect SQL is between 728-938. We further filter based on the location, use flashback mode to generate rollback SQL, and check whether the rollback SQL is correct (Note: In real environments, this step often further filters out the required SQL. Combined with grep, editor, etc.)

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -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 00:00:00', 4, 'Xiao Li'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, 'Xiao Zhao'); #start 728 end 938 time 2016-12-13 20:28:05

4. Confirm that the rollback SQL is correct and execute the rollback statement. Log in to MySQL to confirm that the data rollback is successful.

shell> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < rollback.sql
​
mysql> select * from tbl;
+----+--------+---------------------+
| id | name | addtime |
+----+--------+---------------------+
| 1 | Xiao Zhao | 2016-12-10 00:04:33 |
| 2 | Xiaoqian | 2016-12-10 00:04:48 |
| 3 | Xiaosun | 2016-12-13 20:25:00 |
| 4 | Xiao Li | 2016-12-12 00:00:00 |
+----+--------+---------------------+

Conclusion

3.1 Limitations (Compared to mysqlbinlog)

• MySQL server must be started, it cannot be parsed in offline mode
• The parameter binlog_row_image must be FULL. MINIMAL is not supported yet.
• Parsing speed is not as fast as mysqlbinlog

3.2 Advantages (Compared to mysqlbinlog)

• Pure Python development, easy to install and use
• Built-in flashback and no-primary-key parsing modes, no need to install patches
• In flashback mode, it is more suitable for flashback combat.
• Parse into standard SQL for easy understanding and screening
• The code is easy to modify and can support more personalized analysis

Summarize

This is the end of this article about the detailed installation and configuration of the MySQL flashback tool binlog2sql. For more related MySQL flashback tool binlog2sql content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!