InnoDB Recovery from .frm .ibd files

A happy-end-horror-story the making of.. prerequisites for my recovery plan assuming that you had/have the following InnoDB setting:

    o

  • A recovery (development/virtual) machine
  • o

  • MySQL Utilities for schema (CREATE TABLE) recovery
  • o

  • XAMPP for rebuilding your databases

Create a recovery database

Start XAMPP and create a new database in my case ‘mydatabase’ got moved and stopped working and I created a ‘mydatabase_recovery’ database.

Install the MySQL Utilities and navigate to the installation location and open a command prompt:

This creates the create_mydatabase.sql file which contains the original structure CREATE TABLE code for your database. You can now change the file’s content occurences of ‘mydatabase’ to ‘mydatabase_recovery’ and execute it in your new database.

Once you’ve got your tables back do the following for each new table in ‘mydatabase_recovery’, this will delete the (.idb) data file for your new tables.

Now copy your .ibd files from your copied files to the following directory:

Make sure it has the same user rights and reassociate/import the data files (.idb) with the following code:

And check your table, if all went well you got your data back!

If you have MyISAM files as well just copy the .MYD (MyISAM data) files and execute the following for those tables:

And MySQL will fix your .MYI (MyISAM indexes) files, fortunately MyISAM is easier/more forgiving with restoring.

Errors

Hopefully you now no longer see the following errors:

5 thoughts on “InnoDB Recovery from .frm .ibd files”

  1. Hi,

    Thank you very much for your tutorial! I have followed all the steps with success and I was able to save my databases!

    1. Glad to hear it worked! That was the whole purpose of sharing this tutorial/information in the first place! 🙂 And next time: backups! 😉

    1. BTW, if you get the compact and dynamic error – put this at the end of TABLE creation query – row_format=compact;

Leave a Reply

Your email address will not be published. Required fields are marked *