1. Recover Table Structure From InnoDB Dictionary

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 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

First, we need to get the source code. The code is hosted on GitHub.

To compile it we need gcc, bison and flex.

Recover InnoDB Dictionary

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

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

Now we need to extract the dictionary records from InnoDB pages. Let’s create a directory for table dumps.

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.





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

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:

Recover Table Structure

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 sakila_recovered. And we want to recover is sakila.actor. InnoDB uses a slash ‘/’ as a separator between database name and table name so does sys_parser.

There are few caveats though.

  1. 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
  2. sys_parser generates the table structure eligible for further data recovery. It could but it does not recover secondary indexes, foreign keys.
  3. 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.

Fortunately Oracle is planning to extend InnoDB dictionary and finally get rid of .frm files. I salute that decision, having the structure in two places leads to inconsistencies.

Have a question? Ask the experts!

Previous Post Next Post