Open Tech Notes

MySQL database backup script

#!/bin/bash

#=============================================================================
# MySQL Backup Script (file name: dbbackup)
# Ver. 0.2
# Author Jason Qi (email@yahoo.com)
#
# This script works under CentOS with Appache Friends.
# Before you start, please create folder
#   /opt/lampp\dbbackup
#     and
#   /opt/lampp/htdocs/cams2/dbbackup
# Put this file into /opt/lampp/bin and chmod +x dbbackup.
# Create dbbackupmsg file and put it into /opt/lampp/bin. The content inside
# this file is "Backup is done"
#  
# The last step is create a crontab
#   crontab -e
#   30 3 * * * /opt/lampp/bin/dbbackup
#=============================================================================
#
# Please change the params to fit the system needs.
#
#=============================================================================

USER="root";
PASSWORD="password";
DATABASE="dbname";
EMAIL="email@yahoo.com";
MYSQLDUMP="/opt/lampp/bin/mysqldump";

MPATH="/opt/lampp/dbbackup";
CPATH="/opt/lampp/htdocs/cams2/dbbackup";

MDATE=`/bin/date "+%Y-%m-%d-%Hh%Mm"`;
CDATE=`/bin/date "+%A"`;

MDUMP="$DATABASE-$MDATE";
CDUMP="$DATABASE-$CDATE";

EXT1=".sql";
EXT2=".tar";
EXT3=".gz";

#=============================================================================
# Change log
#=============================================================================
# VER 0.2 - 2007-04-28 Revise thid document by changing all "\" to "/" in pathname
#                      Add crontab command to this document
# VER 0.1 - 2007-04-15
#   Initial Release
#=============================================================================

$MYSQLDUMP –add-drop-table –user=$USER –password=$PASSWORD $DATABASE >> "$DUMP$EXT1";

/bin/tar -cf $MPATH/$DATABASE-$MDATE$EXT2 $DUMP$EXT1;
/bin/gzip -f $MPATH/$DATABASE-$MDATE$EXT2;

/bin/tar -cf $CPATH/$DATABASE-$CDATE$EXT2 $DUMP$EXT1;
/bin/gzip -f $CPATH/$DATABASE-$CDATE$EXT2;

/bin/rm -f $DUMP$EXT1;

cat "dbbackupmsg" | mail -s $MDATE-"Backup is done" $EMAIL;