MySQL 备份 mysqldump 与 XtraBackup

摘要：备份分逻辑备份（mysqldump，导出 SQL 文本，灵活但慢）与物理备份（XtraBackup，拷贝数据文件，快且支持增量）。本文给出两种方式的完整命令、定时任务与备份有效性校验。

适用环境

bash

mysql -uroot -p -e "SELECT VERSION(); SHOW VARIABLES LIKE 'log_bin';"
# XtraBackup 要求：MySQL/Percona Server，且 datadir 可本地访问

mkdir -p /data/backup/{logical,physical}
df -h /data/backup          # 备份盘至少留出 2 倍数据量

操作步骤

1. 逻辑备份：mysqldump 全量（单事务一致性）

bash

mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers \
  --events --set-gtid-purged=ON --databases shopdb \
  | gzip > /data/backup/logical/shopdb_$(date +%F).sql.gz

--single-transaction：InnoDB 一致性快照，不锁表。
--master-data=2：记录备份时的 binlog 位置，便于恢复到指定时间点。

危险

混用 MyISAM 表时 --single-transaction 无法保证一致性，需加 --lock-all-tables（会锁全库，请在业务低峰执行）。

2. 分库分表备份（大库推荐，便于单表恢复）

bash

#!/usr/bin/env bash
# /usr/local/bin/backup_tables.sh
OUT=/data/backup/logical/$(date +%F); mkdir -p "$OUT"
for t in $(mysql -uroot -p"$1" -N -e "SHOW TABLES FROM shopdb"); do
  mysqldump -uroot -p"$1" --single-transaction shopdb "$t" \
    | gzip > "$OUT/shopdb.$t.sql.gz"
done

3. 物理备份：XtraBackup 全量

bash

# 安装（RHEL 系）
sudo yum install -y percona-xtrabackup-80

xtrabackup --backup --user=root --password='密码' \
  --target-dir=/data/backup/physical/full_$(date +%F)

# 备份后必须 prepare，否则不可直接恢复
xtrabackup --prepare --target-dir=/data/backup/physical/full_$(date +%F)

4. XtraBackup 增量备份

bash

# 第一次增量基于全量
xtrabackup --backup --user=root --password='密码' \
  --target-dir=/data/backup/physical/inc1 \
  --incremental-basedir=/data/backup/physical/full_2026-10-09

# prepare 增量：全量加 --apply-log-only，最后一个增量不加
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/physical/full_2026-10-09
xtrabackup --prepare --target-dir=/data/backup/physical/full_2026-10-09 \
  --incremental-dir=/data/backup/physical/inc1

5. 定时任务与保留策略

bash

sudo crontab -e
# 每天 02:00 逻辑全备，保留 7 天；每周日 01:00 物理全备
0 2 * * * mysqldump -uroot -p'密码' --single-transaction --databases shopdb | gzip > /data/backup/logical/shopdb_$(date +\%F).sql.gz
0 3 * * * find /data/backup/logical -name '*.sql.gz' -mtime +7 -delete

cron 中 % 需转义为 \%；更推荐把密码放入 /etc/my.cnf 的 [client] 段并设 600 权限。

验证

bash

# 逻辑备份：看文件头是否包含建表语句，大小是否合理
ls -lh /data/backup/logical/
zcat /data/backup/logical/shopdb_2026-10-09.sql.gz | head -n 20

# 物理备份：检查 xtrabackup_checkpoints 中 backup_type / to_lsn
cat /data/backup/physical/full_2026-10-09/xtrabackup_checkpoints

常见坑

WARNING

备份成功 ≠ 可恢复。只备份不演练等于没备份，务必定期做还原测试。

WARNING

备份写在同一块盘上，磁盘损坏时备份一起丢失；至少保留一份异地/对象存储副本。

DANGER

--prepare 只能用一次且必须按顺序执行；对全量重复执行 --prepare（不带 --apply-log-only）后再应用增量，会导致备份不可用。

MySQL 备份 mysqldump 与 XtraBackup ​

适用环境 ​

操作步骤 ​

验证 ​

常见坑 ​

参考资料 ​

MySQL 备份 mysqldump 与 XtraBackup

适用环境

操作步骤

验证

常见坑

参考资料