Binlog2sql restores MySQL misoperations.

Binlog2sql restores MySQL misoperations.

 

You can use SQL statements similar to flashback or reverse operation Generation Based on binlog in MySQL to restore data deleted by misoperation in MySQL database. This is a very practical function in MySQL.
The principle is not hard to understand. In the MySQL row-based binlog, the SQL information of adding, deleting, and modifying history is recorded, and corresponding SQL statements are parsed based on this (rollback is a reverse SQL statement ).
In the log mode where the binlog format is row, the content in the binlog records the added, deleted, and modified information that has been executed in the database, all of which contain reverse information.
For example, execute delete from table where pk_id = 1; delete a record according to the primary key
The SQL statement in the corresponding binlog is: delete from table where pk_id = 1 and name = 'xxx' and other_column = 'xxx'; The where condition is not only the Id of the original statement, it also includes information about all the fields in this row.
Similarly, the update operation records not only the update operation, but also the values of each field before the update record. In this way, you can use this log to generate reverse operation information.

 

The following is part of a typical binlog file in MySQL parsed using mysqlbinlog. You can see the information of the executed SQL statement clearly.
Here, for some binlog-based applications in MySQL, such as copying or restoring a database, the SQL statements that have been added, deleted, and modified in the history of a database are executed repeatedly.
Question: The binlog function of MySQL records the transaction Statement, which is basically the same as the transaction log of SQL Server.
However, the SQL Server transaction log is binary, and Microsoft does not provide a resolution method, and MySQL can completely parse the log content through mysqlbinlog.

The following is the information in the binlog file parsed by MySQL's mysqlbinlog tool. The SQL statement information is displayed.

 

With the knowledge of the binlog content, you can implement various practical functions based on this binlog. A typical example is the Restoration Operation of accidental deletion of data. For example, Su Jia xiaoradish can solve this parsing function using Python.
The binlog2sql tool written by the DBA of the public comments network is well-known for its similar functions. It is also famous for a long time and finally has a chance to try it out.
To install binlog2sql, you must first install pip.
Pip install reference: https://www.cnblogs.com/technologylife/p/5870576.html

Binlog2sql download and installation: https://github.com/danfengcao/binlog2sql

After the binlog2sql statement is completed, you can use it to restore the data, as shown in the following figure.

When binlog is enabled and the log format is row, run the following SQL statements for the test table test_01:

Insert into test_01 values (1, 'aaa'); insert into test_01 values (2, 'bbb'); insert into test_01 values (3, 'vvv '); -- the following error occurs, updated all data update test_01 set name = 'xxx ';

Use show master logs; to find the current binlog file, and the execution of the corresponding SQL statement is stored in the current binlog. The target of binlog2sql is this file.

For more information, see
Run python binlog2sql. py-h127.0.0.1-P3306-uroot-p 'root'-ddb01-t test_01 -- start-file = 'binlog. 000021 '(for more parameters and usage instructions, refer to the link below). Use binlog2sql to parse the current binlog file,
The parsed SQL statement is the execution of normal SQL statements (insert update (3 rows of records ))
Run python binlog2sql. py-h127.0.0.1-P3306-uroot-p 'root'-ddb01-t test_01 -- start-file = 'binlog. 000021 '-B, uses the-B parameter to generate reverse operation information
The SQL statements parsed with parameter-B are the opposite of the preceding SQL statements, including the order, that is, reverse operations are generated in reverse order.
The original operation is insert update, and the reverse operation is upfate update delete,
In this way, you can intercept the generated reverse SQL statement based on the actual situation to restore the misoperation.

Note the binlog2sql installation path. If the path is incorrect and binlog2sql. py cannot be found, the preceding command cannot be executed.

For more information about binlog2sql parameters, usage, and limits, see GitHub: https://github.com/danfengcao/binlog2sql.