Easy Methods to Recover MySQL Database from FRM and IBD Files

Summary:- When MySQL Database Server is synced with the InnoDB database engine, it creates an .FRM file, which stores table schema and .IBD file, which will store data and indexes of a MySQL database. However, sometimes, it has been found that users face data deletion, database corruption, and data loss issues while accessing their data; in such situations, for accessing and managing data again, it is better to recover MySQL Database from FRM and IBD Files because it contains a table-specific tablespace and index data. Download Now   Purchase Now

In this blog, we will be going to discuss the two best methods to restore MySQL Database from FRM and IBD Files. Besides, you can also use the alternate Aryson MySQL Database Repair Tool if the manual solution does not help you to recover MySQL Database from FRM and IBD files.

Why Need to Restore tables from the .frm and .ibd files?

Several causes lead to the need to recover MySQL Database from FRM and IBD Files:

• A Hardware failure is a cause behind the MySQL database file corruption.
• Bugs occur in the operating system and database product.
• Accidentally or unintentionally shutdown of the system while the database is opened.
• Virus infection or malware detection is also one of the most common reasons to restore data.
• A MySQL database file .frm and .ibd is also corrupted when someone upgrades the MySQL Server from previous versions to newer ones.

Method to Recover MySQL Database from FRM and IBD Files Manually

The following steps will help you to restore Data from .FRM and .IBD Files. Just need to implement the steps correctly:

Step 1:- Create a MySQL Database to Recreate the Table

1. Here, we will create a MySQL Database with the help of phpMyAdmin.
2. First of all, Login into phpMyAdmin.
3. In the Create database section, enter a database name and click on the Create button. 
4. It will show you a newly created database named employee.
5. Go to the path where your MySQL database is present.
6. Here, you will find that a newly created MySQL database has been added to the list. However, make sure the database won’t have any .FRM and .IBD files. 
7. Now, you have to open the ‘employee’ database and see no table.

Step 2: Find the Table Schema

In case the Table Schema is present, then skip this process; otherwise, perform the given below steps:

1. First, download MySQL Utilities. 
2. It comprises a set of scripts to perform some common DBA tasks efficiently. 
3. This utility offers a Mysqlfrm tool that allows users to recover a table structure.
4. Now, you need to perform the given below command:
    mysql.FRM –diagnostic path filename.frm path
5. This command will help you read all the information in the .frm file.
6. You can quickly recover data from the damage .FRM file using this command, but it does not allow you to restore all the data from it. So it is best if you try this command to recover information from the .FRM file:
   mysqlfrm –server=root@127.0.0.1 –port 3307 ./filename.frm

Step 3: Create a new .FRM and .IBD file

In the Database you created earlier, open it and enter the script used in Step 2 to create a new table with new .FRM and .IBD files in the database data folder.

Step 4: Remove the Newly .ibd file

1. Execute this command to delete the newly created.IDB file:
   ALTER TABLE table_name DISCARD TABLESPACE;
2. It will successfully break the link between MySQL and the tablespace to remove the .IBD file.
3. Now, copy the original .IBD file in the new Database.

Step 5: Import Tablespace in MySQL 

1. Now, you need to recreate the connection between the MySQL table and tablespace once you complete the copy process of the original .IBD file. Execute the below command:
   ALTER TABLE table_name IMPORT TABLESPACE;

Drawbacks of the Manual Method

By performing the above steps, you can quickly restore or recover data from the FRM and IBD Files, but at some points, the processes have a few limitations like:

• You must perform all the steps, so you can’t skip any single process.
• The process is time-consuming and lengthy.
• The users must have the technical knowledge to perform the following steps.
• It may be a chance that you won’t recover all the data from the FRM and IBD Files.

Recover MySQL Database from FRM and IBD Files Professionally

Aryson MySQL Database Repair Tool is the best and alternate solution to restore Data from .FRM and .IBD Files without losing any data. The software is competent enough to retrieve or recover MySQL Database data from FRM and IBD files like tables, keys, table properties, data types, views, triggers, etc.

Conclusion

Above, we have discussed the two best methods to recover MySQL Database from FRM and IBD files. The manual approach has multiple following steps but helps you to restore data from FRM and IBD files completely. Although, we recommend you use the alternate solution to restore Data from .FRM and .IBD files.

Related Post