Recovering InnoDB Data Dictionary and Tables After Accidental Deletion or Corruption

When a DBA faces a dropped database, corrupted InnoDB tables, or a damaged disk, the DBMS may lose the ability to locate table definitions, leading to unreadable data. The article walks through the recovery process using the open‑source undrop-for-innodb tool.

InnoDB dictionary tables such as SYS_TABLES, SYS_INDEXES, SYS_COLUMNS and SYS_FIELDS store essential metadata. Their CREATE statements are shown to illustrate the columns that need to be reconstructed.

Tool installation :

wget https://github.com/chhabhaiya/undrop-for-innodb/archive/master.zip

yum install -y gcc flex bison

make

make sys_parser

Parsing utilities : sys_parser -h – prints help; use -u, -p, -d and specify the database/table to extract DDL.

stream_parser -f <innodb_datafile> [-T N:M] [-s size] [-t size] [-V|-g]

– reads raw pages from ibdata1, .ibd or a block device.

c_parser -4|-5|-6 [-dDV] -f <InnoDB page or dir> -t table.sql [-T N:M] [-b <external pages dir>]

– extracts records from a specific page and writes SQL.

Scenario 1 – Drop table (shared tablespace, innodb_file_per_table=OFF):

Stop MySQL immediately and mount the filesystem read‑only.

Identify the table’s TABLE_ID from SYS_TABLES and the primary‑key INDEX_ID from SYS_INDEXES using c_parser on the corresponding page files.

Extract the table’s rows with

c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000376.page -t sakila/actor.sql > dumps/default/actor

.

Recreate a fresh schema, import the recovered dictionary DDL ( cat dictionary/SYS_*.sql | mysql recovered), and load the extracted data ( cat dumps/default/*.sql | mysql sakila).

Scenario 2 – Corrupted InnoDB table (file‑per‑table mode, innodb_file_per_table=ON):

Obtain the table’s TABLE_ID and primary‑key INDEX_ID via a query on INNODB_SYS_TABLES and INNODB_SYS_INDEXES.

Use

c_parser -6f pages-actor.ibd/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql

to dump records, filtering out invalid rows.

Optionally add a /*!FILTER int_min_val: 1 int_max_val: 300 */ clause to the generated CREATE statement to limit the recovered range.

Delete the broken table files, start MySQL with innodb_force_recovery=6, drop the corrupted metadata, recreate the table, and import the cleaned dump.

Scenario 3 – Disk or filesystem damage :

Create a raw image of the faulty block device without overwriting it: dd if=/dev/sdb of=/path/to/faulty_disk.img conv=noerror.

Alternatively, stream the image over the network using nc as shown.

After obtaining the image, repeat the steps from Scenario 2 on the extracted pages.

Key take‑aways :

Never copy live data files as a backup; use physical tools like XtraBackup or logical dumps.

Regularly test backup restores.

Understanding InnoDB’s internal dictionary tables is essential for successful recovery.