How to Restore the Mysql databases from FRM and IBD files

All the issues were sudden. My website stopped working after a server restart. I remember, nothing was done to the server before. This type of error came for the first time.

Also read: Redirect non-www to www using .htaccess

When the error occurs, I was a bit more confident based on my experience in running websites. But, everything went like a nightmare on me. Still, i don’t know what caused the error but i recovered my databases successfully.

The solution i did to recover my 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.

MyISAM or InnoDB

Before beginning, we have to make sure about your MySQL data engine type. Like the headline, most commonly used storage engine in MySQL are MyISAM and InnoDB. Main differences between MyISAM and InnoDB table are the following:

MyISAMInnoDB
only do Full table-level locking Supports row-level locking
Normal crash recovery Better crash recovery
Supports FULLTEXT search indexesNo support
No transactions by tables Implements transactions, foreign keys and relationship constraints

You can find all the differences here.


MyISAM Table

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 Table

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.

InnoDB tables cannot be copied the same way that MyISAM tables can. Just copying the .frm and .ibd files to MySQL data folder will not work.

Requirements:

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

You need to login to phpMyAdmin for finding your SQL engine, for that install XAMPP from below.

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 Database

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 table

Now you should have an idea about MyISAM and InnoDB tables. As i have mentioned, MyISAM tables can be recovered easily by simply copying database files to the data folder.

Exporting database

I recommend you to backup your database tables individually as there may be a chance of having error on some table. If you have a lots of tables then try to open each table and make sure there is no error throwing and then Export as SQL.

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

Importing database

  1. Login to phpMyAdmin
  2. Select Import
  3. Choose your file
  4. Choose Go, your SQL file will be imported.

Recover InnoDB table

Recovering InnoDB database is a bit tricky. You need to use some command-line interface to dumb tables.

How to recover InnoDB database from FRM and IBD files.

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