MySQL Backup Batch Script With 7Zip And Email Alert

This is yet another MySQL Backup batch script with the following features:

• Dumps each database into separate folder and script.
• Allows to set database(s) excluded from backup.
• Each script is compressed (7zip) and encrypted with a password.
• Keeps the last n copies of backups.
• Email alert on backup errors.

It requires the following tools:

• MySQL bin utilities - mysql.exe and mysqldump.exe.
• 7zip compression tool.
• blat emailer tool.

Note: It can backup any networked MySQL databases (either Windows or Linux platforms), as long as the MySQL bin utilities can access them.

This script is easy to configure and highly customizable. You only need to change the values in the "Begin/End Settings" sections. Please refer the script header remarks for more information and other options.

How to use:

1. Create a batch file named as "weizh-mysql-backup.bat" with the following contents:

@ECHO OFF

REM =====================================================================================
REM Copyright 2011 Weizh Chang
REM 
REM This program is free software: you can redistribute it and/or modify
REM it under the terms of the GNU General Public License as published by
REM the Free Software Foundation, either version 3 of the License, or
REM (at your option) any later version.
REM
REM This program is distributed in the hope that it will be useful,
REM but WITHOUT ANY WARRANTY; without even the implied warranty of
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
REM GNU General Public License for more details.
REM
REM You should have received a copy of the GNU General Public License
REM along with this program.  If not, see <http://www.gnu.org/licenses/>.
REM =====================================================================================

REM =====================================================================================
REM Program    : Weizh MySQL Backup Batch Script
REM File       : weizh-mysql-backup.bat
REM Version    : 1.0.0.0
REM URL        : simpcode.blogspot.com
REM Description:
REM
REM Please change the settings in the Begin/End Settings sections below.
REM
REM This script requires:
REM - MySQL bin utilities - mysql.exe and mysqldump.exe.
REM - 7zip compression tool (www.7-zip.org).
REM - blat emailer tool (www.blat.net).
REM
REM Features:
REM - Dumps each database into separate folder and script.
REM - Allows to set database(s) excluded from backup.
REM - Each script is compressed (7zip) and encrypted with a password.
REM - Keeps the last n copies of backups.
REM - Email alert on backup errors.
REM =====================================================================================


@ECHO ON


REM ================ Begin MySQL Settings ====================

SET mySQLBinDir=C:\Program Files\MySQL\MySQL Server 5.0\bin\

SET host=192.168.100.2
SET uid=admin
SET pwd=adminPassw0rd
SET port=3306

SET backupDir=D:\MySQL_Backup\
SET backupPrefix=MySQL_3306_
SET backupCopy=3

REM ==== Set mysqldump switch (optional)
SET sqldumpSwitch=--single-transaction

REM ==== Set database excluded from backup (comma separated, without quotes and spaces)
SET excludeDb=information_schema,performance_schema,test

REM ==== Set the 7zip exe directory and password
SET zipDir=C:\Program Files\7-Zip\
SET zipPwd=yourZipPassw0rd

REM ================ End MySQL Settings ======================

REM ================ Begin Email Settings ====================

REM ==== Set all values to empty if not using email alert
SET emailServer=mail.example.com
SET emailPort=25
SET emailUid=admin@example.com
SET emailPwd=adminPassw0rd
SET sendFrom=admin@example.com
SET sendTry=5
SET subject=MySQL Backup Alert

REM ==== Set receiver email (comma separated, without quotes and spaces)
SET sendTo=alex@example.com

REM ==== Set the blat exe directory
SET emailDir=C:\blat\

REM ================ End Email Settings ======================


REM ================ Begin Backup ============================

ECHO Performing backup...

REM ==== Set date and time
For /F "tokens=2-4 delims=/ " %%a IN ('DATE /t') DO (SET mydate=%%c-%%a-%%b)
SET mytime=%TIME:~0,8%
SET mytime=%mytime: =0%

REM ==== Set command variable
SET doShowDb="%mySQLBinDir%mysql" -B -N -e "SHOW DATABASES" -h %host% -u %uid% -p%pwd% -P %port%
SET doDump="%mySQLBinDir%mysqldump" %sqldumpSwitch% -h %host% -u %uid% -p%pwd% -P %port%
SET doZip="%zipDir%7z.exe" a -t7z -mx9 -mhe -p%zipPwd%
SET doZipTest="%zipDir%7z.exe" -r -p%zipPwd% t

