MySQL传输表空间

MySQL 5.6开始可以把一个表从一个数据库迁移到另一个数据库或者机器上，方便于数据迁移。
传输表空间要求：
1、MySQL 5.6或以上版本
2、innodb_file_per_table是开启状态，即使用独立表空间方式
3、源端与目标端pagesize一致
4、表在传输时该表为只读状态

例：将ray.ray_t数据迁移至lei用户下

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lei                |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
mysql> select count() from ray.ray_t;
+----------+
| count() |
+----------+
|   262144 |
+----------+
1 row in set (0.06 sec)
mysql> select count(*) from lei.ray_t;
ERROR 1146 (42S02): Table 'lei.ray_t' doesn't exist

目标端创建表
在lei下创建跟ray.ray_t一模一样的表

mysql> show create table ray.ray_t;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| ray_t | CREATE TABLE ray_t (
id int(11) DEFAULT NULL,
KEY ray_t_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use lei;
Database changed
mysql> CREATE TABLE ray_t (
->   id int(11) DEFAULT NULL,
->   KEY ray_t_id (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> select count() from lei.ray_t;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

目标端表卸载表空间（lei.ray_t表空间卸载)

mysql> use lei
Database changed
mysql> alter table ray_t discard tablespace;
Query OK, 0 rows affected (0.00 sec)

源端表空间导出

mysql> use ray
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> flush table ray.ray_t for export;
Query OK, 0 rows affected (0.00 sec)

操作系统层复制文件至目标端

[root@ray1 ray]# cp ray_t.ibd ray_t.cfg /mysqldata/my3306/data/lei/
[root@ray1 ray]# chown -R mysql:mysql /mysqldata/my3306/data/lei/

源端解锁表

mysql> use ray;
Database changed
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

目标端表空间导入

mysql> use lei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> alter table lei.ray_t import tablespace;
Query OK, 0 rows affected (0.06 sec)

mysql> select count() from lei.ray_t;
+----------+
| count(*) |
+----------+
|   262144 |
+----------+
1 row in set (0.08 sec)