将5.7更新后的Transportable Tablespace 功能做一总结

一 普通表和分区表的传输

将db1的t1表传输到db2上

普通表创建 CREATE TABLE t1 (i int) ENGINE = InnoDB ;
分区表创建 CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;

db1上
1.flush table t1 for export;
2.将 t1 表的 .cfg 和 .idb 文件拷贝出来
3.unlock tables;

db2上
1.创建相同的表结构 CREATE TABLE t1…
2.alter table t1 discard tablespace ;
3.将 db1 的.cfg 和 .idb 文件拷贝到db2并修改权限
4.alter table t1 import  tablespace ;

二 分区表的若干分区传输

将db1的t1表的p2,p3分区传输到db2上

分区表创建 CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
db1上
1.flush table t1 for export;
2.将 t1 表的对应分区的 .cfg 和 .idb 文件拷贝出来
3.unlock tables;

db2上
1.创建相同的表结构 CREATE TABLE t1…
2.alter table t1 discard PARTITION p2,p3 tablespace ;
3.将 db1 的对应分区的.cfg 和 .idb 文件拷贝到db2并修改权限
4.alter table t1 import PARTITION p2, p3 tablespace ;

三 Internals

The following information describes internals and error log messaging for the transportable tablespaces
copy procedure for a regular InnoDB table.
When ALTER TABLE … DISCARD TABLESPACE is run on the destination instance:
• The table is locked in X mode.
• The tablespace is detached from the table.
When FLUSH TABLES … FOR EXPORT is run on the source instance:
• The table being flushed for export is locked in shared mode.
• The purge coordinator thread is stopped.
• Dirty pages are synchronized to disk.
• Table metadata is written to the binary .cfg file.

When UNLOCK TABLES is run on the source instance:
• The binary .cfg file is deleted.
• The shared lock on the table or tables being imported is released and the purge coordinator thread is
restarted.

When ALTER TABLE … IMPORT TABLESPACE is run on the destination instance, the import algorithm
performs the following operations for each tablespace being imported:
• Each tablespace page is checked for corruption.
Storing InnoDB Undo Logs in Separate Tablespaces
2384
• The space ID and log sequence numbers (LSNs) on each page are updated
• Flags are validated and LSN updated for the header page.
• Btree pages are updated.
• The page state is set to dirty so that it is written to disk.