I’ve noted previously that the new transportable tablespaces for InnoDB in MySQL 5.6 are a big step forward, and newly-released 5.6.9-rc makes importing tablespaces a bit easier. In previous versions, you had to have a .cfg file to import the InnoDB tablespace. That file is produced during FLUSH TABLE <tbl> FOR EXPORT operations, and contains InnoDB metadata that’s not contained in the .ibd tablespace file itself. I filed a feature request requesting the .cfg file be made optional, and Sunny implemented it:
mysql> create table tt (a INT PRIMARY KEY, b VARCHAR(10), KEY(b)) ENGINE = InnoDB; Query OK, 0 rows affected (0.33 sec) mysql> INSERT INTO tt VALUES (1, 'one'), (2, 'two'); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> FLUSH TABLE tt FOR EXPORT; Query OK, 0 rows affected (0.08 sec) mysql> -- this eliminates to .cfg file, will confirm later: mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE tt DISCARD TABLESPACE; Query OK, 0 rows affected (0.09 sec) mysql> exit Bye D:\mysql-5.6.8-rc-win32>dir data\test Volume in drive D is Data Volume Serial Number is 4015-B2FF Directory of D:\mysql-5.6.8-rc-win32\data\test 11/07/2012 02:27 PM <DIR> . 11/07/2012 02:27 PM <DIR> .. 11/07/2012 02:25 PM 114,688 Copy of tt.ibd 10/15/2012 05:17 AM 0 dummy.bak 11/07/2012 02:25 PM 8,578 tt.frm 3 File(s) 123,266 bytes 2 Dir(s) 28,680,040,448 bytes free D:\mysql-5.6.8-rc-win32>xcopy "data\test\Copy of tt.ibd" data\test\tt.ibd Does data\test\tt.ibd specify a file name or directory name on the target (F = file, D = directory)? F data\test\Copy of tt.ibd 1 File(s) copied D:\mysql-5.6.8-rc-win32>dir data\test Volume in drive D is Data Volume Serial Number is 4015-B2FF Directory of D:\mysql-5.6.8-rc-win32\data\test 11/07/2012 02:27 PM <DIR> . 11/07/2012 02:27 PM <DIR> .. 11/07/2012 02:25 PM 114,688 Copy of tt.ibd 10/15/2012 05:17 AM 0 dummy.bak 11/07/2012 02:25 PM 8,578 tt.frm 11/07/2012 02:25 PM 114,688 tt.ibd 4 File(s) 237,954 bytes 2 Dir(s) 28,679,925,760 bytes free D:\mysql-5.6.8-rc-win32>bin\mysql -uroot -P3307 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.8-rc Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Database changed mysql> alter table tt discard tablespace; Query OK, 0 rows affected (0.06 sec) mysql> ALTER TABLE tt IMPORT TABLESPACE; Query OK, 0 rows affected, 1 warning (0.66 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1810 Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\ test\tt.cfg', will attempt to import without schema verification 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `a` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.03 sec) mysql> SELECT * FROM tt; +---+------+ | a | b | +---+------+ | 1 | one | | 2 | two | +---+------+ 2 rows in set (0.06 sec)
This will make it even easier to import InnoDB tablespaces for the vast majority of scenarios. Most notably, it makes it easier when you have to restore from a .ibd file that may not have been collected while FLUSH TABLE … FOR EXPORT was in effect (e.g., a server crash), and don’t have time to workaround the problem by producing dummy .cfg files in the workaround I suggested earlier. As noted by the warning, schema verification can’t be done, so if you try to import a tablespace that doesn’t align with the table structure, you can expect to run into problems.
Thanks Sunny and the InnoDB team for streamlining this process!