1. Recover after DROP TABLE, innodb_file_per_table is ON

Recover after DROP TABLE, innodb_file_per_table is ON

UPDATE (01/01/2017): We stopped further development of undrop-for-innodb and do not support its open source versions.


In the previous post we described the situation when TwinDB recovery toolkit can be used to recover accidentaly dropped table in the case innodb_file_per_table=OFF setting.
In this post we will show how to recover MySQL table or database in case innodb_file_per_table is ON. So, let’s assume that mysql server has setting innodb_file_per_table=ON. This option tells InnoDB to store each table with user in a separate data  file.

We will use for recovery test the same database sakila, that was used in the previous post.

Note the two files related to 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 containing in this table:

Accidental drop

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

Recover after DROP TABLE

This situation is a little bit more complex, since we need to recover deleted file. If the database server has active communication with HDD, it is possible that deleted file will be rewritten by another data. Therefore it is 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 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 ibdata1 file. The dictionary records are always in REDUNDANT format, therefore we specify option -4. We assume that mysql server has flushed changes to the disk, so we add option -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 country table has table_id=228. Next step we will take is to find PRIMARY index of table country. For this purpose we will take records of 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 -t option.

We can  see that index_id of the dropped table country is 547. The following step is different from the step we took in case of innodb_file_per_table=OFF. Since there is no file with data available, we will scan through all the storage device as raw device and look for data that fit in expected structure of the database pages. By the way, this approach can be taken in case we have corrupted data files. If some data is corrupted, recovery tool can perform partial data recovery. In the options of the tool we specify name of the device 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 necessary index is present in the files.

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

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

Load data back to the database

We are going to load data back to the database. Before loading the data we create empty structure of table country:

And now we are loading data itself.

Checking data quality

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

So, we are lucky. Despite the facts that we used for mysql data the system volume (which is not the recommended practice) and that we have not 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