使用undrop-for-innodb工具在无备份的情况下恢复mysql表

栏目: 数据库 · 发布时间: 6年前

内容简介：最近一个朋友说，程序误删了一张表，但是没有备份。我一心想，这不是凉凉了。因为按照正常的情况，如果误删了表，在有备份的情况下，可以通过备份+binlog的方式找回数据。如果没有备份的话，想找回数据基本是没什么希望了。本着死马当活马医的态度，在网上google了一阵，发现了竟然还有undrop-for-innodb这一个工具。undrop-for-innodb工具按照描述，是可以实现在无备份的情况下，通过扫描ibdata1文件和磁盘文件，来找回删除表的数据。话不多说，立马实验一下。使用undrop-for-i

背景

最近一个朋友说，程序误删了一张表，但是没有备份。我一心想，这不是凉凉了。因为按照正常的情况，如果误删了表，在有备份的情况下，可以通过备份+binlog的方式找回数据。如果没有备份的话，想找回数据基本是没什么希望了。本着死马当活马医的态度，在网上google了一阵，发现了竟然还有undrop-for-innodb这一个工具。

undrop-for-innodb工具按照描述，是可以实现在无备份的情况下，通过扫描ibdata1文件和磁盘文件，来找回删除表的数据。话不多说，立马实验一下。

恢复步骤

使用undrop-for-innodb工具分两种情况，分别是innodb_file_per_table=OFF和innodb_file_per_table=ON两种情况。innodb_file_per_table参数描述了innodb存储引擎存储数据的方式。如果为OFF，则库表的所有数据都存放在ibdata*的共享表空间内，若为ON，则每个表会生成独立的文件来存储数据。

在如今的 mysql 版本中，innodb_file_per_table参数默认为ON，于是这里来重点讲一下innodb_file_per_table=ON的这种情况。

一：安装undrop-for-innodb工具。

# git clone https://github.com/twindb/undrop-for-innodb.git 
# yum install -y make gcc flex bison
# cd undrop-for-innodb
# make

安装完之后，会在undrop-for-innodb目录下生成stream_parser和c_parser文件。

二：模拟mysql表误删操作：

这里我有一个iceny.assets_host_auth，里面存放了一些我自己系统的用户信息，如下：

使用undrop-for-innodb工具在无备份的情况下恢复mysql表

删除assets_host_auth表：

mysql> drop table assets_host_auth;
Query OK, 0 rows affected (0.01 sec)

三，扫描磁盘页以及ibdata文件

