Recover InnoDB tables using .frm and .ibd files

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.

rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm.

1	rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm.

Install require packages

yum -y install mysql-server mysql-utilities

1	yum -y install mysql-server mysql-utilities

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.

# mysqlfrm --server=root:root123@localhost --port=3307 --user=root /data-backup/sohail_wp/wp_users.frm # Source on localhost: ... connected. # Spawning server with --user=root. # Starting the spawned server on port 3307 ... done. # Reading .frm files # # Reading the wp_users.frm file. # # CREATE statement for wp_users.frm: # CREATE TABLE `wp_users` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_status` int(11) NOT NULL DEFAULT '0', `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`ID`), KEY `user_login_key` (`user_login`), KEY `user_nicename` (`user_nicename`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci #...done.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

# mysqlfrm --server=root:root123@localhost --port=3307 --user=root /data-backup/sohail_wp/wp_users.frm   # Source on localhost: ... connected. # Spawning server with --user=root. # Starting the spawned server on port 3307 ... done. # Reading .frm files # # Reading the wp_users.frm file. # # CREATE statement for wp_users.frm: #   CREATE TABLE `wp_users` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_status` int(11) NOT NULL DEFAULT '0', `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`ID`), KEY `user_login_key` (`user_login`), KEY `user_nicename` (`user_nicename`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci   #...done.

b) Recover CREATE TABLE query from all tables.

#mysqlfrm --server=root:root123@localhost --port=3307 --user=root /data-backup/sohail-wp/ > /tmp/create-table.sql

1	#mysqlfrm --server=root:root123@localhost --port=3307 --user=root /data-backup/sohail-wp/ > /tmp/create-table.sql

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.

sed 's/^$/;/' /tmp/create-table.sql > /tmp/create-table-complete.sql

1	sed 's/^$/;/' /tmp/create-table.sql > /tmp/create-table-complete.sql

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

mysql> create database sohail_wp; Query OK, 1 row affected (0.00 sec)

1 2	mysql> create database sohail_wp; Query OK, 1 row affected (0.00 sec)

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

mysql> source /tmp/create-table-complete.sql

1	mysql> source /tmp/create-table-complete.sql

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.

mysql> ALTER TABLE table_name DISCARD TABLESPACE;

1	mysql> ALTER TABLE table_name DISCARD TABLESPACE;

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

#mysql -u root -p -e"show tables from sohail_wp" | grep -v Tables_in_sohail_wp | while read a; do mysql -u root -p -e "ALTER TABLE sohail_wp.$a DISCARD TABLESPACE"; done

1	#mysql -u root -p -e"show tables from sohail_wp" | grep -v Tables_in_sohail_wp | while read a; do mysql -u root -p -e "ALTER TABLE sohail_wp.$a DISCARD TABLESPACE"; done

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.

# cp /data-backup/sohail_wp/*.ibd /var/lib/mysql/sohail_wp/

1	# cp /data-backup/sohail_wp/*.ibd /var/lib/mysql/sohail_wp/

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.

mysql> ALTER TABLE table_name IMPORT TABLESPACE;

1	mysql> ALTER TABLE table_name IMPORT TABLESPACE;

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

#mysql -u root -p -e"show tables from sohail_wp" | grep -v Tables_in_sohail_wp | while read a; do mysql -u root -p -e "ALTER TABLE sohail_wp.$a IMPORT TABLESPACE"; done

1	#mysql -u root -p -e"show tables from sohail_wp" | grep -v Tables_in_sohail_wp | while read a; do mysql -u root -p -e "ALTER TABLE sohail_wp.$a IMPORT TABLESPACE"; done

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