Shulou(Shulou.com)05/31 Report--

Editor to share with you how binlog2sql to achieve the recovery of MySQL misoperation, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The recovery of erroneous operation deleted data in MySQL database can be realized by using SQL statements similar to flashback or generating reverse operation based on binlog in MySQL, which is a very practical function in MySQL.

The principle is not difficult to understand, based on the MySQL row format of binlog, record the history of the addition, deletion and modification of SQL information, based on this parsed out the corresponding SQL statement (rollback is the reverse SQL statement).

In the log mode with binlog format and row format, the content in binlog records the information of additions, deletions and modifications that have been performed in the database, such as executing delete from table where pk_id = 1, and deleting 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, but also the update operation that includes the information of all the fields in this row, recording not only the update operation, but also the value of each field recorded by the update record before the update. This allows you to use this log to generate reverse operation information.

The following is part of a typical binlog log file in MySQL parsed by the mysqlbinlog tool, and you can clearly see the information of the executed sql statements.

At this point, some binlog-based applications in MySQL, such as replication or database restore, are actually realized by repeatedly executing SQL statements that have been added, deleted or modified in the history of a database.

Digression: the binlog function of MySQL records the transaction statement, which is basically equivalent to the transaction log of SQLServer. However, the SQL Server transaction log has positive binary content, and Microsoft officially does not provide a method of parsing, but the contents of this log can be parsed through mysqlbinlog in MySQL.

The following is the information in the binlog log file parsed by the mysqlbinlog tool included with MySQL, in which you can see the SQL statement information.

Knowing the contents of binlog, you can achieve a variety of practical functions based on this binlog. Typical is the restore operation of mistakenly deleted data. For example, Su Jia Little Radish uses Python to complete this parsing function.

Similar features are well-known, and Dianping website DBA wrote its own binlog2sql tool, which is also famous for a long time, and finally has a chance to try it.

Binlog2sql requires statement pip installation, so you need to install pip first

Pip installation reference:

Https://www.cnblogs.com/technologylife/p/5870576.html

Download and install binlog2sql:

Https://github.com/danfengcao/binlog2sql

After completing the binlog2sql, you can use it to restore the data, as follows to simulate the recovery of a misoperation

When binlog is enabled and the log format is row, execute the following sql statement for the test table test_01:

Insert into test_01 values (1); insert into test_01 values (2); insert into test_01 values (3);-- the following misoperation, updating all data update test_01 set name = 'xxx'

Find the current binlog file through show master logs;, and the execution of the corresponding sql statement is stored in the current binlog, which is the target of binlog2sql.

Referring to the following figure, you can find that

Execute: python binlog2sql.py-h227.0.0.1-P3306-uroot-paired root'- ddb01-t test_01-start-file='binlog.000021' (for more parameters and usage, please see the link below). Parse the current binlog file through binlog2sql, and the parsed SQL statement is the execution of the normal SQL statement (insert insert insert update (3-line record)).

Execute: python binlog2sql.py-h227.0.0.1-P3306-uroot-paired root'- ddb01-t test_01-start-file='binlog.000021'-B, and generate reverse operation information through the-B parameter

The parsed SQL statement with the parameter-B is the opposite of the above SQL statement, including the order, that is, generating the reverse operation in reverse order.

The original operation is insert insert insert update update update, and the reverse operation is upfate update update delete delete delete. In this way, we can intercept the generated reverse sql statement according to the specific situation and restore the misoperation.

The above operations should pay attention to the path problem of the installed binlog2sql. If the path is wrong and the binlog2sql.py cannot be found, the above command cannot be executed.

The above is all the contents of the article "how to recover the misoperation of MySQL by binlog2sql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.