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.
icon

 

MySQL Database Recovery

Recover Data of .frm and .ibd database files

star 4.4 based on 1,011 user reviews

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.

Steps to Retrieve MySQL database from .frm and .ibd Files

  1. Download and Run the MySQL Database Repair Software.

    Download and Run the Aryson MySQL Database Repair Tool.

  2. Open Frm and Idb file

    Click on the Open button to select MySQL Database (.idb and .frm) file.

  3. Scanning MySQL Database Frm and Idb file

    Click on the OK button to start scanning the selected MySQL Database file.

  4. Preview MySQL Database files

    Check and preview the recovered MySQL Database file.

  5. Save Recovered Frm and Idb file

    Select the MySQL database tables and click the Save MySQL Data button.

  6. Choose Saving options

    Here, choose the saving mode: save in the database or Save as Script.

  7. Click on OK button to Recover Files

    Check the Database connection and click Ok to start the restoration process.

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.

5/5 - (1 vote)

About The Author:

I am a technical content writer at Aryson Technologies with an experience of 2 year specializing in writing about databases, data recovery, file management, e-mail recovery & migration solutions. Also, I am proficient in working in a challenging environment using all my skills and want to serve the reputed growth of the industry in the development field.

Related Post

Aryson Technologies footer logo

united states

2880 Zanker Road, Suite 203, San Jose, CA - 95134, USA

india

A-58 , 2nd Floor A Block Sector 57, Noida, Uttar Pradesh 201301

© Copyrights 2014-2024 by Aryson Technologies Private Limited - All Rights Reserved