How to roll back MySQL quickly after MySQL misoperations

How to roll back MySQL quickly after MySQL misoperations

Basically, Every programmer dealing with databases (or perhaps your colleagues) will have a problem. How can I roll back MySQL quickly after a misoperation? For example, if you delete a table and forget to add restrictions, the whole table is gone. If this is still the core business data of the online environment, this is a big problem. It is very important to roll back data quickly after misoperations.

Binlog2sql quick rollback

First, check that your MySQL server has enabled binlog and set the following parameters:

[mysqld]server-id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 1000Mbinlog-format = row

If binlog is not enabled and SQL rollback is not generated in advance, you cannot roll back quickly. We strongly recommend that you enable binlog for MySQL that stores important business data.

 

Then, install the open-source tool binlog2sql. Binlog2sql is a simple and easy-to-use binlog parsing tool. One function is to generate a rollback SQL statement.

git clone https://github.com/danfengcao/binlog2sql.gitpip install -r requirements.txt

 

Then, we can generate a rollback SQL statement.

Background: The data in the entire table of database test f is accidentally deleted and needs to be rolled back urgently.

Mysql> select * from f; + ----- + ------------------- + | uid | did | updateTime | + ----- + ----------------------- + | 1 | 18 | 12:28:18 | 2 | 19 | 12:55:56 | 3 | 20 | 14:00:58 | 4 | 21 | 14:01:00 | + ----- + --------------------- + misoperation mysql> delete from f; query OK, 4 rows affected (0.00 sec) f table is cleared mysql> select * from f; Empty set (0.00 sec)

 

Rollback procedure:

So far, there is no need to worry about being fired.

FAQs

• Someone may ask, How can I roll back quickly due to DDL misoperations? For example, a large table is dropped.

  It is hard to do. Even in row mode, DDL operations do not record changes in each row of data to binlog, so DDL cannot be rolled back through binlog. To implement DDL rollback, you must back up old data before executing DDL. Someone has implemented DDL fast rollback by modifying the mysql server Source code. I found Alibaba xiaobin lin and submitted a patch. But as far as I know, few Internet companies in China have applied this feature. For the reason, I think it is mainly lazy to do it. There is no need to do this low-frequency function. The secondary reason is that some additional storage will be added.
  Therefore, DDL misoperation can only be restored through backup. If the company cannot even use backup, we recommend that you buy a flight ticket. Why? Runbei

• Does mysql have other rollback tools besides binlog2sql?

  Of course. Alibaba Peng lixun added the flashback feature to mysqlbinlog, which should be the earliest flashback feature in mysql. Peng solved DML rollback and explained the design idea of using binlog for DML flash back. The DDL rollback feature is also proposed and implemented by the Alibaba team. These two features are innovative, and the flash back tools that have emerged since then are basically imitation of the above two. In addition, the open-source Inception is a MySQL automated O & M tool, which is heavy. It supports DML rollback. It is not rolled back from binlog, but from backup, the DDL rollback table structure is also supported. data cannot be rolled back ~

If any of the excellent articles on mysql rollback related tools are missing, please let us know ~

My mailbox danfengcao.info@gmail.com

References

[1] Peng lixun, the design idea of implementing flashback in MySQL

[2] Lixun Peng, Provide the flashback feature by binlog

[3] Ding Qi, MySQL flash back solution discussion and implementation

[4] xiaobin lin, flashback from binlog for MySQL

[5] Wang zhufeng, where to go inception

[6] danfengcao, binlog2sql: Parse MySQL binlog to SQL you want