备份与恢复

先定备份策略

备份不是「有空导个库」，而是一套明确的方案：用什么工具、多久备一次、留多少份、放哪儿。动手前先想清楚两个问题：能容忍丢多少数据（RPO），能容忍多久恢复（RTO）——这个思路在《主从复制与高可用》里还会展开，这里只记住结论：它们决定你的备份频率和工具选型。

经典组合是全量 + 增量 + binlog：

全量备份：每周一次完整拷贝，作为恢复的基线。
增量备份：每天一次，只拷变化的部分，省空间省时间。
binlog 实时保留：记录每一次变更，用来补齐最后一次备份到故障时刻之间的数据。

保留周期建议：每日备份留 7 天，每周备份留 4 周，每月备份留 6~12 个月。另外记住 3-2-1 原则：至少 3 份副本、2 种不同介质、1 份在异地。

逻辑备份 mysqldump

mysqldump 把库导出成 SQL 文本，优点是简单、可读、可跨版本，缺点是慢、占空间，适合中小库（几十 GB 以内）。

一致性备份的原理

关键参数是 --single-transaction：它在导出前开一个事务，利用 InnoDB 的 MVCC 快照，整个过程读到的是同一个时间点的数据，且不锁表。不加这个参数，mysqldump 默认逐表加读锁，导出期间写全被堵死。

只对 InnoDB 有效

--single-transaction 依赖 MVCC，MyISAM 表不适用。另外导出过程中不要做 ALTER TABLE 等 DDL——DDL 会打破快照的可见性，导致导出报错或数据不一致。

常用参数

参数	作用
`--single-transaction`	一致性快照备份，不锁表（InnoDB 必加）
`--master-data=2`	把备份时刻的 binlog 位点写进文件（注释形式），恢复后好接 binlog
`--routines` / `--triggers`	导出存储过程和触发器（触发器默认导出，存储过程默认不导出）
`--set-gtid-purged=OFF`	开了 GTID 的环境恢复时不写 gtid_purged，避免冲突
`--all-databases`	备份所有库
`-B db1 db2`	只备份指定库

备份与恢复示例

# 全量备份（生产标准姿势）
mysqldump -u root -p \
  --single-transaction \
  --master-data=2 \
  --routines \
  --set-gtid-purged=OFF \
  --all-databases | gzip > /backup/full_$(date +%F).sql.gz

# 恢复
gunzip < /backup/full_2024-06-01.sql.gz | mysql -u root -p

# 只备份单个库
mysqldump -u root -p --single-transaction shop | gzip > /backup/shop_$(date +%F).sql.gz

物理备份 xtrabackup

库上了百 GB，mysqldump 的导出导入就要按小时计了，这时候换 Percona XtraBackup：直接拷贝数据文件，备份和恢复都比逻辑备份快一个数量级。

# 安装：先配 Percona 官方源，再装
sudo apt install -y percona-xtrabackup-80

全量备份与恢复

# 1. 全量备份
xtrabackup --backup --user=root --password='xxx' \
  --target-dir=/backup/full_2024-06-01

# 2. prepare：应用 redo log，让数据文件达到一致状态
xtrabackup --prepare --target-dir=/backup/full_2024-06-01

# 3. 恢复（要求目标实例已停服、数据目录已清空）
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full_2024-06-01

# 4. 修复属主再启动
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

增量备份链

全量太贵就配增量：周一全量，之后每天只备变化的部分。

# 周日：全量
xtrabackup --backup --target-dir=/backup/base

# 周一：基于全量的增量
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/base

# 周二：基于周一的增量
xtrabackup --backup --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1

恢复时 prepare 的顺序是最容易踩的坑：redo log 只能应用到最后一份增量，中间的增量必须加 --apply-log-only，提前「定稿」后面的增量就接不上了。

# 全量：只应用日志，不定稿
xtrabackup --prepare --apply-log-only --target-dir=/backup/base

# 中间增量：合并进来，仍然不定稿
xtrabackup --prepare --apply-log-only --target-dir=/backup/base \
  --incremental-dir=/backup/inc1

# 最后一份增量：正常 prepare，收尾
xtrabackup --prepare --target-dir=/backup/base \
  --incremental-dir=/backup/inc2

# 之后照常 copy-back 恢复

什么时候选物理备份：数据量大（百 GB 以上）、备份窗口紧、要求恢复速度快。代价是备份文件不可读、基本只能整实例恢复、版本要求严格（xtrabackup 8.0 对应 MySQL 8.0）。

binlog 与时间点恢复（PITR）

全量再全，最多回到备份那一刻。从备份时刻到「出事前一刻」之间的数据，靠 binlog 补齐——这是增量备份的最后一块拼图。

先确认 binlog 开着

MySQL 8.0 默认开启 binlog，但如果当初手动关了，PITR 无从谈起。检查：SHOW VARIABLES LIKE 'log_bin';。同时建议 binlog_expire_logs_seconds 设长一点（比如 30 天），并定期把 binlog 文件拷走异地保存。

mysqlbinlog 截取

# 按时间段截取：导出 6 月 1 日下午的变更
mysqlbinlog --start-datetime="2024-06-01 14:00:00" \
            --stop-datetime="2024-06-01 15:00:00" \
            /var/lib/mysql/binlog.000123 > /tmp/inc.sql

