使用binlog2sql做数据恢复的简单示例 侵删

有时我们会遇到操作人员误删或者误更新数据的情况，这时我们迫切希望把原来的数据还原回来，今天我们介绍一个简单的工具来方便的实现此功能。

前提条件

在实现数据恢复之前，需要我们的 MySQL 满足以下配置条件：

1. [mysqld]

2. server_id = 1

3. log_bin = bin

4. max_binlog_size = 1G

5. binlog_format = row

6. binlog_row_image = full

以上配置都是 MySQL 的常规配置，不存在特殊配置的情况，所以你其实不用太担心。

构造实验数据

接下来我们建一个简单的 user 表，并插入示例数据。

1. CREATE TABLE `user` (

2. `id` int(11) NOT NULL AUTO_INCREMENT,

3. `username` varchar(255) DEFAULT NULL,

4. `password` varchar(255) DEFAULT NULL,

5. `create_date` datetime DEFAULT NULL,

6. PRIMARY KEY (`id`)

7. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

8.  

9. -- ----------------------------

10. -- Records of user

11. -- ----------------------------

12. INSERT INTO `user` VALUES ('1', 'liping', 'ADFASDFASDF', '2017-10-17 10:57:42');

13. INSERT INTO `user` VALUES ('2', 'xiaoming', 'SDDSFGSDFGSDG', '2017-10-17 10:58:06');

14. INSERT INTO `user` VALUES ('3', 'xiaohong', 'QEWFGFGDSFGSDG', '2017-10-17 10:58:29');

执行误操作

接下来如果操作人员执行了一个误操作，试图修改 liping 的密码为 123456, 但忘记了加 where 条件，即执行了 SQL：

1. update user set `password`='123456';

导致所有的用户密码都被设置为了 123456。

数据恢复

接下来我们将尝试恢复原来的数据，首先因为我们开启了二进制日志，所以其实我们的每次操作都被记录到了二进制日志当中，我们可以使用二进制查看命令，查询到我们刚才所做的操作痕迹。

首先定位到当前 MySQL 写到了哪个二进制文件中，进入到 MySQL 执行命令：

1. show master status;

可以看到当前的二进制文件写到了 bin.000002。于是我们用二进制文件查看命令，找出刚才操作的痕迹。（请注意二进制文件的路径，修改为你的二进制文件路径）

1. mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /mdata/mysql_data/bin.000002 | grep -B 15 '123456'

从上图可以看出二进制文件记录了我们对每行数据修改的痕迹，其中 @1 @2 @3 @4 表示我们 user 表中的 4 个字段，看到这些记录你应该可以想到，如果我们把这些记录逆向执行，这数据就可以恢复。所以这里提供了一个思路，就是可以通过文本操作，把 update set 前后的字符串调换，保存成 SQL，即可实现数据还原，但真正实现起来还是挺麻烦的。这里我们介绍 binlog2sql 工具，你不用那么麻烦的去实现此功能。

binlog2sql 工具

binlog2sql 是大众点评开源的一款用于解析 binlog 的工具，详见：https://github.com/danfengcao/binlog2sql

使用 binlog2sql 将二进制文件转换为 SQL：

1. python binlog2sql.py -h10.89.1.143 -P3306 -udev -p'123456' -dtest -t user --start-file='bin.000002'

使用 binlog2sql 将二进制文件解析为了 SQL 格式，这个文件当中包括我们之前做的建表 SQL 以及插入示例数据的 SQL，当然也包括我们误操作的 UPDATE 语句。

这里我们重点看黄色框包含的内容，它是我们 update 语句记录在二进制文件当中的起止位置，我们可以看到三条 UPDATE 语句都记录到：#start 2616 end 3028，所以我们待会恢复时，只需要恢复这个位置的数据就可以了。

使用 binlog2sql 生成回滚 SQL

1. python binlog2sql.py --flashback -h10.89.1.143 -P3306 -udev -p'123456' -dtest -tuser --start-file='bin.000002' --start-position=2616 --stop-position=3028

可以看到 binlog2sql 生成了：#start 2616 end 3028 位置的回滚 SQL。我们把这个 SQL 复制下来到 MySQL 中执行，即可实现数据恢复。

-END-