mysql - Restore table structure from frm and ibd files

Question

I am trying to restore a database in PMA but only have access to frm and ibd files - not the ib_log files which I understand you need.

I know I may not be able to recover the database data but is it possible to recover the structure of the tables from the frm files?

Answer 1

I restored the table from only .frm and .idb files.

Get the SQL query to create the tables

If you already know the schema of your tables, you can skip this step.

1. First, install MySQL Utilities. Then you can use mysqlfrm command in command prompt (cmd).

2. Second, get the SQL queries from .frm files using mysqlfrm command:

   mysqlfrm --diagnostic <path>/example_table.frm
   

Then you can get the SQL query to create same structured table. Like this:

CREATE TABLE `example_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) NOT NULL,
  `photo_url` varchar(150) NOT NULL,
  `password` varchar(600) NOT NULL,
  `active` smallint(6) NOT NULL,
  `plan` int(11) NOT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Create the tables

Create the table(s) using the above SQL query.

If the old data still exists, you may have to drop the respective database and tables first. Make sure you have a backup of the data files.

Restore the data

Run this query to remove new table data:

ALTER TABLE example_table DISCARD TABLESPACE;

This removes connections between the new .frm file and the (new, empty) .idb file. Also, remove the .idb file in the folder.

Then, put the old .idb file into the new folder, e.g.:

cp backup/example_table.ibd <path>/example_table.idb

Make sure that the .ibd files can be read by the mysql user, e.g. by running chown -R mysql:mysql *.ibd in the folder.

Run this query to import old data:

ALTER TABLE example_table IMPORT TABLESPACE;

This imports data from the .idb file and will restore the data.

Answer 2

Hello @tech

Same, One of my client's business database was corrupted in 2019 and we had only FRM files. You can also use the same solution that worked for me.

 

Here is the solution.

 

1- create some dummy tables with at least one column and SAME NAME with frm files in a new mysql database.

2-stop mysql service

3- copy and paste the old frm files to newly created table's frm files, it should ask you if you want to overwrite or not for each. replace all.

4-start mysql service, and you have your table structure...

 

As the best alternative you can use stellar Repair for MySQL  . I got this software from one of my office friends. You can try it.

Also Read, How to  Restore table structure from frm and ibd files