MySQL 5.7 Transportable Tablespace

2019独角兽企业重金招聘Python工程师标准>>>

将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.

转载于:https://my.oschina.net/xxj123go/blog/1786742