单表数据超1亿行时在MySQL中如何进行高效备份与恢复-mysql教程-PHP中文网

单表超1亿行时，mysqldump默认会oom或极慢，因未加--quick导致整表加载内存、未用--single-transaction引发锁表、无分片造成全表扫描；必须组合--quick--single-transaction--hex-blob--where分段导出并管道gzip压缩。

单表数据超1亿行时在mysql中如何进行高效备份与恢复

单表超1亿行时，mysqldump 默认行为会失败或极慢——不是工具不行，是默认参数没适配大表场景。必须绕过内存加载、避免长事务、跳过锁竞争、压缩传输路径。

为什么 mysqldump 直接跑会卡住或 OOM

默认情况下 mysqldump 会把整张表 SELECT 结果一次性读入客户端内存（尤其未加 --quick），1亿行文本数据轻松吃掉数 GB 内存；同时若未指定 --single-transaction，InnoDB 表可能触发全局锁或长事务阻塞业务写入；而 --where 条件备份若没走索引，全表扫描反而更慢。

mysqldump 不是流式导出工具，它依赖 MySQL server 的结果集缓冲机制
未加 --quick 时，server 端会缓存整张表结果再发给客户端，极易触发 max_allowed_packet 或内存溢出
--single-transaction 虽能避免锁，但对超大表仍需维持一致性快照，undo log 增长快，可能拖慢其他事务
纯 SQL 文件恢复时，MySQL 逐条执行 INSERT，没有批量插入优化，速度极低

真正可行的备份命令组合（含参数解释）

针对 1 亿+ 行 InnoDB 表，以下命令已在生产环境验证（MySQL 5.7/8.0），关键在「分片导出 + 流式压缩 + 跳过元数据干扰」：

用 --quick --no-create-info --skip-triggers --skip-routines --skip-events 关闭所有非必要开销
强制按主键分段导出，避免单次查询过大：例如 --where="id BETWEEN 1 AND 1000000" 配合循环脚本
必须加 --hex-blob 防止二进制字段（如 BLOB、UUID）被截断或乱码
管道直连 gzip，不落地临时文件：mysqldump ... | gzip > user_001.sql.gz

示例（单段导出）：

MySQL

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

下载

mysqldump -uroot -p test_db user \
  --quick \
  --no-create-info \
  --skip-triggers \
  --skip-routines \
  --skip-events \
  --hex-blob \
  --single-transaction \
  --where="id BETWEEN 1 AND 500000" \
  | gzip > /backup/user_part_001.sql.gz

恢复时别直接 mysql

1 亿行 SQL 文件用 mysql 客户端串行执行，基本不可接受（实测常超 6 小时）。应改用服务端批量导入方式，且关闭 autocommit 和唯一性检查：

先建好空表（结构单独备份，用 mysqldump -d）
登录 MySQL 后执行：SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;
用 source 命令导入（比 shell 重定向快 3–5 倍），或改用 mysqlimport（需转成 CSV）
导入完成后执行 COMMIT;，再恢复检查：SET unique_checks=1; SET foreign_key_checks=1;

注意：mysql 客户端默认 max_allowed_packet 是 4MB，导入前务必确认服务端和客户端都调高（如 --max-allowed-packet=512M）

更推荐的替代方案：SELECT INTO OUTFILE + LOAD DATA INFILE

当表有合适索引（尤其是主键连续）且允许短时加读锁，SELECT INTO OUTFILE + LOAD DATA INFILE 比 mysqldump 快 5–10 倍，因为跳过了 SQL 解析层：

SELECT 导出必须用服务端绝对路径（如 /var/lib/mysql-files/user_data.csv），且该目录需在 secure_file_priv 白名单内
导出时显式指定字段顺序、NULL 处理、分隔符，避免恢复歧义：FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'
LOAD DATA INFILE 支持 IGNORE 或 REPLACE，适合追加或覆盖场景
此方式无法导出表结构，必须配合 SHOW CREATE TABLE 单独保存

容易忽略的一点：secure_file_priv 在 MySQL 8.0 默认非空，很多用户卡在这步找不到可写路径，查法：SELECT @@secure_file_priv;