当前位置：首页 > news >正文

Windows Server 设置MySQL自动备份任务(每日凌晨2点执行)

news 2025/7/23 8:03:34

Windows Server 设置MySQL自动备份任务(每日凌晨2点执行)

1. 保存备份脚本

将脚本保存为mysql_backup.bat文件。

@echo off
setlocal enabledelayedexpansion:: ================================================
:: 自动请求管理员权限
:: ================================================
:: 检查是否已管理员身份运行
net session >nul 2>&1
if %ERRORLEVEL% NEQ 0 (echo 正在请求管理员权限...powershell -Command "Start-Process '%~s0' -Verb RunAs"exit /b
):: ================================================
:: 数据库备份脚本
:: 功能：自动备份MySQL数据库，保留7天历史备份
:: 特点：WinRAR压缩、并行执行、错误日志、权限控制
:: 配置日期：2025-07-22
:: ================================================:: ------------------------------
:: 配置区域（根据实际环境修改）
:: 初始化变量
:: ------------------------------
set "MYSQL_BIN=C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump"
set "DATABASE_USER=root"
set "DATABASE_PASSWORD=123456"
set "BACKUP_ROOT=%~dp0"                            
set "DATABASES=app,client"
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
:: 日志目录
set "LOG_DIR=%BACKUP_ROOT%logs"
:: 备份日志文件
set "LOGFILE=%BACKUP_ROOT%logs/backup_%Ymd%.log"
set ERROR_FLAG=0:: 创建日志目录
if not exist "%LOG_DIR%" (mkdir "%LOG_DIR%"
)echo ====================================================================================================================== >> %LOGFILE%
echo [%date% %time%] === 备份开始 === >> %LOGFILE%:: ------------------------------
:: 目录检查与创建
:: ------------------------------
for /l %%i in (1,1,7) do (if not exist %BACKUP_ROOT%D%%i (mkdir %BACKUP_ROOT%D%%iecho [%date% %time%] 提示：创建目录: D%%i >> %LOGFILE%)
):: ------------------------------
:: 清理旧备份（保留7天）
:: ------------------------------
if exist %BACKUP_ROOT%D1/*.sql (del /Q "%BACKUP_ROOT%\D1\*.sql" >> "%LOGFILE%" 2>&1if !ERRORLEVEL! NEQ 0 (echo [%date% %time%] 错误: 删除D1旧备份失败 >> %LOGFILE%set ERROR_FLAG=1)
)else (echo [%date% %time%] 警告: D1无SQL文件可删除 >> %LOGFILE%
):: ------------------------------
:: 目录轮转
:: ------------------------------
for /l %%i in (2,1,7) do (set /a j=%%i-1if exist "%BACKUP_ROOT%D%%i\*.sql" (move /Y "%BACKUP_ROOT%D%%i\*.sql" "%BACKUP_ROOT%D!j!\" >> %LOGFILE% 2>&1if !ERRORLEVEL! NEQ 0 (echo [%date% %time%] 错误: 移动D%%i到D!j!失败（错误码: !ERRORLEVEL!） >> %LOGFILE%set ERROR_FLAG=1))else (echo [%date% %time%] 警告: D%%i无SQL文件可移动 >> %LOGFILE%)
):: ------------------------------
:: 顺序备份数据库
:: ------------------------------
for %%d in (%DATABASES%) do (echo [%date% %time%] 开始备份数据库: %%d >> %LOGFILE%:: 执行备份"%MYSQL_BIN%" -u%DATABASE_USER% -p%DATABASE_PASSWORD% %%d > "%BACKUP_ROOT%D7\%%d_%Ymd%.sql" :: 错误检查if !ERRORLEVEL! NEQ 0 (echo [%date% %time%] 严重错误: 数据库 %%d 备份失败 >> %LOGFILE%set ERROR_FLAG=1) else (echo [%date% %time%] 数据库成功备份: %%d >> %LOGFILE%)
):: ------------------------------
:: 最终状态检查
:: ------------------------------
if %ERROR_FLAG% NEQ 0 (echo [%date% %time%] === 备份完成（有错误）=== >> %LOGFILE%exit /b 1
) else (echo [%date% %time%] === 备份完成（成功）=== >> %LOGFILE%exit /b 0
)endlocal