server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog-format = row
随后,安装开源工具binlog2sql。binlog2sql是一款简单易用的binlog解析工具,其中一个功能就是生成回滚SQL。
shell> pip install -r requirements.txt
shell> pip install -r requirements.txt
+----+--------+---------------------+
| 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)
| Log_name | File_size |
+------------------+-----------+ | mysql-bin.000051 | 967 |
| mysql-bin.000052 | 965 | +------------------+-----------+
-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:05D
ELETE 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
-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
-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 |
所以,DDL误操作的话一般只能通过备份来恢复。如果公司连备份也不能用了,那真的建议去买张飞机票了。干啥?跑呗
这两个功能是有创新精神的,此后出现的闪回工具基本都是对上面两者的模仿。另外,去哪儿开源的Inception是一套MySQL自动化运维工具,这个就比较重了,支持DML回滚,还不是从binlog回滚的,是从备份回滚的,也支持DDL回滚表结构,数据是回滚不了滴~ 还有一种做法叫slave延时备份,搞台不加业务流量的slave,故意延迟一段时间,这其实是在传统办法的基础上去除了实例恢复这步。此法会额外消耗一台机器,我们不推荐这么做。
[1] 彭立勋, MySQL下实现闪回的设计思路
[2] Lixun Peng, Provide the flashback feature by binlog
[3] 丁奇, MySQL闪回方案讨论及实现
[4] xiaobin lin, flashback from binlog for MySQL
[5] 王竹峰, 去哪儿inception
[6] danfengcao, binlog2sql: Parse MySQL binlog to SQL you want