MySQL 高频面试题解析 第03期：InnoDB 怎么做表空间迁移

无为，多年 MySQL DBA 工作经验，现就职于某知名互联网公司，对 MySQL、 Redis、PostgrepSQL 等主流数据库有一定了解，拥有丰富的一线运维经验。

1 迁移场景

2 实战

2.1 在源库创建一张测试表

mysql> use test;
Database changed

mysql> create table tmp_table(id int primary key,b varchar(20))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tmp_table select 1,'a';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tmp_table select 2,'b';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from tmp_table;
+----+------+
| id | b    |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

2.2 生成元数据文件

mysql> flush tables tmp_table for export; -- 注意：这一步会表锁，不能 insert、update

Query OK, 0 rows affected (0.01 sec)

~ root# cd usr/local/mysql/data/test

~ root# ls -l tmp*

-rw-r----- 1 _mysql _mysql  402 Nov 2 19:15 tmp_table.cfg -- 生成的 .cfg 文件

-rw-r----- 1 _mysql _mysql  8580 Nov 2 19:15 tmp_table.frm

-rw-r----- 1 _mysql _mysql 98304 Nov 2 19:15 tmp_table.ibd

2.3 创建目标表并丢弃现有表空间

mysql> create table tmp_table(id int  primary key,b varchar(20))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE  tmp_table DISCARD TABLESPACE; ---丢弃现有表空间

注意：※约束条件、字符集等等也必须一致，建议使用 show create table tmp_table; 来获取创建表的 SQL，否则在新服务器上导入表空间的时候会提示 1808 错误。

2.4 将元数据文件复制到目标实例

~ root# scp tmp_table.ibd tmp_table.cfg root@目标ip:/usr/local/mysql/data/test  ---传输到目标数据库的test schema目录

~ root# chown mysql.mysql tmp_table.cfg tmp_table.ibd --在目标数据库中授权

2.5 在源数据库解锁 tmp_table 表

mysql> unlock table;

Query OK, 0 rows affected (0.01 sec)

2.6 在目标数据库中导入 tmp_table 表空间

mysql> alter table tmp_table import tablespace;
Query OK, 0 rows affected (0.23 sec)
mysql> select * from tmp_table;
+----+------+
| id | b    |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

注意：

ALTER TABLE … IMPORT TABLESPACE 特性并不强制对导入的数据施加外键约束。如果表之间有外键约束，那么所有表都应该在相同的（逻辑的）点上导出。在这种情况下，您将停止更新表，提交所有事务，在表上获得共享锁，然后执行导出操作。