MariaDB/MySQL — Restore Database From .frm And .ibd Files

A very fine morning, you came to know your production database is down.

And you login to mysql and fire select * on random table and mysql Says:

Table Doesn’t exists in database

And then you dig it deeper and get to know your ibdata files are corrupted!

But wait! You have enabled innodb_file_per_table configuration in your mysql!

Let’s get the data back! (Its already there we just need to recreate table space).

Install mysqlfrm and mysql-client (if its already not installed) in a same instance where your mysql is.

sudo apt-get install mysql-utilities mysql-client

Now create a restore database,

mysql -uroot -e “create database restored_mydatabase

Now Lets say you want to restore data of database named “mydatabase”

Step-1:

Get the table schema of mydatabase:

mysqlfrm /var/lib/mysql/mydatabase/*.frm — server=root@localhost — port 3308 — user mysql > mydatabase_schema.sql

Now we need to put semicolon in schema file!

sed -i ‘s/^$/;/’ mydatabase_schema.sql

Change the database name in schema:

sed -i ‘s/mydatabase/restored_mydatabase/g’ mydatabase_schema.sql

Now lets create schema of mydatabase into restored_mydatabase:

mysql -D restored_mydatabase < mydatabase_schema.sql — force

Now we have .frm files created for restored_mydatabase.

Now lets create SQL statements for discarding tablespace for all tables:

mysql -N -B <<’EOF’ > discard-ddl.sql
SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` DISCARD TABLESPACE;’) AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’restored_mydatabase’ AND ENGINE=’InnoDB’;
EOF

Now discarding table space!

mysql -D restored_mydatabase < discard-ddl.sql — force

Now copy all idb files for mydatabase directory to restored_mydatabase directory:

cp /var/lib/mysql/mydatabase/*.ibd /var/lib/mysql/restored_mydatabase/

Change ownership to mysql :

chown -R mysql:mysql /var/lib/mysql/restored_mydatabase/

And now yout restored_database has your old data back but wait! it doesn’t have it in table space!

Create statements for importing tablespace with your data!

mysql -N -B <<'EOF' > import-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` IMPORT TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=‘restored_mydatabase’ AND ENGINE='InnoDB';
EOF

Now Import tablespace:

mysql -D restored_mydatabase < import-ddl.sql — force

Login to mysql and check if you got your data:

mysql -uroot
use restored_mydatabase
select * from table

Now take dump of restored_database and restore to original one:

mysqldump -uroot restored_mydatabase > restored_mydatabase.sql
mysql -uroot -e "DROP DATABASE mydatabase"
mysql -uroot -e "CREATE DATABASE mydatabase"
mysql -uroot -D mydatabase < restored_mydatabase.sql

That’s it!

References:

Alter all table's - discard tablespace

Restore lost data in MySQL using InnoDB engine without file ibdata1

Recover InnoDB database from .frm and .ibd files

5.10 mysqlfrm - File reader for .frm files.