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:

wget https://www.percona.com/redir/downloads/Percona-Server-5.6/LATEST/release-5.6.14-62.0/483/binary/linux/i686/Percona-Server-5.6.14-rel62.0-483.Linux.i686.tar.gz 1 wget https : / / www .percona .com / redir / downloads / Percona - Server - 5.6 / LATEST / release - 5.6.14 - 62.0 / 483 / binary / linux / i686 / Percona - Server - 5.6.14 - rel62 . 0 - 483.Linux.i686.tar.gz

create sandbox

make_sandbox Percona-Server-5.6.14-rel62.0-483.Linux.i686.tar.gz 1 make_sandbox Percona - Server - 5.6.14 - rel62 . 0 - 483.Linux.i686.tar.gz

test it

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select @@versionG" *************************** 1. row *************************** @@version: 5.6.14-rel62.0 1 2 3 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "select @@versionG" * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * @ @ version : 5.6.14 - rel62 . 0

It Works!

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

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "show variables like 'datadir'" +---------------+---------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------+ | datadir | /home/mixa/sandboxes/msb_5_6_14/data/ | +---------------+---------------------------------------+ 1 2 3 4 5 6 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "show variables like 'datadir'" + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + | Variable_name | Value | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + | datadir | / home / mixa / sandboxes / msb_5_6_14 / data / | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "show variables like '%per_table'" +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 2 3 4 5 6 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "show variables like '%per_table'" + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - + | Variable_name | Value | + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - + | innodb_file_per_table | ON | + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - +

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

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "SET GLOBAL innodb_file_per_table=1" 1 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "SET GLOBAL innodb_file_per_table=1"

create empty payment table on 5.6 sandbox

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 test < payment_table.sql 1 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 test < payment_table .sql

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