# df -k
Filesystem              1K-blocks    Used Available Use% Mounted on
/dev/mapper/centos-root  19703808 6701800  13002008  35% /
devtmpfs                   922464       0    922464   0% /dev
tmpfs                      933524       0    933524   0% /dev/shm
tmpfs                      933524   16952    916572   2% /run
tmpfs                      933524       0    933524   0% /sys/fs/cgroup
/dev/sda1                  201380  123252     78128  62% /boot
tmpfs                      186708       0    186708   0% /run/user/0
# ./stream_parser -f /dev/mapper/centos-root -t 19703808k                              
Opening file: /dev/mapper/centos-root
File information:
ID of device containing file:            5
inode number:                         2006
protection:                          60660 (block device)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       6
device ID (if special file):         64768
blocksize for filesystem I/O:         4096
number of blocks allocated:              0
time of last access:            1533986548 Sat Aug 11 07:22:28 2018
time of last modification:      1533984737 Sat Aug 11 06:52:17 2018
time of last status change:     1533984737 Sat Aug 11 06:52:17 2018
total size, in bytes:                    0 (0.000 exp(+0))
Size to process:               20176699392 (18.791 GiB)
Opening file: /dev/mapper/centos-root
File information:
ID of device containing file:            5
inode number:                         2006
protection:                          60660 (block device)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       6
device ID (if special file):         64768
blocksize for filesystem I/O:         4096
number of blocks allocated:              0
time of last access:            1533986548 Sat Aug 11 07:22:28 2018
time of last modification:      1533984737 Sat Aug 11 06:52:17 2018
time of last status change:     1533984737 Sat Aug 11 06:52:17 2018
total size, in bytes:                    0 (0.000 exp(+0))
Size to process:               20176699392 (18.791 GiB)
Worker(0): 1.16% done. 2018-08-13 20:20:29 ETA(in 00:06:06). Processing speed: 25.949 MiB/sec
Worker(1): 1.16% done. 2018-08-13 20:22:01 ETA(in 00:07:37). Processing speed: 20.766 MiB/sec
Worker(0): 2.24% done. 2018-08-13 20:20:29 ETA(in 00:06:02). Processing speed: 25.949 MiB/sec
Worker(1): 2.24% done. 2018-08-13 20:25:05 ETA(in 00:10:34). Processing speed: 14.828 MiB/sec
Worker(0): 3.32% done. 2018-08-13 20:23:30 ETA(in 00:08:57). Processing speed: 17.300 MiB/sec
Worker(1): 3.32% done. 2018-08-13 20:20:33 ETA(in 00:05:58). Processing speed: 25.957 MiB/sec
Worker(0): 4.40% done. 2018-08-13 20:22:01 ETA(in 00:07:23). Processing speed: 20.760 MiB/sec
Worker(1): 4.40% done. 2018-08-13 20:20:33 ETA(in 00:05:54). Processing speed: 25.949 MiB/sec
Worker(0): 5.48% done. 2018-08-13 20:20:32 ETA(in 00:05:50). Processing speed: 25.949 MiB/sec
Worker(1): 5.48% done. 2018-08-13 20:22:02 ETA(in 00:07:18). Processing speed: 20.759 MiB/sec
Worker(0): 6.56% done. 2018-08-13 20:22:00 ETA(in 00:07:13). Processing speed: 20.759 MiB/sec
Worker(1): 6.56% done. 2018-08-13 20:20:34 ETA(in 00:05:46). Processing speed: 25.949 MiB/sec
Worker(0): 7.64% done. 2018-08-13 20:20:33 ETA(in 00:05:42). Processing speed: 25.949 MiB/sec
Worker(1): 7.64% done. 2018-08-13 20:20:34 ETA(in 00:05:42). Processing speed: 25.949 MiB/sec
Worker(0): 8.71% done. 2018-08-13 20:19:07 ETA(in 00:04:13). Processing speed: 34.616 MiB/sec
Worker(0): 9.80% done. 2018-08-13 20:16:18 ETA(in 00:01:23). Processing speed: 104.000 MiB/sec
Worker(1): 8.71% done. 2018-08-13 20:19:08 ETA(in 00:04:13). Processing speed: 34.599 MiB/sec
Worker(0): 11.21% done. 2018-08-13 20:15:58 ETA(in 00:01:02). Processing speed: 136.000 MiB/sec
Worker(1): 9.79% done. 2018-08-13 20:19:08 ETA(in 00:04:10). Processing speed: 34.599 MiB/sec
Worker(0): 12.29% done. 2018-08-13 20:20:25 ETA(in 00:05:25). Processing speed: 25.953 MiB/sec
Worker(1): 10.87% done. 2018-08-13 20:19:08 ETA(in 00:04:07). Processing speed: 34.599 MiB/sec
Worker(1): 11.95% done. 2018-08-13 20:19:08 ETA(in 00:04:04). Processing speed: 34.605 MiB/sec
Worker(0): 13.37% done. 2018-08-13 20:23:07 ETA(in 00:08:01). Processing speed: 17.299 MiB/sec
Worker(1): 13.03% done. 2018-08-13 20:19:08 ETA(in 00:04:01). Processing speed: 34.599 MiB/sec
# ./stream_parser -f /opt/data/mysql_3306/ibdata1

扫描完之后，会在当前目录下生成相关的目录：

使用undrop-for-innodb工具在无备份的情况下恢复mysql表

四，找出assets_host_auth表数据所在的磁盘页。

1，找出assets_host_auth的表ID：

