DevNull 知识库

Appearance

备份与恢复 ​

备份的目标不是在某个时间点生成一个文件——而是在需要的时候能把数据恢复到指定的状态。备份文件存在、能恢复、恢复后数据正确,这三件事要分开验证。很多人只做了第一步(产生备份文件)就以为万事大吉,真出事的时候才发现备份根本恢复不了。

一、备份组合 ​

常见备份方式各有分工:逻辑备份(mysqldump)适合小库、跨版本迁移、跨环境恢复,但几百 GB 以上的库恢复很慢;物理热备(XtraBackup)适合大库、追求恢复速度,但跨大版本恢复可能有问题;binlog 增量(mysqlbinlog)回放到指定时间点,但依赖日志连续性,不能单独当备份用;存储快照(LVM/云盘快照)快速备份和克隆,但需要处理数据库文件的一致性。

常规备份策略是这样搭配的:

text
全量备份（昨晚的 mysqldump 或 XtraBackup）+ binlog（全量之后当天所有的变更）
= 能恢复到今天上午误删之前的任意时间点

几个概念的关键区分要理清:全量备份能还原到一个完整的时间点状态,但只能恢复到备份完成那一刻;binlog能重放备份之后的变更,但必须基于一个全量备份才能起步;两者配合起来才能做时间点恢复(PITR),如果 binlog 断了、丢了就有限制。

二、mysqldump 逻辑备份 ​

备份单库:

bash
mysqldump \
  -uroot -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  app_db > app_db.sql

几个参数:--single-transaction 导出前开启一个一致性读事务,InnoDB 表在导出过程中看到的是同一时刻的数据;--routines 带上存储过程和函数;--triggers 带上触发器;--events 带上定时事件。

备份所有库:

bash
mysqldump -uroot -p --all-databases --single-transaction \
  --routines --triggers --events > all.sql

仅备份某几张表:

bash
mysqldump -uroot -p app_db orders order_items > orders.sql

压缩节省空间:

bash
mysqldump -uroot -p --single-transaction app_db \
  | gzip > app_db-$(date +%F).sql.gz

--single-transaction 的"一致性"只对 InnoDB 这类事务表有效。如果库里有 MyISAM 表,它们在 mysqldump 过程中仍然可能发生变化,导致备份文件内的数据并非同一时刻的快照。所以生产库建表基本都用 InnoDB 而不是 MyISAM,备份一致性也是原因之一。

三、记录 binlog 位点 ​

备份时把当时的 binlog 位点写进 dump 文件,这是为时间点恢复做准备:

bash
mysqldump -uroot -p --single-transaction --master-data=2 \
  app_db > app_db.sql

查看位点:

bash
grep -m1 'CHANGE MASTER' app_db.sql

输出里有 binlog 文件名和位置。这个位点就是全量备份对应的时间戳——恢复时,全量到这一步,binlog 从这个位点开始回放。没有这个位点,全量备份和 binlog 就接不上。

四、mysqldump 恢复 ​

首先创建目标库:

sql
CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4;

导入:

bash
mysql -uroot -p app_db < app_db.sql

压缩文件的恢复:

bash
gunzip -c app_db.sql.gz | mysql -uroot -p app_db

恢复所有库:

bash
mysql -uroot -p < all.sql

恢复前确认目标——是恢复到生产库、测试库,还是临时实例。导错目标是比备份本身失败更严重的操作,把测试数据导进生产库等于一次数据事故。

临时实例恢复验证:

bash
mysql -h 127.0.0.1 -P 3307 -uroot -p < app_db.sql

五、binlog 回放 ​

先看有哪些 binlog 文件:

sql
SHOW BINARY LOGS;

查看 binlog 内容(raw 格式的人类可读版本):

bash
mysqlbinlog /data/mysql/logs/mysql-bin.000123 | less

按时间范围回放到临时实例:

bash
mysqlbinlog \
  --start-datetime='2026-05-21 10:00:00' \
  --stop-datetime='2026-05-21 10:30:00' \
  /data/mysql/logs/mysql-bin.000123 \
  | mysql -uroot -p

按位点(更精确):

bash
mysqlbinlog \
  --start-position=154 \
  --stop-position=98231 \
  /data/mysql/logs/mysql-bin.000123 \
  | mysql -uroot -p

做时间点恢复时,核对这四件事:备份可用性(全量备份能不能恢复成功,备份里 binlog 位点是否清楚)、binlog 连续性(从备份位点到目标时间之间,binlog 文件是否连续完整)、数据正确性(恢复后关键表行数、重要数据是否符合预期)、回写方案(是从临时实例导出少量数据补回生产,还是整库切换)。

少量数据误删的典型恢复流程:

text
1. 确认误删发生的时间
2. 找到这个时间之前最近的一次全量备份
3. 在临时实例恢复全量备份
4. 回放 binlog 到误删发生前的那一刻
5. 从临时实例导出需要恢复的数据
6. 导入生产库

