Cluster of Hard Drives

MySQL InnoDB Data Recovery

A few days ago I had something like 80 tabs open in Chrome and Safari, Atom was running (with several tabs open too), numerous other applications and (listen, listen!) at a certain point I decided that I wanted to “play” with Garage Band and his “cheerful” synthesizers. Of course, I did it without thinking about closing some of the open browser tabs. I couldn’t live without them!
As a result the Macbook crashed. A crash like this has not happened for at least one year.

So far so good! Except that after this event MySQL could no longer start: shutdown itself shortly after launch. Looking at the logs I came to the conclusion that some table probably got corrupted. I tried to upgrade MySQL to a newer version but without success.
I have about 50 databases, installed locally, for my Web development projects and I do not do daily backups (this has been a good lesson to decide to plan a daily, automatic solution) and I opt for them when I think it’s the case. I had a backup copy of the local version of this site dating back about 4 days ago. It would still have been enough but I wanted to try and restore the state it was in just before the accident. That’s why I tried to find a solution on the internet to recover data.

First of all I have to premise that the database tables (mainly local installations of WordPress) were created using InnoDB as an engine. I do not know much about it, but Oracle published some useful utilities among which there is a very useful application called mysqlfrm and it’s part of a package, MySQL Utilities, which can be downloaded from this address: https://downloads.mysql.com/archives/utilities/. The installation is very simple but before you can use them you must also remember to install another package on which they depend: MySQL Connector/Python. Once you have installed this package you will have everything you need to proceed with data recovery.

Note

According to the official website, MySQL Utilities should be installed with MySQL Workbench but I had already installed it on the Macbook and I could not find mysqlfrm. Now I’m trying to install the latest release of MySQL Workbench and we’ll see…
MySQL WorkBench - MySQL Utilities

MySQL WorkBench – MySQL Utilities

Once you have installed these two programs you could also perform the whole procedure manually as explained in countless articles on the Web but I decided to further simplify your life. I have just finished writing a Shell Script between yesterday and today (compatible with all Unix and Unix Like environments) that does everything by itself. You can download it from here: recover_innodb

To run it is easy, open the Terminal application and run this command:

sudo ./recover_innodb.sh -d dest_db_name -b dest_data_dir -s src_db_name -c src_data_dir

The options for now are:

  • -d, to indicate the name of the resulting database,
  • -b, to indicate the path of the destination “data” directory,
  • -s, to indicate the name of the original database,
  • -c, to indicate the path of the original “data” directory.

If you are not wrong writing the paths the script should work correctly.

Good data recovery!