Recently, I got to a situation where I was messing with my server on a live website and over the sudden, the server crashed and so did the website. Fixing the server was impossible so I got stuck only with WordPress database files & WordPress website files which from them I needed to restore the website.
I found myself browsing the web on searching how to restore WordPress database from .frm & .ibd files. Instead of having to restore the database with a few clicks it took me 2 days getting to the solution with a lot of trial and error on the way. Here I will save you time from trying other things that might not work and give the exact solution.

Restore WordPress Database From .frm & .ibd files

I was “playing” with the VPS server with Ubuntu OS, and then I did the most unbelievable thing. I ran the command (The following code is pure evil. Do Not run this code on your server): sudo chmod 7777 / . This Linux command equals to: Linux Devil

Instead of changing privileges on the current folder “./” I did it on the root folder “/” which caused a total crash to the server. Luckily I could enter the storage (volume) in a “back door” sort of speak, and FTP the MySQL database folder & files.

Tutorial Goal: Our goal is to have one big SQL file that has the entire database structure and the database data and executes the SQL code on a brand new database on our existing MySQL server or a new brand server instance with operable MySQL server.

Just for clearness, the tutorial assumes you have the WordPress *.frm & *.ibd files to recover from. Each .frm file holds a table structure and each .ibd file holds the data of a table that matches the .frm file structure.

Note the tutorial also recovers non-WordPress related tables, so if someone added a table to WordPress database the tutorial also recovers it, and hack if there is not even one single WordPress table related the tutorial covers it.

Note the tutorial bypasses getting the error: “#1932 – Table ” doesn’t exist in engine”, from the phpMyAdmin if anyone is searching the solution for that. Copying the recovered database folder to a new instance of MySQL won’t solve the problem!!!
If you did not receive the message anywhere then don’t pay attention to it and just follow the tutorial.

Note the tutorial will be for the Windows operating system. For Mac OS X users try following the steps and finding the parallel operations that match OS X.

Tutorial: Restore WordPress Database from .frm & .ibd files

Step 1: FTP the database folder to your PC.

First, FTP the database folder to your PC with WinSCP / FileZilla. You can zip the database folder if you admin user to the root folder and you can execute commands or just download the entire folder.

The database folder resides on the path:

/var/lib/mysql/<your_site_name>

For this example, we will use the “4test.xyz” domain so we will have a folder named “4test_xyz” after downloading (and unzipping the zip file) on our PC.
Notice that the dot “.” before the domain extension was changed to underscore “_”.

Step 2: Download MySQL Utilities (MSI file).

The MySQL utilities will a have a command line utility that you can restore a table structure from “.frm” file.

Step 2.1: Start the MySQL Utilities installation.

Step 2.2: Download & install Visual C++ Redistributable Packages (If not installed).

After downloading and running the installation you can come across a message that tells you that you need to have installed Visual C++ Redistributable Packages so Download and install it and start the MySQL Utilities installation over again.

Step 2.3: Add the MySQL Utilities to your system PATH.

Go to:

Desktop → My Computer (Right Click) → Properties → Advanced system settings → → Environment Variables…

Step2.3 MySQL Utilities Environment Variables

Select the PATH variable at the “User Variables” section (The upper box) and press “Edit” (This will add all the MySQL utilities to the Windows command line (CMD) for the current Windows user only. If you want to add the utilities for all Windows users than edit the PATH variable of the “System variables” at the bottom).

Step2.3 MySQL Utilities Environment Variables Path

Next, add the path of the MySQL utilities installation folder, for example, the default installation path:

C:\Program Files\MySQL\MySQL Utilities 1.6

First, press the “New” button and then put in the path above:Step2.3 MySQL Utilities Installation Folder Path

Press the “OK” button all the way until you won’t have any settings open nor windows.

Step 3: Download & Install & Run XAMPP.

XAMPP is an AMP (Apache & MySQL& PHP) stack server. It will help us generate the database SQL file.

Search on your Windows OS the term XAMPP and run the search result “XAMPP Control Panel” software.

Step 3.1: Set “innodb_file_per_table=1″ inside “my.ini” of the MySQL module.

On the XAMPP control panel go to MySQL → Config → my.ini, in order to edit the MySQL config file.

