🗄️ 第06章 · 备份与恢复

mysqldump 全量/增量/导入

备份与恢复¶

这一章讲 MySQL 最核心的两件事:怎么备份、怎么在灾难时恢复。备份本身没有价值,能真正恢复出来的备份才有价值——所以本章反复强调"先演练恢复"。本机数据库有 bangdream、lovelive 两个库,适合拿来做备份示例。

备份前先想清楚四件事¶

问题	决定
备份什么	全库 / 单库 / 单表 / 只备份结构与数据
多久备一次	关键系统至少每日,可配增量
备到哪	本机/其他磁盘/对象存储(异地更稳)
怎么恢复	定期拿备份做一次恢复演练

核心观点 | backup 不是目的,restore 才是。每周做一次"把备份导入一个临时库能否查出数据"的演练,并记录恢复耗时。恢复不出来,等于没备份。

全量备份:mysqldump¶

最常用、最通用的逻辑备份工具,导出为 SQL 文本:

# 导出全部数据库
mysqldump --all-databases -u root -p > /backups/all_$(date +%F).sql

# 导出单个库
mysqldump -u root -p bangdream > /backups/bangdream.sql

# 只导出数据结构(不导数据)
mysqldump -u root -p --no-data bangdream > /backups/bangdream_schema.sql

# 常用加固参数:单事务保证一致性、带主库位置日志(便于增量/搭建从库)
mysqldump -u root -p --single-transaction --master-data=2 --routines --triggers bangdream > /backups/bangdream.sql

参数	作用
`--single-transaction`	在 InnoDB 上快照导出,不锁全表,适合在线生产库
`--master-data=2`	记录 binlog 位置(备份时刻),便于做增量或搭主从
`--no-data`	只要结构
`--routines / --triggers`	一起导存储过程与触发器
`--databases a b`	一次导多个库(且包含 CREATE DATABASE 语句)

注意 | 密码别写在命令行里(会暴露在 ps 里)。用 ~/.my.cnf 存 user=root、password=... 并 chmod 600,或输入时用 -p 交互式。

恢复导入¶

# 恢复一个库
mysql -u root -p bangdream < /backups/bangdream.sql

# 恢复全库(--all-databases 导出会带 CREATE DATABASE)
mysql -u root -p < /backups/all_$(date +%F).sql

# 只有结构恢复,再单独倒数据
mysql -u root -p < bangdream_schema.sql

警告 | 导入会覆盖已有数据(若目标表已存在会被 DROP/CREATE 或 REPLACE,取决于 dump 里的语句)。恢复前务必确认 target 库是"想要它被覆盖的",最好先 CREATE DATABASE 临时库 演练一遍。

定时备份 + 保留策略(完整脚本)¶

#!/bin/bash
set -euo pipefail
BACKUP_DIR=/backups/mysql
mkdir -p "$BACKUP_DIR"
STAMP=$(date +%F_%H%M)
mysqldump --single-transaction --master-data=2 --all-databases | gzip > "$BACKUP_DIR/all_${STAMP}.sql.gz"
# 只保留最近 7 天
find "$BACKUP_DIR" -name '*.sql.gz' -mtime +7 -delete
echo "backup ok $STAMP" >> /var/log/mysql_backup.log

crontab 每天 02:00:

0 2 * * * /bin/bash /root/scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

提示 | 用 | gzip 节省磁盘;但压缩会略增加恢复耗时。先 gzip -l/gunzip -t 校验备份可用性,再决定保留几份。最好把备份 rsync/scp 到另一台机器或对象存储,做到"异地容灾"。

增量备份 / binlog 备份思路¶

只做全量的话,两次全量之间的数据会丢。思路:

用 --master-data=2 记录全量时的 binlog 位置。
定期备份 binlog(mysqlbinlog 导出)或开 log_bin + 定时 -P 拉。
恢复时:全量回放 → 再 mysqlbinlog 回放从全量时刻到崩溃点的 binlog。

检查备份可用性¶

# 文件在不在、好坏
ls -lh /backups/mysql/
gunzip -t /backups/mysql/all_2026-*.sql.gz && echo "backup file ok"
# 真的要恢复验证:导进临时库
mysql -u root -p -e "CREATE DATABASE test_restore;"
mysql -u root -p test_restore < /backups/mysql/all_2026-*.sql.gz
mysql -u root -p -e "SELECT COUNT(*) FROM test_restore.<某表>;"