Todd's MySQL Blog

Tiny tidbits of trivia from Todd

Smarter InnoDB transportable tablespace management operations

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!

Single Post Navigation

Leave a Reply

Your email address will not be published. Required fields are marked *


5 − five =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>