Binlog2sql, the data recovery tool you should know

Two articles about data recovery have been shared before, and the links are as follows:

MySQL > restore MySQL

MySQL database backup and restore (2)

Binlog2sql is a data recovery tool that can be used to retrieve SQL from MySQL binlog. It can be used to retrieve original SQL, rollback SQL, delete primary key INSERT SQL, etc. The main uses are as follows:

  • Fast Data Rollback (flash back)

  • Data lost by the new master after the master/slave switchover

  • Generating standard SQL from binlog

1 Preparing the system environment

1.1 install git

This project is shared with Git and can be obtained directly from Git, so you can install Git first

yum install  -y  pip
Copy the code

1.2 install python

Since binlog2SQL depends on python2.7 or Python 3.4 +, it cannot be used if the native Python version is available. For details on installing or upgrading Python, see Python Installation and Upgrade.

1.3 installation points

If PIP is not installed on your system, you will need to install PIP first because you will need PIP later to install the packages required for Python. Refer to the history article PIP Installation in one Minute.

1.4 Binlog2SQL Download and Dependency package Installation

Binlog2sql can be deployed on other machines, rather than having to be deployed on the mysql server

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
Copy the code

2 Prepare the MySQL environment

2.1 Parameter Settings

The following parameters must be set for the MySQL server

[mysqld] server_id = 128 log_bin = /data/mysql/mysql3306/logs/mysql-bin max_binlog_size = 512M binlog_format = row Binlog_row_image = full # Default value, which can be set unexplicitlyCopy the code

2.2 Creating a Recovery Account

Because binlog2SQL is used to obtain binlogs from the slave database, the database account permissions must be set to those required by the slave database at least.

Mysql > create user data_rec@'192.168.56.%' identified by 'XXXXXXXX '; Query OK, 0 rows affected (0.01 SEC) -- mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO data_rec@'192.168.56.%'; Query OK, 0 rows affected (0.00 SEC)Copy the code

Permission Description:

  • Select: The information_schema.COLUMNS table on the server is read to obtain meta information about the table structure and combine it into a visual SQL statement

  • Super/Replication client: Both permissions can be used. You need to run ‘SHOW MASTER STATUS’ to obtain the binlog list of the server

  • Replication slave: Permission to obtain binlog content through BINLOG_DUMP

2.3 Create test tables and data

mysql> use testdb; Database changed mysql> create table t_test1 (id int primary key auto_increment ,c_name varchar(20), c_num int ); Query OK, 4 rows affected (0.02sec) mysql> insert into t_test1(c_name,c_num) values('aaaa',10),('abcc',15),('bacess',9),('andd',10); Query OK, 4 rows affected (0.03 SEC) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t_test1(c_name,c_num) values('bbbaa',1),('dc',5),('vgcess',29),('hdgd',0); Query OK, 4 rows Affected (0.01 SEC) Records: 4 Duplicates: 0 Warnings: 0Copy the code

2.4 Simulation deletes data by mistake

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-18 16:29:08 |
+---------------------+
1 row in set (0.00 sec)

mysql> delete from  t_test1;
Query OK, 8 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-18 16:29:26 |
+---------------------+
1 row in set (0.00 sec)
Copy the code

3 binlog2SQL Restores data

3.1 Generating recovery SQL

Since we know the approximate time of the error deletion, we can recover the data of the specified library table by parsing the binlog of the corresponding time, and the generated result is the SQL used for recovery

Python binlog2sql.py --flashback -h 192.168.56.128 -udata_rec -p' XXXXXXXX '-d testdb -t t_test1 --start-file='mysql-bin.000003' --start-datetime='2020-09-18 16:29:08' --stop-datetime='2020-09-18 16:30:00' >/tmp/rec.sqlCopy the code
The results are as followsCopy the code

3.2 Restoring Data

mysql> select * from t_test1; Empty set (0.00 SEC) mysql> source/TMP /rec.sql; Query OK, 1 row affected (0.00 SEC) Query OK, 1 row affected (0.00 SEC) Query OK, 1 row affected (0.00 SEC) Query OK, 1 row affected (0.00 SEC) Query OK, 1 row affected (0.00 SEC) Query OK, 1 row affected (0.00 SEC) Query OK, 1 row affected (0.01 SEC) Query OK, 1 row affected (0.00 SEC) Query OK, 1 row affected (0.00 SEC) mysql> select * from t_test1; +----+--------+-------+ | id | c_name | c_num | +----+--------+-------+ | 1 | aaaa | 10 | | 2 | abcc | 15 | | 3 | bacess  | 9 | | 4 | andd | 10 | | 5 | bbbaa | 1 | | 6 | dc | 5 | | 7 | vgcess | 29 | | 8 | hdgd | 0 | +----+--------+-------+ 8 Rows in set (0.00 SEC)Copy the code

Data has been recovered.

4 Instructions

Reference link: github.com/danfengcao/…

4.1 Parsing Mode

–stop-never continues parsing binlog. Optional. The default is False and synchronizes to the latest binlog location when the command is executed. In general, parsing a single binlog is sufficient, but continuous parsing may be required if there are operations following the table to be rolled back

-k, –no-primary-key Removes the primary key for INSERT statements. Optional. False by default.

-b, –flashback generates rollback SQL that parses large files without memory limitations. Optional. False by default. You cannot add stop-never or no-primary-key at the same time. This case is used more often

— Back – interval-b specifies the number of seconds for each 1000 rows of SQL to be rolled back with SLEEP. Optional. The default is 1.0.

4.2 Parsing range control

–start-file Specifies the name of the start parsing file instead of the full path. This parameter is mandatory. That is, specify the corresponding binlog start file name

–start-position/–start-pos Start parsing position. Optional. The default value is the start location of start-file.

–stop-file/–end-file Stops parsing files. Optional. The default value is start-file for the same file. If the resolution mode is stop-never, this option is invalid.

–stop-position/–end-pos Terminates the parsing position. Optional. Defaults to the last position of stop-file; If the resolution mode is stop-never, this option is invalid.

–start-datetime Start parsing time in the format of ‘%Y-%m-%d %H:% m :%S’. Optional. No filtering by default.

–stop-datetime Stop parsing time in the format ‘%Y-%m-%d %H:% m :%S’. Optional. No filtering by default.

4.3 Object Filtering

-d, –databases databases are used to analyze only the SQL of the target DB. Databases are separated by Spaces. Optional. The default value is null.

-t, –tables parses only the SQL of the target table. Multiple tables are separated by Spaces, for example, -t tbl1 tbl2. Optional. The default value is null.

–only- DML Parses only DML and ignores DDL. Optional. False by default.

–sql-type resolves only the specified type and supports INSERT, UPDATE, and DELETE. Multiple types are separated by Spaces, for example, –sql-type INSERT DELETE. Optional. The default value is add, delete, and change. If this parameter is used but no type is entered, all three are not parsed.

Highlights from the past

MySQL high availability MHA cluster deployment mysql8.0 new users and encryption rule changes those things

Monitoring tools: Prometheus+Grafana monitors MySQL and Redis databases

MySQL sensitive data encryption and decryption

MySQL > restore MySQL

MySQL database backup and restore (2)