描述
基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了。假如这还是线上环境核心业务数据,那这事就闹大了。误操作后,能快速回滚数据是非常重要的。
传统解法
用全量备份重搭实例,再利用增量binlog备份,恢复到误操作之前的状态。然后跳过误操作的SQL,再继续应用binlog。此法费时费力,不值得再推荐。
利用binlog2sql快速闪回
首先,确认你的MySQL server开启了binlog,设置了以下参数:
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1000M
binlog-format = row
如果没有开启binlog,也没有预先生成回滚SQL,那真的无法快速恢复数据了。对存放重要业务数据的MySQL,强烈建议开启binlog。
随后,安装开源工具binlog2sql。binlog2sql是一款简单易用的binlog解析工具,其中一个功能就是利用binlog进行闪回。
条件python2.7以上,并且有安装pip-python
git clone https://github.com/danfengcao/binlog2sql.git
pip install -r requirements.txt
然后,我们就可以生成回滚SQL了。
背景:误删了test库tb1表整张表的数据,需要紧急回滚。
test库tb1表原有数据
mysql> select * from tb1;
+------+--------+
| id | name |
+------+--------+
| 2 | huijun |
| 1 | xinle |
+------+--------+
4 rows in set (0.00 sec)
mysql> delete from tb1;
Query OK, 2 rows affected (0.00 sec)
tb1表被清空
mysql> select * from tb1;
Empty set (0.00 sec)
恢复数据步骤:
登录mysql,查看目前的binlog文件
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 12262268 |
+------------------+-----------+
最新的binlog文件是mysql-bin.000001,我们再定位误操作SQL的binlog位置
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttb1 --start-file='mysql-bin.000001'
输出:
DELETE FROM `test`.`tb1` WHERE `id`=1 AND `name`='xinle' LIMIT 1; #start 1511 end 1690 time 2016-12-14 15:21:47
DELETE FROM `test`.`tb1` WHERE `id`=2 AND `name`='huijun' LIMIT 1; #start 1511 end 1690 time 2016-12-14 15:21:47
生成回滚sql,并检查回滚sql是否正确
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttb1 --start-file='mysql-bin.000047' --start-pos=1511 --end-pos=1690 -B
输出:
INSERT INTO `test`.`tb1`(`id`, `name`) VALUES (2, 'huijun'); #start 1511 end 1690 time 2016-12-14 15:21:47
INSERT INTO `test`.`tb1`(`id`, `name`) VALUES (1, 'xinle'); #start 1511 end 1690 time 2016-12-14 15:21:47
确认回滚sql正确,执行回滚语句。登录mysql确认,数据回滚成功。
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttb1 --start-file='mysql-bin.000047' --start-pos=1511 --end-pos=1690 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'
mysql> select * from tb1;
+------+--------+
| id | name |
+------+--------+
| 2 | huijun |
| 1 | xinle |
+------+--------+
至此,不用再担心被炒鱿鱼了。
常见问题
有人会问,我DDL误操作了怎么快速回滚?比如drop了一张大表。
很难做到。因为即使在在row模式下,DDL操作也不会把每行数据的变化记录到binlog,所以DDL无法通过binlog回滚。实现DDL回滚,必须要在执行DDL前先备份老数据。确实有人通过修改mysql server源码实现了DDL的快速回滚,我找到阿里的xiaobin lin提交了一个patch。但据我所知,国内很少有互联网公司应用了这个特性。原因的话,我认为最主要还是懒的去折腾,没必要搞这个低频功能,次要原因是会增加一些额外存储。
所以,DDL误操作的话一般只能通过备份来恢复。如果公司连备份也不能用了,那真的建议去买张飞机票了。干啥?跑呗