Answer a 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?

Answers

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.