1. 备份方案怎么选
备份的核心不是 “有没有备份”，而是 “能不能恢复、有没有演练过“。方案选择主要分两类：
表格
| 类型 | 代表工具 | 特点 | 适用 |
|---|---|---|---|
| 逻辑备份 | mysqldump | 导出 SQL，跨版本 / 跨平台可移植，易读易改 | 中小数据量（GB 级） |
| 物理备份 | Percona XtraBackup、MySQL Enterprise Backup | 文件级复制，备份 / 恢复速度快 | 大数据量、要求低 RTO |
推荐策略：每日业务低峰做一次全量（mysqldump）+ 持续归档 binlog 做增量，实现任意时间点恢复（PITR），本地保留 N 天 + 异地 / 对象存储一份。
2. 准备好备份账号（最小权限）
不要用 root 跑备份，按最小权限建专用账号：
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongPass_2026';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS, RELOAD
ON *.* TO 'backup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'backup'@'localhost'; -- 供 SHOW MASTER STATUS 使用
FLUSH PRIVILEGES;
权限作用说明：
SELECT：读取数据LOCK TABLES：对 MyISAM 表保证备份一致性SHOW VIEW / EVENT / TRIGGER：导出视图、事件、触发器定义PROCESS / RELOAD：查看线程状态、执行FLUSH（binlog 切换 / 一致性快照）REPLICATION CLIENT：SHOW MASTER STATUS取当前 binlog 位置
3. 核心：Shell 自动备份脚本
3.1 设计要点
- 配置与逻辑分离：库名、目录、保留天数集中在脚本顶部
- 防并发锁：crontab 可能因上次任务卡死而重叠执行，必须有锁
- 管道退出码检测：
mysqldump | gzip中要用PIPESTATUS判断 mysqldump 真实成败，防止 “压缩成功但导出失败” 的假成功 - 临时文件 + 原子改名：先写
.tmp，成功后再mv，避免恢复时拿到写了一半的备份 - 完整日志：每次执行记录时间、库、大小、清理结果
- 自动保留策略：只留 N 天，避免磁盘被撑爆
3.2 完整脚本（可直接保存为 /usr/local/bin/mysql_backup.sh）
#!/usr/bin/env bash
###############################################################################
# MySQL 全量逻辑备份: mysqldump + gzip + 保留策略 + 防并发 + 日志
# 适用: MySQL 5.7 / 8.0, 建议 bash 4+
###############################################################################
# ---------- 配置区 ----------
DB_HOST="127.0.0.1"
DB_PORT="3306"
DB_USER="backup"
DB_PASS="StrongPass_2026"
DB_SOCKET="" # 指定则走 socket, 留空走 TCP
DATABASES="" # 留空=全库; 多库用空格分隔, 如 "db1 db2"
EXTRA_ARGS="--single-transaction --quick --routines --triggers --events"
BACKUP_BASE="/data/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"
KEEP_DAYS=7 # 本地保留天数
LOCK_FILE="/run/mysql_backup.lock"
# ---------- 日志与锁 ----------
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" >> "$LOG_FILE"
}
if [ -f "$LOCK_FILE" ]; then
log "ERROR 已有锁文件 $LOCK_FILE, 上次任务可能未完成, 本次跳过"
exit 1
fi
: > "$LOCK_FILE"
trap 'rm -f "$LOCK_FILE"' EXIT
# ---------- 组装 mysqldump 参数 ----------
MYSQL_ARGS=(-h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASS")
[ -n "$DB_SOCKET" ] && MYSQL_ARGS=(-S"$DB_SOCKET")
if [ -n "$DATABASES" ]; then
read -ra DB_LIST <<< "$DATABASES"
DUMP_OBJ=("${DB_LIST[@]}")
else
DUMP_OBJ=("--all-databases")
fi
# ---------- 执行备份 ----------
STAMP="$(date +%Y%m%d_%H%M%S)"
BACKUP_DIR="$BACKUP_BASE/$(date +%Y%m%d)"
mkdir -p "$BACKUP_DIR"
OUT_FILE="$BACKUP_DIR/${STAMP}_mysql_backup.sql.gz"
TMP_FILE="${OUT_FILE}.tmp"
log "开始备份: host=$DB_HOST db=${DUMP_OBJ[*]}"
/usr/bin/mysqldump "${MYSQL_ARGS[@]}" $EXTRA_ARGS "${DUMP_OBJ[@]}" \
| /usr/bin/gzip -9 > "$TMP_FILE"
# 关键: 检查 mysqldump 退出码, 失败则丢弃临时文件
if [ "${PIPESTATUS[0]}" -ne 0 ]; then
rm -f "$TMP_FILE"
log "ERROR mysqldump 执行失败, 请查看日志定位原因"
exit 1
fi
# 二次校验: gzip 退出码 + 输出非空
if [ "${PIPESTATUS[1]}" -ne 0 ] || [ ! -s "$TMP_FILE" ]; then
rm -f "$TMP_FILE"
log "ERROR gzip 或写文件失败"
exit 1
fi
mv "$TMP_FILE" "$OUT_FILE" # 原子落盘
SIZE="$(du -h "$OUT_FILE" | cut -f1)"
log "OK 备份完成: $OUT_FILE ($SIZE)"
# ---------- 保留策略: 清理 N 天前的备份 ----------
DELETED="$(find "$BACKUP_BASE" -type f -name "*.sql.gz" -mtime +"$KEEP_DAYS" -delete -print)"
if [ -n "$DELETED" ]; then
log "清理过期备份: $(echo "$DELETED" | wc -l) 个文件"
fi
find "$BACKUP_BASE" -type d -empty -delete 2>/dev/null || true
log "全部完成"
exit 0
3.3 核心细节解读
为什么必须用 PIPESTATUS：mysqldump | gzip > file 这条管道只返回最后一个命令（gzip）的退出码。若 mysqldump 中途报错，gzip 仍可能正常收尾，导致你拿到一个 “完整但没有内容” 的假备份。PIPESTATUS[0] 才是 mysqldump 的真实退出码。
--single-transaction 的一致性：对 InnoDB 生效，利用 MVCC 一致性快照，备份过程不锁表、不阻塞写入。但如果库里还有 MyISAM 表，需要额外加 --lock-tables（会锁表）。生产建议全库 InnoDB，这也是为什么新表一律 InnoDB 很重要。
防并发锁的边界：kill -9 强杀会导致锁文件残留，下次任务会误判。生产版可改为记录 PID：锁文件存在时读取 PID 用 kill -0 判断进程是否还活着，进程不存在则视为过期锁。文中版本作为基础用法足够，如需我可在脚本上再加一层。
4. 挂到 crontab
# 每天凌晨 2:00 执行（业务低峰）
0 2 * * * /usr/local/bin/mysql_backup.sh
两个生产要点：
- cron 的 PATH 极简（通常只有
/usr/bin:/bin），脚本内已用/usr/bin/mysqldump、/usr/bin/gzip绝对路径规避了这个问题。 - 脚本自带日志，cron 的 stdout/stderr 可丢弃或接邮件监控：
MAILTO=you@company.com。
5. 增量备份与任意时间点恢复（binlog）
先确保开启了 binlog（my.cnf）：
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW
binlog_expire_logs_seconds=604800 # 8.0 写法(保留7天)
# expire_logs_days=7 # 5.7 写法
用一个小时级定时任务把已写完的 binlog 归档走，全量备份 + binlog 归档合起来就是完整的恢复链：
#!/usr/bin/env bash
# binlog_backup.sh: 归档非当前正在写的 binlog
BINLOG_DIR="/var/lib/mysql"
BACKUP_DIR="/data/backup/mysql/binlog"
MYSQL_USER="backup"
MYSQL_PASS="StrongPass_2026"
mkdir -p "$BACKUP_DIR"
CURRENT=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e "SHOW MASTER STATUS;" 2>/dev/null | awk '{print $1}')
for f in "$BINLOG_DIR"/mysql-bin.*; do
b="$(basename "$f")"
[ "$b" = "$CURRENT" ] && break # 正在写的文件不备份
[ -f "$BACKUP_DIR/$b" ] && continue
cp "$f" "$BACKUP_DIR/$b"
done
挂 cron：0 * * * * /usr/local/bin/binlog_backup.sh
恢复到误删数据的某个时间点：
# 1) 先恢复最近一次全量
gunzip < /data/backup/mysql/20260826/20260826_020000_mysql_backup.sql.gz \
| mysql -uroot -p
# 2) 再重放该全量之后的 binlog, 截止到误删前一秒
mysqlbinlog --start-datetime="2026-08-26 02:00:00" \
--stop-datetime="2026-08-26 09:29:59" \
/data/backup/mysql/binlog/mysql-bin.000012 \
| mysql -uroot -p
6. 恢复演练（必须做）
备份之后一定要验证：
# 压缩包完整性
gzip -t /data/backup/mysql/20260826/*.sql.gz
# 恢复后校验表
mysqlcheck -uroot -p --all-databases
# 业务侧冒烟: 关键表 count / 最新一条记录对比
mysql -uroot -p -N -e "SELECT COUNT(*) FROM yourdb.orders;"
7. 生产清单
- 备份目录所在磁盘做空间监控（备份体积 = 库大小 × 压缩比，提前预留 2~3 倍余量）
- 脚本退出码接入告警（cron 失败即通知）
- 备份文件异地 / 对象存储一份，重要数据建议加密
- 密码不要硬编码：可改用
my.cnf的[client]段 +--defaults-extra-file，避免凭据出现在进程列表 - 大库（> 几十 GB）换用 Percona XtraBackup 物理备份，mysqldump 恢复时间会不可接受
- 每季度至少做一次完整恢复演练
8. 常见坑速查
表格
| 现象 | 原因 | 解决 |
|---|---|---|
| 备份文件有但恢复后数据不全 | 管道淹没了 mysqldump 失败退出码 | 用 PIPESTATUS 判定 |
Unknown table 'COLUMN_STATISTICS' | mysql 8.0 服务器被旧版本客户端 dump | mysqldump 加 --column-statistics=0 |
Access denied | 备份账号权限不足 | 按第 2 节授权 |
| cron 里执行报 command not found | cron PATH 太简 | 脚本内用绝对路径 |
| MyISAM 表备份时数据不一致 | --single-transaction 只对 InnoDB 有效 | 表迁移到 InnoDB 或加 --lock-tables |
| GTID 环境恢复报错 | GTID_PURGED 冲突 | dump 时加 --set-gtid-purged=OFF，恢复前评估 |