透明表空间传输能够使用物理复制的方式快速迁移一张表，通常用于数据表存在问题时，通过异机恢复后再以该方式将表快速恢复到生产环境中。
源表信息如下：
root@test 15:56: select * from c;
+---+------+
| a | b |
+---+------+
| 1 | lu |
| 2 | heng |
| 3 | xing |
+---+------+
3 rows in set (0.00 sec)
root@test 15:57: desc c;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| a | int | NO | PRI | NULL | auto_increment |
| b | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
|
目标端创建表结构
root@test 16:02: create table c(a int auto_increment primary key,b varchar(20));
Query OK, 0 rows affected (0.02 sec)
|
目标端discard tablespace(会删除表的ibd文件)
root@test 16:02: alter table c discard tablespace;
Query OK, 0 rows affected (0.03 sec)
|
源库将表锁定
root@test 15:58: flush tables c for export;
Query OK, 0 rows affected (0.01 sec)
|
拷贝表的ibd和cfg文件到目标端datadir下
[root@t-luhx01-v-szzb test]# scp c.* @10.0.139.162:/service/mysql/data/test/
[root@t-luhx02-v-szzb test]# chown -R mysql.mysql c.cfg c.ibd
|
源端表解锁
root@test 16:05: unlock tables;
Query OK, 0 rows affected (0.00 sec)
|
目标端导入表空间文件
root@test 16:04: alter table c import tablespace;
Query OK, 0 rows affected (0.11 sec)
root@test 16:10: select * from c;
+---+------+
| a | b |
+---+------+
| 1 | lu |
| 2 | heng |
| 3 | xing |
+---+------+
|
)