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

This article mainly explains "how to recover data quickly after misoperation of MySQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to recover data quickly after misoperation of MySQL.

Traditional solution

Rebuild the instance with full backup, and then use incremental binlog backup to restore to the state before misoperation. Then skip the misoperated SQL and continue to apply the binlog. This method is time-consuming and laborious and is not worth recommending.

Fast flashback with binlog2sql

First, make sure that binlog is enabled on your MySQL server and set the following parameters:

If binlog is not enabled and SQL is not rolled back in advance, it is really impossible to roll back quickly. It is strongly recommended to enable binlog for MySQL that stores important business data.

Then install the open source tool binlog2sql. Binlog2sql is an easy-to-use binlog parsing tool, one of which is to generate a rollback SQL.

Then we can generate a rollback SQL.

Background: Xiao Ming mistakenly deleted the data of the whole tbl table of test library at more than 20:00, which needs to be rolled back urgently.

Steps to restore data:

Log in to mysql to view the current binlog file

two。 The latest binlog file is mysql-bin.000052, and we relocate the binlog location of the misoperation SQL. The misoperator can only know the approximate misoperation time, and we filter the data according to the approximate time.

3. We get that the exact location of the misoperation sql is between 728 and 938, then filter further according to the location, and use the flashback mode to generate the rollback sql to check whether the rollback sql is correct.

4. Verify that the rollback sql is correct and execute the rollback statement. Log in to mysql to confirm that the data was rolled back successfully.

At this point, I believe you have a deeper understanding of "how to quickly recover data after misoperation of MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.