备份文件还在，为什么恢复不出来：备什么、多久一次、怎么验证

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

STAMP=$(date -u +%Y%m%dT%H%M%SZ)
WORK="/var/backups/work/$STAMP"
DEST="${DEST:-/srv/backup}"
DB_NAME="${DB_NAME:-app}"
GPG_RECIPIENT="${GPG_RECIPIENT:?需要设置 GPG 收件人}"
CNF=/etc/backup.cnf    # [client] user=backup password=... ，chmod 600

mkdir -p "$WORK"
trap 'rm -rf "$WORK"' EXIT

# 1. 数据库：InnoDB 用 --single-transaction 拿一致性快照，不锁业务表
mysqldump --defaults-extra-file="$CNF" \
  --single-transaction --quick --routines --triggers --events \
  --default-character-set=utf8mb4 \
  "$DB_NAME" | gzip -9 > "$WORK/db.sql.gz"

# 2. 文件：--numeric-ids 保住属主，--acls --xattrs 保住扩展属性
tar --numeric-ids --acls --xattrs -czf "$WORK/files.tar.gz" -C / \
  var/www/app \
  etc/nginx \
  etc/php \
  etc/letsencrypt \
  etc/systemd/system/app.service

# 3. 清单：恢复的人要知道这份包是什么环境产出的
{
  echo "stamp=$STAMP"
  echo "db=$DB_NAME"
  mysql --defaults-extra-file="$CNF" -N -B -e "SELECT VERSION()"
  uname -srm
  docker version --format 'server={{.Server.Version}}' 2>/dev/null || true
} > "$WORK/MANIFEST.txt"

# 4. 备份那一刻的行数，恢复后拿它对
mysql --defaults-extra-file="$CNF" -N -B -e \
  "SELECT TABLE_NAME FROM information_schema.TABLES
   WHERE TABLE_SCHEMA='$DB_NAME' AND TABLE_TYPE='BASE TABLE'" |
while read -r t; do
  n=$(mysql --defaults-extra-file="$CNF" -N -B -e "SELECT COUNT(*) FROM \`$DB_NAME\`.\`$t\`")
  printf '%s\t%s\n' "$t" "$n"
done | sort > "$WORK/rows.tsv"

# 5. 关键表再留一份校验和
for t in orders payments users; do
  mysql --defaults-extra-file="$CNF" -N -B -e "CHECKSUM TABLE \`$DB_NAME\`.\`$t\`"
done > "$WORK/checksums.tsv"

# 6. 先校验，再加密
cd "$WORK"
sha256sum db.sql.gz files.tar.gz MANIFEST.txt rows.tsv checksums.tsv > SHA256SUMS

# 7. 公钥加密后落到异地
tar -C "$WORK" -cf - . |
  gpg --batch --yes --quiet --encrypt --trust-model always -r "$GPG_RECIPIENT" \
  > "$DEST/$STAMP.tar.gpg"

# 8. 体积突变告警：比历史中位数小 40% 通常是漏了数据（如某个目录挂载掉了）
SIZE=$(stat -c %s "$DEST/$STAMP.tar.gpg")
MEDIAN=$(find "$DEST" -name '*.tar.gpg' ! -name "$STAMP.tar.gpg" -printf '%s\n' |
         sort -n | awk '{a[NR]=$1} END{if(NR) print a[int((NR+1)/2)]}')
if [ -n "${MEDIAN:-}" ] && [ "$SIZE" -lt $((MEDIAN * 6 / 10)) ]; then
  echo "备份体积异常：$SIZE 字节，历史中位数 $MEDIAN" >&2
  exit 2
fi

#!/usr/bin/env bash
# /usr/local/bin/restore-verify.sh  <备份包>
set -euo pipefail
ARCHIVE=$1
TMP=$(mktemp -d)
trap 'rm -rf "$TMP"' EXIT

# 1. 传输完整性
gpg --batch --quiet --decrypt "$ARCHIVE" | tar -C "$TMP" -xf -
cd "$TMP" && sha256sum -c SHA256SUMS

# 2. 导进临时库，DISTINCT 一个独立库名
mysql -e 'DROP DATABASE IF EXISTS verify_db'
mysql -e 'CREATE DATABASE verify_db CHARACTER SET utf8mb4'
gunzip -c db.sql.gz | mysql --default-character-set=utf8mb4 verify_db

# 3. 行数比对（对的是备份当时记下的 rows.tsv，不是现在的生产库）
mysql -N -B -e "SELECT TABLE_NAME FROM information_schema.TABLES
  WHERE TABLE_SCHEMA='verify_db' AND TABLE_TYPE='BASE TABLE'" |
while read -r t; do
  n=$(mysql -N -B -e "SELECT COUNT(*) FROM \`verify_db\`.\`$t\`")
  printf '%s\t%s\n' "$t" "$n"
done | sort > rows_restore.tsv

diff -u rows.tsv rows_restore.tsv && echo "行数一致"

# 4. 关键表校验和
cut -f1 checksums.tsv | while read -r t; do
  mysql -N -B -e "CHECKSUM TABLE \`verify_db\`.\`$t\`" | cut -f2
done > ck_restore.txt
cut -f2 checksums.tsv | diff -u - ck_restore.txt && echo "校验和一致"

-- 两边各跑一次，数字必须一样
SELECT COUNT(*), SUM(total_amount), MAX(created_at)
FROM orders WHERE created_at >= '2026-10-01';

-- 抽一条具体订单，人工看一眼
SELECT id, status, total_amount, created_at FROM orders WHERE id = 102411;

# /etc/cron.d/restore-drill
0 4 * * 0 root /usr/local/bin/restore-verify.sh "$(ls -1t /srv/backup/*.tar.gpg | head -1)" >> /var/log/restore-drill.log 2>&1

#!/usr/bin/env bash
# /usr/local/bin/check-backup-freshness.sh
set -euo pipefail
NEWEST=$(find /srv/backup -name '*.tar.gpg' -printf '%T@\n' | sort -n | tail -1)
AGE=$(( $(date +%s) - ${NEWEST%.*} ))
[ "$AGE" -lt 93600 ] || { echo "最新备份已是 $((AGE/3600)) 小时前" >&2; exit 1; }

docker run --rm -v app_uploads:/v -v /srv/backup:/out alpine \
  tar -C /v -czf /out/uploads.tar.gz .
# 恢复后比对属主，容器里的 www-data 在宿主上可能没有同名用户
docker run --rm -v app_uploads:/v alpine ls -ln /v | head