mysql_multibackup.bat

@echo off
setlocal EnableDelayedExpansion

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

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

:: Correctly load environment variables and strip quotes
for /f "tokens=*" %%a in ('type "%SCRIPT_DIR%.env"') do (
    set "line=%%a"
    if not "!line!"=="" (
        for /f "tokens=1* delims==" %%b in ("!line!") do (
            set "key=%%b"
            set "value=%%c"
            set "value=!value:"=!"
            set "!key!=!value!"
        )
    )
)

:: === Print loaded config for debug ===
echo -----------------------
echo [DEBUG] Config Loaded:
echo MYSQL_USER=%MYSQL_USER%
echo MYSQL_PASSWORD=%MYSQL_PASSWORD%
echo MYSQLDUMP_PATH=%MYSQLDUMP_PATH%
echo BACKUP_ROOT=%BACKUP_ROOT%
echo DATABASES=%DATABASES%
echo ZIP_EXE=%ZIP_EXE%
echo RETENTION_DAYS=%RETENTION_DAYS%
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 ===
for %%D in (%DATABASES%) do (
    set "DB=%%D"
    set "DB_DIR=%BACKUP_ROOT%\!DB!\!DATESTAMP!"
    set "SQL_FILE=!DB_DIR!\!DB!_!TIMESTAMP!.sql"
    set "ZIP_FILE=!SQL_FILE!.zip"

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

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

    "%MYSQLDUMP_PATH%" -u "%MYSQL_USER%" -p"%MYSQL_PASSWORD%" --single-transaction !DB! > "!SQL_FILE!" 2>nul

    if !ERRORLEVEL! NEQ 0 (
        echo [ERROR] Backup failed for !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.
            )
        )
    )
)

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

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

echo [INFO] Backup completed.
endlocal