mysql大表如何快速进行数据迁移_使用mysqldump与物理拷贝比较-mysql教程-PHP中文网

mysqldump迁移大表慢因单线程逐行导出、锁表、日志干扰、字符集转换及sql文件体积膨胀；优化需跳过触发器/存储过程/事件、禁用扩展插入、分批导出导入、关闭autocommit；物理拷贝要求严苛且易出错；xtrabackup是tb级热备首选；字符集与sql mode不一致易致数据异常。

mysql大表如何快速进行数据迁移_使用mysqldump与物理拷贝比较

mysqldump 迁移大表为什么慢得让人怀疑人生

因为 mysqldump 默认是单线程、逐行 SELECT + INSERT 的逻辑导出，对百 GB 级别的表，光是生成 SQL 文本就可能卡住：锁表（--single-transaction 能缓解但不彻底）、慢查询日志干扰、字符集转换开销、客户端网络缓冲溢出都会拖慢速度。更麻烦的是，导出的 SQL 文件本身可能远大于原始数据（比如含大量 INSERT INTO ... VALUES (...),(...),... 语句时，冗余括号和逗号显著膨胀体积）。

实操建议：

必须加 --skip-triggers --skip-routines --skip-events，除非你真需要这些对象
用 --extended-insert=FALSE 反而更快——避免单条超长 INSERT 卡在 MySQL 解析器或网络包限制（如 max_allowed_packet）
配合 --where="id BETWEEN 1000000 AND 2000000" 分批导出，再用 mysql 客户端分批导入，绕过单文件瓶颈
禁用 autocommit：SET autocommit=0; + 手动 COMMIT; 包裹导入 SQL，否则每行都提交一次，I/O 直接爆炸

物理拷贝（直接复制 ibd 文件）的前提和雷区

物理迁移快，但只适用于满足全部条件的场景：MySQL 5.6+、启用 innodb_file_per_table=ON、目标库版本 ≥ 源库、且表没用到全文索引 / 虚拟列 / 压缩页等不兼容特性。一旦漏查，ALTER TABLE ... IMPORT TABLESPACE 会报错 Tablespace is missing for table `db`.`t` 或更隐蔽的 InnoDB: Operating system error number 2 in a file operation。

关键步骤不能跳：

源库执行 FLUSH TABLES t WITH READ LOCK;（注意：不是 LOCK TABLES），然后 SHOW CREATE TABLE t; 记下建表语句（含 ENGINE、ROW_FORMAT、CHARSET）
复制 t.ibd 和 t.cfg（5.7+ 必须有，否则 IMPORT 失败）到目标服务器对应 datadir 下
目标库先建空表（用刚才记下的 CREATE TABLE），再执行 ALTER TABLE t DISCARD TABLESPACE;，再 ALTER TABLE t IMPORT TABLESPACE;
最后 UNLOCK TABLES; —— 忘了这步会导致源库长时间只读

真正适合 TB 级迁移的折中方案：Percona XtraBackup

mysqldump 太慢，纯物理拷贝又太脆，这时候 xbbackup 就成了事实标准。它本质是 InnoDB-aware 的物理备份工具，能热备（不锁表）、支持压缩（--compress）、流式传输（--stream=xbstream）、并行复制（--parallel=4），还能自动处理 .cfg 和元数据一致性校验。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

典型流程：

源库运行 xtrabackup --backup --target-dir=/backup/ --parallel=4 --compress
压缩包直接 scp 到目标机，解压 + xtrabackup --prepare（回滚未提交事务）
停掉目标 MySQL，清空 datadir 下对应库目录，cp -r 恢复数据，改权限，重启
注意：恢复后首次启动会慢（InnoDB 自检），且 server-id 必须和源库不同，否则主从冲突

别忽略字符集与 SQL mode 的隐性破坏

即使数据成功迁移，SELECT 结果异常、ORDER BY 错乱、中文变问号，大概率是两端 character_set_server、collation_server 或会话级 sql_mode 不一致导致。尤其是老库常用 utf8（实际是 utf8mb3），新库默认 utf8mb4，直接拷贝表结构可能让 VARCHAR(255) 实际存储长度缩水（因 utf8mb4 下每个字符最多占 4 字节）。

迁移前必查：

源库执行 SELECT @@character_set_server, @@collation_server, @@sql_mode;
目标库确认 my.cnf 中 init_connect='SET NAMES utf8mb4' 且 skip-character-set-client-handshake 未开启
对含中文字段的表，用 SHOW FULL COLUMNS FROM t; 核对 Collation 列是否全为 utf8mb4_0900_ai_ci（或你期望的校对规则）

跨版本迁移时，INFORMATION_SCHEMA 表结构可能变化，mysqldump --all-databases 导出的系统库不要直接导入，只导业务库。