Quick Solution to Erroneous Erasure of MySQL Data (MySQL Flashback Tool)

Overview

Binlog2sql is an open source MySQL Binlog parsing tool developed by Python, which can parse Binlog into original SQL, and also supports parsing Binlog into rollback SQL and INSERT SQL with primary key removed. It is a good helper for DBA and operation and maintenance personnel to recover data.

1. Install the configuration

1.1 Purpose

Fast data rollback (flashback)
Repair of lost data of new master after master-slave switching
Derivative functions brought by generating standard SQL from binlog
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. How to use it

2.1 Configuration before use

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 user

select, super/replication client, replication slave

Proposed authorization

select, super/replication client, replication slave

Permission description

select: Read information_schema. COLUMNS table at server end, obtain meta-information of table structure, and splice it into visual sql statement super/replication client: Both permissions can be used. You need to execute 'SHOW MASTER STATUS' to obtain the binlog list on the server side replication slave: Access to binlog content through BINLOG_DUMP protocol

2.2 Basic usage

2.2. 1 Basic usage

Analyze the 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`=' Chinese ', `id`=3 WHERE `addtime`='2016-12-10 13:03:22' AND `data`=' Chinese ' 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 out 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`=' Chinese ', `id`=3 WHERE `addtime`='2016-12-10 12:00:00' AND `data`=' Chinese ' AND `id`=3 LIMIT 1; #start 763 end 954

2.2. 2 Options

mysql Connection Configuration

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

Analytic pattern

--stop-never Continuous analysis of binlog. Optional. Default False, synchronized to the latest binlog location when the command was executed.

-K,--no-primary-key strips the primary key for the INSERT statement. Optional. Default False

-B,--flashback generate rollback SQL, can parse large files, not limited by memory. Optional. Default False. It cannot be added with stop-never or no-primary-key.

--In back-interval-B mode, roll back SQL for every 1,000 lines printed, and add one sentence of SLEEP for how many seconds. If you don't want to add SLEEP, please set it to 0. Optional. The default is 1.0.

Analytic range control

--start-file start parsing file, only file name, no full path. Must.

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

--stop-file/--end-file terminates parsing files. Optional. The default is the same file as start-file. If the parsing mode is stop-never, this option is invalid.

--stop-position/--end-pos termination resolution position. Optional. The default is the last position of 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. It is not filtered by default.

--stop-datetime end parsing time in the format '% Y-% m-% d% H:% M:% S'. Optional. It is not filtered by default.

Object filtering

-d,--databases only parses sql of the target db, and multiple libraries are separated by spaces, such as-d db1 db2. Optional. It defaults to null.

-t,--tables only parses sql of the target table, and multiple tables are separated by spaces, such as-t tbl1 tbl2. Optional. It defaults to null.

--only-dml only parses dml, ignoring ddl. Optional. Default False.

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

2.3 Application cases

2.3. 1 Error deletion of entire table data, requiring urgent rollback

For details of flashback, please refer to "Flashback Principle and Practical Practice" under example directory example/mysql-flashback-priciple-and-practice.md

test Library tbl Table Original Data

mysql> select * from tbl; 
+----+--------+---------------------+ 
| id | name | addtime | 
+----+--------+---------------------+ 
| 1 |  Xiao Zhao  | 2016-12-10 00:04:33 | 
| 2 |  Penny  | 2016-12-10 00:04:48 | 
| 3 |  Xiao Sun  | 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) 
​ 
20:28 When, tbl Table misoperation is emptied 

mysql> select * from tbl; 
Empty set (0.00 sec)

Recovery data steps:

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. We relocate the location of binlog where SQL was incorrectly operated. The misoperator can only know the approximate misoperation time, and we filter the data according to 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:

select, super/replication client, replication slave

0

3. We get the exact position of misoperation sql between 728 and 938, and then filter it one step according to the position, use flashback mode to generate rollback sql, and check whether rollback sql is correct (Note: In real environment, this step often filters out the required sql one step. Combined with grep, editor, etc.)

select, super/replication client, replication slave

1

Output:

select, super/replication client, replication slave

2

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 |  Penny  | 2016-12-10 00:04:48 | 
| 3 |  Xiao Sun  | 2016-12-13 20:25:00 | 
| 4 |  Xiao Li  | 2016-12-12 00:00:00 | 
+----+--------+---------------------+

3. Summary

3.1 Restrictions (vs. mysqlbinlog)

mysql server must be turned on and cannot be parsed in offline mode

Parameter binlog_row_image must be FULL, MINIMAL is not supported for the time being

Parsing speed is not as fast as mysqlbinlog

3.2 Advantages (compared to mysqlbinlog)

Pure Python development, installation and use are very simple

Self-contained flashback, no-primary-key parsing mode, no need to install patches

In flashback mode, it is more suitable for flashback actual combat

It is analyzed into standard SQL, which is convenient to understand and screen

The code is easy to modify and can support more personalized parsing

Summarize