How to Recover InnoDB MySQL files using XAMPP on Windows

Last week I had the good luck of having a power failure on one of our production servers, causing the corruption of some InnoDB databases stored on the MySQL server. As you may know, most InnoDB corruptions are hardware-related and corrupted page writes can be caused by power failures or bad memory.

Although there were fresh backups, I asked to myself what would someone without backups do in case of such unfortunate event. For my surprise, a lot of devs claim that is very difficult to restore databases from InnoDB with only the data folder of MySQL, however i tried a basic combination and i was fortunately able to export almost all databases from the files with MySQL dump. Today, i'll share with you how to export .sql files from all the idb and frm files inside the data folder of MySQL in Xampp for Windows.

Requirements

In order to try to recuperate the data of an old MySQL installation in a new one, you will need:

1. A Clean installation of XAMPP (with the same or higher version of MySQL) that works and you are willing to destroy.
2. An entire copy of the mysql data folder from the server that you are trying to recover e.g /var/lib/mysql in Ubuntu with a Plesk installation. This copy includes all the data from your databases, including the ibdata1, ib_logfile0 and ib_logfile1 files.
3. A lot of luck (really, you will need it).

Having said that, let's get started !

1. Make a copy of the mysql/data content of your server

The first step is obviously having a copy of the data folder of MySQL that you want to restore in XAMPP (e.g /var/lib/mysql in Linux). We assume that you have all the content of the data folder of mysql that doesn't work in your server and we'll try to restore it.

This data has a format of folders with the name of every of your databases, inside those folders you will find IDB and FRM files that contain theoretically the data of your databases and you will be able to recover them as long as they aren't corrupted:

Once you have this data, proceed with the next step.

2. Add content to local mysql/data folder

As next you need to create a backup of your mysql/data folder of xampp locally in case that something fails, so you will only need to use the old content of mysql/data and everything will be normal again. In the local mysql/data folder of XAMPP you will need to delete all the original content except for the following directories (they can't change, so be sure to remove those directories from the data of your server):

• mysql (directory)
• mysql_upgrade_info (file)
• performance_schema (directory)

Then, add the content from your server (step 1) without replacing the previously mentioned files and directories.

3. Start MySQL server

After pasting the content, start the mysql service from the control panel:

In case that the server is unable to start

If the server is unable to start due to corrupt tables in some of your databases, you can force it to start using the recovery of InnoDB. To do it, modify the my.cnf (in Windows my.ini at C:/xampp7/mysql/bin) file of MySQL and add 2 new properties namely innodb_file_per_table and innodb_force_recovery in the mysqld block:

[mysqld]
# Increment this until the server starts up to a value of 6 if it the
# server can't start after pasting the new /mysql/data content
# See: https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
innodb_force_recovery = 2
innodb_file_per_table=1

This will allow you to start MySQL to save the databases that aren't corrupted. Save changes on the file and restart the mysql service and you would be theoretically able to dump the databases now.

4. Dump databases

Using the command prompt, switch to the directory of the binaries of MySQL in the installation of Xampp, usually C:\xampp\mysql\bin:

cd C:\xampp\mysql\bin

And execute the mysql console accesing as root or whatever user you used to have on the installation of Xampp:

mysql -u root

This will open the mysql console, this means that you can run queries, specifically the one that list all the databases available on the engine (SHOW DATABASES;):

Finally, quit the mysql process in the command process and start by exporting your databases with mysqldump:

mysqldump -u root -p DATABASE_NAME > OUTPUT_DATABASE_BACKUP.sql

Unless one of the tables of your database is corrupted, you will be able to export it without a problem. Sometimes, the mysql service will crash, however restarting it and trying again will be the solution.

Information

As you may be searching for a solution for your problem, you may probably noticed how difficult it is to find something that works as according to your case, some things may not be an option for you. That's why we'll list as well more articles that may be useful for you and will give you an idea of how to solve your problem (in case that it's really possible, otherwise the backups will be your only salvation):

1. Recovering a corrupted InnoDB Table.
2. How to Recover innoDB MySQL files using MAMP on a Mac.
3. How to restore data from InnoDB IDB and FRM files.
4. Restore table from frm and idb files in InnoDB.

Happy recovering !