Saturday, December 10, 2011

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:

REM =====================================================================================
REM Copyright 2011 Weizh Chang
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 This program is distributed in the hope that it will be useful,
REM but WITHOUT ANY WARRANTY; without even the implied warranty of
REM GNU General Public License for more details.
REM You should have received a copy of the GNU General Public License
REM along with this program.  If not, see <>.
REM =====================================================================================

REM =====================================================================================
REM Program    : Weizh MySQL Backup Batch Script
REM File       : weizh-mysql-backup.bat
REM Version    :
REM URL        :
REM Description:
REM Please change the settings in the Begin/End Settings sections below.
REM This script requires:
REM - MySQL bin utilities - mysql.exe and mysqldump.exe.
REM - 7zip compression tool (
REM - blat emailer tool (
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 =====================================================================================


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

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

SET host=
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 emailPort=25
SET emailPwd=adminPassw0rd
SET sendTry=5
SET subject=MySQL Backup Alert

REM ==== Set receiver email (comma separated, without quotes and spaces)

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

SET errorDbTemp=

REM ==== Test the connection
    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"


ENDLOCAL & SET errorDb=%errorDbTemp%

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

GOTO Finish

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

REM ================ Begin Send Error Email Alert ============

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 There were errors while performing the MySQL Backup on the following databases:
ECHO Date: %mydate%      Time: %mytime%
ECHO Host: %host%      Port: %port%
ECHO -----------------------------------------------------------------
ECHO %errorDb%
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 ==============

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?