导出指定行的数据:

bash
mysqldump -uroot -p --where="id in (1001,1002,1003)" \
  app_db orders > restore_orders.sql

导入前看一眼 SQL 内容确认:

bash
less restore_orders.sql

六、XtraBackup 物理热备 ​

mysqldump 导出的是 SQL 文本,上 GB 就开始吃力。几百 GB 以上的库,用 XtraBackup 做物理备份——直接复制 InnoDB 数据文件,恢复时也是文件级恢复,速度比 SQL 逐行导入快一个量级。

XtraBackup 不能像 mysqldump 那样单库单表导出——它针对的是整个实例的数据目录。版本要和 MySQL 版本匹配,8.0 的 XtraBackup 不能备份 5.7 的实例。

全量备份:

bash
xtrabackup \
  --backup \
  --target-dir=/backup/mysql/full-$(date +%F) \
  --user=backup \
  --password='BackupPassword_123!'

备份完成后需要 --prepare——应用备份期间产生的 redo,让备份目录内的数据文件变成一致可用的状态:

bash
xtrabackup \
  --prepare \
  --target-dir=/backup/mysql/full-2026-05-21

恢复时,先停 MySQL,然后拷贝回去:

bash
systemctl stop mysqld
mv /data/mysql/data /data/mysql/data.bak.$(date +%F-%H%M%S)
mkdir -p /data/mysql/data

xtrabackup --copy-back --target-dir=/backup/mysql/full-2026-05-21

chown -R mysql:mysql /data/mysql/data
systemctl start mysqld

物理恢复会覆盖整个 datadir。恢复前确认目标实例是对的——把测试恢复的命令用在生产环境上,恢复就会变成一次数据丢失事故。这种事发生过,而且不可逆。

七、备份账号 ​

备份账号和普通应用账号分开:

sql
CREATE USER 'backup'@'10.0.%' IDENTIFIED BY 'BackupPassword_123!';

GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, RELOAD, PROCESS, REPLICATION CLIENT
ON *.* TO 'backup'@'10.0.%';

XtraBackup 可能需要更多权限(如 BACKUP_ADMIN),按实际版本文档确认。备份账号的好处:权限范围明确,即使备份工具或脚本被攻击,也不会泄露高权限账号——纵深防御的一环。

八、备份校验与演练 ​

备份文件不是"产生了"就可靠,要做校验:

bash
sha256sum app_db.sql.gz > app_db.sql.gz.sha256
sha256sum -c app_db.sql.gz.sha256

定期做恢复演练——在一个临时实例上恢复全量备份,跑几条核心查询:

sql
SHOW TABLES;
SELECT COUNT(*) FROM orders;
CHECK TABLE orders;

验证的目的是确认:备份文件能恢复、恢复后表能正常读、关键表行数和业务预期一致。备份任务成功日志不一定能证明"恢复能做"——日志说成功,文件可能损坏、字符集弄错、或有表被漏掉没导出来。不演练的备份等于没有备份。

九、备份保留与清理 ​

备份类型保留时间为什么
每日全量7-14 天能回到最近一两周内任意一天
每周全量4-8 周更长时间跨度的完整恢复点
每月全量6-12 月合规和审计需要
binlog覆盖全量备份间隔确保 binlog 不断链

清理前先看要删什么:

bash
find /backup/mysql -type f -mtime +30 -print

确认输出范围没问题后再真正删除:

bash
find /backup/mysql -type f -mtime +30 -delete

备份清理的节奏和恢复需求直接相关。binlog 清太快(比如只保留两天),全量备份间隔却是一周——那中间好几天无法做时间点恢复。全量频率和 binlog 保留时间要匹配,这是设计备份策略时要算清楚的关键点。

十、定时备份脚本 ​

前面几节讲的备份方式、账号、校验、保留策略,最后得串成一个能自动跑、能留下证据、能回溯的定时任务,不然全靠人手备份迟早会忘。这一节给一个生产可用的完整方案:一个 mysqldump 备份脚本 + crontab 定时执行,把前面讲的 --single-transaction、binlog 位点、压缩、校验、轮转全部落地。

备份凭据单独存 ​

密码不要写死在脚本里,更不要出现在命令行——ps 能看到其他进程的命令行参数,命令行带 --password 等于明文暴露。用单独的 .my.cnf 存凭据:

ini
# /root/.mysql-backup.cnf，权限必须是 600，否则 mysqldump 会警告
[client]
user=backup
password=BackupPassword_123!
bash
chmod 600 /root/.mysql-backup.cnf

mysqldump 用 --defaults-extra-file 读这个文件,命令行里就没有密码了。

备份脚本 ​

bash
#!/usr/bin/env bash
# /usr/local/sbin/mysql-backup.sh
set -euo pipefail

