MySQL 备份脚本 for Windows

:::::::::::::::::::::::::::::::::::::::::::::::::::
::
::  MySQL Baker 4 Windows
::
::  DATABASES=以空格分隔的库名或 --all-databases
::
:::::::::::::::::::::::::::::::::::::::::::::::::::

@echo off
setlocal enabledelayedexpansion

for /f "tokens=1-3 delims=-/ " %%1 in ("%date%") do set DATE_STR=%%1%%2%%3
set FILE_DATE=%DATE_STR: =0%

set FILE_PWD=123456
REM set DATABASES=mysql   sys  
set DATABASES=--all-databases
set DB_HOST=localhost
set DB_PORT=3306
set DB_USER=root
set DB_PWD=123456
set P7ZIP=C:\Program Files\7-Zip\7z.exe
set MYSQL_DIR=C:\Program Files\mysql-8.0.20
set LOG_FILE=baker_%FILE_DATE%.log

echo [%date:~0,10% %time%] start >> %LOG_FILE%

:: 检测 mysql 帐号是否可用
"%MYSQL_DIR%\bin\mysqlshow.exe" -u %DB_USER% --password=%DB_PWD%
if not "0"=="%errorlevel%" (
    echo [%date:~0,10% %time%] [error] db_pwd or db_user invalid. >> %LOG_FILE%
    goto ERROR
) else (
    echo [%date:~0,10% %time%] account valid. >> %LOG_FILE%
)

:: 导出数据库
if "%DATABASES%"=="--all-databases" (
    set SQL_FILE=all_%FILE_DATE%.sql
    set SQL_FILES=!SQL_FILE!
    "%MYSQL_DIR%\bin\mysqldump.exe" -h%DB_HOST% -P%DB_PORT% -u%DB_USER% -p%DB_PWD% --routines --single-transaction %DATABASES% --log-error=%LOG_FILE% > !SQL_FILE!
    if not "0"=="!errorlevel!" (
        echo [%date:~0,10% %time%] [error] dump failed: [%DATABASES%] >> %LOG_FILE%
        del /q !SQL_FILES!
        goto ERROR
    ) else (
        echo [%date:~0,10% %time%] dumped: [%DATABASES%] >> %LOG_FILE%
    )
) else (
    set SQL_FILES=
    for %%D in (%DATABASES%) do (
        "%MYSQL_DIR%\bin\mysqldump.exe" -h%DB_HOST% -P%DB_PORT% -u%DB_USER% -p%DB_PWD% --routines --single-transaction %%D --log-error=%LOG_FILE% > db_%%D_%FILE_DATE%.sql
        if not "0"=="!errorlevel!" (
            echo [%date:~0,10% %time%] [error] dump failed: [%%D] >> %LOG_FILE%
            del /q db_%%D_%FILE_DATE%.sql
            goto ERROR
        ) else (
            set SQL_FILES=!SQL_FILES! db_%%D_%FILE_DATE%.sql
            echo [%date:~0,10% %time%] dumped: [%%D] >> %LOG_FILE%
        )
    )
)

::"%MYSQL_DIR%\bin\mysqldump.exe" -h%DB_HOST% -P%DB_PORT% -u%DB_USER% -p%DB_PWD% --routines --single-transaction --all-databases > DB_%FILE_DATE%.sql
::"%MYSQL_DIR%\bin\mysqldump.exe" -h%DB_HOST% -P%DB_PORT% -u%DB_USER% -p%DB_PWD% --routines --single-transaction --databases db1 db2 db3 > DB_%FILE_DATE%.sql
::"%MYSQL_DIR%\bin\mysqldump.exe" -h%DB_HOST% -P%DB_PORT% -u%DB_USER% -p%DB_PWD% --routines --single-transaction db1 > DB_%FILE_DATE%.sql

:: 压缩导出的 SQL 文件
:COMPRESS
set BAK_NAME=db_%DATABASES: =_%_%FILE_DATE%.7z
"%P7ZIP%" a -p%FILE_PWD% %BAK_NAME% %SQL_FILES%
if not "0"=="%errorlevel%" (
    echo [%date:~0,10% %time%] [error] sql compressed failed: [%SQL_FILES%] >> %LOG_FILE%
    goto ERROR
) else (
    echo [%date:~0,10% %time%] db file compressed: [%BAK_NAME%]. >> %LOG_FILE%
    del /q %SQL_FILES%
)

goto END

:: 设置导出状态
:ERROR
set SUCCESSED=failed

:END
if not "%SUCCESSED%"=="failed" set SUCCESSED=success
echo [%date:~0,10% %time%] done[%SUCCESSED%]. >> %LOG_FILE%
echo. >> %LOG_FILE%