Almost every programmer working with databases (or perhaps your colleagues) will encounter this issue: how to quickly roll back after an accidental operation in MySQL? For example, deleting a table without adding a condition, resulting in the entire table being wiped out. If this happens in a production environment with critical business data, it could lead to major problems. Being able to quickly roll back data after a mistake is crucial.
Using binlog2sql for Quick Rollback
First, ensure that your MySQL server has binlog enabled 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 enabled and no rollback SQL has been pre-generated, it will not be possible to quickly roll back. For MySQL databases storing critical business data, it is strongly recommended to enable binlog.
Next, install the open-source tool binlog2sql. Binlog2sql is a simple and easy-to-use binlog parsing tool, one of its features being the ability to generate rollback SQL.
git clone https://github.com/danfengcao/binlog2sql.git pip install -r requirements.txt
Once installed, you can use it to generate rollback SQL.
Example: Emergency Rollback to Restore MySQL Database
Background: Accidentally deleted all data from the tbl table in the test database. An urgent rollback is required.
Original Data in the tbl Table of the test Database mysql> select * from tbl; +----+--------+---------------------+ | id | name | addtime | +----+--------+---------------------+ | 1 | John | 2016-12-10 00:04:33 | | 2 | Mike | 2016-12-10 00:04:48 | | 3 | Emma | 2016-12-10 00:04:51 | | 4 | Lily | 2016-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 Was Emptied** mysql> select * from tbl; Empty set (0.00 sec)
Start to rollback.
1. Log in to MySQL and check the current binlog files.
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000046 | 12262268 | | mysql-bin.000047 | 3583 | +------------------+-----------+
2. Identify the binlog file containing the accidental SQL operation.
For instance, the latest binlog file is mysql-bin.000047. Locate the binlog position where the operation occurred.
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047'
Output：
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='John' LIMIT 1; #start 3346 end 3556
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='Mike' LIMIT 1; #start 3346 end 3556
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:51' AND `id`=3 AND `name`='Emma' LIMIT 1; #start 3346 end 3556
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:56' AND `id`=4 AND `name`='Lily' LIMIT 1; #start 3346 end 3556
3. Generate the rollback SQL and carefully review it for correctness.
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -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, 'Lily'); #start 3346 end 3556
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:51', 3, 'Emma'); #start 3346 end 3556
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, 'Mike'); #start 3346 end 3556
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, 'John'); #start 3346 end 3556
4. Once confirmed, execute the rollback SQL. Log in to MySQL and verify that the data has been successfully rolled back.
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047' --start-pos=3346 --end-pos=3556 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin' mysql> select * from tbl; +----+--------+---------------------+ | id | name | addtime | +----+--------+---------------------+ | 1 | John | 2016-12-10 00:04:33 | | 2 | Mike | 2016-12-10 00:04:48 | | 3 | Emma | 2016-12-10 00:04:51 | | 4 | Lily | 2016-12-10 00:04:56 | +----+--------+---------------------
