mysql-backup.bat

@echo off
REM ============================================================================
REM Purpose: Automated MySQL database backup for Plesk servers
REM Platform: Windows
REM Features:
REM   - Backs up all MySQL databases to individual SQL dump files
REM   - Excludes system databases (information_schema, performance_schema, phpmyadmin)
REM   - Automatically removes orphaned backup files for deleted databases
REM   - Uses Plesk MySQL utilities with proper path handling for spaces/parentheses
REM   - Enhanced error handling and detailed logging
REM   - Success/failure tracking with exit codes
REM Usage: mysql-backup.bat
REM Environment Variables:
REM   plesk_dir - Plesk installation directory (required, auto-set by Plesk)
REM Security: Replace <password_for_mysql> with actual MySQL admin password before running
REM
REM NOTE: This script backs up CUSTOMER databases on the standard MySQL instance (port 3306).
REM       Plesk's own internal databases (psa, etc.) are on a separate instance (port 8306)
REM       and are intentionally excluded from this backup.
REM ============================================================================

setlocal enabledelayedexpansion

REM Validate environment first
if not defined plesk_dir (
    echo ERROR: plesk_dir environment variable is not set
    echo Please ensure Plesk is properly installed and environment is configured.
    exit /b 1
)

REM Configuration - Use delayed expansion to handle paths with parentheses
set "PLESK_DIR=%plesk_dir%"
set "BACKUP_DIR=!PLESK_DIR!\Databases\MySQL\backup"
set "DB_LIST=!BACKUP_DIR!\db_list.txt"
set "MYSQL_BIN=!PLESK_DIR!\MySQL\bin\mysql.exe"
set "MYSQLDUMP_BIN=!PLESK_DIR!\MySQL\bin\mysqldump.exe"
REM NOTE: Port 3306 is the customer-facing MySQL instance (where hosted databases live).
REM       Plesk's own system database (psa, etc.) runs on port 8306 and is NOT backed up here.
set "MYSQL_USER=admin"
set "MYSQL_PASSWORD=<password_for_mysql>"
set "MYSQL_PORT=3306"

REM Verify MySQL binaries exist
if not exist "!MYSQL_BIN!" (
    echo ERROR: MySQL client not found at: !MYSQL_BIN!
    exit /b 1
)

if not exist "!MYSQLDUMP_BIN!" (
    echo ERROR: mysqldump utility not found at: !MYSQLDUMP_BIN!
    exit /b 1
)

REM Create backup directory if it doesn't exist
if not exist "!BACKUP_DIR!\" (
    echo Creating backup directory: !BACKUP_DIR!
    mkdir "!BACKUP_DIR!"
    if !errorlevel! neq 0 (
        echo ERROR: Failed to create backup directory
        exit /b 1
    )
)

echo ============================================================================
echo MySQL Database Backup - Starting
echo ============================================================================
echo Backup directory: !BACKUP_DIR!
echo.

REM Get list of all databases
echo Retrieving list of databases...
"!MYSQL_BIN!" -u%MYSQL_USER% -p%MYSQL_PASSWORD% -P%MYSQL_PORT% -Ne"SHOW DATABASES" > "!DB_LIST!" 2>&1

if !errorlevel! neq 0 (
    echo ERROR: Failed to retrieve database list. Please check MySQL credentials.
    echo Hint: Replace ^<password_for_mysql^> with your actual MySQL admin password.
    if exist "!DB_LIST!" del /q "!DB_LIST!"
    exit /b 1
)

REM Count databases to backup
set "DB_COUNT=0"
set "SUCCESS_COUNT=0"
set "FAILED_COUNT=0"
set "CLEANUP_COUNT=0"

REM Change to backup directory
cd /d "!BACKUP_DIR!"
if !errorlevel! neq 0 (
    echo ERROR: Failed to change to backup directory
    exit /b 1
)

echo Starting database backup...
echo.

REM Clean up backup files for databases that no longer exist
echo Checking for orphaned backup files...
for %%f in ("!BACKUP_DIR!\*.sql") do (
    set "BACKUP_FILE=%%~nf"
    set "FOUND=0"
    
    REM Check if this database still exists in the database list
    for /F "usebackq tokens=*" %%j in ("!DB_LIST!") do (
        set "CURRENT_DB=%%j"
        if /i "!BACKUP_FILE!"=="!CURRENT_DB!" set "FOUND=1"
    )
    
    REM If database not found in list, delete the backup file
    if !FOUND! equ 0 (
        if /i not "!BACKUP_FILE!"=="db_list" (
            echo Removing orphaned backup: !BACKUP_FILE!.sql (database no longer exists)
            del /q "%%f"
            set /a CLEANUP_COUNT+=1
        )
    )
)

if %CLEANUP_COUNT% equ 0 (
    echo No orphaned backup files found
) else (
    echo Cleaned up %CLEANUP_COUNT% orphaned backup file(s)
)
echo.

REM Loop through each database and create backup
for /F "usebackq tokens=*" %%j in ("!DB_LIST!") do (
    set "DB_NAME=%%j"
    
    REM Skip system databases.
    REM 'mysql' and 'sys' are system schemas on the customer MySQL instance (port 3306).
    REM Plesk's own system databases (psa, etc.) live on a separate instance (port 8306)
    REM and are never visible here.
    if /i "!DB_NAME!"=="information_schema" (
        echo Skipping system database: !DB_NAME!
    ) else if /i "!DB_NAME!"=="performance_schema" (
        echo Skipping system database: !DB_NAME!
    ) else if /i "!DB_NAME!"=="phpmyadmin" (
        echo Skipping system database: !DB_NAME!
    ) else if /i "!DB_NAME!"=="mysql" (
        echo Skipping system database: !DB_NAME!
    ) else if /i "!DB_NAME!"=="sys" (
        echo Skipping system database: !DB_NAME!
    ) else (
        set /a DB_COUNT+=1
        echo [!DB_COUNT!] Backing up database: !DB_NAME!
        
        REM Delete old backup if it exists
        if exist "!DB_NAME!.sql" del /q "!DB_NAME!.sql"
        
        REM Create backup with routines and databases flag
        "!MYSQLDUMP_BIN!" -u%MYSQL_USER% -p%MYSQL_PASSWORD% -P%MYSQL_PORT% --routines --databases !DB_NAME! > "!DB_NAME!.sql" 2>&1
        
        if !errorlevel! neq 0 (
            echo     ERROR: Failed to backup database: !DB_NAME!
            set /a FAILED_COUNT+=1
        ) else (
            echo     Successfully backed up: !DB_NAME!
            set /a SUCCESS_COUNT+=1
        )
    )
)

REM Clean up database list file
if exist "!DB_LIST!" del /q "!DB_LIST!"

echo.
echo ============================================================================
echo MySQL Database Backup - Completed
echo ============================================================================
echo Total databases processed: !DB_COUNT!
echo Successful backups: !SUCCESS_COUNT!
echo Failed backups: !FAILED_COUNT!
echo Orphaned backups cleaned: !CLEANUP_COUNT!
echo Backup location: !BACKUP_DIR!
echo ============================================================================

REM Exit with error code if any backups failed
if !FAILED_COUNT! gtr 0 (
    exit /b 1
)

exit /b 0