MySQL误操作数据恢复的简单实践（r11笔记第67天）

前几天有个同事碰到了一个MySQL数据恢复的问题，他运行了一条update语句，结果忘记了加where条件，结果等反应过来已经晚了。我简单确认了下，是否存在备份，没有，是否开启了日志，没有。所以这个恢复无从谈起。

当然后来他也花了些功夫逐条数据修复，事情过去了，数据恢复的重要性，人为操作的重要性就不言而喻了，但是有些时间工作职责还是需要下移。我觉得还是需要好好总结下数据恢复的问题。我会从以下几个方面来谈。

⊙ 手工恢复数据的简单示例

⊙使用开源工具恢复数据的配置

⊙ 使用开源工具恢复数据的实践

⊙小结

首先手工恢复数据，其实有一些思路，一种就是通过全备+binlog的时间、偏移量来恢复。另外一类是通过解析binlog来恢复，前提条件是日志格式为row。我们来简单模拟解析binlog的恢复方式。

手工恢复数据的简单示例

先看一看binlog的情况，可以看到当前的binlog是序号为15的日志文件。

create table test (id int not null primary key,name varchar(20),memo
varchar(50)) ENGINE=InnoDB auto_increment=100 default charset=utf8;插入几条数据

> insert into test values(1,’name1′,’memo1′),(2,’name2′,’memo2′),(3,’name3′,’memo3′),(4,’name4′,’memo4′),(5,’name5′,’memo5′);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0查看一下数据的基本情况：

> delete from test where id in (1,3);
Query OK, 2 rows affected (0.01 sec)

> update test set memo=’new’ where id in(2,4);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

> insert into test values(6,’name6′,’memo6′);
Query OK, 1 row affected (0.00 sec)做完上面三个DML操作之后，我们标记一下时间。

> select current_timestamp();
+———————+
| current_timestamp() |
+———————+
| 2017-02-06 04:15:44 |
+———————+下面我们来解读一下binlog，根据时间戳得到一个基本可读的日志，里面还有这些数据变更，但是语句和执行的还是有一些出入，我们直接拷贝一份binlog到/tmp目录下解析。

mysqlbinlog –no-defaults -v –start-datetime=”2017-02-06 04:14:33″
–stop-datetime=”2017-02-06 04:15:44″ /tmp/mysql-bin.000016
–result-file=/tmp/result.sql生成的文件result.sql内容如下，可以看到这些操作在binlog中都有了很详细的标记，数据的情况基本都是一目了然，update的部分变化前变化后的数据都一览无余。其实DML中难度较大的就是update,而insert,delete就是一个加减法。

delete操作对应binlog日志中的SQL

### DELETE FROM `test`.`test`
### WHERE
### @1=1
### @2=’name1′
### @3=’memo1′
### DELETE FROM `test`.`test`
### WHERE
### @1=3
### @2=’name3′
### @3=’memo3′
# at 998969666update操作对应binlog日志中的SQL

### UPDATE `test`.`test`
### WHERE
### @1=2
### @2=’name2′
### @3=’memo2′
### SET
### @1=2
### @2=’name2′
### @3=’new’
### UPDATE `test`.`test`
### WHERE
### @1=4
### @2=’name4′
### @3=’memo4′
### SET
### @1=4
### @2=’name4′
### @3=’new’
# at 998971422

insert操作对应binlog日志中的SQL

### INSERT INTO `test`.`test`
### SET
### @1=6
### @2=’name6′
### @3=’memo6′
# at 998973859值得一提的是-v（–verbose）选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以使用-vv选项，这样可以包含一些数据类型和元信息的注释内容。

比如：

-vv的结果：

### DELETE FROM `test`.`test`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=’name1′ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @3=’memo1′ /* VARSTRING(150) meta=150 nullable=1 is_null=0 */回到数据恢复的问题，如果需要手工恢复就需要做几件事情，一个就是根据字段标示拼接出可运行的SQL语句，然后按照逆向的顺序执行即可。

从上面的步骤可以看到，如果手工修复其实还是可以实现的，不过手工的操作不少，这个时候能够简化你的工作就是好工具，我试用了下binglog2sql这个开源工具，也是大众点评的DBA团队推出的，总体还不错。

这个工具是Python开发，当然有一些依赖的库和环境需要配置。如果你的服务器是联网环境，那就省事多了。

两个步骤即可完成。

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt –会安装额外需要的插件而如果不是，那么就有一些额外的工作需要你去做。比如我这个环境连pip都没有。可以先在其他服务器上下载到对应的脚本，部署即可。

