How to Recover InnoDB database from .frm and .ibd files in WordPress

There are chances of losing databases of websites. I too faced the same situation with this website. One day my website is not accessible, after some research i found that my server MySQL service is not loaded. I tried to start MySQL service, but my MySQL was never started.

I did a little trick to get my data back and to restore my database. This trick will only work with WordPress Databases. So I am sharing this trick to help others who experienced a similar issue.

This method will only work with WordPress Websites.

Getting the Structure

Before beginning, you should have a small understanding of MyISAM and InnoDB databases. These are the most commonly used storage engines by MySQL. You can find the difference between MyISAM and InnoDB.

We use Xampp to Recover MySQL database and export our databases. It is the easiest and convenient way to recover corrupted databases. When i wrote this article i used PHP 7.2 in XAMPP. A small version change may bring troubles. So here i will mention the things i used to recover databases. We have published a guide on recovering MyISAM database from files recently.

InnoDB Databases

InnoDB is a common storage engine used by MySQL to store its data. Unlike other storage engines, InnoDB databases are more complicated to handle but good at performance. Follow below steps to recover InnoDB database for your wordpress website.

There are two table files for an InnoDB database. If wp_option is the table then there will be two files named wp_options.frm and wp_options.ibd.

Requirements:

  • Windows OS
  • XAMPP – Download
  • PHP version: 7.2.23
  • MySQL Data Files

Recover InnoDB Database for WordPress

Download and install XAMPP from above or ignore if you have already installed. Now open the xampp control panel and start apache and MySQL server.

Place your old MySQL data files somewhere, we need this later.

Now access your PHPMyAdmin via http://localhost/phpmyadmin and your database should be listed there. Upon selecting your database you will be able to see the tables and options in the database. You can also find your MySQL storage engine type in TYPE column. see below image:

myisam or innodb

If you select any table then PhpMyAdmin will return you the error given below:

#1932 – table ‘tb.wp_options’ doesn’t exist in engine.

mysql table doesn't exist

This is common for InnoDB databases. So read the following steps to backup your databases tables.

Download WordPress

DownloadWordPress

Download WordPress to rebuild your database files. Download WordPress then extract it to C:\xampp\htdocs folder.

Now we need to create a connection between WordPress and localhost. For that, we need to enter credentials in wp-config.php in WordPress package.

Create a DATABASE, DATABASE_USERNAME and PASSWORD using PhpMyAdmin.

Select New

phpmyadmin add new database

Type your desired Database Name and click Create

add new database

Select Privileges tab on top and will able to see the privileged users in first column. Click on Add user account to create a new user.

add database user

Enter your user account name and Generate a password for the account. Grant all privileges by checking the check all option.

Don’t forget to save the password somewhere else.

Recover InnoDB database
mysql add user permission

Connect with WordPress

Edit wp-config.php

Rename the wp-config-sample.php file to wp-config.php (located in the root install-directory).

Edit the following DB_NAME, DB_USER and DB_PASSWORD with your own.

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'database_name_here' );
/** MySQL database username */
define( 'DB_USER', 'username_here' );
/** MySQL database password */
define( 'DB_PASSWORD', 'password_here' );
/** MySQL hostname */
define( 'DB_HOST', 'localhost' );[ 

Now try to access the same http://localhost/your_folder_name. If the connection was successful you will be redirected to the install page.

Recover InnoDB database

After the installation login with your username and password for wordpress admin. If all went success leave it there come back to phpMyAdmin, then select your newly created database and there you will see a list of default tables created by WordPress itself.

Using Shell Command

Now, we need to open the cmd SHELL in XAMPP. Open SHELL from XAMPP control panel.

xampp command

Now a command window will appear to you, then type:

mysql -u root

Now, you have logged into MySQL, select your database by:

use database_name;

Your database should now be selected, type below code to discard your tablespace

ALTER TABLE _yourtable_name_ DISCARD TABLESPACE;

Then replace your old wp_options.frm and wp_options.ibd files with new files.

e.g. replace old wp_options.frm and wp_options.ibd files with new files.

Now, import tablespace by:

ALTER TABLE _yourtable_name_ IMPORT TABLESPACE;

Wait for a few seconds for the success message saying tablespace imported successfully. You can now exit from the command prompt and check whether your table imported successfully.

In this way, you can Recover InnoDB database for WordPress websites. If this guide helped you suggest to your friends whom you know about experiencing the same problem.

Follow TechBiriyani on FacebookTwitter to get all the latest updates.

Related threads:

Founder
View posts

A passionate blogger and a tech enthusiast who is always keen and awaiting to know about fully fledged new technologies who's currently using Zenfone 3 as a part of daily routine. You can spot me @ #facebook, #twitter & #Google +

Leave a Comment