The method of fast rollback after misoperation of Mysql database _mysql

Basically, every programmer working with the database (and probably your colleagues) will have a problem, how to quickly rollback after MySQL misoperation? For example, delete a table, forget to limit the condition, the whole table is gone. If this is the core business data of the online environment, then it will be a big deal. After the misoperation, it is very important to roll back the data quickly.

Binlog2sql Quick Rollback

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

[Mysqld]
Server-id = 1
log_bin =/var/log/mysql/mysql-bin.log
max_binlog_size = 1000M
Binlog-format = row

If the binlog is not turned on, and the rollback SQL is not generated beforehand, it is really impossible to roll back quickly. For MySQL, which holds important business data, it is highly recommended to open binlog.

Subsequently, install the Open Source Tool binlog2sql. Binlog2sql is an easy-to-use binlog parsing tool, and one of the features is to generate rollback SQL.

git clone https://github.com/danfengcao/binlog2sql.git
pip Install-r requirements.txt

Then we can generate rollback SQL.

Background: The data of the entire table in the test library F has been mistakenly deleted, requiring urgent rollback.

Existing data

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 |
+-----+-----+---------------------+
misoperation mysql> delete from F; Query OK, 4 rows Affected (0.00 sec)
F table is emptied
mysql> select * from F;
Empty Set (0.00 sec)

Rollback step:

Log in to MySQL to view the current Binlog file

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

The newest Binlog file is mysql-bin.000002, we reposition the binlog location of the Misoperation 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 of the =19
DELETE from ' test '. ' F ' WHERE ' did ' and ' updatetime ' = ' 2016-12-06 12:55:56 ' and ' uid ' =2 LIMIT 1; #st  Art 4 End of the =20
DELETE from ' test '. ' F ' WHERE ' did ' and ' updatetime ' = ' 2016-12-07 14:00:58 ' and ' uid ' =3 LIMIT 1; #start 4 End of the =21
DELETE from ' test '. ' F ' WHERE ' did ' and ' updatetime ' = ' 2016-12-07 14:01:00 ' and ' uid ' =4 LIMIT 1; #start 4 End 314

Generate rollback SQL and check if rollback SQL is correct

$ python binlog2sql/binlog2sql.py-h127.0.0.1-p3306-uadmin-p ' admin '-dtest-t f--start-file= ' mysql-bin.000002 '--star T-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 of the did
INSERT into ' test '. ' F ' (' ', ' updatetime ', ' uid ') VALUES (' 2016-12-07 14:00:58 ', 3); #start 4 en D-
did INSERT into ' test '. ' F ' (' the ' updatetime ', ', ', ' uid ') VALUES (, ' 2016-12-06 12:55:56 ', 2); #start 4
End Nsert into ' test '. ' F ' (' did ', ' updatetime ', ' uid ') VALUES (18, ' 2016-12-06 12:28:18 ', 1); #start 4 End 314

Confirm that the rollback SQL is correct and that the rollback statement is executed. Login MySQL, data rollback successful.

$ python binlog2sql.py-h127.0.0.1-p3306-uadmin-p ' admin '-dtest-t f--start-file= ' mysql-bin.000002 '--start-pos=4--en D-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 |
+-----+-----+---------------------+

You don't have to worry about getting fired anymore.

Problems

• Some people will ask, I DDL misoperation how fast rollback? Like drop a big table.

Hard to do. Because DDL operations do not record changes in each row of data to binlog even in row mode, DDL cannot be rolled back through Binlog. To implement DDL rollback, you must back up the old data before executing the DDL. Indeed someone by modifying the MySQL server source code to achieve a quick rollback of the DDL, I found Ali's Xiaobin Lin submitted a patch. But as far as I know, few internet companies in the country have applied this feature. Reason, I think the most important or lazy to toss, do not need to engage in this low-frequency function, the secondary reason is to add some additional storage.

As a result, DDL misoperation is generally only recoverable through backup. If the company can't even use a backup, it really suggests buying a plane ticket. What are you doing? Run.

• Does MySQL have other rollback tools in addition to Binlog2sql?

Of course. Ali Peng Lixun to Mysqlbinlog added flashback features, this should be the earliest MySQL flashback function, Peng solves the DML rollback, and explains the use of Binlog for DML flashback design ideas. The DDL rollback feature is also proposed and implemented by the Ali team. These two features are innovative, and the flash-back tools that follow are basically imitations of both. In addition, where to go Open source inception is a set of MySQL Automation operational tools, this is heavier, support DML rollback, not from the binlog rollback, is back from the backup rollback, also supports the DDL rollback table structure, the data is not rolled back ~

The above is a small set to introduce the MySQL database after the wrong operation of the fast rollback method, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!