So – you can go to any one of 100,000 sites that will tell you how to do an automated MySQL database dump with some combination of mysqldump and crond, etc. But, I was recently faced with the question, “what happens if the dump file is corrupt? can we validate it before we pack it away with our backup service?” So I came up with this little shell script.

It does the following:

1. Creates a backup of the selected db using mysqldump
2. Generates an MD5 checksum of the backup file (written to a separate file)
3. Attempts to restore the dumped file into a dummy test database
4. If errors are encountered, it grabs the error and sends an email to the designated address
5. If no errors are encountered, wraps the .sql and .sql.md5 in a timestamped, gzipped, tarball – then deletes the originals

Download the file here

#!/bin/sh
#######################################################
# LICENSE:
# (c) 2007 Brian Bell (GNU LGPL V2.1) You may
# view the full copyright text at:
# http://www.opensource.org/licenses/lgpl-license.html
#
# DESCRIPTION:
# A simple BASH script to do automate MySQL database
# backup; includes testing and MD5 hash creation.
# Emails designated address on failure.
#######################################################

## CONFIGURATION VARS
MYSQL_NAME=
MYSQL_HOST=
MYSQL_USER=
MYSQL_PASS=
MYSQL_TESTDB=
BACKUP_PATH=/path/to/backup/dir # No trailing slash
MAIL_SUBJECT=”TESTING MySQL Backup Error”
MAIL_TO=”monitor@yourdomain.com”

#######################################################
## We need to create a unique timestamp for use on the filename
TIMESTAMP=`date +%Y_%m_%d`

## Generate the base part of the filename to use in backing up
BACKUP_FILE_BASE=”${MYSQL_NAME}_${TIMESTAMP}”

echo “Backing up $MYSQL_NAME…”
/usr/bin/mysqldump –opt -c -e -Q -h $MYSQL_HOST -u $MYSQL_USER –password=$MYSQL_PASS \
–add-drop-table $MYSQL_NAME > $BACKUP_PATH/$BACKUP_FILE_BASE.sql

## MD5 the backup file
/usr/bin/md5sum -t $BACKUP_PATH/$BACKUP_FILE_BASE.sql > $BACKUP_PATH/$BACKUP_FILE_BASE.sql.md5

## Try to import the backup sql into a test db
MYSQL_RESULT=`/usr/bin/mysql -h ${MYSQL_HOST} -u ${MYSQL_USER} –password=${MYSQL_PASS} ${MYSQL_TESTDB} < \
${BACKUP_PATH}/${BACKUP_FILE_BASE}.sql >

${BACKUP_PATH}/mysql_test.log`

if [[ “$MYSQL_RESULT” =~ “ERROR” ]]
then
echo “The following error was encountered at `date` ” > ${BACKUP_PATH}/error_email.log
echo “” >> ${BACKUP_PATH}/error_email.log
echo “#####################################################################” >> ${BACKUP_PATH}/error_email.log
echo $MYSQL_RESULT >> ${BACKUP_PATH}/error_email.log

echo “SENDING ERROR EMAIL TO: ${MAIL_TO}”
/bin/mail -s “$MAIL_SUBJECT” “$MAIL_TO” < ${BACKUP_PATH}/error_email.log
else
tar czpf $BACKUP_PATH/$TIMESTAMP_$BACKUP_FILE_BASE.sql.tar.gz $BACKUP_PATH/$BACKUP_FILE_BASE.sql* –remove-files
fi