./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep assets
000000002D0A    2A0000017E28FC  SYS_TABLES      "iceny/assets\_host\_auth"      185     4       33      0       80      ""      178
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp/undrop-for-innodb-master/dumps/default/SYS_TABLES000000002D0A      2A0000017E28FC  SYS_TABLES      "iceny/assets\_host\_auth"      185     4       33      080       ""      178
' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
-- STATUS {"records_expected": 122, "records_dumped": 34, "records_lost": true} STATUS END

表ID为表名后面的那个数字，这里为185。

2，利用找出来的表ID，查找assets_host_auth的磁盘页ID：

#  ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep 185
000000002D0A    2A0000017E27EB  SYS_INDEXES     185     335     "PRIMARY"       1       3       178     4294967295
000000001140    2500000139185D  SYS_INDEXES     119     207     "PRIMARY"       1       3       105     4294967295
000000002D0A    2A0000017E27EB  SYS_INDEXES     185     335     "PRIMARY"       1       3       178     4294967295
000000001140    2500000139185D  SYS_INDEXES     119     207     "PRIMARY"       1       3       105     4294967295
SET FOREIGN_KEY_CHECKS=0;

这里可以明细那看到，ID185对应的数值是335，这里的335就是assets_host_auth数据所在的的磁盘页ID。

3，查看扫描的磁盘页中是否存在335的page：

# ll pages-centos-root/FIL_PAGE_INDEX/0000000000000335.page 
-rw-r--r-- 1 root root 65536 Aug 13 20:31 pages-centos-root/FIL_PAGE_INDEX/0000000000000335.page

可以看到335page确实存在。

4，创建assets_host_auth.sql文件，内容是assets_host_auth表的数据结构：

# vi iceny/assets_host_auth.sql
CREATE TABLE `assets_host_auth` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `host_ip` varchar(20) NOT NULL,
  `password` varchar(100) DEFAULT NULL,
  `env` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

5，利用存在的磁盘page查看assets_host_auth数据是否还在；

./c_parser -6f pages-centos-root/FIL_PAGE_INDEX/0000000000000335.page -t iceny/assets_host_auth.sql |  head -5 
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
000000002690    DF000001C10110  assets_host_auth        1       "192.168.204.128"       "123456"        "cmh\_test"
000000002690    DF000001C1011D  assets_host_auth        17      "192.168.1.11"  "]t;:<8qCLfse+8LZZhK+11"        "HB"
000000002690    DF000001C1012A  assets_host_auth        19      "192.168.1.12"  "]t;:<8qCLfse+8LZZhK+12"        "HB"
000000002690    DF000001C10137  assets_host_auth        20      "192.168.1.113" "]t;:<8qCLfse+8LZZhK+113"       "HB"

这里可以看到找到了一些相关的数据。

6，导出数据，生成 sql 导入文件：

# ./c_parser -6f pages-centos-root/FIL_PAGE_INDEX/0000000000000335.page -t iceny/assets_host_auth.sql  > dumps/default/assets_host_auth 2> dumps/default/assets_host_auth.sql

# more dumps/default/assets_host_auth.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp/undrop-for-innodb-master/dumps/default/assets_host_auth' REPLACE INTO TABLE `assets_host_auth` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STA
RTING BY 'assets_host_auth\t' (`id`, `host_ip`, `password`, `env`);
-- STATUS {"records_expected": 20, "records_dumped": 20, "records_lost": false} STATUS END

