Windows下备份所有mysql数据库

目标

1. 基于mysqldump进行数据库的备份
2. 自动获取所有的数据库并分文件夹进行备份保存
3. 自动检测7zip并进行压缩存放
4. 自动清理90天之前的数据备份

以下是基于bat的完整代码

@echo off
REM REM MySQL配置
set MYSQL_PATH="C:\Program Files\MySQL\MySQL Server 8.1\bin\mysqldump.exe"
set DB_USER=root
set DB_PASS=sa123!SA
set BACKUP_ROOT=D:\backData
REM REM 生成时间戳（格式：YYYYMMDD_HHmmss）
set TIMESTAMP=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set TIMESTAMP=%TIMESTAMP: =0%
REM REM 获取数据库列表（排除系统库）
for /f "usebackq skip=1 tokens=*" %%d in (`mysql -u%DB_USER% -p%DB_PASS% -e "SHOW DATABASES;"`) do (
 set "db=%%d"
 if not "%%d"=="information_schema" (
 if not "%%d"=="mysql" (
 if not "%%d"=="performance_schema" (
 if not "%%d"=="sys" (
 REM == 动态生成专属备份目录 ==
 REM 此处别用set会导致变量不更新的问题
 REM set "BACKUP_DIR=%BACKUP_ROOT%\%%d"
 if not exist %BACKUP_ROOT%\%%d (
 mkdir %BACKUP_ROOT%\%%d
 )
 
 REM REM 执行分库备份
 %MYSQL_PATH% --user=%DB_USER% --password=%DB_PASS% %%d > %BACKUP_ROOT%\%%d\%%d_%TIMESTAMP%.sql
 
 REM REM 可选：压缩备份文件
 if exist "C:\Program Files\7-Zip\7z.exe" (
 "C:\Program Files\7-Zip\7z.exe" a -t7z %BACKUP_ROOT%\%%d\%%d_%TIMESTAMP%.7z %BACKUP_ROOT%\%%d\%%d_%TIMESTAMP%.sql
 del %BACKUP_ROOT%\%%d\%%d_%TIMESTAMP%.sql
 forfiles /p "%BACKUP_ROOT%\%%d" /m *.7z /d -90 /c "cmd /c del @path"
 ) else (
 forfiles /p "%BACKUP_ROOT%\%%d" /m *.sql /d -90 /c "cmd /c del @path"
 )
 )
 )
 )
 )
)
REM REM 记录备份日志
echo [%DATE% %TIME%] 分库备份完成 >> %BACKUP_ROOT%\backup.log

‍