# ---- 配置区 ----
mysql_host="127.0.0.1"
mysql_port=3306
defaults_file="/root/.mysql-backup.cnf"
databases="app_db"              # 多个库用空格分隔，备份全部用 --all-databases
backup_root="/backup/mysql"
retention_days=7                # 保留天数
log_file="/var/log/mysql-backup.log"

# ---- 锁，防止上一次没跑完下一次又触发 ----
lock_file="/var/run/mysql-backup.lock"
exec 9>"$lock_file"
flock -n 9 || { echo "[$(date '+%F %T')] 上一次备份还在跑，跳过" >> "$log_file"; exit 0; }

# ---- 初始化 ----
today="$(date +%F)"
backup_dir="$backup_root/$today"
mkdir -p "$backup_dir"

log() {
    printf '[%s] %s\n' "$(date '+%F %T')" "$*" | tee -a "$log_file"
}

log "开始备份：host=$mysql_host databases=$databases"

# ---- 执行备份 ----
for db in $databases; do
    dump_file="$backup_dir/${db}-${today}.sql.gz"

    log "备份 $db -> $dump_file"

    mysqldump \
        --defaults-extra-file="$defaults_file" \
        --host="$mysql_host" \
        --port="$mysql_port" \
        --single-transaction \
        --master-data=2 \
        --routines \
        --triggers \
        --events \
        "$db" | gzip > "$dump_file"

    # 生成校验文件，方便事后验证备份完整性
    sha256sum "$dump_file" > "${dump_file}.sha256"

    log "$db 备份完成，大小 $(du -h "$dump_file" | cut -f1)"
done

# ---- 顺便记一下当前 binlog 位点，留作时间点恢复的参考 ----
mysqldump \
    --defaults-extra-file="$defaults_file" \
    --host="$mysql_host" \
    --port="$mysql_port" \
    -N -B \
    -e "SHOW MASTER STATUS" > "$backup_dir/binlog-position-${today}.txt" 2>/dev/null || true

# ---- 清理过期备份 ----
log "清理 ${retention_days} 天前的旧备份"
find "$backup_root" -maxdepth 1 -type d -mtime +"$retention_days" -print >> "$log_file"
find "$backup_root" -maxdepth 1 -type d -mtime +"$retention_days" -exec rm -rf {} +

log "备份流程结束"

脚本要点逐条对应前面讲的内容:--single-transaction 保证一致性快照(第二节)、--master-data=2 把 binlog 位点写进 dump 文件(第三节)、--routines/--triggers/--events 带全存储过程触发器和事件、gzip 压缩省空间、sha256sum 生成校验文件(第八节)、find -mtime 清理过期备份(第九节)、flock 防止并发。每个细节都不是多余的,去掉任何一个都可能在实际出事的时候露馅。

crontab 定时执行 ​

脚本放好后,用 cron 每天凌晨跑一次:

bash
chmod +x /usr/local/sbin/mysql-backup.sh
crontab -e
cron
# 每天凌晨 2:30 执行 MySQL 备份
30 2 * * * /usr/local/sbin/mysql-backup.sh

时间选凌晨 2 点到 4 点之间,这个时段业务低峰、写入压力小,--single-transaction 的一致性快照更稳定。别选整点(0 点、2 点整)——太多任务挤在整点跑,容易撞车,错开几分钟(比如 2:30)更稳。

cron 环境的几个坑 ​

脚本手动跑没问题、放进 cron 就失败,十有八九是环境差异:

  • PATH 不一样:cron 的 PATH 通常只有 /usr/bin:/bin,mysqldump 如果装在 /usr/local/mysql/bin/ 就找不到。要么脚本里写绝对路径,要么在脚本开头 export PATH:
    bash
    export PATH="/usr/local/mysql/bin:/usr/bin:/bin:$PATH"
  • .my.cnf 找不到:cron 执行时 HOME 可能不是 /root,--defaults-extra-file 用绝对路径最保险。
  • 权限问题:cron 以哪个用户跑,这个用户就得能读 .my.cnf、能写备份目录和日志文件。生产备份一般用 root 的 crontab 跑,省得调权限。
  • 错误没察觉:set -euo pipefail 让脚本失败即退出,但 cron 不会主动告诉你失败了。配置邮件告警,或者在脚本失败时主动发通知(比如调企业微信/钉钉 webhook),不然备份静默失败几天你都不知道。

验证备份真的能用 ​

定时备份跑起来之后,一定要做一次恢复演练(第八节讲过)。把某天的备份文件恢复到一台测试实例,跑几条核心查询,确认数据完整。光看 mysqldump 退出码是 0、日志写了"备份完成",不代表备份文件没坏——磁盘故障、压缩损坏、字符集问题都可能让备份文件看起来正常但实际恢复不了。没演练过的备份不能信任,这条得当成铁律。