MySQL数据恢复大法一

mysql 运维

来源：danfengcao
my.oschina.net/u/3119184/blog/802700

基本上每个跟数据库打交道的程序员（当然也可能是你同事）都会碰一个问题，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进行闪回。

git clone https://github.com/danfengcao/binlog2sql.git
pip install -r requirements.txt

然后，我们就可以生成回滚SQL了。

背景：误删了test库tbl表整张表的数据，需要紧急回滚。

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-10 00:04:51 |
| 4 | 小李 | 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)

tbl表被清空
mysql> select * from tbl;
Empty set (0.00 sec)

恢复数据步骤：

登录mysql，查看目前的binlog文件

mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000046 | 12262268 |
| mysql-bin.000047 | 3583 |
+------------------+-----------+

最新的binlog文件是mysql-bin.000047，我们再定位误操作SQL的binlog位置

$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047'
输出：
DELETE FROM test.tbl WHERE addtime='2016-12-10 00:04:33' AND id=1 AND name='小赵' LIMIT 1; #start 3346 end 3556
DELETE FROM test.tbl WHERE addtime='2016-12-10 00:04:48' AND id=2 AND name='小钱' LIMIT 1; #start 3346 end 3556
DELETE FROM test.tbl WHERE addtime='2016-12-10 00:04:51' AND id=3 AND name='小孙' LIMIT 1; #start 3346 end 3556
DELETE FROM test.tbl WHERE addtime='2016-12-10 00:04:56' AND id=4 AND name='小李' LIMIT 1; #start 3346 end 3556

生成回滚sql，并检查回滚sql是否正确

$ 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
输出：
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-10 00:04:56', 4, '小李'); #start 3346 end 3556
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-10 00:04:51', 3, '小孙'); #start 3346 end 3556
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 3346 end 3556
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 3346 end 3556

确认回滚sql正确，执行回滚语句。登录mysql确认，数据回滚成功。

$ 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 | 小赵 | 2016-12-10 00:04:33 |
| 2 | 小钱 | 2016-12-10 00:04:48 |
| 3 | 小孙 | 2016-12-10 00:04:51 |
| 4 | 小李 | 2016-12-10 00:04:56 |
+----+--------+---------------------+

至此，不用再担心被炒鱿鱼了。