mysql大表备份导致业务卡顿怎么办_使用XtraBackup物理备份方案-mysql教程-PHP中文网

mysqldump 备份大表会卡业务，因其逻辑备份需逐行SELECT，在REPEATABLE-READ下持锁、挤占Buffer Pool、加剧I/O/CPU/内存压力，导致慢查询堆积、连接超时、主从延迟飙升。

mysql大表备份导致业务卡顿怎么办_使用xtrabackup物理备份方案

为什么 mysqldump 备份大表会让业务卡住

因为 mysqldump 是逻辑备份，需要逐行 SELECT 数据，会持续持有表级或行级锁（尤其在 REPEATABLE-READ 隔离级别下），同时大量读取触发 Buffer Pool 淘汰、磁盘 I/O 竞争，还会拉高 CPU 和内存压力。对千万级以上表，备份过程常伴随慢查询堆积、连接超时、主从延迟飙升。

Percona XtraBackup 备份时不锁表的关键条件

XtraBackup 能实现“几乎不锁表”的物理备份，但前提是满足几个硬性条件：

MySQL 必须启用 innodb_file_per_table = ON（默认 5.6+ 已开启）
必须使用 InnoDB 表；MyISAM 表在 backup 阶段仍需全局只读锁（FLUSH TABLES WITH READ LOCK）
备份期间不能执行 DDL（如 ALTER TABLE、DROP TABLE），否则 XtraBackup 可能因元数据不一致而失败或报错 Failed to get metadata lock on table
建议关闭 innodb_lock_wait_timeout 的短超时设置（如 50 秒），避免备份线程被误杀

生产环境推荐的 xtrabackup 命令参数组合

直接用默认参数跑 xtrabackup --backup 容易压垮 I/O 或占满磁盘。以下参数组合兼顾速度、资源可控性和可恢复性：

xtrabackup \
  --defaults-file=/etc/my.cnf \
  --user=backup_user \
  --password=xxx \
  --backup \
  --target-dir=/backup/xtrabackup/$(date +%Y%m%d_%H%M%S) \
  --parallel=4 \
  --compress \
  --compress-threads=2 \
  --throttle=100 \
  --rsync

说明：

MySQL

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

下载

--parallel=4：控制并发拷贝线程数，一般设为磁盘数 × 2，SSD 可到 8，HDD 建议 ≤4
--throttle=100：限制每秒 IO 操作数（IOPS），100 是较安全起点，可按监控调整
--compress + --compress-threads：边拷贝边压缩，减少磁盘写入量，但会多占 CPU
--rsync：用 rsync 加速非 InnoDB 文件（如 ib_logfile*、mysql-bin.*）同步，降低锁表时间

备份后必须做的两件事，否则恢复可能失败

XtraBackup 备份出的是“未提交事务未回滚、日志未应用”的原始文件，直接 copy 回去无法启动 MySQL。必须执行：

运行 xtrabackup --prepare：回放 redo log、回滚未提交事务。这步是 CPU 和磁盘密集型操作，建议在备机或空闲时段做
确认 xtrabackup_binlog_info 文件存在且内容完整：它记录了备份结束时的 binlog 位置，用于后续搭建从库或 PITR（基于时间点恢复）

漏掉 --prepare 就恢复，MySQL 启动会报错 InnoDB: Database page corruption on disk 或直接 crash。