How to recover an orphaned .ibd file with MySQL 5.6

A few years ago Yves Trudeau and Aleksandr Kuzminsky wrote posts about different ways for recovering orphaned .ibd files:

Today I want to show you how to do that in a more easy and quick way. In my example, I’ll restore a “payment.ibd” file (payment table) from Sakila DB on a server with MySQL 5.5 (but with help from MySQL 5.6 and sandbox).

In my case the OS is CentOS. So I needed to:
install mysqlsandbox(check instructions there)
download latest Percona Server 5.6:

create sandbox

test it

It Works!

check datadir and if the innodb_file_per_table option enabled (this is a requirement)

If it’s not enabled then you’ll need to enable it

create empty payment table on 5.6 sandbox

payment_table.sql – is file with “SHOW CREATE TABLE” statement for payment table. The table structure should be the same.

stop sandbox

replace .ibd file (in my case the correct copy of it is located in my homedir)

make sure permissions are ok for .ibd file

start sandbox

Currently, if you’ll try to select something from the table you’ll get an error:

select from table

error log