MySQL Backup to FTP backup account and Email notification.

#! /bin/bash
########################################################################
#Author : Prajith #
#Date : 06/08/2011 #
#Contact : #
# #
########################################################################

SERVER=`hostname`
BACKDIR=
DATE=`date +’%m-%d-%Y’`
USER=username
PASS=password
DUMPALL=y
MAIL=y
EMAILAddress=””
SUBJECT=”backup on $SERVER ($DATE)”
FTP=y
FTPHOST=”ftp.server.com”
FTPUSER=”username”
FTPPASS=”pass”
FTPDIR=”backups”
RSYNC=y
RSYUSER=” “
RSYSERVER=” “
RSYDIR=” “
DELETE=y
DAYS=3

if [ -e $BACKDIR ];
then
echo Backups directory already exists
else
mkdir -p $BACKDIR
fi

if [ $DUMPALL = “y” ];
then
 for db in `echo ‘show databases;’ | mysql –user=$USER –password=$PASS |

grep -v ^Database$`;

do mysqldump –opt $db | gzip > $BACKDIR/$db-$DATE.sql.gz ;done

if [ $MAIL = “y” ]
then
BODY=”backup details”
ATTACH=`for file in $BACKDIR/$db-$DATE.sql.gz; do echo -n “-a ${file} “; done`

echo “$BODY” | mutt -s “$SUBJECT” $ATTACH $EMAILS

echo -e “Your backup details has been emailed to your email address \n”
fi

if [ $FTP = “y” ]
then
echo “connecting to ftp server”
cd $BACKDIR
A=`for file in *$DATE.sql.gz; do echo “put ${file}”; done`

ftp -nv <<EOF
open $FTPHOST
user $FTPUSER $FTPPASS
cd $FTPDIR
$A
quit
EOF
echo -e “FTP transfer complete \n”
fi
if [ $RSYNC = “y” ]; then
rsync -e ssh -avvz –progress –delete $BACKDIR/$db-$DATE.sql.gz $RSYUSER@$RSYSERVER:$RSYDIR
fi

if [ $DELETE = “y” ]
then
find $BACKDIR -name “*.sql.gz” -mtime $DAYS -exec rm {} \;

if [ $DAYS = “1” ]
then
echo “Yesterday’s backup has been deleted.”
else
echo “$DAYS backup hs been deleted.”
fi
fi
fi

NOTE: If you are using rsync option:

add ssh key for password less login.