Recover InnoDB tables using .frm and .ibd files

on April 12 | in Hosting / Servers, Linux, Programming / Scripting | by | with 1 Comment

In this article I will show how to recover or restore InnoDB tables using .frm and .ibd files. MySQL server was enabled with innodb_file_per_table which means  it stores InnoDB tables data in a tbl_name.ibd and table information in tbl.name.frm. If you are using MySQL 5.6.x or higher then you are using innodb_file_per_file as a default. This method will work even you lost original ibdata1 file.

1- Install MySQL Server and Utilities

We will require MySQL Server 5.6.x or higher and mysqlfrm from utilities package which manage to extract table information and generate CREATE TABLE query from .frm file.

First enable oracle mysql yum repository for CentOS 7/RHEL 7. Note below rpm is the latest at the time of download, please check for latest everytime.

Install require packages

2- Recover.

Location for my backup database were /data-backup/sohail-wp/. Now I will recover my CREATE TABLE sql queries from .frm files using mysqlfrm utility.

a) Recover CREATE TABLE query from one table.

b) Recover CREATE TABLE query from all tables.

c) Run following command to replace empty lines with ; (simi-colons) in create-table.sql file . This is a quick and dirty method to make CREATE TABLE sql query executable on mysql prompt by importing the file otherwise you need to enter ; (semi-colon) after every CREATE TABLE sql query end one by one.

d) Create database same as original, in my case it is sohail_wp

e) Re-create all tables from create-table-complete.sql files inside sohail_wp database.

The above comamnd will generate all tables inside the default location of mysql datadir i.e /var/lib/mysql/sohail-wp with names tbl_name.frm and tbl_name.ibd.

f) As they are newly created empty table, now we will discard tablespace to get import our data from old backup from tbl_name.ibd files.

You need to run above ALTER TABLE command for each table. It can be scripted through command line.

The above command will delete all *.ibd files from /var/lib/mysql/sohail_wp/ directory. Which were having empty data as newly created table. We will replace them with backup.

g) Copy only *.ibd files old backup.

Here we copied all the *.ibd files from backup which contains actual table data from our old database.

h) Now its time to get import tablespace from copied *.ibd files.

For all tables you can below script, modified as per your requirements.

This helps me to restore/recover my InnoDB Database. It might help others, please comment for questions.

pixelstats trackingpixel
Pin It

related posts

One Response to Recover InnoDB tables using .frm and .ibd files

  1. John says:

    THx but pls help me 🙁

    mysql> ALTER TABLE userseries IMPORT TABLESPACE; ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

    i tried to swith that into compact at first run, the error was the same but switched 😀 like wtf

    before ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_COMPACT row format, .ibd file has ROW_TYPE_DYNAMIC row format.)

    now

    ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

Leave a Reply

Your email address will not be published. Required fields are marked *

«