MySQL database rollback method after misoperation, mysql database

MySQL database rollback method after misoperation, mysql database

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 entire table in table f of the test database is deleted by mistake and needs to be rolled back urgently.

Original data

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 steps:

Log on to mysql and view the current binlog file.

mysql> show master logs;+------------------+-----------+| Log_name     | File_size |+------------------+-----------+| mysql-bin.000001 | 12262268 || mysql-bin.000002 |  132776 |+------------------+-----------+

The latest binlog file is the mysql-bin.000002, and we can then locate the binlog location of the misoperations SQL

$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002'

Output:

DELETE FROM `test`.`f` WHERE `did`=18 AND `updateTime`='2016-12-06 12:28:18' AND `uid`=1 LIMIT 1; #start 4 end 314DELETE FROM `test`.`f` WHERE `did`=19 AND `updateTime`='2016-12-06 12:55:56' AND `uid`=2 LIMIT 1; #start 4 end 314DELETE FROM `test`.`f` WHERE `did`=20 AND `updateTime`='2016-12-07 14:00:58' AND `uid`=3 LIMIT 1; #start 4 end 314DELETE FROM `test`.`f` WHERE `did`=21 AND `updateTime`='2016-12-07 14:01:00' AND `uid`=4 LIMIT 1; #start 4 end 314

Generate a rollback SQL statement and check whether the rollback SQL statement is correct.

$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B

Output:

INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (21, '2016-12-07 14:01:00', 4); #start 4 end 314INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (20, '2016-12-07 14:00:58', 3); #start 4 end 314INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (19, '2016-12-06 12:55:56', 2); #start 4 end 314INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (18, '2016-12-06 12:28:18', 1); #start 4 end 314

Check that the SQL rollback statement is correct and run the rollback statement. Log on to mysql and the data is rolled back successfully.

$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'mysql> select * from f;+-----+-----+---------------------+| uid | did | updateTime     |+-----+-----+---------------------+|  1 | 18 | 2016-12-06 12:28:18 ||  2 | 19 | 2016-12-06 12:55:56 ||  3 | 20 | 2016-12-07 14:00:58 ||  4 | 21 | 2016-12-07 14:01:00 |+-----+-----+---------------------+

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 ~

The above section describes how to roll back a MySQL database quickly after misoperation. I hope it will be helpful to you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!