如何在mysql中迁移大表数据-mysql教程-PHP中文网

如何在mysql中迁移大表数据

P粉602998670

发布： 2025-10-31 13:03:02

原创

654人浏览过

迁移大表数据需分批处理以降低影响，优先选用mysqldump分段导出、SELECT INTO OUTFILE结合LOAD DATA INFILE提升速度，或用pt-archiver实现低负载迁移，超大表可采影子表+双写方案确保不停服，全程避免锁表并监控资源。

如何在mysql中迁移大表数据

迁移 MySQL 中的大表数据需要兼顾效率、稳定性和对线上服务的影响。直接使用常规的 INSERT 或 mysqldump 可能会导致锁表时间长、网络压力大或执行失败。以下是几种实用的方法和最佳实践。

1. 使用 mysqldump 分批导出导入

适合跨服务器迁移，尤其是数据量在几 GB 到上百 GB 的场景。

操作建议：

使用 --where 参数分批导出，避免一次性加载全部数据
加上 --single-transaction 避免锁表（适用于 InnoDB）
禁用外键检查和唯一索引校验提升导入速度

示例命令：

mysqldump -u user -p db_name big_table --single-transaction \
  --where="id >= 1000000 AND id < 2000000" > part2.sql
<p>mysql -u user -p target_db < part2.sql</p>

登录后复制

导入前可执行：

SET unique_checks=0;
SET foreign_key_checks=0;
SET autocommit=0;

登录后复制

2. 使用 SELECT ... INTO OUTFILE 和 LOAD DATA INFILE

这是最快的数据导出导入方式，适合同机或局域网内迁移。

表单大师AI

一款基于自然语言处理技术的智能在线表单创建工具，可以帮助用户快速、高效地生成各类专业表单。

查看详情

关键点：

SELECT ... INTO OUTFILE 将数据保存为文本文件
LOAD DATA INFILE 快速加载到目标表
需确保 MySQL 有文件读写权限且路径正确

示例：

-- 导出
SELECT * FROM big_table 
WHERE id BETWEEN 1000000 AND 1999999 
INTO OUTFILE '/tmp/big_table_part2.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
<p>-- 导入
LOAD DATA INFILE '/tmp/big_table_part2.csv'
INTO TABLE target_big_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';</p>

登录后复制

3. 使用 pt-archiver 工具分批迁移

Percona Toolkit 中的 pt-archiver 支持高效、低影响地迁移或归档大表数据。

优势：

自动分批处理，减少锁时间和内存占用
支持迁移同时删除源数据（归档场景）
可指定条件过滤迁移部分数据

示例：将数据从源表迁移到远程库

pt-archiver \
--source h=localhost,D=mydb,t=big_table \
--dest h=remote_host,D=mydb,t=big_table \
--where "create_time < '2023-01-01'" \
--limit 10000 \
--commit-each \
--progress 10000

登录后复制