MySQL如何在不停机时备份InnoDB大表-mysql教程-PHP中文网

对单个innodb大表执行mysqldump --single-transaction不安全，因依赖mvcc快照且易被长事务阻塞、ddl操作导致快照失效；真正可靠的是percona xtrabackup物理备份。

mysql如何在不停机时备份innodb大表

mysqldump --single-transaction 对大表真的安全吗

对单个 InnoDB 大表执行 mysqldump --single-transaction 看似“热”，但实际极易卡住或失败。它依赖 MVCC 快照，而快照建立前会等待所有活跃事务结束——如果备份开始时恰好有长事务（比如一个未提交的 UPDATE 或 DELETE），mysqldump 就会挂起，直到该事务超时或被 kill。

更隐蔽的问题是：大表导出耗时长，期间若发生 DDL（如 ALTER TABLE），快照即失效，导出内容可能结构错乱、数据缺失，甚至中断报错 ERROR 2013 (HY000): Lost connection to MySQL server during query。

必须确认当前无长事务：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(NOW()) - TIME_TO_SEC(TRX_STARTED) > 60;
备份窗口内禁止任何 DDL 操作，包括 DROP、RENAME、TRUNCATE
--single-transaction 仅对 InnoDB 表生效，若该库混用 MyISAM，需加 --skip-lock-tables，但风险自担

Percona XtraBackup 是唯一靠谱的物理热备方案

真正支持不停机备份 InnoDB 大表的，只有 xtrabackup 这类物理层工具。它不走 SQL 层，而是直接读取 ibdata1、.ibd 和 redo log，配合 checkpoint 定位一致性点，全程不阻塞写入。

注意：它不是“一键完事”。备份后必须执行 --prepare 才能用于恢复——这步会回放未提交事务、清理脏页，否则恢复必报错 Tablespace is not empty 或启动失败。

全量备份命令：xtrabackup --backup --target-dir=/backup/full_$(date +%F)
准备备份：xtrabackup --prepare --target-dir=/backup/full_$(date +%F)
若启用了 innodb_file_per_table=ON，可单独备份某张大表：xtrabackup --backup --tables="^mydb\.big_table$" --target-dir=/backup/big_table
备份过程会占用大量 I/O 和磁盘空间，建议在低峰期运行，并监控 iostat -x 1

直接复制 .ibd 文件？只在极小概率下可行

有人想跳过工具，直接 cp /var/lib/mysql/mydb/big_table.ibd /backup/。这只有在同时满足以下全部条件时才可能成功：

MySQL

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

下载

该表启用独立表空间：SHOW CREATE TABLE big_table 中含 ROW_FORMAT=COMPACT 且无 CREATE TABLE ... TABLESPACE
MySQL 已执行 FLUSH TABLES big_table FOR EXPORT;（生成 .cfg 元数据文件）
复制后立即执行 UNLOCK TABLES;，否则表被锁死
恢复时必须先 CREATE TABLE 占位，再 DISCARD TABLESPACE，再 COPY + IMPORT TABLESPACE

漏掉任意一步，恢复时就会报 Tablespace for table 'mydb/big_table' exists. Please DISCARD the tablespace before IMPORT 或数据校验失败。这不是常规操作，而是应急兜底手段。

为什么不能用 mydumper 替代 xtrabackup 备大表

mydumper 是并行逻辑导出工具，比 mysqldump 快，但它仍是 SQL 层导出，本质没脱离 --single-transaction 的限制。对单张百 GB 级别的 InnoDB 表，它依然要拉一次全表扫描、生成海量 INSERT 语句、消耗大量内存和临时空间。

实测中常见问题：

导出进程 OOM 被系统 kill（尤其 -t 8 多线程时）
生成的 SQL 文件单个超 50GB，mysql 导入时频繁断连、字符集解析错误
无法保证跨表外键约束的一致性（除非加 --serializable，等于变相锁表）
不记录 binlog 位点，无法做精确时间点恢复

真正的大表（>50GB）、高并发写入场景，mydumper 只能作为辅助验证手段，不能替代 xtrabackup。

最易被忽略的点：无论用哪种方式，备份前必须确认 autocommit=1。如果客户端连接里手动开了事务却忘了 COMMIT，--single-transaction 会静默失败，而 xtrabackup 则会在 --prepare 阶段报错 Log block checksum mismatch——此时已无法补救。