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

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.

Answer 3

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;

Answer 4

After years i wanna join in the discussion to share my solution, founded yesterday when i met the same issue.

I searched and tried tons of different stuff (like use Mysql Utilities script), recreate the table and so on ... but the real solution was a bit more easy (and tricky) to do:

Step 1)

• Backup old data folder (usually under mysql/data path)

Step 2)

• Format or install somewhere else a fresh and clear mysql-server

Step 3)

• Import from the backup (step 1) the folder(s) containing the frm/idb files in mysql/data

Note: ONLY the folders not all the files

Step 4)

• Import from the backup (step 1) the file called ibdata1 and overwrite it in the fresh mysql installation

Step 5)

• Restart the mysql server (this may ask some seconds before boot-up, just wait)

And that's all! Mysql server should boot-up normally after some seconds and if nothing is corrupted, files should be restored!

Little image to explain better:

Explanation:

- Blue: files to import in the new mysql-server (step 3)

- Red: files to NOT import in the new mysql-server

- Green: ibdata1 file to import in the new mysql-server (step 4)

Answer 5

You can also try with mysql utility.

From book.frm file to a file book.sql:

mysqlfrm --server=root:mysqladmin@localhost:3306 D:\yahwehdb\yahweh_altera\book.frm > D:\yahwehdb\yahweh_altera\book.frm\book.sql --diagnostic --port=3307 --user=root

From a directory containing all .frm file to a file all.sql:

mysqlfrm --server=root:mysqladmin@localhost:3306 D:\yahwehdb\yahweh_altera > D:\yahwehdb\yahweh_altera\all.sql --diagnostic --port=3307 --user=root

Answer 6

This is actually quite easy as long as you know how to do it, and no external software or shell commands are needed.

The database data is stored in C:\xampp\mysql\data\ or similar by default. The folders are the database tables. Inside each folder, the .frm file are the columns. The .ibd hold the row values.

First create the database(s) in PHPMyAdmin.

Get the SQL query generated from this site, under menu Recover structure > From .frm file:

https://recovery.twindb.com/

Upload each .frm file, and then copy and paste these queries into the SQL command to create the tables in PHPMyAdmin.

Then, on each table, do this SQL query:

ALTER TABLE table_name DISCARD TABLESPACE

This will automatically remove the new .ibd file from the database directory. Copy the old .ibd file into the database folder. Run the following command to activate the table again:

ALTER TABLE table_name IMPORT TABLESPACE

And that's it! You should be able to view and access all of your old values again.