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

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

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

目录

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

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

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

⊙小结

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

手工恢复数据的简单示例

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

> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000014 | 1073742219 |
| mysql-bin.000015 |  998953054 |
+------------------+------------+
2 rows in set (0.00 sec)

为了方便模拟,我们可以切换一下日志,flush logs之后得到的日志情况如下:

> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000015 | 999120424 |
| mysql-bin.000016 |      6722 |
+------------------+-----------+
2 rows in set (0.00 sec)

创建表test

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

查看一下数据的基本情况:

> select * from test;
+----+-------+-------+
| id | name  | memo  |
+----+-------+-------+
|  1 | name1 | memo1 |
|  2 | name2 | memo2 |
|  3 | name3 | memo3 |
|  4 | name4 | memo4 |
|  5 | name5 | memo5 |
+----+-------+-------+

为了测试方便,先标记一个时间戳

>  select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2017-02-06 04:14:33 |
+---------------------+

我们开始模拟DML的操作。

> 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 <module> 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 -h127.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 -h127.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

运行了如上的语句之后,再次查看数据,数据就恢复了正常。

> select *from test;
+----+-------+-------+
| id | name  | memo  |
+----+-------+-------+
|  1 | name1 | memo1 |
|  2 | name2 | memo2 |
|  3 | name3 | memo3 |
|  4 | name4 | memo4 |
|  5 | name5 | memo5 |
+----+-------+-------+
5 rows in set (0.00 sec)

小结

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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-02-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏我和PYTHON有个约会

数据库连接引擎那点事儿

天长,地久。天地之所以能长且久者,以其不自生也,故能长生。是以圣人后其身而身先,外其身而身存,非以其无私邪?故能成其私。——老子

882
来自专栏CaiRui

Mysql-4-数据库的基本操作

1.创建数据库 create database database_name; 例:create database aa; show create databas...

1967
来自专栏运维

zabbix2.4.5迁移到zabbix3.0

http://qicheng0211.blog.51cto.com/3958621/1744603

2133
来自专栏逸鹏说道

SQL Server 执行计划缓存

概述 了解执行计划对数据库性能分析很重要,其中涉及到了语句性能分析与存储,这也是写这篇文章的目的,在了解执行计划之前先要了解一些基础知识,所以文章前面会讲一些...

3889
来自专栏Java面试通关手册

MySQL常见的两种存储引擎:MyISAM与InnoDB的爱恨情仇

Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_G...

2297
来自专栏哎_小羊

私信基本功能数据库设计

上一篇写了基于resin4.0+websocket实现私信功能服务端消息推送文章,趁热打铁,在写一篇关于私信功能的数据库设计文章,非代码篇,希望想对第一次做设计...

4077
来自专栏匠心独运的博客

数据库技术之记一次排查DB死锁的分析与思考

虽然很多童鞋在学数据库课程时都了解数据库隔离级别、死锁和事务等概念,但在测试/线上环境遇到死锁却不一定能够及时分析并解决这类问题。本文主要以作者在测试环境中遇到...

1991
来自专栏Java技术交流群809340374

MySQL有哪些存储引擎,各自的优缺点,应用场景

经常面试都会问到MYSQL有哪些存储引擎,以及各自的优缺点。今天主要分享常见的存储引擎:MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB...

1973
来自专栏杨建荣的学习笔记

MySQL query rewrite插件简单测试

在机场继续努力一把,学习了下MySQL query rewrite这个插件,感觉还不错,j简答测试了下,已经找到Oracle FGA的影子了。

1612
来自专栏Linyb极客之路

MySQL锁

  MySQL的锁机制,就是数据库为了保证数据的一致性而设计的面对并发场景的一种规则。

861

扫码关注云+社区

领取腾讯云代金券