Restore table structure from frm and ibd files

Question

I'm trying to restore the database in the PMA but can only access the frm and ibd files - not the ib_log file 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 table from the frm file?

Answer 1

InnoDB requires the ib_log file for data recovery, but it also requires the ibdata1 file, which contains the data dictionary and sometimes the pending data for the table.

The data dictionary is a repeating system that records the structure of a table and matches the table ID to the physical .ibd file that contains the table data.

You cannot move an .ibd file without an InnoDB data dictionary, and the data dictionary must match the table ID found in the .ibd file. You canreattach the .ibd file and recover the data, but the process is not for the faint of heart. See http://www.chriscalender.com/recovering innodb tables from ibd files only/

You can use .frm files to restore the structures with some file tricks, but you can't create them as InnoDB tables in the first place. Here is a blog that explains how to restore .frm files to MyISAM tables: http://www.percona.com/blog/2008/12/17/recovering-create-table-statement-from-frm-file/" percona.com/blog/2008/12/17/recovering- create-table-statement-from-frm-file/

You will not be able to use PMA for this. You need superuser access to the data directory on the server.

Answer 2

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

Get the SQL query used to create the table

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

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

2. Secondly, use the mysqlfrm command to get the SQL query from the .frm file:

   mysqlfrm --diagnostic <path>/example_table.frm

You can then get the SQL query to create the 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 table

Create the table using the above SQL query.

If the old data still exists, you may need to delete the corresponding database and table first. Make sure you have a backup of your data files.

Data recovery

Run this query to delete new table data:

ALTER TABLE example_table DISCARD TABLESPACE;

This will delete the connection between the new .frm file and the (new, empty) .idb file. Also, delete the .idb file in the folder.

Then, put the old .idb file into the new folder, for example:

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

Ensure that the .ibd file can be read by the mysql user, for example by running chown -R mysql:mysql *.ibd in the folder.

Run this query to import old data:

ALTER TABLE example_table IMPORT TABLESPACE;

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