Windows下备份所有mysql数据库

Windows下备份所有mysql数据库

目标

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

1. 按数据库分别备份

@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=password
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

2. 按数据表，数据库分别备份

@echo off

REM MySQL配置
set MYSQL_PATH="C:\Program Files\MySQL\MySQL Server 8.1\bin\mysqldump.exe"
set DB_USER=root
set DB_PASS=password
set BACKUP_ROOT=D:\backData

REM 生成时间戳
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 获取数据库列表（排除系统库）
for /f "usebackq skip=1 tokens=*" %%d in (`mysql -u"%DB_USER%" -p"%DB_PASS%" -e "SHOW DATABASES;" 2^>nul`) do (
    if not "%%d"=="information_schema" (
        if not "%%d"=="mysql" (
            if not "%%d"=="performance_schema" (
                if not "%%d"=="sys" (
                    REM 创建数据库专属备份目录（包含时间戳）
                    if not exist "%BACKUP_ROOT%\%%d\%TIMESTAMP%" mkdir "%BACKUP_ROOT%\%%d\%TIMESTAMP%"
                    
                    REM 获取当前数据库的所有表
                    mysql -u"%DB_USER%" -p"%DB_PASS%" -D "%%d" -e "SHOW TABLES;" > "%BACKUP_ROOT%\%%d\%TIMESTAMP%\tables_list.tmp" 2>nul
                    
                    if exist "%BACKUP_ROOT%\%%d\%TIMESTAMP%\tables_list.tmp" (
                        REM 移除标题行
                        more +2 "%BACKUP_ROOT%\%%d\%TIMESTAMP%\tables_list.tmp" > "%BACKUP_ROOT%\%%d\%TIMESTAMP%\tables_list.txt"
                        del "%BACKUP_ROOT%\%%d\%TIMESTAMP%\tables_list.tmp"
                        
                        REM 为每个表生成单独的备份文件
                        for /f %%t in ('type "%BACKUP_ROOT%\%%d\%TIMESTAMP%\tables_list.txt"') do (                            
                            echo 备份表 %%t...
                            %MYSQL_PATH% -u"%DB_USER%" -p"%DB_PASS%" "%%d" "%%t" > "%BACKUP_ROOT%\%%d\%TIMESTAMP%\%%t.sql" 2>nul
                        )
                        
                        REM 备份存储过程、函数和触发器
                        %MYSQL_PATH% -u"%DB_USER%" -p"%DB_PASS%" --no-data --routines --triggers "%%d" > "%BACKUP_ROOT%\%%d\%TIMESTAMP%\procedures_functions_triggers.sql" 2>nul
                        
                        del "%BACKUP_ROOT%\%%d\%TIMESTAMP%\tables_list.txt"
                        echo 数据库 %%d 备份完成!
                    ) else (
                        echo 警告：无法获取数据库 %%d 的表列表
                    )
                )
            )
        )
    )
)

endlocal

REM 记录备份日志（回到普通变量环境）
echo [%DATE% %TIME%] 按表分库备份完成 >> %BACKUP_ROOT%\backup.log
goto :eof