Step3.1 XAMPP MySQL Config File

In the “my.ini” file search for the text:

port= 3306

Under the port number line you should add the row (If the innodb_file_per_table variable exists than just change the value to 1):

innodb_file_per_table = 1

Step3.1 MySQL Editing my.ini File

Step 3.2: Change the Apache port number.

For most computers, the port number 80 is always taken by other software that is run on Windows. It is best to leave the “fight” on port 80 to other soft-wares and change the port that Apache runs with.

Back to XAMPP, on the Apache row go to Config → Apache (httpd.conf)

Step3.2 XAMPP Apache Config File

Next search & replace the line:

Listen 80

With the line:

Listen 8888

You can choose any port number you like up until 65535 but you have to make sure that it is not taken by any other software you have installed on your PC. You can check out the port numbers list that popular soft-wares use. It is preferably good you use a number greater than 8000. If you have a software that runs on port 8888 than just change the port number to 8889 and so on, until you find a free port,

Step3.2 XAMPP Apache httpd.Conf

Step 3.3: Start Apache & MySQL.

Click the “Start” buttons for each one of the modules: Apache & MySQL:

Step3.3 XAMPP Apache MySQL Start Buttons

Wait until both the Apache & MySQL texts have a green background. If they stick up until a minute with a red or yellow background than something went wrong and you have to fix it.

Notice that both Apache & MySQL have their own port numbers.

Apache → 8888
MySQL → 3306

Step3.3 XAMPP Apache MySQL Running Status

Step 4: Open Windows Command Line.

Search for term “cmd” on Windows OS search and run the program: “Command Prompt“.

Step 5: Navigate to the recovered database folder in the command line.

Let’s say the recovered folder name is “4test_xyz”, so we need to run the command:

cd c:\4test_xyz

Step 6: Create a .bat file that generates SQL file for creating tables structure from the .frm files.

Create a new text file in the recovered folder where the .frm files reside and change its name and extension to “RestoreTablesStructure.bat“.

Next, put the following code in the .bat file:

:: Creating temporary folder for storing all the tables structure SQL files
set structureFolder=tables_structure_0123456789
md "%structureFolder%"

:: This is the code that restores the tables structure from the *.frm files
for %%f in (*.frm) do (
 mysqlfrm --server=root:@localhost:3306 "%%~nf.frm" > "%structureFolder%\%%~nf.sql" --port 3310 -q
)

:: Creating SQL file for storing all the tables structure
set structureSQLFile=%structureFolder%.sql
type NUL > %structureSQLFile%

:: Concatenating the entire tables structure SQL files into one
for %%f in (%structureFolder%\*.sql) do (
 more +1 "%structureFolder%\%%~nf.sql" >> %structureSQLFile%
 echo ; >> %structureSQLFile%
)

:: Deleting the temporary structure folder
rmdir /s /q %structureFolder%

:: Creating the discard tablespan SQL commands for each table
for %%f in (*.frm) do (
 echo ALTER TABLE %%~nf DISCARD TABLESPACE; >> %structureSQLFile%
)

:: Creating the import tablespan SQL commands for each table
for %%f in (*.frm) do (
 echo ALTER TABLE %%~nf IMPORT TABLESPACE; >> tables_import_tablespan_0123456789.sql
)

Next run the bat file in the Windows CMD (command line), by entering the following command:

RestoreTablesStructure.bat

The process of generating the SQL file will take up to 5 minutes (on slow computers even up to 10). The thing that makes the process slow is restoring the tables structure from the .frm files. The command that restores the tables structure is mysqlfrm , which was installed in the MySQL Utilities.

Basically what the .bat code file does is:

Step 7: Navigate to the phpMyAdmin of the XAMPP servers.

Go to the browser and open a new tab with the following URL:

http://localhost:8888/phpmyadmin/

Step 8: Create a new database.

On phpMyAdmin create a new database. Click on the “New” link button on the left sidebar and on the right will show up a create database form which you will need to fill the following fields:

Press the “Create” button when you are done.

Step8 PhpMyAdmin Create New Database Form

Step 9: Execute the tables structure SQL.

Now is the time to execute the SQL we have generated previously for retrieving the tables structure.

