@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
|
set SEVENZIP="C:\Program Files\7-Zip\7z.exe"
|
|
:: 创建备份目录
|
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"
|
|
:: 使用 7-Zip 压缩 SQL 文件为 ZIP
|
%SEVENZIP% a -tzip "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.zip" "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql"
|
|
:: 删除原始 SQL 文件
|
del /F /Q "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql"
|
) else (
|
echo Skipping database: %%D (contains "test")
|
)
|
)
|
|
:: 删除超过 3 天的 ZIP 文件
|
forfiles /p "%BACKUP_DIR%" /m *.zip /d -3 /c "cmd /c del /F /Q @path"
|
|
echo Backup complete. ZIP files saved in %BACKUP_DIR%.
|
