Restore table structure from frm and ibd files

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.

InnoDB needs the ib_log files for data recovery, but it also needs the ibdata1 file which contains the data dictionary and sometimes contains pending data for the tables.

The data dictionary is kind of a duplicate system that records table structure and also matches a table id to the physical .ibd file that contains the table data.

You can't just move .ibd files around without the InnoDB data dictionary, and the data dictionary must match the table id found inside the .ibd file. You can reattach a .ibd file and recover the data, but the procedure is not for the faint of heart. See http://www.chriscalender.com/recovering-an-innodb-table-from-only-an-ibd-file/

You can recover the structure using the .frm files with some file trickery, but you will not be able to create them as InnoDB tables at first. Here's a blog that covers a method for recovering .frm files as MyISAM tables: http://www.percona.com/blog/2008/12/17/recovering-create-table-statement-from-frm-file/

You won't be able to use PMA for this. You need superuser access to the data directory on the server.