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

How to Fix it? In 5.6 tablespace management is very improved so the only thing needed is “ALTER TABLE .. DISCARD TABLESPACE” and “ALTER TABLE .. IMPORT TABLESPACE”.

Please check also limitations: Tablespace Copying Limitations

Look at an example:

Discard tablespace

Import tablespace

That’s it, data recovered, payment table accessible on 5.6 sandbox.

Now check if data exists in payment table on sandbox:


So dump it from sandbox and restore on 5.5:

dump from 5.6

restore to 5.5

Check if data exists on 5.5

During my work with this case, I got into a situation in which the drop table payment on 5.5 wasn’t possible because of payment.idb there wasn’t correct – so the server crashed each time I tried to access this table. The workaround is:
– stop server
– rm .ibd file
– start server
– drop table as usually by DROP TABLE command

Share this post

Comments (14)

  • Peter

    Hi, Thanks for this post, I tried following the same steps outlined above. but was still getting this error in the import table space step: ERROR 1146 (42S02) at line 1: Table ‘test.mytesttable’ doesn’t exist

    Any ideas?
    I’m using the manajro linux OS


    November 30, 2013 at 4:26 pm
  • Michael Rikmas

    I have no idea…
    On other OS I see this message before discard namespace, but not before import.
    I didn’t try manajro linux OS but I’ll try to reproduce it and will write result here.

    December 8, 2013 at 9:43 am
  • Peter


    Just to give an update, I got it to work by running the commands while logged into mysql instead of using the mysql execute option with mysql -e

    December 18, 2013 at 12:44 pm
  • Linas

    Thanks for the tutorial.
    The point is: you should backup your tables one by one. I mean that you need to drop the old table in the sandbox database before starting to backup the new one.

    February 19, 2014 at 1:42 am
  • MK_dir

    What is your my.cnf Configuration? Because after “alter table table_name import tablespace” i get allways an error:

    ERROR 2013 (HY000): Lost connection to MySQL server during query
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect…
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (111)
    Can’t connect to the server

    April 2, 2014 at 8:23 am
  • Peter Leiser

    Great guide! I followed your directions and recovered the data, but then I realized that all of the timestamps in my recovered data were incorrect. Then I looked at your examples and saw that all of your timestamps looked incorrect (e.g 0000-00-09 03:49:32 | 2028-02-08 12:32). Any idea on what’s going on with the timestamps? Cheers.

    January 18, 2015 at 9:57 pm
    • Antonio

      I have the same problem have you solved???

      June 12, 2016 at 6:16 pm
      • pavan

        anyone got the solution for the issue change timestamp????? Please update its urgent

        January 10, 2018 at 7:51 am
  • arunreasey

    My database got errors with ibd and frm.
    What should I do?
    ūüôĀ Please help me.

    July 6, 2015 at 11:11 am
  • Thomson

    nice tips, by the way. thanks for sharing.

    September 8, 2015 at 2:50 am
  • John

    good techniques. ūüôā

    September 8, 2015 at 2:52 am
  • Samy

    The tips are great. I found them useful.

    March 17, 2016 at 12:49 am
  • Jomana

    can you tell me or send me some sources to learn the basics of MySQL , as im still newbie and i want to learn more about it.

    September 17, 2016 at 11:52 am
  • Toowoomba

    What happen if I’m using something different than CentOS?

    May 19, 2020 at 7:48 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.