为什么我们需要恢复innodb的字典信息？当我们drop 一个表时，发现误操作，这时又没有备份，那么想恢复数据是非常困难的。所以我们想恢复被删除的表时，首先就需要恢复表结构，目前已经有了undrop-for-innodb这个工具，相比之前的工具percona-data-recovery-tool-for-innodb有了更大的进步，这两个工具都是同一个作者开发的。这个工具我以前也写了相关的博客--Percona Data Recovery Tool 单表恢复，现在undrop-for-innodb这个工具更加完美，少了很多限制。但是对于drop table以后表结构的恢复还是需要使用percona-data-recovery-tool-for-innodb。没有看见undrop-for-innodb可以恢复表结构，这也是比较遗憾的地方吧。
1.TwinDB恢复工具包编译安装
安装工具包依赖的软件
yum install gcc bison flex bzr -y
2.下载软件包并编译安装
[root@mysql-server-01 ~]# bzr branch lp:undrop-for-innodb You have not informed bzr of your Launchpad ID, and you must do this to write to Launchpad or access private data. See "bzr help launchpad-login". Branched 18 revision(s). [root@mysql-server-01 ~]#
[root@mysql-server-01 ~]# cd undrop-for-innodb/ [root@mysql-server-01 undrop-for-innodb]# make cc -g -O3 -I./include -c stream_parser.c cc -g -O3 -I./include -pthread -lm stream_parser.o -o stream_parser flex sql_parser.l bison -o sql_parser.c sql_parser.y sql_parser.y: conflicts: 6 shift/reduce cc -g -O3 -I./include -c sql_parser.c cc -g -O3 -I./include -c c_parser.c cc -g -O3 -I./include -c tables_dict.c cc -g -O3 -I./include -c print_data.c cc -g -O3 -I./include -c check_data.c cc -g -O3 -I./include sql_parser.o c_parser.o tables_dict.o print_data.o check_data.o -o c_parser -pthread -lm cc -g -O3 -I./include -o innochecksum_changer innochecksum.c [root@mysql-server-01 undrop-for-innodb]#
如果没有抛出错误，证明就ok了，会生成编译好的工具c_parser。
一. 解析 ibdata1
InnoDB的字典信息存储在ibdata1。因此，我们需要使用stream_parser工具对它进行解析并获得存储在字典中的记录页。该工具在make以后就自动生成了。
[root@mysql-server-01 undrop-for-innodb]# ./stream_parser -f /data/mysql/user_3306/data/ibdata1 Opening file: /data/mysql/user_3306/data/ibdata1 File information: ID of device containing file: 2055 inode number: 77760163 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 498 group ID of owner: 500 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 53248 time of last access: 1407057268 Sun Aug 3 17:14:28 2014 time of last modification: 1407057326 Sun Aug 3 17:15:26 2014 time of last status change: 1407057326 Sun Aug 3 17:15:26 2014 total size, in bytes: 27262976 (26.000 MiB) Size to process: 27262976 (26.000 MiB) All workers finished in 0 sec [root@mysql-server-01 undrop-for-innodb]#
stream_parser这个工具发现在ibdata1中，找到了存储页面类型（FIL_PAGE_INDEX或FIL_PAGE_TYPE_BLOB）由index_id的整理InnoDB的页面。
[root@mysql-server-01 pages-ibdata1]# pwd /root/undrop-for-innodb/pages-ibdata1 [root@mysql-server-01 pages-ibdata1]# ll total 8 drwxr-xr-x 2 root root 4096 Aug 3 17:15 FIL_PAGE_INDEX drwxr-xr-x 2 root root 4096 Aug 3 17:15 FIL_PAGE_TYPE_BLOB [root@mysql-server-01 pages-ibdata1]#
SYS_TABLES
SYS_TABLES本身是一个表，该表用于规范innodb各种表定义和保存innodb中各种表的基本信息，结合SYS_COLUMNS，SYS_INDEXES和SYS_FOREIGN系统表定义了特定某个表的所有信息。
[root@mysql-server-01 undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -rw-r--r-- 1 root root 16384 Aug 3 17:15 pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page
SYS_INDEXES
SYS_INDEXES用于保存innodb中每个表定义的每个索引对象。
[root@mysql-server-01 undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -rw-r--r-- 1 root root 16384 Aug 3 17:15 pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page
SYS_COLUMNS
SYS_COLUMNS用于保存innodb引擎每个表定义的列，与SYS_TABLES相似。
[root@mysql-server-01 undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -rw-r--r-- 1 root root 16384 Aug 3 17:15 pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page
SYS_FIELDS
SYS_FIELDS用于保存innodb中每个索引的每个列对象。
[root@mysql-server-01 undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -rw-r--r-- 1 root root 16384 Aug 3 17:15 pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page
从SYS_TABLES和SYS_INDEXES导出记录
获取记录了需要使用c_parser工具。但首先我们要创建转存的数据目录
[root@mysql-server-01 undrop-for-innodb]# mkdir -p dumps/default [root@mysql-server-01 undrop-for-innodb]#
InnoDB的字典总是在冗余格式，因此选择-4是必需的：
[root@mysql-server-01 undrop-for-innodb]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql [root@mysql-server-01 undrop-for-innodb]#
现在找到我们关心的数据库，我之前有导入MySQL官方的示例数据库，sakila
[root@mysql-server-01 undrop-for-innodb]# grep sakila dumps/default/SYS_TABLES 000000000312 93000001320110 SYS_TABLES "sakila/actor" 13 4 1 0 0 "" 0 000000000313 94000001350110 SYS_TABLES "sakila/address" 14 8 1 0 0 "" 0 000000000314 95000001380110 SYS_TABLES "sakila/category" 15 3 1 0 0 "" 0 000000000315 960000013A0110 SYS_TABLES "sakila/city" 16 4 1 0 0 "" 0 000000000316 970000013D0110 SYS_TABLES "sakila/country" 17 3 1 0 0 "" 0 000000000317 98000001400110 SYS_TABLES "sakila/customer" 18 9 1 0 0 "" 0 000000000319 9A000001460110 SYS_TABLES "sakila/film" 19 13 1 0 0 "" 0 00000000031B 9C0000014C0110 SYS_TABLES "sakila/film\_actor" 20 3 1 0 0 "" 0 00000000031D 9E000001500110 SYS_TABLES "sakila/film\_category" 21 3 1 0 0 "" 0 00000000031F A0000001540110 SYS_TABLES "sakila/inventory" 22 4 1 0 0 "" 0 000000000321 A2000001590110 SYS_TABLES "sakila/language" 23 3 1 0 0 "" 0 000000000322 A30000015B0110 SYS_TABLES "sakila/payment" 24 7 1 0 0 "" 0 000000000325 A6000001620110 SYS_TABLES "sakila/rental" 25 7 1 0 0 "" 0 000000000328 A90000016A0110 SYS_TABLES "sakila/staff" 26 11 1 0 0 "" 0 00000000032A AB0000016F0110 SYS_TABLES "sakila/store" 27 4 1 0 0 "" 0 [root@mysql-server-01 undrop-for-innodb]#
可见该库下的所有表都看见了（用过sakila示例库的同学都知道^_^）。
dumps/default/SYS_TABLES使使用LOAD DATA INFILE命令生成的。具体的命令c_parsers打印到标准错误输出。把改SQL保存在了dumps/default/SYS_TABLES.sql
[root@mysql-server-01 undrop-for-innodb]# cat dumps/default/SYS_TABLES.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES ' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
用同样的方法导出SYS_INDEXES：
[root@mysql-server-01 undrop-for-innodb]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql [root@mysql-server-01 undrop-for-innodb]#
查看一下结果，确保没有异常。
[root@mysql-server-01 undrop-for-innodb]# head -5 dumps/default/SYS_INDEXES -- Page id: 11, Format: REDUNDANT, Records list: Valid, Expected records: (43 43) 000000000300 810000012D0177 SYS_INDEXES 11 11 "ID\_IND" 1 3 0 302 000000000300 810000012D01A5 SYS_INDEXES 11 12 "FOR\_IND" 1 0 0 303 000000000300 810000012D01D3 SYS_INDEXES 11 13 "REF\_IND" 1 0 0 304 000000000300 810000012D026D SYS_INDEXES 12 14 "ID\_IND" 2 3 0 305 [root@mysql-server-01 undrop-for-innodb]# head -5 dumps/default/SYS_INDEXES.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES ' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`); [root@mysql-server-01 undrop-for-innodb]#
现在我们可以使用字典了，如果表在数据库中，那么会更加方便。
加载字典表到MySQL
通过grep可以从SYS_TABLES和SYS_INDEXES的主要用途是得到为了表名index_id。如果SYS_TABLES和SYS_INDEXES在MySQL中，那么操作将变得更简单。
root@localhost : (none) 18:10:31> create database yayun; Query OK, 1 row affected (0.00 sec) root@localhost : (none) 18:10:42>
上面创建的库主要用来导入两张表而已。
[root@mysql-server-01 undrop-for-innodb]# mysql -uroot -p123456 -S /data/mysql/user_3306/mysql.sock yayun < dictionary/SYS_TABLES.sql [root@mysql-server-01 undrop-for-innodb]# mysql -uroot -p123456 -S /data/mysql/user_3306/mysql.sock yayun < dictionary/SYS_INDEXES.sql [root@mysql-server-01 undrop-for-innodb]#
继续导入数据
[root@mysql-server-01 undrop-for-innodb]# mysql -uroot -p123456 -S /data/mysql/user_3306/mysql.sock yayun < dumps/default/SYS_TABLES.sql [root@mysql-server-01 undrop-for-innodb]# mysql -uroot -p123456 -S /data/mysql/user_3306/mysql.sock yayun < dumps/default/SYS_INDEXES.sql [root@mysql-server-01 undrop-for-innodb]#
现在我们的InnoDB字典信息已经存在在MySQL中，我们可以像普通的表进行查询
root@localhost : (none) 20:56:44> use yayun Database changed root@localhost : yayun 20:56:48> SELECT * FROM SYS_TABLES WHERE NAME = 'sakila/actor'; +--------------+----+--------+------+--------+---------+--------------+-------+ | NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE | +--------------+----+--------+------+--------+---------+--------------+-------+ | sakila/actor | 13 | 4 | 1 | 0 | 0 | | 0 | +--------------+----+--------+------+--------+---------+--------------+-------+ 1 row in set (0.08 sec) root@localhost : yayun 20:56:51> SELECT * FROM SYS_INDEXES WHERE TABLE_ID = 13; +----------+----+---------------------+----------+------+-------+---------+ | TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO | +----------+----+---------------------+----------+------+-------+---------+ | 13 | 15 | PRIMARY | 1 | 3 | 0 | 307 | | 13 | 16 | idx_actor_last_name | 1 | 0 | 0 | 308 | +----------+----+---------------------+----------+------+-------+---------+ 2 rows in set (0.05 sec) root@localhost : yayun 20:58:14>
通过上述的查询，我们可以看到sakila.actor表有两个索引，PRIMARY和idx_actor_last_name，分别对应15,16,。如果我们不小心误操作，drop table actor，那么恢复数据就需要知道相应的id。后续文章将介绍drop table后如何恢复数据。
参考资料