1. Recovering The InnoDB Dictionary

Recovering The InnoDB Dictionary

MySQL table example

Why Do We Need To Recover The InnoDB Dictionary

c_parser is a tool from TwinDB recovery toolkit that can read InnoDB pages and fetch records out of them. Although it can scan any stream of bytes, recovery quality is higher when you feed c_parser with pages that belong to the PRIMARY index of the table. All InnoDB indexes have their identifiers a.k.a. index_id. The InnoDB dictionary stores correspondence between table name and index_id. That would be reason number one.

Another reason – it’s possible to recover the table structure from the InnoDB dictionary. When a table is dropped, MySQL deletes the respective .frm file. If you had neither backups nor table schema, it becomes quite a challenge to recover the table structure. This topic however deserves a separate post which I write some other day.

Let’s assume you’re convinced enough and we can proceed with InnoDB dictionary recovery.

Compiling The TwinDB Recovery Toolkit

The source code of the toolkit is hosted on GitHub. You will need git to get the latest revision, so make sure you have it:

Get the latest revision of the toolkit:

As prerequisites we would need gcc, flex and bison. Check if you have them:

Good. Now let’s compile the code:

If there are no errors we are ready to proceed.

Splitting Ibdata1

The InnoDB dictionary is stored in ibdata1. We need to parse it and get pages that store records of the dictionary. stream_parser does it.

stream_parser finds InnoDB pages in ibdata1 and stores them sorted by page type(FIL_PAGE_INDEX or FIL_PAGE_TYPE_BLOB) by index_id.
Here are the indexes:

SYS_TABLES

SYS_INDEXES

SYS_COLUMNS

and SYS_FIELDS

As you can see, the dictionary is pretty small, just one page per index.

Dumping Records From SYS_TABLES and SYS_INDEXES

To fetch records out of the index pages you need c_parser. But first, let’s create a directory for dumps

InnoDB dictionary is always in REDUNDANT format, so option -4 is mandatory:

Here are our sakila tables:

dumps/default/SYS_TABLES is a dump of the table eligible for the LOAD DATA INFILE command. The exact command c_parsers prints to standard error output. I saved it in dumps/default/SYS_TABLES.sql

The same way, let’s dump SYS_INDEXES:

Make sure we have a sane result in the dumps.

Now, we can work with the dictionary, but it’s more convenient if the tables are in MySQL.

Loading Dictionary Tables Into MySQL

The main usage of SYS_TABLES and SYS_INDEXES is to get index_id by table name. It’s possible to run two greps. Having SYS_TABLES and SYS_INDEXES in MySQL makes job easier.

Before we can process, let’s make sure the mysql user can read from the root’s home directory. Maybe it’s not wise from the security standpoint. If it’s your concern, create a whole recovery environment somewhere in /tmp.

Create empty dictionary tables in any database (e.g. test)

And load the dumps:

Now, we have the InnoDB dictionary in MySQL and we can query it as any other MySQL table:

Here we can see that sakila.actor has two indexes: PRIMARY and idx_actor_last_name. Respective index_id are 1828 and 1829.

Stay tuned to learn what to do with them and how to recover sakila.actor

Have a question? Ask the experts!

Previous Post Next Post