# 按位点截取：从位点 12345 恢复到出事前的 67890
mysqlbinlog --start-position=12345 \
            --stop-position=67890 \
            /var/lib/mysql/binlog.000123 > /tmp/inc.sql

误删表的完整恢复流程

假设 15:03 有人 DROP TABLE orders，现在手上有昨晚的全量备份和连续的 binlog：

# 1. 全量备份恢复到昨晚的位点（mysqldump 文件头部注释里有 binlog 位点）
gunzip < /backup/full_2024-05-31.sql.gz | mysql -u root -p

# 2. 找到出事的准确位置：在 binlog 里搜 DROP TABLE
mysqlbinlog /var/lib/mysql/binlog.000125 | grep -B5 "DROP TABLE"

# 3. 重放 binlog 到出事前一刻（stop 在 DROP TABLE 之前的位点或时间）
mysqlbinlog --start-position=154 \
            --stop-datetime="2024-06-01 15:02:59" \
            /var/lib/mysql/binlog.000125 | mysql -u root -p

# 4. 数据回到 15:02:59，DROP TABLE 及其后的变更没有被重放

恢复出来的是「出事前一刻」的完整状态。这类操作强烈建议先恢复到一台临时实例，把表导出来再回填生产，别直接在生产上重放。

恢复演练

没演练过的备份等于没有备份。 备份文件损坏、参数记错、缺 binlog，这些问题只会在真出事时暴露——除非你自己定期制造「事故」。

恢复演练清单（每季度至少一次）：

找一台隔离的测试机，装好同版本 MySQL。
取最近一次的全量备份，完整走一遍恢复流程。
接上 binlog，验证能重放到最新位点。
抽查关键表：行数对不对、最新数据在不在、字符集正不正常。
记录耗时——这就是你真实的 RTO，写进运维文档。
演练用的临时实例用完即销毁。

备份自动化

人工备份迟早会忘，交给 crontab。脚本骨架：

#!/bin/bash
# /usr/local/bin/mysql-backup.sh —— 每日全量 + binlog 归档
set -e

BACKUP_DIR=/backup/mysql
DATE=$(date +%F)
KEEP_DAYS=7

mkdir -p "$BACKUP_DIR/$DATE"

# 1. 全量备份
mysqldump -u root -p'xxx' \
  --single-transaction --master-data=2 \
  --routines --set-gtid-purged=OFF \
  --all-databases | gzip > "$BACKUP_DIR/$DATE/full.sql.gz"

# 2. binlog 实时拷贝（另起一个常驻任务或 rsync 定时同步到异地）
rsync -a /var/lib/mysql/binlog.* "$BACKUP_DIR/$DATE/binlog/"
rsync -a "$BACKUP_DIR/$DATE" backup@10.0.0.99:/backup/mysql/

# 3. 清理过期备份
find "$BACKUP_DIR" -maxdepth 1 -type d -mtime +$KEEP_DAYS -exec rm -rf {} \;

# 4. 备份结果校验：大小异常直接告警
SIZE=$(stat -c%s "$BACKUP_DIR/$DATE/full.sql.gz")
if [ "$SIZE" -lt 1048576 ]; then
  echo "ALERT: backup too small: $SIZE bytes" | mail -s "MySQL 备份异常" ops@example.com
fi

# 每天凌晨 2 点执行
0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

脚本之外两件事不能省：备份结果校验（除了大小检查，定期试恢复才是真校验）和异地拷贝（备份文件和生产库放同一台机器，机器挂了备份也一起没了——用 rsync、对象存储都行，关键是离开这台机器）。

常见坑

坑	后果	正确做法
mysqldump 忘加 `--single-transaction`	导出期间锁全库，业务写全部阻塞	InnoDB 备份必加
binlog 没开或保留期太短	没法做时间点恢复	确认 `log_bin=ON`，过期时间设 30 天
GTID 环境直接灌备份	`gtid_purged` 冲突，复制断裂	备份加 `--set-gtid-purged=OFF`
恢复后忘记重新授权	应用连不上或权限不对	恢复后核对 `mysql.user` 和业务账号权限
备份文件放同一台机器	机器故障 = 数据备份双丢	必须异地/异机存放
增量链中途 prepare 定稿	后续增量接不上，整链作废	中间增量必须 `--apply-log-only`

小结

策略先行：全量 + 增量 + binlog 三件套，留够副本，遵循 3-2-1 原则。
小库用 mysqldump（--single-transaction 是底线），大库用 xtrabackup（增量链的 prepare 顺序别搞错）。
binlog 是时间点恢复的最后一公里：先确认它开着、留得够久、拷得够远。
备份的价值只在恢复时体现：自动化脚本 + 定期演练，缺一不可。

备份解决「数据丢了怎么办」，但恢复再快也有停机时间。下一篇《主从复制与高可用》讲怎么靠复制和 HA 方案让数据库尽量不挂、挂了自动切换。

先定备份策略​

逻辑备份 mysqldump​

一致性备份的原理​

常用参数​

备份与恢复示例​

物理备份 xtrabackup​

全量备份与恢复​

增量备份链​

binlog 与时间点恢复（PITR）​

mysqlbinlog 截取​

误删表的完整恢复流程​

恢复演练​

备份自动化​

常见坑​

小结​