0%

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