How does MySQL recover data after a false operation? A tutorial on fast recovery of data after MySQL Misoperation

Absrtact: Using Binlog Flash-back error operation data.
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.

Traditional solution

Use a full backup to repeat the instance, and then use the incremental Binlog backup to revert to the state before the error operation. Then skip the error-operated SQL and continue to apply Binlog. This method is time-consuming and laborious and is not worth recommending again.

Using Binlog2sql to flash back quickly

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 = 100M 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 ps://github.com/danfengcao/binlog2sql ">binlog2sql." Binlog2sql is an easy-to-use binlog parsing tool, and one of the features is to generate rollback SQL.

shell> git clone https://github.com/danfengcao/binlog2sql.git shell> Pip Install-r requirements.txt

Then we can generate rollback SQL.

Background: Xiao Ming mistakenly deleted the data of the whole table of test library TBL at 20 o ' time, needing urgent rollback.

Test Library TBL Table existing data mysql>select*fromtbl; +----+--------+---------------------+ | ID |name | addtime   & nbsp;   | +----+--------+---------------------+ | 1 | Xiao Zhao   | 2016-12-10 00:04:33 | | 2 | Penny   | 2016-12-10 00:04:48 | | 3 | Sun   | 2016-12-13 20:25:00 | | 4 | li   | 2016-12-12 00:00:00 | +----+--------+---------------------+ 4rowsinset (0.00 sec) &N Bsp mysql>deletefromtbl; Query OK, 4rowsaffected (0.00 sec)   20:28, the TBL Table error operation is emptied mysql>select*fromtbl; Emptyset (0.00 sec)

To recover data steps:

1, login MySQL, view the current Binlog file

1 2 3 4 5 6 7

mysql> Show Master status; +------------------+-----------+ | log_name     | File_size | +------------------+-----------+ | mysql-bin.000051 |&nbs p;   967 | | mysql-bin.000052 |    965 | +------------------+-----------+

2, the latest Binlog file is mysql-bin.000052, we reposition the binlog location of the misuse of SQL. The operator can only know the approximate error operation time, we filter the data according to the approximate time.

shell> python binlog2sql/binlog2sql.py-h127.0.0.1-p3306-uadmin-p ' admin '-dtest-ttbl--start-file= ' mysql-bin.000052 '--start-datetime= ' 2016-12-13 20:25:00 '--stop-datetime= ' 2016-12-13 20:30:00 ' Output: Insertinto ' test '. ' Tbl ' (' addtime ', ' id ', ' name ') VALUES (' 2016-12-13 20:25:00 ', 3, ' Sun '); #start 4end290time2016-12-13 20:25:46 Insertinto ' test '. ' Tbl ' (' addtime ', ' id ', ' name ') VALUES (' 2016-12-13 20:26:00 ', 4, ' Xiao Li '); #start 317end487time2016-12-13 20:26:26 UPDATE ' test ', ' tbl ' SET ' addtime ' = ' 2016-12-12 00:00:00 ', ' id ' =4, ' name ' = ' Xiao Li ' WHERE ' addtime ' = ' 2016-12-13 20:26:00 ' and ' Id ' =4and ' name ' = ' Xiao Li ' LIMIT 1; #start 514end701time2016-12-13 20:27:07 Deletefrom ' test ', ' tbl ' WHERE ' addtime ' = ' 2016-12-10 00:04:33 ' and ' id ' =1and ' name ' = ' Xiao Zhao ' LIMIT 1; #start 728end938time2016-12-13 20:28:05 Deletefrom ' test ', ' tbl ' WHERE ' addtime ' = ' 2016-12-10 00:04:48 ' and ' id ' =2and ' name ' = ' Penny ' LIMIT 1; #start 728end938time2016-12-13 20:28:05 Deletefrom ' test ', ' tbl ' WHERE ' addtime ' = ' 2016-12-13 20:25:00 ' and ' id ' =3and ' name ' = ' grandchild ' LIMIT 1; #start 728end938time2016-12-13 20:28:05 Deletefrom ' test ', ' tbl ' WHERE ' addtime ' = ' 2016-12-12 00:00:00 ' and ' id ' =4and ' name ' = ' Xiao Li ' LIMIT 1; #start 728end938time2016-12-13 20:28:05

3, we got the error of the operation of the SQL accurate location between 728-938, and further filtering based on location, using flashback mode to generate rollback SQL, check rollback SQL is correct

shell> python binlog2s Ql/binlog2sql.py-h127.0.0.1-p3306-uadmin-p ' admin '-dtest-ttbl--start-file= ' mysql-bin.000052 '--start-pos=3346-- End-pos=3556-b Output: insertinto ' test '. ' Tbl ' (' addtime ', ' id ', ' n Ame ') VALUES (' 2016-12-12 00:00:00 ', 4, ' Xiao Li '); #start 728end938time2016-12-13 20:28:05 insertinto ' test '. ' Tbl ' (' addtime ', ' id ', ' name ') VALUE S (' 2016-12-13 20:25:00 ', 3, ' Xiao Sun '); #start 728end938time2016-12-13 20:28:05 insertinto ' test '. ' Tbl ' (' addtime ', ' id ', ' name ') VALUE S (' 2016-12-10 00:04:48 ', 2, ' Penny '); #start 728end938time2016-12-13 20:28:05 insertinto ' test '. ' Tbl ' (' addtime ', ' id ', ' name ') VALUE S (' 2016-12-10 00:04:33 ', 1, ' Xiao Zhao '); #start 728end938time2016-12-13 20:28:05

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

shell> python binlog2sql.py-h127.0.0.1-p3306-uadmin-p ' admin '-dtest-ttbl--start-file= ' mysql-bin.000052 '-- start-pos=3346--end-pos=3556-b | Mysql-h127.0.0.1-p3306-uadmin-p ' admin ' mysql>select*fromtbl; +----+--------+---------------------+ | ID |name | Addtime | +----+--------+---------------------+ | 1 | Xiao Zhao | 2016-12-10 00:04:33 | | 2 | Pennies | 2016-12-10 00:04:48 | | 3 | Xiao Sun | 2016-12-13 20:25:00 | | 4 | Xiao Li | 2016-12-12 00:00: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, from the backup rollback, also support the DDL rollback table structure, the data is not rolling back ~ There is a practice called slave delayed backup, Do not add traffic slave, deliberately delay a period of time, this is actually on the basis of traditional methods to recover this step in addition to the example. This method consumes an additional machine, which we do not recommend.