#!/usr/bin/env bash
# surinch.com · MySQL 备份可恢复性演练 v1.0
# 做法：mysqldump 逻辑备份 -> 恢复到新建临时库 -> 逐表 count(*) 比对 -> 写 CSV 证据 -> 删除临时库
# 对源库只读；会在同一实例 CREATE/DROP 临时库 <源库>_restore_drill_<时间戳>，需 CREATE 权限与足够磁盘。
# 用法：MYSQL_PWD=... ./mysql-backup-restore-drill-v1.0.sh <源库名> [输出目录]
# 连接参数：MYSQL_HOST / MYSQL_TCP_PORT / MYSQL_USER / MYSQL_PWD（或 ~/.my.cnf）
# 建议使用与服务器同系列的 mysqldump（MySQL 8 客户端；MariaDB 客户端可能缺 --set-gtid-purged 等选项）
set -euo pipefail
SRC_DB="${1:?用法: $0 <源库名> [输出目录]}"
OUT_DIR="${2:-./drill-$(date +%Y%m%d-%H%M%S)}"
TS="$(date +%Y%m%d%H%M%S)"
DRILL_DB="${SRC_DB}_restore_drill_${TS}"
mkdir -p "$OUT_DIR"
DUMP="$OUT_DIR/${SRC_DB}.sql.gz"

mysql_base=(mysql --protocol=TCP -N -B)
dump_base=(mysqldump --protocol=TCP --single-transaction --routines --triggers --no-tablespaces)
if [[ -n "${MYSQL_HOST:-}" ]]; then mysql_base+=(-h "$MYSQL_HOST"); dump_base+=(-h "$MYSQL_HOST"); fi
if [[ -n "${MYSQL_TCP_PORT:-}" ]]; then mysql_base+=(-P "$MYSQL_TCP_PORT"); dump_base+=(-P "$MYSQL_TCP_PORT"); fi
if [[ -n "${MYSQL_USER:-}" ]]; then mysql_base+=(-u "$MYSQL_USER"); dump_base+=(-u "$MYSQL_USER"); fi

if mysqldump --help 2>/dev/null | grep -q -- '--set-gtid-purged'; then
  dump_base+=(--set-gtid-purged=OFF)
fi

# 输出：table_name,row_count（不含 schema，便于两侧 join）
# 注意：mysql 客户端在管道/循环里会吞掉 stdin，每次调用必须 </dev/null
count_rows() {
  local db="$1"
  local tables t c
  tables=$("${mysql_base[@]}" -e "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA='${db}' AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME;" </dev/null)
  while IFS= read -r t; do
    [[ -z "$t" ]] && continue
    c=$("${mysql_base[@]}" -e "SELECT COUNT(*) FROM \`${db}\`.\`${t}\`;" </dev/null)
    echo "$t,$c"
  done <<< "$tables"
}

t0=$(date +%s)
"${dump_base[@]}" "$SRC_DB" | gzip -c > "$DUMP"
t1=$(date +%s)

"${mysql_base[@]}" -e "CREATE DATABASE \`${DRILL_DB}\`;" </dev/null
cleanup() { "${mysql_base[@]}" -e "DROP DATABASE IF EXISTS \`${DRILL_DB}\`;" </dev/null >/dev/null 2>&1 || true; }
trap cleanup EXIT

gunzip -c "$DUMP" | "${mysql_base[@]}" "$DRILL_DB"
t2=$(date +%s)

count_rows "$SRC_DB"   > "$OUT_DIR/src_counts.csv"
count_rows "$DRILL_DB" > "$OUT_DIR/restored_counts.csv"
echo "table,src_rows,restored_rows,match" > "$OUT_DIR/compare.csv"
join -t, -a1 -a2 -e MISSING -o 0,1.2,2.2 "$OUT_DIR/src_counts.csv" "$OUT_DIR/restored_counts.csv" \
  | awk -F, '{print $0","($2==$3?"OK":"DIFF")}' >> "$OUT_DIR/compare.csv"
DIFFS=$(grep -c ',DIFF$' "$OUT_DIR/compare.csv" || true)
TABLES=$(($(wc -l < "$OUT_DIR/compare.csv") - 1))
{
  echo "item,value"
  echo "source_db,$SRC_DB"
  echo "mysql_version,$("${mysql_base[@]}" -e 'SELECT VERSION();' </dev/null)"
  echo "dump_bytes,$(stat -c %s "$DUMP")"
  echo "dump_seconds,$((t1-t0))"
  echo "restore_seconds,$((t2-t1))"
  echo "tables_compared,$TABLES"
  echo "tables_diff,$DIFFS"
  echo "result,$([ "$DIFFS" = 0 ] && echo PASS || echo FAIL)"
} > "$OUT_DIR/summary.csv"
cat "$OUT_DIR/summary.csv"
cat "$OUT_DIR/compare.csv"
[ "$DIFFS" = 0 ]