MySQL-Auto-Backup-Script/mysql_multiServerbackup.bat at main · BUDEGlobalEnterprise/MySQL-Auto-Backup-Script · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
@echo off
setlocal EnableDelayedExpansion

:: ✅ Get script's directory
set "SCRIPT_DIR=%~dp0"
cd /d "%SCRIPT_DIR%"

:: === Load Main .env ===
if not exist "%SCRIPT_DIR%.env" (
    echo [ERROR] .env file not found in: %SCRIPT_DIR%
    exit /b 1
)

for /f "tokens=1* delims==" %%a in ('type "%SCRIPT_DIR%.env"') do (
    set "%%a=%%b"
)

:: === Print loaded config for debug ===
echo -----------------------
echo [DEBUG] Main Config Loaded:
echo BACKUP_ROOT=%BACKUP_ROOT%
echo MYSQLDUMP_PATH=%MYSQLDUMP_PATH%
echo ZIP_EXE=%ZIP_EXE%
echo RETENTION_DAYS=%RETENTION_DAYS%
echo Servers to backup: %SERVERS%
echo -----------------------

:: === Check Required Paths ===
if not exist "%MYSQLDUMP_PATH%" (
    echo [ERROR] mysqldump.exe not found at: "%MYSQLDUMP_PATH%"
    exit /b 1
)

if not exist "%ZIP_EXE%" (
    echo [WARNING] 7-Zip not found at: "%ZIP_EXE%"
    echo Compression will be skipped.
)

:: === Get Timestamp ===
for /f "delims=" %%i in ('wmic os get localdatetime ^| findstr /r /c:"[0-9]"') do set ts=%%i
set "DATESTAMP=!ts:~0,4!-!ts:~4,2!-!ts:~6,2!"
set "TIMESTAMP=!DATESTAMP!_!ts:~8,2!!ts:~10,2!"

:: === Begin Backup Loop for Each Server ===
for %%S in (%SERVERS%) do (
    set "SERVER_NAME=%%S"
    echo [INFO] Loading configuration for server: !SERVER_NAME!...

    :: Load specific server config
    if not exist "!SCRIPT_DIR!servers\!SERVER_NAME!.env" (
        echo [ERROR] Server config file not found: "!SCRIPT_DIR!servers\!SERVER_NAME!.env"
        continue
    )

    :: Initialize variables to avoid carrying over from previous loop
    set "DB_HOST="
    set "DB_USER="
    set "DB_PASSWORD="
    set "DATABASES="

    :: Correctly read server config variables
    for /f "tokens=1* delims==" %%a in ('type "!SCRIPT_DIR!servers\!SERVER_NAME!.env"') do (
        if "%%a"=="DB_HOST" ( set "DB_HOST=%%b" )
        if "%%a"=="DB_USER" ( set "DB_USER=%%b" )
        if "%%a"=="DB_PASSWORD" ( set "DB_PASSWORD=%%b" )
        if "%%a"=="DATABASES" ( set "DATABASES=%%b" )
    )
    :: Strip quotes from DATABASES variable
    set "DATABASES=!DATABASES:"=!"

    echo -----------------------
    echo [DEBUG] Server Config Loaded:
    echo DB_HOST=!DB_HOST!
    echo DB_USER=!DB_USER!
    echo DATABASES=!DATABASES!
    echo -----------------------

    :: === Begin Database Backup Loop ===
    for %%D in (!DATABASES!) do (
        set "DB=%%D"
        set "DB_DIR=!BACKUP_ROOT!\!SERVER_NAME!\!DB!\!DATESTAMP!"
        set "SQL_FILE=!DB_DIR!\!DB!_!TIMESTAMP!.sql"
        set "ZIP_FILE=!SQL_FILE!.zip"

        echo [INFO] Backing up !DB! from !SERVER_NAME!...

        if not exist "!DB_DIR!" mkdir "!DB_DIR!"

        :: ✅ Always skip column statistics for cross-version compatibility
        "%MYSQLDUMP_PATH%" -h !DB_HOST! -u !DB_USER! -p!DB_PASSWORD! --single-transaction --skip-column-statistics !DB! > "!SQL_FILE!"

        if !ERRORLEVEL! NEQ 0 (
            echo [ERROR] Backup failed for !DB! on !SERVER_NAME!
            echo [DEBUG] Command failed: "%MYSQLDUMP_PATH%" -h !DB_HOST! -u !DB_USER! -p!DB_PASSWORD! --single-transaction --skip-column-statistics !DB!
        ) else (
            echo [SUCCESS] Backup created: "!SQL_FILE!"

            if exist "%ZIP_EXE%" (
                "%ZIP_EXE%" a -tzip "!ZIP_FILE!" "!SQL_FILE!" >nul
                if !ERRORLEVEL! EQU 0 (
                    del "!SQL_FILE!"
                    echo [SUCCESS] Compressed to: "!ZIP_FILE!"
                ) else (
                    echo [ERROR] Compression failed for !DB! on !SERVER_NAME!.
                )
            )
        )
    )
)

:: === Cleanup ===
echo [INFO] Cleaning old backups...

for %%S in (%SERVERS%) do (
    set "SERVER_NAME=%%S"

    :: Re-read the database list for cleanup
    set "DATABASES="
    for /f "tokens=1* delims==" %%a in ('type "!SCRIPT_DIR!servers\!SERVER_NAME!.env"') do (
        if "%%a"=="DATABASES" ( set "DATABASES=%%b" )
    )
    set "DATABASES=!DATABASES:"=!"

    for %%D in (!DATABASES!) do (
        if exist "%BACKUP_ROOT%\!SERVER_NAME!\%%D" (
            forfiles /p "%BACKUP_ROOT%\!SERVER_NAME!\%%D" /d -%RETENTION_DAYS% /c "cmd /c echo Deleting: @path && rd /s /q @path"
        )
    )
)

echo [INFO] Backup completed.
endlocal