A quick solution to accidentally delete MySQL data (MySQL Flashback Tool)

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 lost data on the new master after master-slave switch Generate standard SQL from binlog, and the derived functions support MySQL 5.6 and 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

select, super/replication client, replication slave

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

For a detailed introduction to flashback, see "Flashback Principle and Practice" in the example directory example/mysql-flashback-priciple-and-practice.md

The original data of the tbl table in the test library

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 | 
+----+--------+---------------------+ 
4 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:

1. 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 | 
+------------------+-----------+

2. 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 and 938. We further filter based on the location, use the flashback mode to generate the rollback SQL, and check whether the rollback SQL is correct (Note: In a real environment, 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

The above is a quick solution to the problem of accidental deletion of MySQL data introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!