REM ==== Loop and backup each database
SETLOCAL ENABLEDELAYEDEXPANSION

SET errorDbTemp=

REM ==== Test the connection
!doShowDb!
IF !ERRORLEVEL! NEQ 0 (
    SET errorDbTemp=MySQL Connection Error
    GOTO EndLoop
)

FOR /F "tokens=*" %%G IN ('"!doShowDb!"') DO (
    
    SET exclude=0
    SET errorFound=0

    IF NOT "%excludeDb%"=="" (
        FOR %%S IN (%excludeDb%) DO (
            IF /I "%%S"=="%%G" SET exclude=1
        )
    )

    IF !exclude!==0 (
        REM ==== Set backup directory and file for this db
        IF NOT EXIST "%backupDir%%%G" MD "%backupDir%%%G"
        For /F "tokens=2-4 delims=/ " %%a IN ('DATE /t') DO (SET backupDate=%%c-%%a-%%b)
        SET backupTime=!TIME:~0,8!
        SET backupTime=!backupTime: =0!
        SET backupFile=%backupDir%%%G\%backupPrefix%%%G_!backupDate:-=!_!backupTime::=!

        REM ==== Perform MySQLDump
        !doDump! --databases %%G > "!backupFile!.sql"

        IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)

        IF NOT EXIST "!backupFile!.sql" (
            SET errorFound=1
        ) ELSE (
            REM ==== Zip then delete the sql file
            !doZip! "!backupFile!.7z" "!backupFile!.sql"

            IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)

            REM ==== Test the zip integrity
            !doZipTest! "!backupFile!.7z"

            IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)
 
            DEL /Q "!backupFile!.sql"
        )

        IF !errorFound!==1 (
            REM ==== If errors, clean up and append the db name to a temp variable
            IF EXIST "!backupFile!.*" DEL /Q "!backupFile!.*"
            SET errorDbTemp=!errorDbTemp!%%G;
        ) ELSE (
            REM ==== Delete old zip copies, sort by file name descending
            IF EXIST "%backupDir%%%G\%backupPrefix%%%G_*.7z" (
                SET zipCount=0

                FOR /F "tokens=*" %%U in ('DIR /A:-D-H /O:-N /B "%backupDir%%%G\%backupPrefix%%%G_*.7z"') DO (
                    SET /A zipCount+=1
                    IF !zipCount! GTR %backupCopy% DEL /Q "%backupDir%%%G\%%U"
                )
            )
        )
    )
)

:EndLoop

ENDLOCAL & SET errorDb=%errorDbTemp%


IF NOT "%errorDb%"=="" GOTO SendError

GOTO Finish

REM ================ End Backup ==============================


REM ================ Begin Send Error Email Alert ============
:SendError

IF "%emailServer%"=="" (
    ECHO Backup error on %errorDb%
    GOTO Finish
)

ECHO Sending error email alert

REM ==== Set sendTo with surrounding quotes
SET sendTo="%sendTo%"

REM ==== Set email temp file
SET tmpErrorFile="%~dp0mysql_backup_error-%RANDOM%.tmp"

(
ECHO Dear Database Admin,
ECHO.
ECHO There were errors while performing the MySQL Backup on the following databases:
ECHO.
ECHO Date: %mydate%      Time: %mytime%
ECHO Host: %host%      Port: %port%
ECHO.
ECHO -----------------------------------------------------------------
ECHO %errorDb%
ECHO -----------------------------------------------------------------
ECHO.
ECHO.
ECHO Regards,
ECHO MySQL Server Admin
) >%tmpErrorFile%


REM ==== Send error email alert
"%emailDir%blat.exe" %tmpErrorFile% -server %emailServer%:%emailPort% -f %emailUid% -u %emailUid% -pw %emailPwd% -from %sendFrom% -to %sendTo% -subject "%subject%" -try %sendTry%

REM ==== Cleanup
IF EXIST %tmpErrorFile% DEL /Q %tmpErrorFile%

REM ================ End Send Error Email Alert ==============


:Finish
ECHO Backup process completed

2. Set values in the Begin/End MySQL Settings and Begin/End Email Settings sections. Put this file into any folder you like.

3. You may use the Windows Task Scheduler to run this script routinely or at anytime you prefer.

If you find this post helpful, would you buy me a coffee?