Recovering a Dropped MySQL Database Without Backups

After an accidental DROP DATABASE, the first step is to immediately stop MySQL and any web services, then copy the entire MySQL data directory (e.g., /var/lib/mysql) for safekeeping.

The recovery relies on two external resources: the DBA StackExchange discussion (http://dba.stackexchange.com/questions/23251/is-there-a-way-to-recover-a-dropped-mysql-database) and the undrop-for-innodb tool hosted at https://github.com/chhabhaiya/undrop-for-innodb (a fork of the original Twindb utility). Clone the repository on an Ubuntu 14.04 64‑bit server and compile it, installing any required dependencies.

Using the compiled stream_parser, parse the ibdata1 file to reconstruct the InnoDB system tables SYS_TABLES and SYS_INDEXES. Place the generated files in dumps/default exactly as the reference guides specify; naming mismatches cause later errors.

Be aware of a subtle flag difference: the original guide uses c_parser -4Df, not -4f. Omitting the capital D leads to parsing failures.

Next, import the recovered data dictionary into MySQL. If you encounter the error "ERROR 1148 (42000) ... command is not allowed with this MySQL version", add the --local-infile option to the MySQL client. The recover_dictionary.sh script from the tool can be edited to include this flag, replacing the generic mysql calls with mysql --local-infile -uroot -pYOUR_PASSWORD.

After the dictionary is restored, obtain CREATE TABLE statements for each table. If the original schema is lost, use the application's migration framework (e.g., Django, Rails, Laravel) to recreate the tables, then run mysqldump -d DBNAME TABLENAME -uroot -p > schema.sql. The c_parser only accepts clean CREATE statements, so the dump options --add-drop-table=0 and --add-lock=0 are required.

Extract table data with the following command (adjust paths and table names accordingly):

./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000002410.page -t output.sql > dumps/default/TABLENAME 2> load_cmd.sql

Load the extracted rows using:

mysql --local-infile -uroot -p DBNAME < load_cmd.sql

Verify the import by querying the restored tables. Repeat the three‑step process (export schema, extract data, import) for each table you need to recover. Note that tables using utf8mb4 with emoji characters may fail during import, and no reliable fix is provided in this guide.

Following these steps successfully restores individual tables and, by repetition, most of the lost database, provided the data files remain intact.