#!/bin/bash

# === 配置部分 ===
BACKUP_BASE_DIR="/data/backup_mysql"
DATE=$(date +%F)
LOG_FILE="${BACKUP_BASE_DIR}/logs/backup-${DATE}.log"
LOCK_FILE="/tmp/mysql_backup.lock"

# 備份選項(xiàng)（1=啟用，0=禁用）
ENABLE_FULL_BACKUP=1
ENABLE_PER_DB_BACKUP=1

# === 日志重定向 ===
mkdir -p "${BACKUP_BASE_DIR}/logs"
exec >> "$LOG_FILE" 2>&1

echo "===== MySQL Backup Started: $(date) ====="

# === 加鎖，避免重復(fù)執(zhí)行 ===
exec 9>"$LOCK_FILE"
if ! flock -n 9; then
  echo "?? 另一個(gè)備份任務(wù)正在運(yùn)行，退出..."
  exit 1
fi

# === 全量備份 ===
if [ "$ENABLE_FULL_BACKUP" -eq 1 ]; then
  echo "?? 開始全庫(kù)備份..."
  FULL_BACKUP_FILE="${BACKUP_BASE_DIR}/all/all-databases-${DATE}.sql"
  FULL_GZ_FILE="${FULL_BACKUP_FILE}.gz"
  mkdir -p "${BACKUP_BASE_DIR}/all"

  mysqldump --all-databases --single-transaction --quick --lock-tables=false > "$FULL_BACKUP_FILE"

  if [ $? -eq 0 ]; then
    echo "? 全庫(kù)備份成功: $FULL_BACKUP_FILE"
    gzip "$FULL_BACKUP_FILE"
    if [ $? -eq 0 ]; then
      echo "? 全庫(kù)壓縮成功: $FULL_GZ_FILE"
    else
      echo "? 全庫(kù)壓縮失敗: $FULL_BACKUP_FILE" >&2
    fi
  else
    echo "? 全庫(kù)備份失敗" >&2
    rm -f "$FULL_BACKUP_FILE"
  fi
fi

# === 每庫(kù)備份 ===
if [ "$ENABLE_PER_DB_BACKUP" -eq 1 ]; then
  echo "?? 開始庫(kù)級(jí)別備份..."
  databases=$(mysql -e "SHOW DATABASES;" | grep -Ev "^(Database|information_schema|performance_schema|mysql|sys)$")

  for db in $databases; do
    echo "?? 正在備份: $db"
    DB_BACKUP_DIR="${BACKUP_BASE_DIR}/${db}"
    mkdir -p "$DB_BACKUP_DIR"
    DB_BACKUP_FILE="${DB_BACKUP_DIR}/${db}-${DATE}.sql"
    DB_GZ_FILE="${DB_BACKUP_FILE}.gz"

    mysqldump --single-transaction --quick --lock-tables=false "$db" > "$DB_BACKUP_FILE"

    if [ $? -eq 0 ]; then
      echo "? 備份成功: $DB_BACKUP_FILE"
      gzip "$DB_BACKUP_FILE"
      if [ $? -eq 0 ]; then
        echo "? 壓縮成功: $DB_GZ_FILE"
      else
        echo "? 壓縮失敗: $DB_BACKUP_FILE" >&2
      fi
    else
      echo "? 備份失敗: $db" >&2
      rm -f "$DB_BACKUP_FILE"
    fi
  done
fi

# === 清理過(guò)期文件（.sql / .sql.gz） ===
echo "?? 清理 7 天前的備份文件..."
find "$BACKUP_BASE_DIR" -type f \( -name "*.sql" -o -name "*.sql.gz" \) -mtime +7 -exec rm -f {} \;

echo "===== MySQL Backup Completed: $(date) ====="

mysql -u root -p -e "
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME='imip_ecb';
"

SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME
----------- | -------------------------- | ------------------------
imip_ecb    | utf8mb4                    | utf8mb4_general_ci

mkdir -p /data/backup_mysql/manual_before_restore

mysqldump --single-transaction --quick --lock-tables=false \
  imip_ecb > /data/backup_mysql/manual_before_restore/imip_ecb-before-restore-`date +%F`.sql

cd /data/backup_mysql/manual_before_restore
ll
# imip_ecb-before-restore-2025-12-11.sql

mysql -u root -p -e "DROP DATABASE IF EXISTS imip_ecb;"

mysql -u root -p -e "
CREATE DATABASE imip_ecb
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;
"

zcat /data/backup_mysql/imip_ecb/imip_ecb-2025-12-11.sql.gz \
  | mysql -u root -p imip_ecb

mysql -u root -p -e "SHOW DATABASES LIKE 'imip_ecb';"

mysql -u root -p -D imip_ecb -e "SHOW TABLES;"

EMS_EQUIPMENT_INFO
EMS_INSPECT_TASK
EMS_MAINTENANCE_WORK_ORDER
ERP_STOCK
ERP_STOCK_IN
ERP_STOCK_OUT
ERP_SUPPLIER
ERP_WAREHOUSE
infra_task_attachment
...