On the phpMyAdmin go to the newly created database on the left sidebar, which in our case is “4test_xyz“, and click on it. On the right side will show up the database control panel. Click on the SQL tab on the top. Copy & paste the SQL code from the file: “tables_structure_0123456789.sql” we have generated earlier into the big text area and click the “Go” button at the bottom.

Step9 PhpMyAdmin Execute Tabels Structure SQL

Wait until it finishes executing the SQL. You should receive a message with a green background that says: “# MySQL returned an empty result set (i.e. zero rows).“.

Do not touch anything and go straight to the next step!!!

Step 10: Copy & paste the recovered *.ibd files into the new database folder.

On Windows Explorer go to the new database’s folder, which resides in the MySQL folder in the XAMPP installation. Assuming the database is “4test_xyz” then the path to its folder is:

C:\xampp\mysql\data\4test_xyz

At this point, you should only see .frm files only of the new database without the .ibd files, because we have deleted them with the discard tablespan SQL commands.

Now, copy & paste your recovered folder’s *.ibd files into the new database folder path.

Step 11: Execute the import tablespan SQL commands.

Go back to the phpMyAdmin from where ewe left off and click the SQL tab:

Step11 PhpMyAdmin SQL Success Execution

Now take the SQL code from the file: “tables_import_tablespan_0123456789.sql” we have generated earlier and execute it (Note this file is totally different from the file of the tables structure so pay attention to the file you are copying. One of the differences is the number of lines of the code, which far is far smaller).

Step11 PhpMyAdmin Import TableSpan SQL

Now we have recovered the database with its tables structure & data.

Step 12: Export the entire database into one big SQL file that contains all tables structure & data.

Go to the database control panel and select the “Export” tab.

On the “Export method” section select the option: “Custom – display all possible options“.

Step12 PhpMyAdmin Database Export

Scroll down to the “Object creation options” and checkmark the option:
Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement
This will make sure that each table we create will be deleted first before hand if it exists.

Step12 PhpMyAdmin Database Export Add Drop Table Commands

Next scroll down and press the “Go” button.

Step12 PhpMyAdmin Database Export Go Button

It will start downloading the database SQL file in the defined browser downloads folder. It will take some time so be patient.

Step 13: Create a new WordPress website with a regular installation.

Create a new WordPress installation with its own database and files like as you did regularly when starting a website.

Step 14: Import the SQL file we have Exported from the recovered database.

Two steps ago we have Exported the recovered database with its tables structure and data now its time to Import it:

Method 1:  Go to the phpMyAdmin the production environment and click on the left sidebar the newly installed WordPress database. On the right side of the database control panel press the “Import” tab.

Choose the exported SQL file on your PC and  hit the “Go” button at the bottom of the page.

Step14 PhpMyAdmin Import Database

Note you might get into a problem when trying to upload the SQL file because you will need the Max file size for uploading to be bigger at least with 1MB then the SQL file at hand.

This can be accomplished by changing the following parameters in the php.ini file at the Apache folder at your server:

upload_max_filesize = 25M
...
post_max_size = 25M

Of course, you will need to restart the server:

service httpd restart (CentOs)
service apache2 restart (ubuntu)

For shared hosting add the following in your .htaccess file:

php_value upload_max_filesize 25M
php_value post_max_size 25M

Make sure you delete those lines after finishing the import.

Method 2: Executing the mysql command for executing SQL files on remote servers from Windows Command line.

In Windows CMD go to:

cd C:\xampp\mysql\bin

Execute the following command by changing the parameters:

mysql -u {db_username} -h {MySQL_Host_name} -p {db_name} < {Full_path_to_the_SQL_file_on_PC}

Example:

mysql -h 45.55.229.215 -u 4test_xyz -p 4test_xyz < "C:\4test_xyz\4test_xyz.sql"

Step 15: Exit XAMPP Control Panel.

At the Window tray bar right click on the XAMPP icon and click on the “Quit” option.

Wrapping Up

We have finally finished retrieving the database from the .frm & .ibd files. For not getting to the situation again it is preferred to use a backup plugin for WordPress like BackupBuddy.

If you encounter any problem in the tutorial please add a comment and I will try my best to resolve your problem, or if you found an error or such.

Leave a Reply

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