无备份情况下恢复MySQL误删的表

小编寄语

想必大家都知道,Oracle ACE李真旭(Roger)是国内最专业的Oracle 数据库恢复专家。但知识都是触类旁通,真正的专家,从来不会局限在一个方向上。今天分享的内容,是他在MySQL数据恢复上所做的尝试。

本文主要分享在没有备份的情况下,MySQL数据库如何恢复被删除的表。

包含两个主要的场景:

1、drop table后的恢复

2、truncate table后的恢复

正文:

我们都知道,MySQL Server都很多存储引擎,并不是每种都可以进行异常情况之下都恢复,比如drop table/tuncate table/delete table/update table /drop database又或者是ibdata文件损坏之类的。用的最多的就是Myisam和innodb存储引擎。目前基本上都是5.5+版本了,我想几乎没有人再去使用Myisam了吧。我这里所测试都5.6,5.7版本中默认都存储引擎已经是Innodb了。因此这里我以Innodb引擎为例子进行说明。

MySQL drop table

这里我们首先来测试innodb_file_per_table为off的情况,即表结构和数据存在同一个文件中。这里我分别测试了表存在主键和不存在主键的情况,供参考。

innodb_file_per_table参数为off(有主键的情况)

1创建测试表

2备份表结构

[root@killdb ~]# mysqldump --opt -d -uroot -proger recover test_drop0801 > /tmp/innodb_recovery/recover/test_drop0801.sql

3删除表

mysql> drop table test_drop0801; Query OK, 0 rows affected (0.00 sec)

4扫描数据文件

5创建用于恢复的数据字典

6查询需要恢复表的信息

7确认数据page中数据是否存在

8抽取page中的数据

9加载数据到数据库

我们可以看到,顺利完成了drop table的恢复,而且数据完好无损。实际上我这里还同时测试了无主键的情况,经过测试都类似,可以进行完美的恢复。这里不再累述。

MySQL truncate table

首先这里我要利用undrop_for_innodb 这个开源工具包(当然需要编译),目前该工具已经在2017年1月宣布闭源了,而且开始收费。但是我们仍然开源使用之前都开源工具包。另外这里可以告诉大家,不久的将来,odu 也会支持MySQL.

如下是我的truncate table 测试过程:

1创建测试表

2备份表结构

[root@killdb innodb_recovery]# mysqldump --opt -d -uroot -proger recover t_enmotech > /tmp/innodb_recovery/recover/t_enmotech.sql [root@killdb innodb_recovery]#

3truncate table

mysql> truncate table t_enmotech; Query OK, 0 rows affected (0.00 sec)

4获取数据字典

5扫描逻辑卷

6创建数据字典表

该工具包提供的recover_dictionary脚本会创建一个test数据库,并创建一些数据字典表供恢复查询使用。同时也会在当前目录创建dictionary目录,该目录下会存放数据字典信息。

7查询需要恢复的表的index_id信息

可以看到被truncate的表的index_id 为178,我们应该进一步从178 的page中获取数据。

8确认数据是否存在

9抽取page中的数据

抽取数据之前,必须提前准备好表的表结构,由于这里是truncate,因此表结构是存在的,很容易获取。我这里是测试,所以之前就备份了结构。

那么如果是drop table 呢? 实际上我们也可以通过该工具来恢复表结构。

10加载数据到mysql server

11验证数据

我们可以看到,被truncate 掉的数据被成功恢复了回来。

这里我测试的truncate table的场景,其实对于drop table、delete table 恢复方法均类似(已测试过)。另外,对于更为严重的drop database 其实也是可以进行恢复的。

当然,对于实际的生产库来讲,数据不一定能够恢复,因为有可能被覆盖而导致数据恢复不全。MySQL 对于空间的重用机制与Oracle 有很大区别,对于Oracle 而言,如果是delete的数据,还是很难被覆盖掉的,对于drop 和truncate 则领导别论。然而MySQL则有所不同,MySQL 默认会启动一些purge 进程来进行空间重用,这是MySQL 5.6的情况:

在MySQL 5.7 版本中更为坑爹,MySQL 默认会启动4个purge 线程,因此很容易就会导致空间被重用,最终导致数据无法恢复,如下是MySQL 5.7的purge相关参数:

因此,一旦你遭遇turncate table/drop table/delete /drop database等情况,建议立刻停止服务或者停止数据库,保留现场,以防止环境进一步恶化,最终导致数据无法恢复的情况出现。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-09-25

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

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

一个MySQL死锁问题的反思

很早之前我写过几篇关于MySQL死锁的分析,比如 换个角度看待MySQL死锁的一点简单认识 MySQL死锁的两个小案例 MySQL在RR隔离级别下的unique...

3638
来自专栏祝威廉

如何提高ElasticSearch 索引速度

这篇文章会讲述上面几个参数的原理,以及一些其他的思路。这些参数大体上是朝着两个方向优化的:

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

一则报警信息所折射出来的诸多问题(r9笔记第14天)

在主备库环境中,如果出现数据文件级的一些不一致,后期修复会很麻烦,所以这种情况可以提前规避,减少后期的隐患,我定制了一个数据库监控选项,即数据文件状态的检查。 ...

3548
来自专栏散尽浮华

分布式监控系统Zabbix-3.0.3-完整安装记录-新报微信报警(企业微信)

一般来说,Zabbix可以通过多种方式把告警信息发送到指定人,常用的有邮件,短信报警方式,但是现在越来越多的企业开始使用zabbix结合微信作为主要的告警方式,...

27110
来自专栏IT大咖说

TiDB 原理与实战|架构师实践日

摘要 本篇文章出自七牛云和 PingCAP 联合主办的架构师实践日上,来自 PingCAP 的开发工程师李霞分享的《 TiDB 原理与实战》的演讲,介绍了目前分...

4687
来自专栏逸鹏说道

SQL Server安全(3/11):主体和安全对象(Principals and Securables)

在保密你的服务器和数据,防备当前复杂的攻击,SQL Server有你需要的一切。但在你能有效使用这些安全功能前,你需要理解你面对的威胁和一些基本的安全概念。这篇...

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

巧用外部表备份历史数据(r5笔记第62天)

在很多的系统中,随着时间的推移,都会沉淀大量的历史数据。一般数据量达到一定程度都会考虑使用分区表来处理。根据业务规则,可能有些历史数据隔一段时间就需要做清理了,...

37712
来自专栏王硕

原 Postgres-X2 MPP部署试验

3916
来自专栏微信终端开发团队的专栏

微信 iOS SQLite 源码优化实践

本文将分享在 SQLite 源码上进行的多线程并发、I/O性能优化等,并介绍优化相关的 SQLite 原理。

5190
来自专栏IT大咖说

当Facebook创造的cassandra遇上饿了么

摘要 1、饿了么大数据为什么选择cassandra 2、 Cassandra的基本原理 3、饿了么cassandra实践 4、 Cassandra和大数据离线平...

5617

扫码关注云+社区

领取腾讯云代金券