How to quickly recover data after MySQL misoperation

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

This article mainly introduces the method of quickly recovering data after MySQL misoperation. Friends in need can refer to it

Abstract: Using binlog Flash back misoperation data.
Basically every programmer who works with databases (of course it may be your colleagues) will encounter a problem, how to quickly roll back after MySQL misoperation? For example, if you delete a table and forget to add restrictions, the entire table will be lost. If this is still the core business data of the online environment, then this matter will be a big deal. It is very important to be able to quickly roll back data after a misoperation.

Traditional solution

Use full backup to recreate the instance, and then use incremental binlog backup to restore to the state before the misoperation. Then skip the misoperation SQL and continue to use binlog. This method is time-consuming and labor-intensive and is not worth recommending.

Use binlog2sql to quickly flashback

First, confirm that your MySQL server has binlog turned on, 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 binlog is not turned on, there is no pre-generated response If you roll SQL, you really can't roll back quickly. For MySQL that stores important business data, it is strongly recommended to enable binlog.

Subsequently, install the open source tool binlog2sql. binlog2sql is a simple and easy-to-use binlog parsing tool. One of its functions 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 accidentally deleted the entire data in the tbl table of the test library at around 20 o'clock, and needed an emergency rollback.

test库tbl表原有数据
mysql> select * from tbl;
+----+--------+---------------------+
| id | name  | addtime       |
+----+--------+---------------------+
| 1 | 小赵  | 2016-12-10 00:04:33 |
| 2 | 小钱  | 2016-12-10 00:04:48 |
| 3 | 小孙  | 2016-12-13 20:25:00 |
| 4 | 小李  | 2016-12-12 00:00:00 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

mysql> delete from tbl;
Query OK, 4 rows affected (0.00 sec)

20:28时，tbl表误操作被清空
mysql> select * from tbl;
Empty set (0.00 sec)

Steps to restore data:

1. Log in to mysql and view the current binlog file

mysql> show master status;
+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000051 |    967 |
| mysql-bin.000052 |    965 |
+------------------+-----------+

2. The latest binlog file is mysql-bin.000052, let’s locate it again Misoperation of SQL binlog location. Misoperators can only know the approximate time of misoperation, and we filter the data based on 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'
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 4 end 290 time 2016-12-13 20:25:46
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26
UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; 
#start 514 end 701 time 2016-12-13 20:27:07
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

3. We got the exact location of the misoperation sql between 728-938, and then further filtered based on the location, used flashback mode to generate rollback sql, and checked whether the rollback sql was correct

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-pos=3346 --end-pos=3556 -B
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05

Confirm that the rollback sql is correct and execute the rollback statement. Log in to mysql to confirm that the data rollback is 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 * from tbl;
+----+--------+---------------------+
| id | name  | addtime       |
+----+--------+---------------------+
| 1 | 小赵  | 2016-12-10 00:04:33 |
| 2 | 小钱  | 2016-12-10 00:04:48 |
| 3 | 小孙  | 2016-12-13 20:25:00 |
| 4 | 小李  | 2016-12-12 00:00:00 |
+----+--------+---------------------+

At this point, you no longer have to worry about being fired.

FAQ

Some people may ask, how can I quickly roll back my DDL operation by mistake? For example, a large table is dropped.

It’s hard to do. Because even in row mode, DDL operations do not record changes in each row of data to the binlog, so DDL cannot be rolled back through the binlog. To implement DDL rollback, old data must be backed up before executing DDL. Someone has indeed achieved fast rollback of DDL by modifying the mysql server source code. I found Alibaba's xiaobin lin and submitted a patch. But as far as I know, very few domestic Internet companies have applied this feature. As for the reason, I think the main reason is that I am too lazy to bother with it. There is no need to engage in this low-frequency function. The secondary reason is that it will add some extra storage.

Therefore, DDL misoperation can generally only be restored through backup. If the company can no longer use backup, then it is really recommended to buy a plane ticket. What to do? Run

mysql In addition to binlog2sql, are there any other rollback tools?

Of course. Ali Peng Lixun added the flashback feature to mysqlbinlog. This should be the earliest flashback function in mysql. Peng solved the problem of DML rollback and explained the design idea of ​​using binlog for DML flashback. The DDL rollback feature was also proposed and implemented by the Alibaba team. These two functions are innovative, and the flashback tools that have appeared since then are basically imitations of the above two. In addition, Qunar's open source Inception is a set of MySQL automated operation and maintenance tools. This one is relatively heavy. It supports DML rollback, not from binlog, but from backup. It also supports DDL rollback table structure. Data cannot be rolled back~ There is another method called slave delayed backup, which involves setting up a slave that does not add business traffic and deliberately delaying it for a period of time. This actually eliminates the instance recovery step based on the traditional method. This method consumes an additional machine and is not recommended.

If there are any excellent tools and excellent articles related to mysql rollback that are missing, please let us know.

The above is the content of how to quickly recover data after MySQL misoperation. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!