How to recover data quickly after MySQL error! ! ~

Basically every programmer who works with the database (or perhaps your colleague) will have a problem, how do I get back quickly after the MySQL error? For example, delete a table, forget to add restrictions, the whole table is gone. If this were the core business data of the online environment, it would be a big problem. It is important to be able to roll back data quickly after misoperation.

Traditional Solutions

Use a full-scale backup to re-build the instance, and then use the incremental Binlog backup to revert to the state before the misoperation. Then skip the wrong SQL and continue to apply Binlog. This method is laborious and not worth recommending.

Fast Flash back with Binlog2sql

First, verify that your MySQL server has the Binlog set up with the following parameters:

[Mysqld]

Server-id = 1

Log_bin = /var/log/mysql/mysql-bin. Log

Max_binlog_size = 1000M

Binlog-format = row

If Binlog is not turned on, and there is no pre-generated rollback SQL, it is really not possible to recover the data quickly. For MySQL, which stores important business data, it is highly recommended to turn on Binlog.

Then, install the Open Source Tool binlog2sql. Binlog2sql is an easy-to-use Binlog parsing tool, one of which is to use Binlog for Flash back.

Git clone HTTPS://github.com/danfengcao/binlog2sql.git

Pip Install -r requirements. TXT

Then we can generate a rollback of SQL.

Background: The data of the entire table of the test database TBL table is deleted by mistake and needs urgent rollback.

Test Library TBL Table legacy data

MySQL> Select * from tbl;

+----+--------+---------------------+

| ID | name | addtime |

+----+--------+---------------------+

| 1 | Xiao Zhao | -12-10 00:04:33 |

| 2 | Penny | -12-10 00:04:48 |

| 3 | Xiao Sun | -12-10 00:04:51 |

| 4 | Xiao Li | -12-10 00:04:56 |

+----+--------+---------------------+

4 rows in set (0.00 sec)

MySQL> delete from tbl;

Query OK, 4 rows affected (0.00 sec)

The TBL table is emptied

MySQL> Select * from tbl;

Empty Set (0.00 sec)

To recover data steps:

Log in to MySQL to view the current Binlog file

MySQL> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

|  MySQL-bin.000046 | 12262268 |

| MySQL-bin.       000047 | 3583 |

+------------------+-----------+

The latest Binlog file is mysql-bin.000047, and we reposition the binlog location of the mis-operated SQL

$ python binlog2sql/binlog2sql.py-h 127.0.0.1-p 3306-u admin-p ' admin '-dtest-ttbl--start-file= ' mysql-bin.00 0047 '

Output:

DELETE from ' test '. ' tbl ' WHERE 'addtime ' =' 2016-12-10 00:04:33 ' and ' Id ' =1 and ' name ' =' Xiao Zhao ' LIMIT 1; #start 3346 End 3556

DELETE from ' test '. ' tbl ' WHERE 'addtime ' =' 2016-12-10 00:04:48 ' and ' id ' =2 and ' name ' =' Penny ' LIMIT 1; #start 3346 End 3556

DELETE from ' test '. ' tbl ' WHERE 'addtime ' =' 2016-12-10 00:04:51 ' and ' id ' =3 and ' name ' =' little grandchild ' LIMIT 1; #start 3346 End 3556

DELETE from ' test '. ' tbl ' WHERE 'addtime ' =' 2016-12-10 00:04:56 ' and ' id ' =4 and ' name ' =' Xiao Li ' LIMIT 1; #start 3346 End 3556

Generate rollback SQL and check that rollback SQL is correct

$ python binlog2sql/binlog2sql.py-h 127.0.0.1-p 3306-u admin-p ' admin '-dtest-ttbl--start-file= ' Mysql-bin. 000047 '--start-pos=3346--end-pos=3556-b

Output:

INSERT into ' test '. ' Tbl ' ('addtime ', ' id ', ' name ') VALUES (' 2016-12-10 00:04:56 ', 4, ' Xiao Li '); #start 3346 End 3556

INSERT into ' test '. ' Tbl ' ('addtime ', ' id ', ' name ') VALUES (' 2016-12-10 00:04:51 ', 3, ' Xiao Sun '); #start 3346 End 3556

INSERT into ' test '. ' Tbl ' ('addtime ', ' id ', ' name ') VALUES (' 2016-12-10 00:04:48 ', 2, ' Penny '); #start 3346 End 3556

INSERT into ' test '. ' Tbl ' ('addtime ', ' id ', ' name ') VALUES (' 2016-12-10 00:04:33 ', 1, ' Xiao Zhao '); #start 3346 End 3556

Verify that the rollback SQL is correct and the rollback statement is executed. Log in to MySQL to confirm that the data rollback was successful.

$ python binlog2sql.py-h 127.0.0.1-p 3306-u admin-p ' admin '-dtest-ttbl--start-file= ' mysql-bin.000047 '--start-p os=3346--end-pos=3556-b | Mysql-h127.0.0.1-p 3306-u admin-p ' admin '

MySQL> Select * from tbl;

+----+--------+---------------------+

| ID | name | addtime |

+----+--------+---------------------+

| 1 | Xiao Zhao | -12-10 00:04:33 |

| 2 | Penny | -12-10 00:04:48 |

| 3 | Xiao Sun | -12-10 00:04:51 |

| 4 | Xiao Li | -12-10 00:04:56 |

+----+--------+---------------------+

At this point, restore OK, no longer worry about being fired.

FAQ

• Some people will ask, I DDL mistakenly operation how fast rollback? Like the drop of a big watch.

  It's hard to do. Because DDL operations do not log changes to Binlog per row of data even in row mode, the DDL cannot be rolled back through Binlog. To implement DDL rollback, you must back up old data before executing the DDL. Someone did a quick rollback of the DDL by modifying the MySQL server source, and I found Ali's Xiaobin Lin submitting a patch. But as far as I know, few internet companies in China have applied this feature. Reasons, I think the main or lazy to toss, there is no need to engage in this low-frequency function, the secondary reason is to add some additional storage.

  As a result, DDL misuse can generally only be restored by backup. If the company doesn't even have a backup, it's really a good idea to buy a plane ticket. What are you doing? Run.

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

  Of course. Ali Peng Lixun to Mysqlbinlog added flashback characteristics, this should be the earliest flashback function of MySQL, Peng solves the DML rollback, and illustrates the use of Binlog for DML flash back design ideas. The DDL rollback feature is also presented and implemented by the Ali team. These two features are innovative, and the flashback tools that followed are basically imitations of the two above. In addition, where to go Open source inception is a set of MySQL Automation Operations tool, this is more heavy, support DML rollback, not from the binlog rollback, is from the backup rollback, also supports the DDL rollback table structure, data is not a drop back-there is a practice called slave delay Backup, Do not add business traffic slave, deliberately delayed for a period of time, which is actually on the basis of traditional methods in addition to the example of recovery this step. This method consumes an additional machine, which we do not recommend.

How to recover data quickly after a MySQL error! ! ~