MySQL误DROP表恢复,innodb_file_per_table=ON

参考
https://twindb.com/recover-after-drop-table-innodb_file_per_table-is-on/

Undrop-for-innodb安装

详见https://github.com/chhabhaiya/undrop-for-innodb

删除Country表并恢复

使用sakila示例数据库作为例子,sakila相当于Oracle的SCOTT
innodb每个表包含两个文件.frm文件包含建表语句.ibd文件包含数据

[mysql@master sakila]$ ll country.*
-rw-r----- 1 mysql mysql  8652 Aug 26 14:25 country.frm
-rw-r----- 1 mysql mysql 98304 Aug 26 14:25 country.ibd

接下来删除country表,删之前看一下checksum

SESSION_A> use sakila
Database changed
SESSION_A> select count(*) from country;
+----------+
| count(*) |
+----------+
|      109 |
+----------+
1 row in set (0.01 sec)

SESSION_A>checksum table country;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| sakila.country      | 2039770088 |
+---------------------+------------+
1 row in set (0.00 sec)

接下来删除country表

SESSION_A>drop table country;
Query OK, 0 rows affected (0.01 sec)

drop table后,.frm和.ibd文件也被删除了

[mysql@master sakila]$ ll country*
ls: cannot access country*: No such file or directory

恢复表

这是情况比较复杂,因为误删除了表,此时如果数据库还是活动的,那么很有可能country表的数据会被rewrite.此时应该立即停止数据库,并把曾经包含country表数据的磁盘分区设为read-only以免被覆盖.
在这里,我们只停止MySQL就好了

[mysql@master ~]$ mysqladmin -S /data/mysqldata/3306/mysql.sock -umysql -pmysql shutdown
Warning: Using a password on the command line interface can be insecure.

[mysql@master ~]$ ps -ef | grep mysqld | grep -v grep
[mysql@master ~]$ 

尽管我们使用的是独立表空间,但是数据字典仍然存储在ibdata1中.

使用stream_parser解析数据文件

[mysql@master undrop-for-innodb]$ ./stream_parser -f /data/mysqldata/3306/data/ibdata1 
Opening file: /data/mysqldata/3306/data/ibdata1
File information:

ID of device containing file:        64768
inode number:                    106448290
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:        4194304
time of last access:            1482762932 Mon Dec 26 22:35:32 2016
time of last modification:      1482762932 Mon Dec 26 22:35:32 2016
time of last status change:     1482762932 Mon Dec 26 22:35:32 2016
total size, in bytes:           2147483648 (2.000 GiB)

Size to process:                2147483648 (2.000 GiB)
Worker(0): 3.90% done. 2016-12-26 22:36:46 ETA(in 00:00:27). Processing speed: 71.922 MiB/sec
Worker(0): 30.46% done. 2016-12-26 22:36:22 ETA(in 00:00:02). Processing speed: 544.000 MiB/sec
Worker(0): 55.46% done. 2016-12-26 22:36:22 ETA(in 00:00:01). Processing speed: 512.000 MiB/sec
Worker(0): 75.39% done. 2016-12-26 22:36:23 ETA(in 00:00:01). Processing speed: 408.000 MiB/sec
Worker(0): 92.96% done. 2016-12-26 22:36:23 ETA(in 00:00:00). Processing speed: 360.000 MiB/sec
All workers finished in 5 sec

解析之后会生成如下目录和文件

[mysql@master undrop-for-innodb]$ tree pages-ibdata1/
pages-ibdata1/
├── FIL_PAGE_INDEX
│   ├── 0000000000000001.page
│   ├── 0000000000000002.page
│   ├── 0000000000000003.page
│   ├── 0000000000000004.page
│   ├── 0000000000000005.page
│   ├── 0000000000000011.page
│   ├── 0000000000000012.page
│   ├── 0000000000000013.page
│   ├── 0000000000000014.page
│   ├── 0000000000000015.page
│   ├── 0000000000000016.page
│   ├── 0000000000000529.page
│   ├── 0000000000000655.page
│   ├── 0000000000000657.page
│   ├── 0000000000000658.page
│   ├── 0000000000000659.page
│   ├── 0000000000000661.page
│   ├── 0000000000000665.page
│   ├── 0000000000000666.page
│   ├── 0000000000000667.page
│   ├── 0000000000000669.page
│   ├── 0000000000000680.page
│   ├── 0000000000000718.page
│   └── 18446744069414584320.page
└── FIL_PAGE_TYPE_BLOB

