@echo off
chcp 65001 >nul
setlocal EnableDelayedExpansion

:: ============================================
:: MySQL 自动备份脚本 (Windows BAT 版)
:: 功能：备份数据库 + 自动清理6个月前备份
:: ============================================

:: --- 配置区（根据你的环境修改这5项）---
set DB_HOST=localhost
set DB_PORT=3306
set DB_USER=your_username
set DB_PASSWORD=your_password
set DB_NAME=your_database

:: 备份目录（支持本地路径或网络共享 \\server\share）
set BACKUP_DIR=\\idmt-fileg\DB_BK\mysql_DataBAK
set LOG_FILE=%BACKUP_DIR%\backup.log
set RETENTION_DAYS=180

:: mysqldump 路径（根据实际安装路径修改）
set MYSQLDUMP="C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe"

:: --- 初始化 ---
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"

:: 获取时间戳（调用 PowerShell，兼容所有区域设置）
for /f "usebackq delims=" %%a in (`powershell -Command "Get-Date -Format yyyyMMdd_HHmmss"`) do set TIMESTAMP=%%a
for /f "usebackq delims=" %%a in (`powershell -Command "Get-Date -Format 'yyyy-MM-dd HH:mm:ss'"`) do set LOGTIME=%%a

set BACKUP_FILE=%BACKUP_DIR%\backup_%DB_NAME%_%TIMESTAMP%.sql
set ZIP_FILE=%BACKUP_FILE%.zip

:: --- 开始备份 ---
echo [%LOGTIME%] 开始备份数据库: %DB_NAME% >> "%LOG_FILE%"

:: 执行备份（通过环境变量传密码，避免进程列表暴露密码）
set "MYSQL_PWD=%DB_PASSWORD%"
%MYSQLDUMP% -h %DB_HOST% -P %DB_PORT% -u %DB_USER% --single-transaction --routines --triggers --events %DB_NAME% > "%BACKUP_FILE%"
set "MYSQL_PWD="

if %ERRORLEVEL% neq 0 (
    echo [%LOGTIME%] 备份失败！错误代码: %ERRORLEVEL% >> "%LOG_FILE%"
    if exist "%BACKUP_FILE%" del "%BACKUP_FILE%"
    exit /b 1
)

if not exist "%BACKUP_FILE%" (
    echo [%LOGTIME%] 备份文件未生成，请检查目录权限 >> "%LOG_FILE%"
    exit /b 1
)

:: --- 压缩（尝试打包成 zip，失败则保留 .sql）---
echo [%LOGTIME%] 正在压缩备份文件... >> "%LOG_FILE%"

set "PS_TEMP=%TEMP%\mysql_zip_%TIMESTAMP%.ps1"
(
    echo Add-Type -AssemblyName System.IO.Compression.FileSystem -ErrorAction Stop
    echo $temp = '%BACKUP_DIR%\temp_%TIMESTAMP%'
    echo New-Item -ItemType Directory -Path $temp -Force ^| Out-Null
    echo Copy-Item '%BACKUP_FILE%' "$temp\backup.sql" -Force
    echo if (Test-Path '%ZIP_FILE%') { Remove-Item '%ZIP_FILE%' -Force }
    echo [System.IO.Compression.ZipFile]::CreateFromDirectory($temp, '%ZIP_FILE%', [System.IO.Compression.CompressionLevel]::Optimal, $false)
    echo Remove-Item $temp -Recurse -Force
    echo Remove-Item '%BACKUP_FILE%' -Force
) > "%PS_TEMP%"

powershell -ExecutionPolicy Bypass -File "%PS_TEMP%" >nul 2>&1

if exist "%ZIP_FILE%" (
    echo [%LOGTIME%] 备份成功: %ZIP_FILE% >> "%LOG_FILE%"
) else (
    echo [%LOGTIME%] 备份成功: %BACKUP_FILE% (压缩失败，保留SQL文件) >> "%LOG_FILE%"
)

if exist "%PS_TEMP%" del "%PS_TEMP%"

:: --- 清理6个月前的备份（Windows自带 forfiles）---
echo [%LOGTIME%] 开始清理 %RETENTION_DAYS% 天前的备份... >> "%LOG_FILE%"

forfiles /p "%BACKUP_DIR%" /m "backup_%DB_NAME%_*.sql*" /d -%RETENTION_DAYS% /c "cmd /c del @path" >nul 2>&1

echo [%LOGTIME%] 清理完成 >> "%LOG_FILE%"
echo [%LOGTIME%] 本次任务结束 >> "%LOG_FILE%"
echo ---------------------------------------- >> "%LOG_FILE%"

endlocal