Planet MySQL :: Planet MySQL - Archives - Understanding InnoDB transportable tablespaces in ...

If you’re anything like me, your initial reaction upon hearing about transportable tablespaces for InnoDB in 5.6 was to imagine it like MyISAM, where you can copy the .frm, .myi and .myd files around to your heart’s content, and everything will be great. You might have read Sunny’s excellent blog, and realized that there’s a bit more to it than that – you have to explicitly prepare the tablespace for copying using FLUSH TABLES FOR EXPORT. That’s perfectly acceptable for the bulk of use cases, such as single-table InnoDB backups, and opens up exciting new possibilities for moving or copying InnoDB data at the filesystem level.

But for situations where the need is a little different, you might really dug into it and start to wonder about the .cfg files produced during the FLUSH TABLES FOR EXPORT operation. What happens if you don’t have – and can’t produce – that information? What does that .cfg file contain that makes it critical to success importing the tablespace later?

The .cfg file contains InnoDB data dictionary information – how many columns there are, what the data types are, what order they are in, what indexes exist. That information is key to deciphering the .ibd contents. Without that map, InnoDB can’t map out the .ibd file contents. If the .cfg file doesn’t align with the .ibd file, you get errors like this:

mysql> CREATE TABLE tt1 (a INT PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO tt1 VALUES (1);
Query OK, 1 row affected (0.06 sec)

mysql> FLUSH TABLES tt1 FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

mysql> -- save tt1.cfg file for later
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE tt1 MODIFY a INT UNSIGNED;
Query OK, 1 row affected (0.30 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> FLUSH TABLES tt1 FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

mysql> -- save tt1.ibd file for later
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE tt1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.05 sec)

mysql> -- restore tt1.cfg and tt1.ibd
mysql> ALTER TABLE tt1 IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Column a precise type mismatch.)
mysql>

You also need the target table definition to align with the .cfg (data dictionary) and .ibd (data) files. Trying to restore to a table where the table definition (stored in .frm file) does not match the .cfg and .ibd files produces similar errors:

mysql> CREATE TABLE tt2 LIKE tt1;
Query OK, 0 rows affected (0.13 sec)

mysql> ALTER TABLE tt1 MODIFY a BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
Query OK, 4194304 rows affected (1 min 7.93 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

mysql> FLUSH TABLES tt1 FOR EXPORT;
Query OK, 0 rows affected (0.01 sec)

mysql> -- copy tt1.ibd as tt2.ibd and tt1.cfg as tt2.cfg here
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE tt2 IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Column a precise type mismatch.)

So, you need three things to be exactly aligned in order to successfully execute IMPORT TABLESPACE:

Data organization of .ibd file
InnoDB data dictionary information stored in .cfg file
Server table definition

The good news is that the .cfg files do not have to correspond to the .ibd file state at the time it was produced. If you know the (exact) table definition that corresponds to the .ibd file you have, you can create a new .cfg file that will allow InnoDB to map the .ibd file to the target table. Here’s an example:

mysql> CREATE TABLE tt2 LIKE tt1;
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM tt2;
Empty set (0.00 sec)

mysql> INSERT INTO tt1 SELECT NULL FROM tt1;

Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tt1 SELECT NULL FROM tt1;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0
...
mysql> INSERT INTO tt1 SELECT NULL FROM tt1;
Query OK, 2097152 rows affected (32.89 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM tt1;
+----------+
| count(*) |
+----------+
|  4194304 |
+----------+
1 row in set (1.44 sec)

mysql> FLUSH TABLES tt1, tt2 FOR EXPORT;
Query OK, 0 rows affected (0.01 sec)

mysql> -- copy tt1.ibd,  tt2.cfg for later
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE tt2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.04 sec)

mysql> -- copy tt2.cfg back, and tt1.ibd as tt2.ibd
mysql> ALTER TABLE tt2 IMPORT TABLESPACE;
Query OK, 0 rows affected (3.24 sec)

mysql> SELECT COUNT(*) FROM tt2;
+----------+
| count(*) |
+----------+
|  4194304 |
+----------+
1 row in set (1.62 sec)

So, if you know the table structure, you don’t have to worry about .cfg files – you can always recreate them as needed. And there’s this feature request, which will make it so that you won’t even necessarily need .cfg files. That is helpful when all you have is the file-per-table tablespace. Most importantly, you can use this MySQL 5.6 feature to restore data from stranded file-per-table .ibd files from earlier MySQL versions:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.27    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tt_55 (a INT PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO tt_55 VALUE (1);
Query OK, 1 row affected (0.01 sec)

mysql> EXIT
Bye

C:\mysql-5.6.6-m9-winx64>REM shutting down 5.5 to get clean tt_55.ibd file

C:\mysql-5.6.6-m9-winx64>bin\mysqladmin -uroot -P3309 shutdown

C:\mysql-5.6.6-m9-winx64>bin\mysql -uroot -P3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.6-m9 MySQL Community Server (GPL)
...
mysql> CREATE TABLE tt_55 (a INT PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> FLUSH TABLES tt_55 FOR EXPORT;
Query OK, 0 rows affected (0.02 sec)

mysql> -- save a copy of tt_55.cfg
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE tt_55 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.04 sec)

mysql> -- copy tt_55.ibd from 5.5 install to 5.6
mysql> -- make sure tt_55.cfg is in proper location
mysql> ALTER TABLE tt_55 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.10 sec)

mysql> SELECT * FROM tt_55;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

That’s exciting progress for data recovery when you have a valid file-per-table .ibd file, but a corrupted or missing InnoDB system tablespace file. It gives hope for relatively easy data recovery using the new 5.6 feature – even if the stranded .ibd file was produced by an earlier version of MySQL.

Good work, InnoDB team!

MySQL Links