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

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

内容简介：最近一个朋友说，程序误删了一张表，但是没有备份。我一心想，这不是凉凉了。因为按照正常的情况，如果误删了表，在有备份的情况下，可以通过备份+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，把数据磁盘挂载为只读模式，避免数据页被刷新。

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

查看所有标签

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

码农书籍

Head First Web Design

Ethan Watrall、Jeff Siarto / O’Reilly Media, Inc. / 2009-01-02 / USD 49.99

Want to know how to make your pages look beautiful, communicate your message effectively, guide visitors through your website with ease, and get everything approved by the accessibility and usability ......一起来看看《Head First Web Design》这本书的介绍吧!

码农工具