# wget “https://pypi.python.org/packages/source/p/pip/pip-1.5.4.tar.gz#md5=834b2904f92d46aaa333267fb1c922bb” –no-check-certificate件有些环境在pip部署的时候可能有下面的错误。

# python setup.py install
Traceback (most recent call last):
File “setup.py”, line 6, in
from setuptools import setup, find_packages
ImportError: No module named setuptools看来还和一个setuptools的库有关，我们继续安装。

# wget https://bootstrap.pypa.io/ez_setup.py –no-check-certificate然后使用python ez_setup.py install 即可编译成功setuptools

再次尝试python setup.py install即可完成pip的安装。

对于插件PyMySQL可以使用如下的方式来安装：

git clone https://github.com/PyMySQL/PyMySQL对于插件mysql replication可以使用如下的方式：

git clone https://github.com/noplay/python-mysql-replication就这样前期的工作就做好了。

使用开源工具恢复数据的实践

完成了环境的配置，工具使用起来和mysqlbinlog还是有一些相似之处，好的地方就是多了一些辅助功能。

我们创建一个用户admin来解析。

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘admin’@’127.0.0.1’ IDENTIFIED BY ‘admin’;比如我们使用如下的命令来解析binlog得到指定时间戳范围内的SQL情况，在此我们限定数据为test

python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uadmin -padmin
-dtest –start-file=’mysql-bin.000016′ –start-datetime=’2017-02-06
04:14:33′ –stop-datetime=’2017-02-06 04:15:44′ > /tmp/tmp.log得到的文件内容如下：

#cat /tmp/tmp.log
DELETE FROM `test`.`test` WHERE `memo`=’memo1′ AND
`id`=1 AND `name`=’name1′ LIMIT 1; #start 11127 end 11321 time
2017-02-06 04:15:23
DELETE FROM `test`.`test` WHERE `memo`=’memo3′
AND `id`=3 AND `name`=’name3′ LIMIT 1; #start 11127 end 11321 time
2017-02-06 04:15:23
UPDATE `test`.`test` SET `memo`=’new’, `id`=2,
`name`=’name2′ WHERE `memo`=’memo2′ AND `id`=2 AND `name`=’name2′ LIMIT
1; #start 11400 end 11625 time 2017-02-06 04:15:29
UPDATE
`test`.`test` SET `memo`=’new’, `id`=4, `name`=’name4′ WHERE
`memo`=’memo4′ AND `id`=4 AND `name`=’name4′ LIMIT 1; #start 11400 end
11625 time 2017-02-06 04:15:29
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES (‘memo6’, 6, ‘name6′); #start 12062 end 12239 time 2017-02-06 04:15:37其实看起来还是很省事了。

如果希望得到闪回的语句，有一个flashback的选项，其实就是在原来的基础上进行了解析和顺序调整。

python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uadmin -padmin
-dtest –flashback –start-file=’mysql-bin.000016′
–start-datetime=’2017-02-06 04:14:33′ –stop-datetime=’2017-02-06
04:15:44’ > /tmp/tmp.log得到的内容如下：

#cat /tmp/tmp.log
DELETE FROM `test`.`test` WHERE `memo`=’memo6′ AND
`id`=6 AND `name`=’name6′ LIMIT 1; #start 12062 end 12239 time
2017-02-06 04:15:37
UPDATE `test`.`test` SET `memo`=’memo4′, `id`=4,
`name`=’name4′ WHERE `memo`=’new’ AND `id`=4 AND `name`=’name4′ LIMIT 1;
#start 11400 end 11625 time 2017-02-06 04:15:29
UPDATE `test`.`test`
SET `memo`=’memo2′, `id`=2, `name`=’name2′ WHERE `memo`=’new’ AND
`id`=2 AND `name`=’name2′ LIMIT 1; #start 11400 end 11625 time
2017-02-06 04:15:29
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES (‘memo3’, 3, ‘name3’); #start 11127 end 11321 time 2017-02-06 04:15:23
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES (‘memo1’, 1, ‘name1’); #start 11127 end 11321 time 2017-02-06 04:15:23运行了如上的语句之后，再次查看数据，数据就恢复了正常。

对于DML的闪回其实还是有一些技巧可以参考，对于DDL的闪回相对来说就麻烦的多了。我们后续也会跟进这方面的工作。但是DML的闪回场景相对要多一些，我们尤其需要注意。

相关推荐: MySQL主从同步的简单理解

那么来讲一开发云主机域名下具体的工作原理，如图：相关推荐: 怎么写数据库的存储过程今天就跟大家聊聊有关怎么写数据库的存储过程，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。　　SQL语句需要先编译然后执行…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。