Recover InnoDB database from .frm and .ibd files

Yesterday in Magebit Urgent Support an unusual issue came in. A client’s website stopped working after restart of the server so the client came to us. Nothing was done on the server based on the incoming request.

The solution we did was not available anywhere so I thought that I should make this post to help others that have a similar issue.

Getting to the root cause

When our team started investigating the issue we found the root cause of the issue. Together with the restart the MySQL had upgraded from 5.5.49 to 5.5.53.

This error popped out in the logs:

[ERROR] Failed to access directory for — secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server.

Seems like a minor change, but it is not at all so small. Within these versions the MySQL directory changes from /var/lib/mysql to /var/lib/mysql-files.

In case anyone is having a similar issue even after the folder change, please check also the my.cnf file and make sure the socket and datadir for mysqld and client are set properly.

Table exists or not?

Once the directory was changed, the database still didn’t want to work. While it started and functioned (process was running), it showed that the tables do not exist in the Magento database when any query was ran. Select, describe or any query resulted in the following:

ERROR 1146 (42S02): Table ‘database.mage_core_resource’ doesn’t exist

Database exports did not work. Table structure was not possible to get via the MySQL service as all queries returned the error above.

Step 1: Get the structure

Note: This only works if you have the following in the config file. It is required so the data gets stored in the .frm/.ibd files not in the ibdata file.

[mysqld]innodb_file_per_table = 1

So the first step to recover InnoDB database from only *.frm and *.ibd files is to get the tables structure dump (CREATE TABLE …). There are multiple ways to get the structure but I will tell how we did it.

There is a site https://recovery.twindb.com/ — it is very helpful and saved us a lot of time. For full database restore it asked 299 USD and it did not contain any data (so they understood only .frm files). We used the site for its FREE tool — Recover structure from .frm file. It is also possible to upload an archive with all .frm files inside. That is where we got the CREATE TABLE statements in a file.

Another way that you could use to get the structure is available in this page: http://www.hexblot.com/blog/recovering-innodb-tables-ibd-and-frm-files

We didn’t use it but you might want to try it if this method does not succeed.

Step 2: Create a sandbox

In MySQL 5.5 the Step 3 does not work because the InnoDB is not that advanced in 5.5. We used Amazon Web Services to create an empty instance on Linux with clean MySQL 5.6.

At first we needed to get the dummy database .sql file with the CREATE TABLE statements. We also need the broken mysql folder here as we will need it in Step 3. So copy the /var/lib/mysql-files/brokendb folder to the sandbox (we put them in home dir). Then make sure that the MySQL config has the following configs:

[mysqld]innodb_file_per_table = 1innodb_force_recovery = 1

Next, we created the dummy database and imported the tables structure we got in Step 1. Then we needed to remove the .frm file dependencies to the .ibd tablespaces. That could be done on the working dummy database with the following query:

ALTER TABLE mage_core_resource DISCARD TABLESPACE;

We had 448 tables so we did not write this manually for all tables. We used this query to generate the queries:

USE INFORMATION_SCHEMA;SELECT CONCAT(“ALTER TABLE `”, TABLE_SCHEMA,”`.`”, TABLE_NAME, “` DISCARD TABLESPACE;”) AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = “dummy”;

From the output we removed heading and footer and then find&replace all | symbols. Then it was ready to go for copy and paste in the MySQL.

Step 3: Re-link .ibd files with .frm files

At this moment the MySQL should be stopped so we did it. After that we copied all *.ibd files from the real database on the other (broken) environment to the sandbox environment dummy database.

cp -f ~/brokendb/*.ibd /var/lib/mysql-files/dummy/

Then we started the MySQL service. It started but the database was still not with the data. It was an empty database. We had to import the tablespaces from .ibd files so MySQL can understand them. This was achieved by the following query:

ALTER TABLE mage_core_resource IMPORT TABLESPACE;

We had 448 tables so we used the query generation method again. This time with IMPORT instead of DISCARD.

USE INFORMATION_SCHEMA;SELECT CONCAT(“ALTER TABLE `”, TABLE_SCHEMA,”`.`”, TABLE_NAME, “` IMPORT TABLESPACE;”) AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = “dummy”;

Did the same what before so we can copy and paste this into MySQL. In some places we read it says that it is ok that some errors occur but we didn’t get any.

In case you get error like below, try using another method and check if your sandbox has enough storage available (df -h). This article could help: http://sq4ind.eu/restoring-corrupted-innodb-mysql-databases/

ERROR 1030 (HY000): Got error -1 from storage engine

Step 4: Export and import

Now the database was recovered. The only thing left was to get the data exported from the dummy database.

mysqldump -uroot -p dummy > ~/dump.sql

Then the dump needs to be copied over to the real environment. We did it with scp but others may prefer sFTP or similar.

scp user@sandbox:~/dump.sql user@env:~/dump.sql

And then imported in a new database on the environment with MySQL 5.5 where the issue happened at the first place.

mysql -uroot -p workingdatabase < ~/dump.sql

That’s it!

As it was a Magento site we had to switch the database from broken to recovered one in app/etc/local.xml file and the site was back again.

In case you need any urgent help for any kind of website work — let us know and we will be happy to assist you. To receive urgent help use this link: https://magebit.com/urgent-help.html