知识问答

Recover Table Structure From InnoDB Dictionary

When a table gets dropped MySQL removes respective .frm file. This post explain how to recover table structure if the table was dropped. You need the table structure to recover a...

When a table gets dropped MySQL removes respective .frm file. This post explain how to recover table structure if the table was dropped.

You need the table structure to recover a dropped table from InnoDB tablespace. The B+tree structure of InnoDB index doesn’t contain any information about field types. MySQL needs to know that in order to access records of InnoDB table. Normally MySQL gets the table structure from .frm file. But when MySQL drops a table the respective frm file removed too.

Fortunately there is one more place where MySQL keeps the tables structure . It is the InnoDB dictionary.

InnoDB dictionary is a set of tables where InnoDB keeps some information about the tables. I reviewed them in details is a separate InnoDB Dictionary post earlier. After the DROP InnoDB deletes records related to the dropped table from the dictionary. So we need to recover deleted records from the dictionary and then get the table structure.

Compiling Data Recovery Tool

#git clone https://github.com/chhabhaiya/undrop-for-innodb.git

#cd undrop-for-innodb

#yum install -y make gcc flex bison

# make

Now let’s create dictionary tables in database sakila_recovered. The data recovery tool comes with structure of the dictionary tables.

# cat dictionary/SYS_* | mysql -u root -ppass test # 使用dictionary 目录下表结构导入到test库

The dictionary is stored in ibdata1 file. So, let’s parse it.

# ./stream_parser -f /webdata/opt/local/mysql/data/ibdata1 #通过工具stream_parser 解析 ibdata1 innodb-file-per-table=on时，里面不存数据。

##此工具，比perconna的 percona-data-recovery-tool-for-innodb-0.5的page_parser 快很多

Size to process: 79691776 (76.000 MiB)

Worker(0): 84.13% done. 2014-09-03 16:31:20 ETA(in 00:00:00). Processing speed: 7.984 MiB/sec

Worker(2): 84.21% done. 2014-09-03 16:31:20 ETA(in 00:00:00). Processing speed: 8.000 MiB/sec

Worker(1): 84.21% done. 2014-09-03 16:31:21 ETA(in 00:00:00). Processing speed: 4.000 MiB/sec

All workers finished in 2 sec

# mkdir -p dumps/default

And now we can generate table dumps and LOAD INFILE commands to load the dumps. We also need to specify -D option to c_parser because the records we need were deleted from the dictionary when the table was dropped.

SYS_TABLES

# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page \

-t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql

#解析出SYS_TABLES 数据，SYS_TABLES 为具体数据，没有字段。fileds SYS_TABLES.sql 生成 load data local infile 语句

##00000000000000001.page中存 SYS_TABLES的数据

SYS_INDEXES

# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page \

-t dictionary/SYS_INDEXES.sql >dumps/default/SYS_INDEXES 2>dumps/default/SYS_INDEXES.sql

SYS_COLUMNS

# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page \

-t dictionary/SYS_COLUMNS.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql

and SYS_FIELDS

# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page \

-t dictionary/SYS_FIELDS.sql >dumps/default/SYS_FIELDS 2>dumps/default/SYS_FIELDS.sql

With the generated LOAD INFILE commands it’s easy to load the dumps.

# cat dumps/default/*.sql | mysql -u root -ppass test #解析生成的数据导入到test库中原先创建结构的几个表结构中。

Now we have InnoDB dictionary loaded into normal InnoDB tables.

Compiling sys_parser

sys_parser is a tool that reads dictionary from tables stored in MySQL and generates CREATE TABLE structure for a table.

To compile it we will need MySQL libraries and development files. Depending on a distribution they may be in -devel or -dev package. On RedHat based system you can check it with command yum provides “*/mysql_config” . On my server it was package mysql-community-devel.

If all necessary packages are installed compilation boils down to simple command:

# make sys_parser #需要mysql的 mysql_config 文件加入到环境变量中

Now sys_parser can do its magic. Just run it to get the CREATE statement in standard output.

It will use root as username to connect to MySQL, querty as the password. The dictionary is stored in SYS_* tables in database test. And we want to recover is audit.conn_log. InnoDB uses a slash ‘/’ as a separator between database name and table name so does sys_parser.

# ./sys_parser -u root -p qwerty -d test audit/conn_log #会打印出恢复的表结构

There are few caveats though. #局限

InnoDB doesn’t store all information you can find in the frm file. For example, if a field is AUTO_INCREMENT InnoDB dictionary knows nothing about it. Therefore, sys_parser will not recover that property. If there were any field or table level comments they’ll be lost

sys_parser generates the table structure eligible for further data recovery. It could but it does not recover secondary indexes, foreign keys.

InnoDB doesn’t stores DECIMAL type as a binary string. It doesn’t store precision of a DECIMAL field. So that information will be lost.

For example, table payment uses DECIMAL to store money.

# ./sys_parser -u root -p qwerty -d sakila_recovered sakila/payment

CREATE TABLE `payment`(

`payment_id` SMALLINT UNSIGNED NOT NULL,

`customer_id` SMALLINT UNSIGNED NOT NULL,

`staff_id` TINYINT UNSIGNED NOT NULL,

`rental_id` INT,

`amount` DECIMAL(6,0) NOT NULL,

`payment_date` DATETIME NOT NULL,

`last_update` TIMESTAMP NOT NULL,

PRIMARY KEY (`payment_id`)

) ENGINE=InnoDB;