MySQL Backup Script

This script is used on Windows 2003 (MySQL 5.0). It uses mysqldump to create a *.sql backup then sends a notification e-mail.It works online.Its Pre-Requisites are BLAT & GZIP, for e-mail and compression.

Script:

@ECHO OFF
CLS
Title MySQL Backup
ECHO ================================================
ECHO MySQL Backup Script
ECHO Description: This scrip does the following:
ECHO - Backs up the specified database.
ECHO - Compresses the backup.
ECHO - Backup dump @ d\:backups.
ECHO - Writes to a log file.
ECHO - Sends a notification e-mail.
ECHO Prerequisites:BLAT, GZIP, MYSQLDUMP
ECHO By: MySQL DBA
ECHO ================================================

@REM Date Configuration
for /f "tokens=1-4 delims=/ " %%a in ('date/t') do (
set dw=%%aset mm=%%b
set dd=%%c
set yy=%%d
)

@REM Backup Configuration
SET servername=server1
SET database=DB1
SET backupdir=D:\BACKUPS
SET workdir=c:\TEMP
SET mysqldir="C:\Program Files\MySQL\MySQL Server 5.0\bin"
SET utils=D:\UTILS
SET mysqlpassword=passwd
SET mysqluser=root
SET logdir=D:\log\mysql
SET filename=%servername%.%database%.%yy%%mm%%dd%.sql

@REM Mail Configuration
SET mailsmtp="192.168.2.11"
SET mail=%utils%\Blat\full\blat.exe
SET mailto=Notification@email.com
SET mailfrom="MySQL Backup Service on %servername% "
SET mailsubject="MySQL Backup of %database%"
SET start=%date% - %time%: Database Backup of %database% Started

@REM Start Logging
ECHO %start%
ECHO %start% >> %logdir%\mysqlbackup.log

@REM dump database. This is all one line.
mysqldump -u %mysqluser% -p%mysqlpassword% %database% > %workdir%\%filename%
if not exist %workdir%\%filename% goto FAIL_DUMP

@REM Zip up database
%utils%\gzip.exe -f %workdir%\%filename%
if not exist %workdir%\%filename%.gz goto FAIL_GZIP

@REM Move to random file name
move %workdir%\%filename%.gz %backupdir%\%filename%.gz
if not exist %backupdir%\%filename%.gz goto FAIL_MOVE

@REM All is well
GOTO SUCCESS

:FAIL_DUMPSET
message=%date% - %time%: Database Dump of %database% Failed
GOTO END
:FAIL_GZIPSET
message=%date% - %time%: Backup Compression of %database% Failed
GOTO END
:FAIL_MOVESET message=%date% - %time%: Backup Move of %database% Failed
GOTO END
:SUCCESSSET message=%date% - %time%: Backup of %database% Completed Succesfully
GOTO END

:END
ECHO %message%
ECHO %message% >> %logdir%\mysqlbackup.log
ECHO ===============================================================
ECHO Sending Notification E-Mail ...
ECHO To: %mailto%
ECHO From: %mailfrom%
ECHO Subject: %mailsubject%%mail% -q -server %mailsmtp% -f %mailfrom% -to %mailto% -subject %mailsubject% -body "%message%"
ECHO ===============================================================