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

You can recover table structure from .frm files and data from ibd files.

Using mysqlfrm tool which is part of MySQL Utilities

shell> mysqlfrm --diagnostic myfile.frm

Recreate the table in a database of the same name using table structure.

mysql> CREATE mytable (int i);

Discard the tablespace of the newly created table.

mysql> ALTER TABLE mytable DISCARD TABLESPACE;

Copy the orphan .idb file from your backup directory to the new database directory. Ensure that the .ibd file has the necessary file permissions.

Import the orphan .ibd file. A warning is issued indicating that InnoDB will attempt to import the file without schema verification.

mysql> ALTER TABLE r IMPORT TABLESPACE;SHOW WARNINGS;