InnoDB 怎么做表空间迁移

亦凉 2022-12-21 06:08 145阅读 0赞

### **1** **迁移场景**     ###

在工作中经常会碰到单独迁移、复制或者备份某一张表的需求，一般可以通过逻辑/物理备份来实现。但是在 5.6.6+ 的版本中我们还可以用到一种基于表空间迁移的快速方法，本节内容就来聊聊这一操作。

**2** **实战**

**实验环境**：两个 MySQL 5.7.22  版本数据库实例。测试步骤如下：

**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** **生成元数据文件**

在源库上运行 FLUSH TABLES … FOR EXPORT 锁定表并生成 .cfg 元数据文件

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** **将元数据文件复制到目标实例**

将 .ibd 文件和 .cfg 元数据文件从源实例复制到目标实例：

~ 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** **表空间**

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