How to use Undrop For InnoDB to Recover InnoDB Data

APPLIES TO:

MySQL Server - Version 4.1 to 5.6 [Release 4.1 to 5.6]

Information in this document applies to any platform.

GOAL

How to extract data from corrupted tables using undrop-for-innodb
 

SOLUTION

Using the open-source UnDROP tool for InnoDB from twindb.com, it is sometimes possible to recover data from tables that can not be read even using innodb_force_recovery.  

The undrop tool is intended to be used against ibdata files that are not in active use by a live DB server.  

In general terms, the tool works by extracting index pages from the monolithic ibdata file(s) and/or from independent InnoDB tablespace files (.ibd files where innodb_file_per_table is in use).  Where applicable, blob pages are extracted to a separate subdirectory.  

Once the data is extracted to index pages, the next step is to retrieve the primary key or the general clustered index ID from the data dictionary, then extract that data into a file suitable for using with LOAD DATA INFILE.  

If possible, start with at least a schema dump of the database(s) to be recovered, and recover as much data as possible by dumping the tables, using innodb_force_recovery if necessary.  Even an out-of-date backup is better than nothing.  Although UnDROP can sometimes extract a valid table definition from the ibdata file, it's not perfect at handling all column types.  If you have NO backup at all, the .frm files can be used to rebuild the table definitions.  If you don't have a backup or .frm files, then as a last resort the table definition that UnDROP can extract from ibdata can be used to try to get at least some of the data back.

The UnDROP tools are as follows, in the order they are used to extract data:

 stream_parser

stream_parser is the tool that is used to extract pages from the ibdata.  Its use is straightforward:

./stream_parser -f <path_to_ibdata>

Pages will be extracted by default to "pages-<ibdata_file_name>".  Index pages are stored in the subdirectory FIL_PAGE_INDEX and blob pages are stored in the subdirectory FIL_PAGE_TYPE_BLOB.

To extract all the data for a table, it is necessary to identify the data dictionary's index ID number for the table's primary key (or general index in the absence of a primary key).  This can be accomplished by extracting the dictionary data from the extracted index pages into the 'test' schema on a running server by using the "recover_dictionary.sh" script that comes with the UnDROP tools, like this:

 Now the dictionary can be queried to find the index ID that corresponds to any given table.  Example given is for the table called mdl2_user in the moodle2 schema:

 The INDEX_ID returned corresponds to the name of one the extracted page files:

c_parser 

At this point, with a known table definition, the data can be recovered with c_parser, like this, where mdl2_user.sql contains the table definition:

Using the following script and given table definitions in the dumps directory for each schema, all of the tables found in the data dictionary can be extracted into files suitable for  LOAD DATA INFILE, along with separate .load files to actually load them.  Uncomment the sys_parser line only as a last resort if original table definitions can't be provide or extracted from .frm files.  If original table defs can be provided, they should be stored as separate sql files in dumps/<schema>/<table>.sql:

sys_parser 

As noted above, sys_parser can be used to extract a table definition from the ibdata file, but it should be used only as a last resort.  Some guesswork may be required and it may be impossible to fully extract available data from ibdata if valid table definitions can not be provided.

Extraction of a valid table definition from a .frm file

It is possible using mysqlfrm from the MySQL Utilities package to extract a valid table definition from a non-corrupted .frm file.  It is important to note that using mysqlfrm in --diagnostic mode is susceptible to the same kind of problems as extracting from ibdata using sys_parser, so it is important to use mysqlfrm with the --server flag.  The output must also be tweaked in order to work with c_parser, because c_parser will die if comment lines, warnings, or default charset information is found in the table definition.  Here is a way to extract table definitions along with a single schema.sql to be used to create the schema(s) from all .frm files found in a given directory:

 extract_schema.pl looks like this:

While it's exciting that the UnDROP tool is available, getting into a situation where it must be used is a very, very bad idea.  There is no guarantee that any data  can be recovered when a database is corrupted.  The way to avoid being in this situation is to use MySQL Enterprise Backup to create reliable backups on a regular basis, along with at least one replication slave.  Smart administrators who care about their data will also establish a replication slave in a remote location and take regular backups of that server as a disaster recovery precaution.