mysql5.7的版本表空间迁移技术

表空间迁移技术：实现表快速迁移。

1. 停止相关业务

mysql> lock tables t100w read;
mysql> delete from t100w where id=1000;
ERROR 1099 (HY000): Table 't100w' was locked with a READ lock and can't be updated
mysql> select * from t100w limit 10;

2. 目标库创建一摸一样的表

mysql> create database test  charset utf8mb4;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
mysql> CREATE TABLE `t100w` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `num` int(11) DEFAULT NULL,
    ->   `k1` char(2) DEFAULT NULL,
    ->   `k2` char(4) DEFAULT NULL,
    ->   `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
Query OK, 0 rows affected (0.02 sec)
*主：这里创建库可以查看原来库里面创建的库的语句
show create table t100w;

3. 将目标空的ibd表空间删除

mysql> alter table t100w discard tablespace;

4. 把原来库的idb文件迁移至目标库

[root@db01 test]# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/
[root@db01 test]# chown -R mysql.mysql /data

5. 目标库导入表空间

mysql> alter table t100w import tablespace;
检查是否导入成功；
mysql> select * from t100w limit 10;
+------+--------+------+------+---------------------+
| id   | num    | k1   | k2   | dt                  |
+------+--------+------+------+---------------------+
|    1 |  25503 | 0M   | IJ56 | 2019-08-12 11:41:16 |
|    2 | 756660 | rx   | bc67 | 2019-08-12 11:41:16 |
|    3 | 876710 | 2m   | tu67 | 2019-08-12 11:41:16 |
|    4 | 279106 | E0   | VWtu | 2019-08-12 11:41:16 |
|    5 | 641631 | At   | rsEF | 2019-08-12 11:41:16 |
|    6 | 584039 | QJ   | VWlm | 2019-08-12 11:41:16 |
|    7 | 541486 | vc   | ijKL | 2019-08-12 11:41:16 |
|    8 | 771751 | 47   | ghLM | 2019-08-12 11:41:16 |
|    9 | 752847 | aQ   | CDno | 2019-08-12 11:41:16 |
|   10 | 913759 | ej   | EFfg | 2019-08-12 11:41:16 |

6.把原来的库解锁业务

mysql> unlock tables ;