1. Recovery After DROP TABLE, With Innodb_file_per_table OFF

Recovery After DROP TABLE, With Innodb_file_per_table OFF

UPDATE (01/01/2017): We stopped further development of undrop-for-innodb and don’t support its open source versions anymore.


Unfortunately, human mistakes are inevitable. That’s how life is. Wrong “DROP DATABASE” or “DROP TABLE” may destroy critical data on the MySQL server. Obviously, backups would help, however they’re not always available. This situation is frightening but not hopeless. In many cases it’s possible to recover almost all the data that was in the database or table.
Let’s look at how we can do it. The recovery plan depends on whether InnoDB kept all data in a single ibdata1 or each table had its own tablespace. In this post we will consider the case when innodb_file_per_table=OFF. This option assumes that all tables are stored in a common file, usually located at /var/lib/mysql/ibdata1.

One Wrong Move, And The Table’s Gone

For our scenario, we use the sakila test database that comes together with the tool.
Supposedly, we drop my mistake table actor:

Recovery After DROP TABLE From Ibdata1

The table is gone, but the information contained in the table can still be in the database file. The data remains untouched until InnoDB reuses free pages. Hurry up and stop MySQL ASAP!
For the recovery, we use the TwinDB recovery toolkit. Check out our recent post “Recover InnoDB dictionary” for details on how to download and compile it.

Parse InnoDB Tablespace

InnoDB stores all data in B+tree indexes. A table has one clustered index PRIMARY, all fields are stored there. Thus, if the table has secondary keys, each key has an index. Each index is identified by index_id.

Consequently, if we want to recover a table, we have to find all pages that belong to a particular index_id.

Stream_parser reads the InnoDB tablespace and sorts InnoDB pages by type and by index_id.

Data from the database pages is saved by the stream_parser to folder pages-ibdata1:

Now, each index_id from the InnoDB tablespace is saved in a separate file. We can use c_parser to fetch records from the pages. But we need to know what index_id corresponds to table sakila/actor. This information we can acquire from the dictionary – SYS_TABLES and SYS_INDEXES.


SYS_TABLES is always stored in file index_id 1 which is file pages-ibdata1/FIL_PAGE_INDEX./0000000000000001.page
Let’s find the table_id of sakila/actor. If MySQL had enough time to flush changes to disk, then add the -D option which means “find deleted records”. The dictionary is always in REDUNDANT format, so we specify option -4:

Note number 158 right after the table name. This is table_id.

The next thing do is to find the index id of the PRIMARY index of table actor. For this purpose, we fetch records of SYS_INDEXES from the file 0000000000000003.page (this table will contain information about  index_id and table_id). The structure of  SYS_INDEXES is passed with -t option.

As you can see from the output, the necessary index_id is 376. Therefore, we look for the actor data in the file 0000000000000376.page

The resulting output looks correct, so let’s save the dump in a file. To make loading simpler, c_parser outputs LOAD DATA INFILE command to stderr.

We use the default location of these files: dump/default

And here’s a command to load the table.

Load Data Back To The Database

Now, it’s time to recover the data into the database. But before loading the dump we need to create an empty structure of the table actor:

Now, the table actor is ready. We can load our data after recovery.

Checking Recovered Data

And finally, checking data quality. We will see the total number of records, preview several records and calculate checksum.

As you can see, checksum after recovery is 3596356558 which is equal to the checksum taken before the accidental drop of the table. Therefore, we can be sure that all the data was recovered correctly.
In the next posts we will see other cases of recovery.

Have a question? Ask the experts!

Previous Post Next Post