@echo off
|
setlocal enabledelayedexpansion
|
|
:: 获取当前日期时间作为时间戳（年月日_时分秒）
|
for /f %%i in ('wmic os get localdatetime ^| find "."') do set DT=%%i
|
set DATE=%DT:~0,8%
|
set TIME=%DT:~8,6%
|
set DATETIME=%DATE%_%TIME%
|
|
:: 设置变量
|
set BACKUP_DIR=C:\Program Files\MySQL\database_backups
|
set MYSQL_BIN=C:\Program Files\MySQL\MySQL Server 8.0\bin
|
set MYSQL_USER=root
|
set MYSQL_PASSWORD=Root_ltkj123
|
|
:: 创建备份目录
|
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"
|
|
:: 遍历数据库（排除包含 test 的）
|
for /F "skip=1 delims=" %%D in ('""%MYSQL_BIN%\mysql.exe" -u%MYSQL_USER% -p%MYSQL_PASSWORD% -e "SHOW DATABASES LIKE 'ltkjpeis%%';" -s -N"') do (
|
echo %%D | find /I "test" >nul
|
if errorlevel 1 (
|
set DB_NAME=%%D
|
echo Backing up !DB_NAME!...
|
|
:: 生成 SQL 文件
|
"%MYSQL_BIN%\mysqldump.exe" -u%MYSQL_USER% -p%MYSQL_PASSWORD% --routines --triggers --events !DB_NAME! > "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql"
|
|
:: 压缩为 ZIP（需要 PowerShell 5.0+）
|
powershell -NoProfile -ExecutionPolicy Bypass -Command ^
|
"Compress-Archive -Path '%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql' -DestinationPath '%BACKUP_DIR%\!DB_NAME!_%DATETIME%.zip'"
|
|
:: 删除原始 SQL 文件
|
del /F /Q "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql"
|
) else (
|
echo Skipping database: %%D (contains "test")
|
)
|
)
|
|
:: 删除超过 3 天的 ZIP 文件
|
powershell -NoProfile -ExecutionPolicy Bypass -Command ^
|
"Get-ChildItem -Path \"%BACKUP_DIR%\" -Filter *.zip ^| Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-3) } ^| Remove-Item -Force"
|
|
echo Backup complete. ZIP files saved in %BACKUP_DIR%.
|
