Recover InnoDB tables using .frm and .ibd files

on April 12 | in Hosting / Servers, Linux, Programming / Scripting | by | with No Comments

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

Leave a Reply

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

«