innodb表空间传输

原库：t1

新库：t2

1.在t1库下会形成t1.ibd和t1.cfg文件，此时t1表只能读不能写。

root@localhost:mysql3317.sock  09:34:32 [t1]>select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshuo |
+------+-----------+

root@localhost:mysql3317.sock  09:34:34 [t1]>flush tables t1 for export;
Query OK, 0 rows affected (0.05 sec)

  root@localhost:mysql3317.sock 09:37:14 [t1]>insert into t1(id,name) values(2,'zhangsanfeng');
  ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

  [root@bogon ~]# cd /data/mysql/mysql3317/data/t1/
  [root@bogon test]# ls
  t1.cfg t1.frm t1.ibd

2.创建新表t1，表结构必须与源表t1相同(使用另一个会话)。

root@localhost:mysql3317.sock  09:51:03 [t2]>create table t1 (id int,name varchar(255));

3.释放新表t1表空间后t1.ibd会消失。

root@localhost:mysql3317.sock  09:51:39 [t2]>alter table t1 discard tablespace;

4.将xxx.ibd和xxx.cfg拷贝到目标位置。

[root@bogon t1]# cp -p t1.cfg t1.ibd /data/mysql/mysql3317/data/t2/

5.unlock tables

root@localhost:mysql3317.sock  09:59:26 [t1]>unlock tables;

6.接到文件更新权限。

root@localhost:mysql3317.sock  10:18:38 [t2]>alter table t1 import tablespace;
root@localhost:mysql3317.sock  10:19:31 [t2]>select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshuo |
+------+-----------+