How to Recover InnoDB MySQL Table Data from ibdata and frm Files?

MySQL Server uses two types of database storage engines – InnoDB and MyISAM. When InnoDB storage engine is used, MySQL Server stores the data and indexes in .ibdata file, and table schema in .frm file. In case of corruption in InnoDB tables, you can easily recover the InnoDB MySQL tables’ data from .ibdata and .frm files, instead of restoring the complete database backup. In this article, we will discuss how to recover data from .ibdata and .frm files in MySQL Server.

Stepwise Process to Recover InnoDB Tables from ibdata and frm Files

Here’s the step-by-step process to recover InnoDB tables’ data from .frm and .ibdata files in MySQL Server:

• You need to first create a new MySQL database. For this, login into the PhpMyAdmin, click on the Databases option, type a database name (e.g. Clients), and then click Create.

• Next, go to the folder of your XAMPP installation to check the newly created database. The default location is C:xamppmysqldata.

• When you open the new database, you will find that it does not contain any tables. To create the table, you need the schema of table. If you have an updated backup file, then you can get the schema and name of the database. You can use the same name to create the table. But if you don’t have backup, then you can use the .frm file to extract the schema of the table. There are various reliable utilities available online that can help you to extract table schema.

• Now use the same schema to create table in the newly created database.

• Once you create the table, it will automatically create new .frm and .ibd files in the database folder.

• Next, go to your XAMPP installation folder and delete the .ibd file.

• Then, copy the original .ibd file and paste it into the new database.

• Before copying the original .ibd file, execute the below query:
  ALTER TABLE table_name DISCARD TABLESPACE;
  Note: The DISCARD TABLESPACE command will break the link between the MySQL table and the tablespace, thus helps prevent inconsistencies and other issues.

• Next, restore the link between MySQL table and Tablespace by using the below command:
  ALTER TABLE table_name IMPORT TABLESPACE;
  Note: You can restore data from a single table at a time by using .frm and .ibd files. If you need to restore multiple tables, then it will take a lot of time as you need to perform all the steps for each table manually.

Alternate Solution to Recover InnoDB MySQL Table

To restore multiple InnoDB tables at once from MySQL Server database quickly and easily, you can use a professional MySQL database recovery tool. Stellar Repair for MySQL is one such easy-to-use tool that can recover InnoDB tables and other data, such as primary keys, views, triggers, etc. from corrupted or damaged MySQL databases. The tool features a rich and interactive user interface that helps you to perform the repair and recovery operation quickly in just a few simple steps.

Key Features of Stellar Repair for MySQL

Here are some key capabilities of Stellar Repair for MySQL:

• Supports MySQL Server 8.0.36 and lower versions

• Supports all MariaDB versions up to 11.3.2

• Restores all data from MySQL databases, including deleted records and partition tables

• Repairs MySQL tables with complete precision

• Provides an enhanced preview of repaired data before saving

• Allows repairing of multiple databases at once

• Allows you to save repaired MySQL database in multiple formats, like MySQL, MariaDB, SQL Script, CSV, HTML, and XLS

• Compatible with both Windows and Linux operating systems, including CentOS 7 (64-bit) / Red Hat Enterprise Linux 7 (64-bit) and Ubuntu 16.04 (32-bit & 64-bit) / Ubuntu 18.04 & 19.10 (64-bit)

Conclusion

Above, we have explained the detailed step-by-step process to recover InnoDB MySQL table data from ibdata and .frm files. However, this is not suitable for restoring multiple tables as you need to perform all the steps manually for every table. Alternatively, you can use a professional MySQL database recovery tool, such as Stellar Repair for MySQL that can help you extract all the objects, including tables, primary keys, views, triggers, etc. from corrupted database files with complete integrity. The tool supports repairing of databases created using both InnoDB and MyISAM storage engines. It is designed to address a wide range of corruption-related errors in MySQL database. A free demo version of Stellar Repair for MySQL is also available that allows to scan the database file and preview the recoverable data.

The above is the detailed content of How to Recover InnoDB MySQL Table Data from ibdata and frm Files?. For more information, please follow other related articles on the PHP Chinese website!