【MySQL】mysql表空间迁移

1、非general表空间的迁移

（1）只支持innodb引擎的表，且必须innodb_file_per_table=1。
（2）支持导入不需要.cfg文件的表空间，这给崩溃恢复带来了很大的便利。
（3）flush table … for export;会有读锁，此时只能读不能写。

Transportable Tablespace(TT) of innodb provids a different way in backing up and restoring a single table between servers.
TT merely supports innodb engine which can store data in tablespaces of their own by setting "innodb_file_per_table=1".
TT supports importing tablespace without .cfg file what brings about us much convenience in crash recovery.
Notice that there will be shared read locks on the tables after execute "flush table ... for export;" what really influences the tables need to be write.

参考：https://www.cnblogs.com/aaron8219/p/9265169.html

需求：将a实例的表的数据迁移到b实例上.

操作：
1.在目标库b上创建一个相同结构的表

2.在目标库b上执行ALTER TABLE t DISCARD TABLESPACE;

3.在源库a上执行FLUSH TABLES t FOR EXPORT;生成.cfg文件 ----会有锁

4.将.ibd文件和.cfg文件拷贝到目标库b文件下

5.在源库a执行unlock tables;

6.在目标库b执行ALTER TABLE t IMPORT TABLESPACE;

2、general表空间的迁移

因上述方法不适用general表空间，则general表空间目前无法迁移，只能做表导出再导入。

root my1>flush table my1.player for export;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root my1>show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                      |
+---------+------+------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1235 | InnoDB: This version of MySQL doesn't yet support 'FLUSH TABLES FOR EXPORT on table `my1`.`player` in a general tablespace.' |
+---------+------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)