2 directories, 24 files

为了找出TABLE_ID和INDEX_ID,我们需要查看SYS_TABLES和SYS_INDEXES数据字典.我们会从ibdata1中取出数据.
因为数据字典记录总是冗余的,所以我们要执行 -4选项,同时我们假定mysql已经将变化刷到磁盘了.所以指定-D选项,表示找出删除的记录.SYS_TABLES表的信息存储在index_id=1的文件中,即pages-ibdata1/FIL_PAGE_INDEX./0000000000000001.page

[mysql@master undrop-for-innodb]$ ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep country
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/var/lib/mysql/undrop-for-innodb/dumps/default/SYS_TABLES' 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`);
00000004FA1A    150000037222EC  SYS_TABLES      "sakila/country"  714     3       65      0       80      ""      703
00000004FA1A    150000037222EC  SYS_TABLES      "sakila/country"  714     3       65      0       80      ""      703

我们可以看到country表的table_id是714.接下来我们要找到country表的主键索引.
我们需要查看0000000000000003.page文件中的SYS_INDEXES表数据 (SYS_INDEXES table contains mapping between table_id and index_id).

[mysql@master undrop-for-innodb]$ ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep 714
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/var/lib/mysql/undrop-for-innodb/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);
00000004FA1A    150000037221CC  SYS_INDEXES     714     729     "PRIMARY"       1       3       703     4294967295

我们可以看到country表的index_id是729

由于没有可用数据的文件，我们将扫描所有存储设备作为原始设备，并查找适合数据库页面的预期结构的数据。 顺便说一句，这种方法可以用于已经损坏的数据文件。 如果一些数据被损坏，恢复工具可以执行部分数据恢复。 在工具的选项中，我们指定设备的名称和设备大小（可以是近似值）。

Since there is no file with data available, we will scan through all the storage device as raw device and look for data that fit in expected structure of the database pages. By the way, this approach can be taken in case we have corrupted data files. If some data is corrupted, recovery tool can perform partial data recovery. In the options of the tool we specify name of the device and device size (can be approximate).

这里我的数据放在/dev/mapper/centos-oracle 下

所以root用户scan 30G数据,磁盘要有足够空间,并且请注意不要在曾经包含被删除表.ibd文件所挂载的磁盘下执行下面的命令,因为他会生成大量文件,可能导致rewrite.

[root@master undrop-for-innodb]# ./stream_parser -f /dev/mapper/centos-oracle -t 20000000k
Opening file: /dev/mapper/centos-oracle
File information:

ID of device containing file:            5
inode number:                        15178
protection:                          60660 (block device)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       6
device ID (if special file):         64770
blocksize for filesystem I/O:         4096
number of blocks allocated:              0
time of last access:            1482761058 Mon Dec 26 22:04:18 2016
time of last modification:      1481179251 Thu Dec  8 14:40:51 2016
time of last status change:     1481179251 Thu Dec  8 14:40:51 2016
total size, in bytes:                    0 (0.000 exp(+0))

Size to process:               20480000000 (19.073 GiB)
Worker(0): 1.06% done. 2016-12-26 22:41:21 ETA(in 00:01:36). Processing speed: 199.621 MiB/sec
Worker(0): 2.09% done. 2016-12-26 22:42:58 ETA(in 00:03:11). Processing speed: 99.806 MiB/sec
Worker(0): 3.11% done. 2016-12-26 22:41:22 ETA(in 00:01:34). Processing speed: 199.612 MiB/sec
Worker(0): 4.13% done. 2016-12-26 22:42:57 ETA(in 00:03:07). Processing speed: 99.807 MiB/sec
Worker(0): 5.15% done. 2016-12-26 22:41:23 ETA(in 00:01:32). Processing speed: 199.617 MiB/sec
...

