Restore Table Structure from Frm and Ibd Files

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 /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 /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.

Restore MySQL database using only .frm and .ibd files

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.

How to restore data on database when only have the .frm files and ibdata1

Simply by using XAMPP I can restore the database back as what I expect only with .frm files and ibdata1.

Here is the experiment:

1. Installing XAMPP in this case using XAMPP v3.2.1
2. Open drive (in this case drive C:) then search xampp folder
3. Find the mysql folder
4. Copy your old database that only contain .frm files and your ibdata1 to data folder inside mysql folder
5. Start your mysql server then check your database, your data is back!

NOTE: When you already install XAMPP on your computer, you must stop mysql first before copying the old database

---

Related Topics