Recover MAMP MySQL database using .frm and .ibd files after InnoDB crash

After a power faliur (also can be a sudden restart or system crash), I ended up with corrupted database and lost the access to my local hosted websites for development. Even the MAMP's MySQL server was not starting.

Preparation

You will need to find the databases folders, in case of MAMP they are located in Applications/MAMP/db/mysql56 (or mysql57 depending on MySQL version).

You will find folders containing the database name, inside them you will find .frm and .ibd files. Take a copy of the entire folder for backup in another place, the desktop for example.

Go back to the Applications/MAMP/db/mysql56 and delete 3 files, ib_logfile0, ib_logfile1 and ibdata1. This will get your MAMP's MySQL server back to work.

We will use dbsake on macOS to recover tables structure.

Install dbsake

In the terminal type the following commands:

curl -s http://get.dbsake.net > dbsake

chmod u+x dbsake

Then insure that you installed dbsake properly by typing the following command:

./dbsake --version

You should get something like this:

dbsake, version 2.1.2 (git: 50de953)

Restore Tables Structure

In the in database backup folder on the Desktop, for every .frm in the database folder, do the following command in the terminal:

./dbsake frmdump /path/to/database/folder/filename.frm

HINT: Just type ./dbsake frmdump (notice the sapce at the end of the line), then drag the .frm file to the terminal window and the path will be inserted automatically.

The command will return SQL query that will be used to create the table in the database via phpMyAdmin, Sequel Pro or similar tools. You will get something like this:

  CREATE TABLE `wp_options` (
    `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `option_name` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `option_value` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `autoload` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes',
    PRIMARY KEY (`option_id`),
    UNIQUE KEY `option_name` (`option_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Copy this code and go to phpMyAdmin, delete (drop) the old corrupted database and create new empty one with the same name (don't forget to take a backup copy as stated above), click on it and go to SQL tab and passte the code. A table will be created.

Repeat this with every .frm file you have in the database folder. And now you have restored the database with tables structure but it has no data.

Restore Old Data

Now in the same SQL tab of the database in phpMyAdmin, paste the folowing SQL query (Change wp_options with the table name that you have):

ALTER TABLE wp_options DISCARD TABLESPACE;

This code will unlink and delete the empty, newly created .ibd files. Thses files holdes the actual data of the database tables, while .frm holds the tables structure only. Repeat this line of code for every database table you have (of course change the table name evry time).

After that, go to the database backup folder on the Desktop and copy all the .ibd files there and paste them in the newlycreated database folder in Applications/MAMP/db/mysql56 (or mysql57).

Then, in the same SQL tab of the database in phpMyAdmin, paste the folowing SQL query (Change wp_options with the table name that you have):

ALTER TABLE wp_options IMPORT TABLESPACE;

This will link the new database tables to the old .ibd files which hold the actual old data.

Voila! you're done and you should get your local sites working again.