Recover MySQL database from FRM files in WordPress

Everything happens in a day. I realized that my website (yeah! techbiriyani.com) stopped working only when i tried to log in to my website. Everything seems dead, i logged in from hosting account and found that Apache is working fine but, MySQL server is not running.

Also read: Disable Driver Signature Verification In Windows 10

I tried to restart MySQL but, every time it failed to respond. Now my fear went over my files and my website data. I have no recent backup’s. There’s no way to backup my website data unless MySQL starts running. After a week of research, i can’t find a possible solution, so i copied all files from my MySQL data directory and removed all other files.

What now? while asking this question myself i started searching for guides to recover MySQL data from files. Every guide i found on the internet is not suitable for my needs. So i decided to give a try myself with the available guides.

The solution i did to Recover MySQL databases was not available anywhere on the internet. so I thought that I should make this post to help others who experienced a similar issue.

As my website is powered by wordpress all database tables were created by wordpress itself, so i used wordpress table names in recovering steps. Apart from this one, all other functions were the same for both wordpress sites and normal web sites.

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 or read a quick summary of them below:

MyISAM Databases

A MySQL MyISAM table is the combination of three files:

  • The FRM file is the table definition.
  • The MYD file is where the actual data is stored.
  • The MYI file is where the indexes created on the table are stored.

You should be able to restore by copying them in your database folder (In xampp, the default location is C:\xampp\mysql\data)


InnoDB Databases

InnoDB stores its tables and indexes in a single tablespace *, which by default consist of the 3 files ibdata1, ib_logfile0, and ib_logfile1. For restoring a database you would also need these files. It is a combination of two files.

  • The FRM file is the table definition.
  • IBD file is where the actual data is stored.

Trending posts:

Recover MySQL database

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.

Requirements:

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

Some tables may show errors. In any case don’t forget to recover the tbale which contains your site data. These are the tables that you shouldn’t miss.

wp_optionsThe wp_options table is one of the most important WordPress database table and stores all the settings of a WordPress site like the URL, the title, installed plugins, etc.
wp_posts The most important table in WordPress and contains your site posts.
wp_postmeta Stores any additional information which is connected to a post
wp_commentsStores all your site comments
wp_terms The categories for both posts and links and the tags for posts are found within the wp_terms table.
wp_term_relationships Coordinate and connects all tags to posts, pages, and links.
wp_term_taxonomy It acts like metadata for the table wp_terms

Download and install XAMPP from above. After the installation completes browse to the folder C:\XAMPP\mysql\data and copy your database files here. Now open the xampp control panel and start apache and MySQL server.

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

Recover MyISAM database for WordPress

Recover MySQL database for MyISAM databases are quite simple but at various stages, it can make some troubles. I suggest you do a check on all tables by selecting them individually, so that way we can identify if any table causing an error. If all tables are working fine you can just export the database. To export a database:

  1. Login to phpMyAdmin
  2. Select Database
  3. Choose Export
  4. Click on Go, your table SQL file will be downloaded.

Most probably wp_options table may return you the error given below:

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

mysql table doesn't exist

So if you got an error similar to this then try to drop the table. Although the wp_options table is important for wordpress to run, it stores only website settings which we can recreate later, so it will not affect your website data. If deleting the table went success then it’s okay but if it returns an error similar to the image below read further.

1347 ‘tb.wp_options’ is not of type ‘VIEW’

mysql table drop error

Using Shell Command

If the table DROP method fails using phpMyAdmin we need to do that using 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:

use database_name;

Your database should now be selected, type below code to delete your table;

DROP TABLE wp_options;

Then delete wp_options.frm and wp_options.ibd files from C:\XAMPP\mysql\data\your_database folder.

Rebuilding WordPress

Next, we want to rebuild our WordPress website structure using the exported tables. For that, download WordPress and extract it to the C:\XAMPP\htdocs folder.

Download: WordPress

After extract, open wordpress folder. Rename wp-config-sample.php file to wp-config.php or make a copy. This is the file which we used to enter the credentials for the database connection. Now, open wp-config.php in a text editor and update the following:

You should create a new database and user in order to connect WordPress

// ** 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.

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.

Get the new files

Now select your new database and EXPORT your preferred table (eg: wp_options) you wish to backup and your table will be downloaded as SQL file.

Now, select your old database, then choose IMPORT to restore your table.

Now your entire database is repaired. Do this to each table which returns any error given above. Once again select each table in your database and ensure that everything working without any error.

We cannot recover InnoDB databases in this way. It is a bit more complicated than MyISAM tables. However, we have shared a guide on recovering InnoDB databases using FRM and IBD files in WordPress.

This is the guide to Recover MySQL database of MyISAM databases. If you find this post helpful don’t forget to share with your friends.

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