1. Recovery After DROP TABLE, With Innodb_file_per_table ON

Recovery After DROP TABLE, With Innodb_file_per_table ON

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


In the previous post, we described a situation when the TwinDB recovery toolkit can be used to recover an accidentally dropped table with innodb_file_per_table=OFF.
In this post, we’ll show how to recover MySQL tables or databases if innodb_file_per_table is ON. So, let’s assume that the mysql server is set to innodb_file_per_table=ON. This option tells InnoDB to store each table with a user in a separate data file.

For the recovery test, we’ll use the same sakila database that we used in the previous post.

Note the two files related to the table country: country.frm, country.ibd.
We will drop this table and try to recover it. First, we take the checksum and preview the records this table contains:

Accidental Drop

Now, we drop the table and look for files related to the table. As you can see from the list, files with country table data are no longer there:

Recovery After DROP TABLE

This situation is a little bit more complex, since we need to recover a deleted file. If the database server has active communication with HDD, it’s possible that the deleted file will be rewritten with other data. Therefore, it’s critical to stop the server and to mount the partition read-only. But for the test we will just stop mysql service and continue with the recovery.

Despite the fact that user data is stored in separate files per each table, data dictionary is still stored in the ibdata1 file. That’s why we need to use stream_parser for /var/lib/mysql/ibdata1. For the details of usage, please refer to the post Recover after DROP TABLE.
In order to find table_id and index_id for the table country, we will use the dictionary stored in SYS_TABLES and SYS_INDEXES. We will fetch the data from the ibdata1 file. The dictionary records are always in REDUNDANT format, therefore we specify option -4. We assume that the mysql server has flushed changes to the disk, so we add the -D option which means “find deleted records”. SYS_TABLES information is stored in the file with index_id=1 which is file pages-ibdata1/FIL_PAGE_INDEX./0000000000000001.page:

We can see that the country table has table_id=228. The next step would be to find the PRIMARY index of the table country. For this purpose, we take the records of the SYS_INDEXES table from the file 0000000000000003.page (SYS_INDEXES table contains mapping between table_id and index_id). The structure of SYS_INDEXES is added to the tool with a -t option.

Almost There!

We can  see that index_id of the dropped table country is 547. The following step is different from the step we took in the case with innodb_file_per_table=OFF. Since there is no file with data available, we’ll scan through the whole storage device as a raw device and look for data that fits the expected structure of the database pages. By the way, you can use this approach with corrupted data files. If some data is corrupted, the recovery tool can perform partial data recovery. In the options of the tool we specify device name and device size (can be approximate).

Stream parser stores the resulted files with pages to the folder pages-vda (name derived from the title of the device). We can see that the necessary index is present in the files.

We will look for the data in the file 0000000000000547.page. Utility c_parser provides us with information according to expected table structure, supplied with -t option.

The result looks valid, so we prepare files for loading data back to the database. The LOAD DATA INFILE command with necessary options is sent to stderr device.

Loading Data Back To The Database

Now, we’re going to load data back to the database. Before loading the data, we create an empty structure of the table country:

And now we are loading data itself.

Checking Data Quality

So, the last thing that remaining is to check the quality of recovered data. We’ll preview several records, calculate the total number of records and checksum.

So, we’re in luck. Despite the fact that we used the system volume (which is not the recommended practice) for mysql data, and that we haven’t re-mounted partition as read-only (and other processes were continuing to perform writing to the disk), we managed to recover all the records. Calculated checksum after the recovery (3658016321) is equal to the checksum taken before the drop (3658016321).

Have a question? Ask the experts!

Previous Post Next Post