# more /tmp/undrop-for-innodb-master/dumps/default/assets_host_auth
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
000000002690    DF000001C10110  assets_host_auth        1       "192.168.204.128"       "123456"        "cmh\_test"
000000002690    DF000001C1011D  assets_host_auth        17      "192.168.1.11"  "]t;:<8qCLfse+8LZZhK+11"        "HB"
000000002690    DF000001C1012A  assets_host_auth        19      "192.168.1.12"  "]t;:<8qCLfse+8LZZhK+12"        "HB"
000000002690    DF000001C10137  assets_host_auth        20      "192.168.1.113" "]t;:<8qCLfse+8LZZhK+113"       "HB"
000000002690    DF000001C10144  assets_host_auth        21      "192.168.1.15"  "]t;:<8qCLfse+8LZZhK+15"        "HB"
-- Page id: 3, Found records: 5, Lost records: NO, Leaf page: YES
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
000000002690    DF000001C10110  assets_host_auth        1       "192.168.204.128"       "123456"        "cmh\_test"
000000002690    DF000001C1011D  assets_host_auth        17      "192.168.1.11"  "]t;:<8qCLfse+8LZZhK+11"        "HB"
000000002690    DF000001C1012A  assets_host_auth        19      "192.168.1.12"  "]t;:<8qCLfse+8LZZhK+12"        "HB"
000000002690    DF000001C10137  assets_host_auth        20      "192.168.1.113" "]t;:<8qCLfse+8LZZhK+113"       "HB"
000000002690    DF000001C10144  assets_host_auth        21      "192.168.1.15"  "]t;:<8qCLfse+8LZZhK+15"        "HB"
-- Page id: 3, Found records: 5, Lost records: NO, Leaf page: YES
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
000000002690    DF000001C10110  assets_host_auth        1       "192.168.204.128"       "123456"        "cmh\_test"
000000002690    DF000001C1011D  assets_host_auth        17      "192.168.1.11"  "]t;:<8qCLfse+8LZZhK+11"        "HB"
000000002690    DF000001C1012A  assets_host_auth        19      "192.168.1.12"  "]t;:<8qCLfse+8LZZhK+12"        "HB"
000000002690    DF000001C10137  assets_host_auth        20      "192.168.1.113" "]t;:<8qCLfse+8LZZhK+113"       "HB"
000000002690    DF000001C10144  assets_host_auth        21      "192.168.1.15"  "]t;:<8qCLfse+8LZZhK+15"        "HB"
-- Page id: 3, Found records: 5, Lost records: NO, Leaf page: YES
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
000000002690    DF000001C10110  assets_host_auth        1       "192.168.204.128"       "123456"        "cmh\_test"
000000002690    DF000001C1011D  assets_host_auth        17      "192.168.1.11"  "]t;:<8qCLfse+8LZZhK+11"        "HB"
000000002690    DF000001C1012A  assets_host_auth        19      "192.168.1.12"  "]t;:<8qCLfse+8LZZhK+12"        "HB"
000000002690    DF000001C10137  assets_host_auth        20      "192.168.1.113" "]t;:<8qCLfse+8LZZhK+113"       "HB"
000000002690    DF000001C10144  assets_host_auth        21      "192.168.1.15"  "]t;:<8qCLfse+8LZZhK+15"        "HB"
-- Page id: 3, Found records: 5, Lost records: NO, Leaf page: YES

可以看到，我们已经找回了我们所删除的数据，最后要做的，就是把这些数据导入数据库中去：

7，把数据导入数据库：

#导入表结构
mysql> use iceny;
Database changed
mysql> source /tmp/undrop-for-innodb-master/iceny/assets_host_auth.sql 
Query OK, 0 rows affected (0.21 sec)

导入数据，并查看：

使用undrop-for-innodb工具在无备份的情况下恢复mysql表

对比之前的数据，发现并无差异，至此，我们就通过undrop-for-innodb工具在无备份的情况下找回了数据。

undrop-for-innodb的一些问题（个人猜想）

由于undrop-for-innodb是通过扫描磁盘页来找回数据的，所以在删除表的一定短时间内，来用这种方法来找回数据是可行的。若是时间过长，磁盘页由于大量的IO操作，导致刷新了，有可能数据就被刷新掉了，这时候能不能找回数据，就不一定了。所以，在发现执行了误操作后，应立即停止mysql，把数据磁盘挂载为只读模式，避免数据页被刷新。


以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍
分布式算法导论

分布式算法导论

特尔 (Gerard Tel) / 电子工业出版社 / 2003-7 / 59.00

《分布式算法导论(第2版)(英文版)》由电子工业出版社出版。一起来看看 《分布式算法导论》 这本书的介绍吧!

码农工具
JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2024 码农网 粤ICP备17054400号-3