shanxia2.0/生产环境部署/生产环境数据库备份的shell脚本/backup-mysql.sh

#!/bin/bash
# ============================================
# shanxia2.0 生产 MySQL 备份 + API 日志表超限清理
# 库：shanxia2.0
# - 全库备份，但 IGNORE_DATA_TABLE（默认 sys_api_request_log）只导结构不导数据
# - 该表 data+index 超过阈值（默认 2G）时 TRUNCATE 清理
# 用法：由 cron 调用，或手动 ./backup-mysql.sh
# ============================================

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
CONF_FILE="${CONF_FILE:-$SCRIPT_DIR/backup.conf}"
CRON_TAG="shanxia2.0-mysql-backup"

RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

log_info()  { echo -e "${GREEN}[INFO]${NC} $(date '+%F %T') $1"; }
log_warn()  { echo -e "${YELLOW}[WARN]${NC} $(date '+%F %T') $1"; }
log_error() { echo -e "${RED}[ERROR]${NC} $(date '+%F %T') $1"; }

die() {
  log_error "$1"
  exit 1
}

load_conf() {
  if [ ! -f "$CONF_FILE" ]; then
    die "缺少配置文件: $CONF_FILE （请先 cp backup.conf.example backup.conf 并填写密码）"
  fi
  # shellcheck disable=SC1090
  source "$CONF_FILE"

  : "${MYSQL_HOST:?缺少 MYSQL_HOST}"
  : "${MYSQL_PORT:?缺少 MYSQL_PORT}"
  : "${MYSQL_USER:?缺少 MYSQL_USER}"
  : "${MYSQL_PASSWORD:?缺少 MYSQL_PASSWORD}"
  : "${MYSQL_DATABASE:?缺少 MYSQL_DATABASE}"
  : "${BACKUP_DIR:?缺少 BACKUP_DIR}"
  : "${LOG_DIR:?缺少 LOG_DIR}"

  KEEP_DAYS="${KEEP_DAYS:-14}"
  LOG_TABLE_MAX_BYTES="${LOG_TABLE_MAX_BYTES:-2147483648}"
  IGNORE_DATA_TABLE="${IGNORE_DATA_TABLE:-sys_api_request_log}"
}

require_cmds() {
  command -v mysql >/dev/null 2>&1 || die "未找到 mysql 客户端，请先安装 mysql-client"
  command -v mysqldump >/dev/null 2>&1 || die "未找到 mysqldump，请先安装 mysql-client"
  command -v gzip >/dev/null 2>&1 || die "未找到 gzip"
}

# 写入临时 defaults 文件，避免密码出现在进程列表
make_defaults_file() {
  local tmp
  tmp="$(mktemp)"
  chmod 600 "$tmp"
  cat >"$tmp" <<EOF
[client]
host=${MYSQL_HOST}
port=${MYSQL_PORT}
user=${MYSQL_USER}
password=${MYSQL_PASSWORD}
default-character-set=utf8mb4
EOF
  echo "$tmp"
}

mysql_cli() {
  mysql --defaults-extra-file="$DEFAULTS_FILE" "$@"
}

mysqldump_cli() {
  mysqldump --defaults-extra-file="$DEFAULTS_FILE" "$@"
}

human_size() {
  local bytes="$1"
  if command -v numfmt >/dev/null 2>&1; then
    numfmt --to=iec --suffix=B "$bytes" 2>/dev/null || echo "${bytes}B"
  else
    awk -v b="$bytes" 'BEGIN{
      if (b < 1024) printf "%dB", b;
      else if (b < 1048576) printf "%.1fKiB", b/1024;
      else if (b < 1073741824) printf "%.1fMiB", b/1048576;
      else printf "%.2fGiB", b/1073741824;
    }'
  fi
}

# 查询表 data_length + index_length（字节）；表不存在返回 0
get_table_bytes() {
  local db="$1" table="$2"
  local size
  size="$(mysql_cli -N -e "
    SELECT IFNULL(data_length,0) + IFNULL(index_length,0)
    FROM information_schema.tables
    WHERE table_schema = '${db}' AND table_name = '${table}'
    LIMIT 1;
  " 2>/dev/null || true)"
  if [ -z "${size}" ]; then
    echo 0
  else
    echo "$size"
  fi
}

cleanup_log_table_if_needed() {
  local db="$MYSQL_DATABASE"
  local table="$IGNORE_DATA_TABLE"
  local max_bytes="$LOG_TABLE_MAX_BYTES"
  local size

  size="$(get_table_bytes "$db" "$table")"
  log_info "表 ${db}.${table} 当前占用: $(human_size "$size")（阈值 $(human_size "$max_bytes")）"

  if [ "$size" -le "$max_bytes" ]; then
    log_info "未超过阈值，跳过清理"
    return 0
  fi

  log_warn "超过阈值，开始 TRUNCATE \`${table}\` …"
  mysql_cli -e "TRUNCATE TABLE \`${db}\`.\`${table}\`;"
  size="$(get_table_bytes "$db" "$table")"
  log_info "清理完成，当前占用: $(human_size "$size")"
}

do_backup() {
  local db="$MYSQL_DATABASE"
  local table="$IGNORE_DATA_TABLE"
  local stamp out_sql out_gz ignore_arg

  stamp="$(date '+%Y%m%d_%H%M%S')"
  out_sql="${BACKUP_DIR}/${db}_${stamp}.sql"
  out_gz="${out_sql}.gz"
  ignore_arg="--ignore-table=${db}.${table}"

  mkdir -p "$BACKUP_DIR" "$LOG_DIR"

  log_info "开始备份库 ${db} → ${out_gz}"
  log_info "排除表数据: ${db}.${table}（仍会导出表结构）"

  # 1) 全库（忽略日志表整表，含结构）
  # 2) 单独导出日志表结构（无数据）
  {
    mysqldump_cli \
      --single-transaction \
      --quick \
      --routines \
      --triggers \
      --events \
      --hex-blob \
      --default-character-set=utf8mb4 \
      --set-gtid-purged=OFF \
      ${ignore_arg} \
      "${db}"

    echo ""
    echo "-- ------------------------------------------------"
    echo "-- Structure only for ${table} (data intentionally omitted)"
    echo "-- ------------------------------------------------"
    mysqldump_cli \
      --no-data \
      --single-transaction \
      --default-character-set=utf8mb4 \
      --set-gtid-purged=OFF \
      "${db}" \
      "${table}"
  } | gzip -c >"${out_gz}"

  if [ ! -s "${out_gz}" ]; then
    die "备份文件为空: ${out_gz}"
  fi

  log_info "备份完成: ${out_gz}（$(human_size "$(wc -c <"${out_gz}")")）"
}

purge_old_backups() {
  local days="$KEEP_DAYS"
  log_info "清理 ${BACKUP_DIR} 中超过 ${days} 天的 *.sql.gz"
  find "$BACKUP_DIR" -type f -name "${MYSQL_DATABASE}_*.sql.gz" -mtime "+${days}" -print -delete 2>/dev/null || true
}

main() {
  load_conf
  require_cmds

  DEFAULTS_FILE="$(make_defaults_file)"
  trap 'rm -f "$DEFAULTS_FILE"' EXIT

  # 先探测连通性
  mysql_cli -e "SELECT 1;" >/dev/null || die "无法连接 MySQL ${MYSQL_HOST}:${MYSQL_PORT}"

  cleanup_log_table_if_needed
  do_backup
  purge_old_backups
  log_info "全部完成"
}

main "$@"