MySQL透明表空间传输

透明表空间传输能够使用物理复制的方式快速迁移一张表，通常用于数据表存在问题时，通过异机恢复后再以该方式将表快速恢复到生产环境中。

源表信息如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


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    |                |
+-------+-------------+------+-----+---------+----------------+

目标端创建表结构

1
2


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文件)

1
2


root@test 16:02:  alter table c discard tablespace;
Query OK, 0 rows affected (0.03 sec)

源库将表锁定

1
2


root@test 15:58:  flush tables c for export;
Query OK, 0 rows affected (0.01 sec)

拷贝表的ibd和cfg文件到目标端datadir下

1
2


[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

源端表解锁

1
2


root@test 16:05:  unlock tables;
Query OK, 0 rows affected (0.00 sec)

目标端导入表空间文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


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 |
+---+------+