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

‍