When a table gets dropped, MySQL removes the respective .frm file. This post explains how to recover the table structure if the table was dropped.
You need the table structure to recover a dropped table from the InnoDB tablespace. The B+tree structure of the InnoDB index doesn’t contain any information about field types. MySQL needs to know that in order to access records of the InnoDB table. Normally, MySQL gets the table structure from the .frm file. But when MySQL drops a table the respective frm file removed too.
Fortunately, there’s one more place where MySQL keeps the table structure. It’s the InnoDB dictionary.
The InnoDB dictionary is a set of tables where InnoDB keeps information about the tables. I reviewed them in detail in a separate InnoDB Dictionary post earlier. After the DROP, InnoDB deletes records related to the dropped table from the dictionary. So, we need to recover deleted records from the dictionary and then get the table structure.
Compiling Data Recovery Tool
First, we need to get the source code. The code is hosted on GitHub.
git clone https://github.com/twindb/undrop-for-innodb.git
To compile it, we need gcc, bison and flex. Install these packages with a package manager (yum/apt/etc). Then, time to compile.
# make cc -g -O3 -I./include -c stream_parser.c cc -g -O3 -I./include -pthread -lm stream_parser.o -o stream_parser flex sql_parser.l bison -o sql_parser.c sql_parser.y sql_parser.y: conflicts: 6 shift/reduce cc -g -O3 -I./include -c sql_parser.c cc -g -O3 -I./include -c c_parser.c cc -g -O3 -I./include -c tables_dict.c cc -g -O3 -I./include -c print_data.c cc -g -O3 -I./include -c check_data.c cc -g -O3 -I./include sql_parser.o c_parser.o tables_dict.o print_data.o check_data.o -o c_parser -pthread -lm cc -g -O3 -I./include -o innochecksum_changer innochecksum.c
Recover The InnoDB Dictionary
Now, let’s create dictionary tables in the sakila_recovered database. The data recovery tool comes with the structure of the dictionary tables.
# cat dictionary/SYS_* | mysql sakila_recovered
The dictionary is stored in the ibdata1 file. So, let’s parse it.
./stream_parser -f /var/lib/mysql/ibdata1 ... Size to process: 79691776 (76.000 MiB) Worker(0): 84.13% done. 2014-09-03 16:31:20 ETA(in 00:00:00). Processing speed: 7.984 MiB/sec Worker(2): 84.21% done. 2014-09-03 16:31:20 ETA(in 00:00:00). Processing speed: 8.000 MiB/sec Worker(1): 84.21% done. 2014-09-03 16:31:21 ETA(in 00:00:00). Processing speed: 4.000 MiB/sec All workers finished in 2 sec
Now, we need to extract the dictionary records from the InnoDB pages. Let’s create a directory for the table dumps.
# mkdir -p dumps/default
And now we can generate table dumps and LOAD INFILE commands to load the dumps. We also need to specify the -D option to the c_parser because the records we need were deleted from the dictionary when the table was dropped.
SYS_TABLES
# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page \ -t dictionary/SYS_TABLES.sql \ > dumps/default/SYS_TABLES \ 2> dumps/default/SYS_TABLES.sql
SYS_INDEXES
# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page \ -t dictionary/SYS_INDEXES.sql \ > dumps/default/SYS_INDEXES \ 2> dumps/default/SYS_INDEXES.sql
SYS_COLUMNS
# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page \ -t dictionary/SYS_COLUMNS.sql \ > dumps/default/SYS_COLUMNS \ 2> dumps/default/SYS_COLUMNS.sql
SYS_FIELDS
# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page \ -t dictionary/SYS_FIELDS.sql \ > dumps/default/SYS_FIELDS \ 2> dumps/default/SYS_FIELDS.sql
With the generated LOAD INFILE commands it’s easy to load the dumps.
# cat dumps/default/*.sql | mysql sakila_recovered
Now we have the InnoDB dictionary loaded into normal InnoDB tables.
Compiling sys_parser
ys_parser is a tool that reads the dictionary from tables stored in MySQL and generates the CREATE TABLE structure for a table.
To compile it we need MySQL libraries and development files. Depending on distribution, they may be in -devel or -dev package. On RedHat based systems, you can check it with the command yum provides “*/mysql_config” . On my server it was the mysql-community-devel package.
If all necessary packages are installed, the compilation boils down to a simple command:
# make sys_parser /usr/bin/mysql_config cc `mysql_config --cflags` `mysql_config --libs` -o sys_parser sys_parser.c
Recover Table Structure
Now sys_parser can do its magic. Just run it to get the CREATE statement in the standard output.
# ./sys_parser sys_parser [-h <host>] [-u <user>] [-p <passowrd>] [-d <db>] databases/table
It will use “root” as the username to connect to MySQL, “querty” – as the password. The dictionary is stored in SYS_* tables in the sakila_recovered database. What we want to recover is sakila.actor. InnoDB uses a slash “/” as a separator between database name and table name, so does sys_parser.
# ./sys_parser -u root -p qwerty -d sakila_recovered sakila/actor CREATE TABLE `actor`( `actor_id` SMALLINT UNSIGNED NOT NULL, `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`actor_id`) ) ENGINE=InnoDB;
# ./sys_parser -u root -p qwerty -d sakila_recovered sakila/customer CREATE TABLE `customer`( `customer_id` SMALLINT UNSIGNED NOT NULL, `store_id` TINYINT UNSIGNED NOT NULL, `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `email` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci', `address_id` SMALLINT UNSIGNED NOT NULL, `active` TINYINT NOT NULL, `create_date` DATETIME NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`customer_id`) ) ENGINE=InnoDB;
There are few caveats though.
- InnoDB doesn’t store all information you can find in the
.frmfile. For example, if a field isAUTO_INCREMENT, the InnoDB dictionary knows nothing about it. Therefore,sys_parserwon’t recover that property. If there were any field or table level comments, they’ll be lost. sys_parsergenerates the table structure eligible for further data recovery. It could but it doesn’t recover secondary indexes, or foreign keys.- InnoDB stores the
DECIMALtype as a binary string. It doesn’t store the precision of aDECIMALfield. So, that information will be lost.
For example, table payment uses DECIMAL to store money.
# ./sys_parser -u root -p qwerty -d sakila_recovered sakila/payment CREATE TABLE `payment`( `payment_id` SMALLINT UNSIGNED NOT NULL, `customer_id` SMALLINT UNSIGNED NOT NULL, `staff_id` TINYINT UNSIGNED NOT NULL, `rental_id` INT, `amount` DECIMAL(6,0) NOT NULL, `payment_date` DATETIME NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`payment_id`) ) ENGINE=InnoDB;
Fortunately, Oracle is planning to extend the InnoDB dictionary and finally get rid of .frm files. I salute that decision, having the structure in two places leads to inconsistencies.
Image credit: m.khajoo
