学习
实践
活动
工具
TVP
写文章
专栏首页数据和云无备份情况下恢复MySQL误删的表

无备份情况下恢复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等情况,建议立刻停止服务或者停止数据库,保留现场,以防止环境进一步恶化,最终导致数据无法恢复的情况出现。

文章分享自微信公众号:
数据和云

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

作者:李真旭
原始发表时间:2017-09-25
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • MYSQL无备份情况下恢复误删除的user权限表

    前几天客户反馈,误删除了权限表,导致无法连接到实例中了,但是又没有备份,咨询要怎么去恢复;

    SEian.G
  • SQL Server 2008无备份误删数据的恢复

    系统已上线,给客户修改bug的时候,使用delete语句删表数据,没想到库没切换成测试库。误删了SQL Server正式库的数据,而且一次备份都没有做过,玩大了...

    星哥玩云
  • Mysql误删表中数据与误删表的恢复方法

    当时想了一下,因为博主没有遇到过这个问题,但是也多少了解一些,所以就回答通过mysql的binlog日志进行恢复。

    用户1685462
  • 偷梁换柱 | 无备份情况下的数据恢复实践

    在实际环境中,许多数据库环境并没有做好完整的数据备份恢复计划及容灾方案,无法保证数据安全,并且出现一些灾难性的错误。那么我们就面临这样的问题:在什么样的最极端情...

    数据和云01
  • 偷梁换柱 | 无备份情况下的数据恢复实践

    在实际环境中,许多数据库环境并没有做好完整的数据备份恢复计划及容灾方案,无法保证数据安全,并且出现一些灾难性的错误。那么我们就面临这样的问题:在什么样的最极端情...

    数据和云
  • 偷梁换柱 | 无备份情况下的数据恢复实践(二)

    在数据恢复实践(一)中,我们了解到在 Windows 的11.2.0.1的环境中恢复过程比较顺利,那么接下来的测试,是我遇到更加复杂的情况:系统表空间保护两个不...

    数据和云01
  • MySQL Case-在线表误删除恢复

    今天晚上业务系统升级,提前跑到表结构中,表需要减字段,在执行drop colum时报如下错误:

    姚崇
  • 从Mysql备份中恢复单个表

    因为云平台的备份是把库中所有的表都打包成一个 .sql文件,然而这一个.sql文件大约有20G,现阶段的方法是把.sql文件source到数据库数据处理机器上,...

    行 者
  • MYSQL 8.0 XTRABACKUP 备份压缩与部分表恢复

    MYSQL 8.0后,XTRABACKUP 的参数有哪些变化,如果是通过XTRABACKUP 来备份那些参数的意义在哪里,是不是需要进行调整

    AustinDatabases
  • mysql的备份及恢复

    我们试着想一想, 在生产环境中什么最重要?如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢?这可能是最恐怖的事情了吧...

    用户4877748
  • mysql 找回误删表的数据办法

    有备份的话很简单,只需要生成一个最近备份的数据 然后用mysqlbinlog找回备份时间点之后的数据 再恢复到现网即可。

    似水的流年
  • 只读表空间的备份与恢复

    --====================== --  只读表空间的备份与恢复 --====================== 一、只读表空间的特性...

    Leshami
  • Oracle 误删除表的几种恢复方法

    在删除Oracle数据的时候不小心,把delete语句执行错了,把别的表给delete,而且还执行了commit!那么如何进行恢复呢,下面介绍几种恢复方法。 第...

    星哥玩云
  • mysql 找回误删表的数据办法

    有备份的话很简单,只需要生成一个最近备份的数据 然后用mysqlbinlog找回备份时间点之后的数据 再恢复到现网即可。 要是没有备份 可能就会比较麻...

    似水的流年
  • MySQL的数据备份与恢复

    备份就是在数据库发生宕机的情况下保证数据不丢失,或者最小程度丢失的解决方法。Mysql 提供了 mysqldump、ibbackup、replication 工...

    晚上没宵夜
  • mysql 找回误删表的数据办法

    有备份的话很简单,只需要生成一个最近备份的数据 然后用mysqlbinlog找回备份时间点之后的数据 再恢复到现网即可。

    似水的流年
  • oracle数据库误删的表以及表中记录的恢复

    oracle数据库误删的表以及表中记录的恢复 ...

    Java架构师必看
  • MySQL用全库备份数据恢复单表数据

    备份数据库时,采用了全库备份,但是因为某些原因需要回滚一个表的数据到备份数据库上,如果回滚整个库就比较费时间,因为可能这个表只有几十M,但是其它表可能有十几上百...

    星哥玩云
  • Mysql-12-mysql的备份和恢复

    1.数据库的备份   备份=拷贝加管理,防止数据的丢失同时记录用户的操作记录。其中最有效的备份是对IT架构进行备份。 原则: (1)数据库要定期备份,备份的周期...

    用户1173509

扫码关注腾讯云开发者

领取腾讯云代金券