代码改变世界

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

2017-10-17 11:59 飘扬的红领巾 阅读(...) 评论(...) 编辑 收藏

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

前提条件

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

[mysqld]
server_id = 1
log_bin = bin
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

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

构造实验数据

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

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'liping', 'ADFASDFASDF', '2017-10-17 10:57:42');
INSERT INTO `user` VALUES ('2', 'xiaoming', 'SDDSFGSDFGSDG', '2017-10-17 10:58:06');
INSERT INTO `user` VALUES ('3', 'xiaohong', 'QEWFGFGDSFGSDG', '2017-10-17 10:58:29');

执行误操作

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

update user set `password`='123456';

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

W3R83DZK)BA1_GM4]RYBK52

数据恢复

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

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

show master status;

WOU`]BQ3O(P30R`%OGDF{ZI

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

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

P)[3D)$H3$@ERU1SCXQJJD2

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

binlog2sql工具

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

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

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

G_PH_5D$}2@Q(Q4@FJ`48D4

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

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

使用binlog2sql生成回滚SQL

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

image

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

image