[mysql@master undrop-for-innodb]$ tree pages-centos-oracle/
pages-centos-oracle/
├── FIL_PAGE_INDEX
│   ├── 0000000000000001.page
│   ├── 0000000000000002.page
│   ├── 0000000000000003.page
│   ├── 0000000000000004.page
│   ├── 0000000000000005.page
│   ├── 0000000000000011.page
│   ├── 0000000000000012.page
│   ├── 0000000000000013.page
│   ├── 0000000000000014.page
│   ├── 0000000000000015.page
│   ├── 0000000000000016.page
│   ├── 0000000000000092.page
│   ├── 0000000000000347.page
│   ├── 0000000000000400.page
│   ├── 0000000000000407.page
│   ├── 0000000000000511.page
│   ├── 0000000000000529.page
│   ├── 0000000000000655.page
│   ├── 0000000000000657.page
│   ├── 0000000000000658.page
│   ├── 0000000000000659.page
│   ├── 0000000000000661.page
│   ├── 0000000000000665.page
│   ├── 0000000000000666.page
│   ├── 0000000000000667.page
│   ├── 0000000000000669.page
│   ├── 0000000000000680.page
│   ├── 0000000000000718.page
│   ├── 0000000000000729.page
│   └── 18446744069414584320.page
└── FIL_PAGE_TYPE_BLOB

2 directories, 30 files

0000000000000729.page使我们需要的.这里需要country的建表语句country.sql,所以说请备份好元数据.这里undrop-for-innodb自带了sakila的建表语句以供实验使用

[mysql@master undrop-for-innodb]$ ./c_parser -6f pages-centos-oracle/FIL_PAGE_INDEX/0000000000000729.page -t sakila/country.sql |  head -5
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/var/lib/mysql/undrop-for-innodb/dumps/default/country' REPLACE INTO TABLE `country` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'country\t' (`country_id`, `country`, `last_update`);
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (109 109)
00000004FA13    90000002820110  country 1       "Afghanistan"   "2006-02-15 04:44:00"
00000004FA13    9000000282011B  country 2       "Algeria"       "2006-02-15 04:44:00"
00000004FA13    90000002820126  country 3       "American Samoa"        "2006-02-15 04:44:00"
00000004FA13    90000002820131  country 4       "Angola"        "2006-02-15 04:44:00"

看起来我们找到了数据,下面通过c_parser生成load data infile语句和数据

[mysql@master undrop-for-innodb]$ mkdir -p dumps/default
[mysql@master undrop-for-innodb]$ ./c_parser -6f pages-centos-oracle/FIL_PAGE_INDEX/0000000000000729.page -t sakila/country.sql > dumps/default/country 2> dumps/default/country_load.sql

查看一下生成的文件

[mysql@master undrop-for-innodb]$ cd dumps/default/
[mysql@master default]$ ls
country  country_load.sql

这是包含数据的文件
[mysql@master default]$ more country  
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (109 109)
00000004FA13    90000002820110  country 1       "Afghanistan"   "2006-02-15 04:44:00"
00000004FA13    9000000282011B  country 2       "Algeria"       "2006-02-15 04:44:00"
00000004FA13    90000002820126  country 3       "American Samoa"        "2006-02-15 04:44:00"
00000004FA13    90000002820131  country 4       "Angola"        "2006-02-15 04:44:00"
00000004FA13    9000000282013C  country 5       "Anguilla"      "2006-02-15 04:44:00"
00000004FA13    90000002820147  country 6       "Argentina"     "2006-02-15 04:44:00"

这是生成的写好的load data infile语句
[mysql@master default]$ more country_load.sql 
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/var/lib/mysql/undrop-for-innodb/dumps/default/country' REPLACE INTO TABLE `country` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'co
untry\t' (`country_id`, `country`, `last_update`);

开始恢复数据

启动数据库

mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &

创建表

(mysql@localhost) [(none)]> use sakila
Database changed
(mysql@localhost) [sakila]> source sakila/country.sql

load回数据

(mysql@localhost) [sakila]> source dumps/default/country_load.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 109 rows affected (0.01 sec)
Records: 109  Deleted: 0  Skipped: 0  Warnings: 0

(mysql@localhost) [sakila]> SELECT COUNT(*) FROM country;
+----------+
| COUNT(*) |
+----------+
|      109 |
+----------+
1 row in set (0.00 sec)

checksum和之前也是一样的
(mysql@localhost) [sakila]> CHECKSUM TABLE country;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| sakila.country      | 2039770088 |
+---------------------+------------+
1 row in set (0.00 sec)