MySQL通过传输表空间实现大表ibd文件的物理迁移的具体方案

目录

• 主要sql脚本
• 重要前提与警告
• 迁移场景与步骤
• 场景一：从运行中的mysql服务器迁移（最常用）
• 场景二：从物理备份文件恢复（仅有 .frm 和 .ibd 文件）
• 总结与工作流图示

基于 *.ibd 和 *.frm 文件进行 InnoDB 表的数据迁移，核心是利用了 InnoDB 的 “可传输表空间” 特性。

这种方法比执行 mysqldump 或 SELECT ... INTO OUTFILE 要快得多，尤其适用于大表迁移，因为它直接复制物理文件。

主要sql脚本

以下sql脚本支持在MySQL5.7.44和MySQL8.4.6之间进行ibd的迁移和恢复！

在MySQL 8目标库上操作——

1. create database test1;

2. CREATE TABLE test1.`sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(50) DEFAULT NULL,
  `sale_date` date DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

3. ALTER TABLE test1.sales DISCARD TABLESPACE;

4. 导入MySQL5.7.44中sales表的ibd文件至对应的数据目录下；

5. 重启数据库后执行下述导入表空间操作；

6. ALTER TABLE test1.sales IMPORT TABLESPACE;

SELECT * FROM sales;

SELECT Vhttp://www.devze.comERSION();

重要前提与警告

1. MySQL版本：此方法适用于 MySQL 5.6 及更高版本。不同大版本之间（如从 5.7 迁移到 8.0）可能有问题，最好在同版本或小版本间进行。
2. 存储引擎：必须是 InnoDB 表。
3. 配置：必须开启 innodb_file_per_table（默认就是开启的）。这个配置意味着每个表都有自己独立的 *.ibd 文件。
4. 文件一致性：复制的 *.ibd 文件必须与数据库的逻辑状态保持一致。因此，操作过程中需要将表置于一种锁定的状态。
5. MySQL 8.0+ 注意：从 MySQL 8.0 开始，不再有 *.frm 文件。表结构存储在数据字典中。如果你只有 *.frm 和 *.ibd&nbsIgAxIp;文件，说明它们来自旧版本（如 5.7）。在 8.0 中恢复时，需要先创建一个表结构完全相同的表。

迁移场景与步骤

假设我们要将表 mydatabase.mytable 从 源服务器 迁移到 目标服务器。

场景一：从运行中的MySQL服务器迁移（最常用）

这种方法适用于源表可被短暂锁定的情况。

在源服务器上操作：

• 在目标服务器上创建空表

-- 在目标服务器的数据库中，先创建一个表结构完全相同的空表。
-- 你可以通过 `SHOW CREATE TABLE mydatabase.mytable\G` 在源服务器上获取建表语句。
CREATE TABLE mydatabase.mytable (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

• 丢弃目标表的表空间

-- 这个操作会删除目标表新创建的、空的 .ibd 文件。
ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

执行后，目标服务器的 mytable.ibd 文件会被删除。

在源服务器上操作：

• 锁定并准备源表

-- 对源表加一个读锁，并生成一个 .cfg 文件（包含表空间元数据）。
FLUSH TABLES mydatabase.mytable FOR EXPORT;编程客栈

执行这个命令后：

• 表 mytable 会被加上读锁，仅允许查询，不允许写入。
• 在 mydatabase&n编程bsp;目录下，会生成一个 mytable.cfg 文件。

复制文件

在操作系统层面，从源服务器的数据目录复制三个文件到安全的地方：

# 进入MySQL数据目录下的数据库目录
cd /var/lib/mysql/mydatabase

# 复制文件
cp mytable.cfg mytable.ibd /path/to/backup/directory/

解锁源表

-- 复制完成后，立即解锁源表，恢复写入。
UNLOCK TABLES;

这个操作会同时删除 mytable.cfg 文件。

在目标服务器上操作：

传输文件

将刚才复制的 mytable.ibd 和 mytable.cfg 文件传输到目标服务器的对应数据库目录下（如 /var/lib/mysql/mydatabase/），并确保文件所有者是 mysql 用户。

scp /path/to/backup/mytable.{ibd,cfg} user@target-server:/var/lib/mysql/mydatabase/
chown mysql:mysql /var/lib/mysql/mydatabase/mytable.*

导入表空间

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

执行这个命令后，MySQL 会读取 mytable.cfg 文件来验证表空间的一致性，然后将数据导入。

验证

SELECT COUNT(*) FROM mydatabase.mytable;

场景二：从物理备份文件恢复（仅有 .frm 和 .ibd 文件）

这种情况通常是你只有物理文件，没有运行中的源MySQL实例。这更像是一种数据恢复操作。

前提：你必须知道http://www.devze.com该表的精确表结构。

在目标服务器上操作：

• 创建表结构完全相同的空表

-- 这是最关键的一步！表结构必须与源表100%一致（列名、类型、索引、行格式等）。
CREATE TABLE mydatabase.mytable (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

• 丢弃目标表的表空间

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

• 复制文件

  将你拥有的 mytable.ibd 文件（如果有 mytable.cfg 也一起）复制到目标服务器的数据库目录，并修改所有者。

cp mytable.ibd /var/lib/mysql/mydatabase/
chown mysql:mysql /var/lib/mysql/mydatabase/mytable.ibd

导入表空间之前需要重启一下数据库！！！

并且支持在MySQL5.7和8之间进行迁移

尝试导入表空间

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

可能遇到的问题与解决方案：

• 错误：Schema mismatch：表结构不匹配。请仔细检查并重新创建表，确保每个细节都相同。
• 错误：表空间ID不匹配：这是正常现象，IMPORT TABLESPACE 过程就是为了解决这个问题。
• MySQL 8.0 恢复 5.7 的表：
  • 你没有 *.frm 文件，需要在 8.0 中根据记忆或文档创建表结构。
  • 最好先在 MySQL 5.7 实例中通过 SHOW CREATE TABLE 获取精确的表结构。

总结与工作流图示

标准流程（场景一）：

目标库：创建空表 -> DISCARD TABLESPACE
源库：FLUSH TABLE ... FOR EXPORT -> 复制 .ibd & .cfg -> UNLOCK TABLES
目标库：传输文件 -> IMPORT TABLESPACE -> 验证

核心命令三部曲：

1. 目标库准备：ALTER TABLE ... DISCARD TABLESPACE; (清空舞台)
2. 源库锁定并复制：FLUSH TABLE ... FOR EXPORT; -> cp -> UNLOCK TABLES; (准备并搬运货物)
3. 目标库导入：ALTER TABLE ... IMPORT TABLESPACE; (接收货物)

以上就是MySQL通过传输表空间实现大表ibd文件的物理迁移的具体方案的详细内容，更多关于MySQL大表ibd文件物理迁移的资料请关注编程客栈(www.devze.com)其它相关文章！