cat payment_table.sql CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`) ) ENGINE=InnoDB 1 2 3 4 5 6 7 8 9 10 11 12 13 14 cat payment_table .sql CREATE TABLE ` payment ` ( ` payment_id ` smallint ( 5 ) unsigned NOT NULL AUTO_INCREMENT , ` customer_id ` smallint ( 5 ) unsigned NOT NULL , ` staff_id ` tinyint ( 3 ) unsigned NOT NULL , ` rental_id ` int ( 11 ) DEFAULT NULL , ` amount ` decimal ( 5 , 2 ) NOT NULL , ` payment_date ` datetime NOT NULL , ` last_update ` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY ( ` payment_id ` ) , KEY ` idx_fk_staff_id ` ( ` staff_id ` ) , KEY ` idx_fk_customer_id ` ( ` customer_id ` ) , KEY ` fk_payment_rental ` ( ` rental_id ` ) ) ENGINE = InnoDB

stop sandbox

./sandboxes/msb_5_6_14/stop 1 . / sandboxes / msb_5_6_14 / stop

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

cp ~/payment.ibd ~/sandboxes/msb_5_6_14/data/test/ -f 1 cp ~ / payment .ibd ~ / sandboxes / msb_5_6_14 / data / test / - f

make sure permissions are ok for .ibd file

sudo chmod 660 ~/sandboxes/msb_5_6_14/data/test/payment.ibd sudo chown : ~/sandboxes/msb_5_6_14/data/test/payment.ibd 1 2 sudo chmod 660 ~ / sandboxes / msb_5_6_14 / data / test / payment .ibd sudo chown : ~ / sandboxes / msb_5_6_14 / data / test / payment .ibd

start sandbox

./sandboxes/msb_5_6_14/start 1 . / sandboxes / msb_5_6_14 / start

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

select from table

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" ERROR 1146 (42S02) at line 1: Table 'test.payment' doesn't exist 1 2 3 4 5 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "select count(*) from test.payment" ERROR 2013 ( HY000 ) at line 1 : Lost connection to MySQL server during query mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "select count(*) from test.payment" ERROR 1146 ( 42S02 ) at line 1 : Table 'test.payment' doesn ' t exist

error log

2013-11-02 14:36:34 b7eff990 InnoDB: Error: table 'test/payment' InnoDB: in InnoDB data dictionary has tablespace id 7, InnoDB: but a tablespace with that id does not exist. There is InnoDB: a tablespace of name test/payment and id 10, though. Have InnoDB: you deleted or moved .ibd files? ... ... 2013-11-02 14:36:36 11640 [ERROR] InnoDB: Failed to find tablespace for table '"test"."payment"' in the cache. Attempting to load the tablespace with space id 7. 2013-11-02 14:36:36 11640 [ERROR] InnoDB: In file './test/payment.ibd', tablespace id and flags are 10 and 0, but in the InnoDB data dictionary they are 7 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2013-11-02 14:36:36 a31a2b90 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. 2013-11-02 14:36:36 11640 [ERROR] InnoDB: Could not find a valid tablespace file for 'test/payment'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2013-11-02 14:36:36 a31a2b90 InnoDB: cannot calculate statistics for table "test"."payment" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html 1 2 3 4 5 6 7 8 9 10 11 12 13 14 2013 - 11 - 02 14 : 36 : 34 b7eff990 InnoDB : Error : table 'test/payment' InnoDB : in InnoDB data dictionary has tablespace id 7 , InnoDB : but a tablespace with that id does not exist . There is InnoDB : a tablespace of name test / payment and id 10 , though . Have InnoDB : you deleted or moved .ibd files ? . . . . . . 2013 - 11 - 02 14 : 36 : 36 11640 [ ERROR ] InnoDB : Failed to find tablespace for table '"test"."payment"' in the cache . Attempting to load the tablespace with space id 7. 2013 - 11 - 02 14 : 36 : 36 11640 [ ERROR ] InnoDB : In file './test/payment.ibd' , tablespace id and flags are 10 and 0 , but in the InnoDB data dictionary they are 7 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE ? Please refer to http : / / dev .mysql .com / doc / refman / 5.6 / en / innodb - troubleshooting - datadict .html for how to resolve the issue . 2013 - 11 - 02 14 : 36 : 36 a31a2b90 InnoDB : Operating system error number 2 in a file operation . InnoDB : The error means the system cannot find the path specified . 2013 - 11 - 02 14 : 36 : 36 11640 [ ERROR ] InnoDB : Could not find a valid tablespace file for 'test/payment' . See http : / / dev .mysql .com / doc / refman / 5.6 / en / innodb - troubleshooting - datadict .html for how to resolve the issue . 2013 - 11 - 02 14 : 36 : 36 a31a2b90 InnoDB : cannot calculate statistics for table "test" . "payment" because the .ibd file is missing . For help , please refer to http : / / dev .mysql .com / doc / refman / 5.6 / en / innodb - troubleshooting .html

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

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "alter table test.payment discard tablespace; show warnings;" +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1812 | InnoDB: Tablespace is missing for table 'test/payment' | | Warning | 1812 | InnoDB: Tablespace is missing for table 'payment' | +---------+------+--------------------------------------------------------+ 1 2 3 4 5 6 7 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "alter table test.payment discard tablespace; show warnings;" + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Level | Code | Message | + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Warning | 1812 | InnoDB : Tablespace is missing for table 'test/payment' | | Warning | 1812 | InnoDB : Tablespace is missing for table 'payment' | + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

Import tablespace

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "alter table test.payment import tablespace; show warnings" +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/payment.cfg', will attempt to import without schema verification | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ 1 2 3 4 5 6 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "alter table test.payment import tablespace; show warnings" + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Level | Code | Message | + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Warning | 1810 | InnoDB : IO Read error : ( 2 , No such file or directory ) Error opening './test/payment.cfg' , will attempt to import without schema verification | + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

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

Now check if data exists in payment table on sandbox:

mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" +----------+ | count(*) | +----------+ | 16049 | +----------+ mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select * from test.payment limit 1G" *************************** 1. row *************************** payment_id: 1 customer_id: 1 staff_id: 1 rental_id: 76 amount: 2.99 payment_date: 0000-00-09 03:49:32 last_update: 2028-02-08 12:32:35 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "select count(*) from test.payment" + -- -- -- -- -- + | count ( * ) | + -- -- -- -- -- + | 16049 | + -- -- -- -- -- + mysql - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 - e "select * from test.payment limit 1G" * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * payment_id : 1 customer_id : 1 staff_id : 1 rental_id : 76 amount : 2.99 payment_date : 0000 - 00 - 09 03 : 49 : 32 last_update : 2028 - 02 - 08 12 : 32 : 35

Exists.

So dump it from sandbox and restore on 5.5:

dump from 5.6

mysqldump -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 --add-drop-table test payment > ~/payment_dump.sql 1 mysqldump - umsandbox - pmsandbox -- host = 127.0.0.1 -- port = 5614 -- add - drop - table test payment > ~ / payment_dump .sql

restore to 5.5

mysql -u user -p < ~/payment_dump.sql 1 mysql - u user - p < ~ / payment_dump .sql

Check if data exists on 5.5

mysql -u root -e "select * from test.payment limit 3;" +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | payment_id | customer_id | staff_id | rental_id | amount | payment_date | last_update | +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | 1 | 1 | 1 | 76 | 2.99 | 0000-00-09 03:49:32 | 2028-02-08 12:32:35 | | 2 | 1 | 1 | 573 | 0.99 | 0000-00-09 03:49:32 | 0000-00-00 00:00:00 | | 3 | 1 | 1 | 1185 | 5.99 | 0000-00-09 03:49:37 | 0000-00-00 00:00:00 | +------------+-------------+----------+-----------+--------+---------------------+---------------------+ 1 2 3 4 5 6 7 8 mysql - u root - e "select * from test.payment limit 3;" + -- -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + | payment_id | customer_id | staff_id | rental_id | amount | payment_date | last_update | + -- -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + | 1 | 1 | 1 | 76 | 2.99 | 0000 - 00 - 09 03 : 49 : 32 | 2028 - 02 - 08 12 : 32 : 35 | | 2 | 1 | 1 | 573 | 0.99 | 0000 - 00 - 09 03 : 49 : 32 | 0000 - 00 - 00 00 : 00 : 00 | | 3 | 1 | 1 | 1185 | 5.99 | 0000 - 00 - 09 03 : 49 : 37 | 0000 - 00 - 00 00 : 00 : 00 | + -- -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - +

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