首页 > 代码库 > Recover InnoDB dictionary

Recover InnoDB dictionary

为什么我们需要恢复innodb的字典信息?当我们drop 一个表时,发现误操作,这时表结构又没有备份,那么想恢复数据是非常困难的。所以我们想恢复被删除的表时,首先就需要恢复表结构,目前已经有了undrop-for-innodb这个工具,相比之前的工具percona-data-recovery-tool-for-innodb有了更大的进步,这个工具我以前也写了相关的博客--Percona Data Recovery Tool 单表恢复,现在undrop-for-innodb这个工具更加完美,少了很多限制。

1.TwinDB恢复工具包编译安装

安装工具包依赖的软件

yum install gcc bison flex bzr -y

2.下载软件包并编译安装

[root@mysql-server-01 ~]# bzr branch lp:undrop-for-innodbYou have not informed bzr of your Launchpad ID, and you must do this towrite 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]# makecc -g -O3 -I./include -c stream_parser.ccc -g -O3 -I./include  -pthread -lm stream_parser.o -o stream_parserflex  sql_parser.lbison  -o sql_parser.c sql_parser.ysql_parser.y: conflicts: 6 shift/reducecc -g -O3 -I./include -c sql_parser.c cc -g -O3 -I./include -c c_parser.ccc -g -O3 -I./include -c tables_dict.ccc -g -O3 -I./include -c print_data.ccc -g -O3 -I./include -c check_data.ccc -g -O3 -I./include  sql_parser.o c_parser.o tables_dict.o print_data.o check_data.o -o c_parser -pthread -lmcc -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/ibdata1File information:ID of device containing file:         2055inode number:                     77760163protection:                         100660 (regular file)number of hard links:                    1user ID of owner:                      498group ID of owner:                     500device ID (if special file):             0blocksize for filesystem I/O:         4096number of blocks allocated:          53248time of last access:            1407057268 Sun Aug  3 17:14:28 2014time of last modification:      1407057326 Sun Aug  3 17:15:26 2014time of last status change:     1407057326 Sun Aug  3 17:15:26 2014total 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]# lltotal 8drwxr-xr-x 2 root root 4096 Aug  3 17:15 FIL_PAGE_INDEXdrwxr-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_TABLES000000000312    93000001320110  SYS_TABLES      "sakila/actor"  13      4       1       0       0       ""      0000000000313    94000001350110  SYS_TABLES      "sakila/address"        14      8       1       0       0       ""      0000000000314    95000001380110  SYS_TABLES      "sakila/category"       15      3       1       0       0       ""      0000000000315    960000013A0110  SYS_TABLES      "sakila/city"   16      4       1       0       0       ""      0000000000316    970000013D0110  SYS_TABLES      "sakila/country"        17      3       1       0       0       ""      0000000000317    98000001400110  SYS_TABLES      "sakila/customer"       18      9       1       0       0       ""      0000000000319    9A000001460110  SYS_TABLES      "sakila/film"   19      13      1       0       0       ""      000000000031B    9C0000014C0110  SYS_TABLES      "sakila/film\_actor"    20      3       1       0       0       ""      000000000031D    9E000001500110  SYS_TABLES      "sakila/film\_category" 21      3       1       0       0       ""      000000000031F    A0000001540110  SYS_TABLES      "sakila/inventory"      22      4       1       0       0       ""      0000000000321    A2000001590110  SYS_TABLES      "sakila/language"       23      3       1       0       0       ""      0000000000322    A30000015B0110  SYS_TABLES      "sakila/payment"        24      7       1       0       0       ""      0000000000325    A6000001620110  SYS_TABLES      "sakila/rental" 25      7       1       0       0       ""      0000000000328    A90000016A0110  SYS_TABLES      "sakila/staff"  26      11      1       0       0       ""      000000000032A    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 \t OPTIONALLY ENCLOSED BY " LINES STARTING BY SYS_TABLES\t (`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       302000000000300    810000012D01A5  SYS_INDEXES     11      12      "FOR\_IND"      1       0       0       303000000000300    810000012D01D3  SYS_INDEXES     11      13      "REF\_IND"      1       0       0       304000000000300    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 \t OPTIONALLY ENCLOSED BY " LINES STARTING BY SYS_INDEXES\t (`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 yayunDatabase changedroot@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后如何恢复数据。

 

参考资料

https://twindb.com/how-to-recover-innodb-dictionary/