如何在mysql中迁移大表数据
迁移大表数据需分批处理以降低影响，优先选用mysqldump分段导出、SELECT INTO OUTFILE结合LOAD DATA INFILE提升速度，或用pt-archiver实现低负载迁移，超大表可采影子表+双写方案确保不停服，全程避免锁表并监控资源。
迁移 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.sqlmysql -u user -p target_db < part2.sql
导入前可执行：
SET unique_checks=0; SET foreign_key_checks=0; SET autocommit=0;
2. 使用 SELECT ... INTO OUTFILE 和 LOAD DATA INFILE
这是最快的数据导出导入方式，适合同机或局域网内迁移。
关键点：- 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';-- 导入 LOAD DATA INFILE '/tmp/big_table_part2.csv' INTO TABLE target_big_table FIELDS TERMINATED BY ',' ENCLOSED B
Y '"' LINES TERMINATED BY '\n';
Y '"'
LINES TERMINATED BY '\n';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
4. 在线DDL与影子表迁移（大数据量 + 不停服）
对于超大表（TB级），推荐使用“影子表”方式逐步迁移，避免长时间锁表。
步骤：- 创建结构相同的目标表（如 big_table_new）
- 用脚本或工具分批将数据从原表复制过去
- 使用触发器或应用层双写保证增量同步
- 切换应用指向新表，重命名表完成迁移
也可结合 gh-ost 或 pt-online-schema-change 实现无锁变更，这些工具内部采用类似机制。
基本上就这些方法。选择哪种取决于你的数据量、停机窗口、网络环境和是否允许短暂性能下降。关键是避免一次性操作，始终分批处理，监控资源使用。不复杂但容易忽略细节。
