#!/bin/bash

export MYUSER='MYSQLUSERNAMETHATCANDOAMYSQLDUMP WITH LOCKS' # Your MySQL User
export MYPASS='ENTERYOURPASSWORDHERE' #MySQL User PAss
export BOXNAME="YOURBOXHOSTNAME"  #A unique way to identify your server
export CLIENTNAME="" #A unique way to identify your server group
export TEMPDIR="/home/backups/tmp"
export BACKUPDIR="/home/backups/mysql"
export DATABASELIST="/home/backups/tmp/dblist.txt"
export MAILTO="SOMEEMAILADDRESSYOUCANCHECK@.com"
export OFFSITEBACKUP="username@hostname.com"

# Make sure you uncomment/comment out the line starting with scp towards the
# end of the file. 

########### DO NOT TOUCH ANYTHING BELOW THIS LINE ############

export DATE=`date +%m%d%Y_%H_%M%Z`
export FILENAME2="$CLIENTNAME-$BOXNAME-mysql-$DATE.tar.gz"


echo "Saving $BOXNAME to " $FILENAME2 

mkdir $TEMPDIR

/usr/bin/mysql -e "show databases" -u $MYUSER --password=$MYPASS | sed s/^Database//g | sed s/\|//g | sed s/\ //g  > $DATABASELIST

for i in `cat $DATABASELIST`
do
  export FILENAME1="$i.sql"
  echo "Backing up $i to $FILENAME1 ...."
  /usr/bin/mysqldump -u $MYUSER --opt --password=$MYPASS $i > $TEMPDIR/$FILENAME1
done

cd $TEMPDIR
tar cjf $BACKUPDIR/$FILENAME2 *
cd ..
rm -rf $TEMPDIR

scp $BACKUPDIR/$FILENAME2 $OFFSITEBACKUP:~/mysql/$CLIENTNAME/$BOXNAME/$FILENAME2

echo "$DATE MySQL DB backup of $BOXNAME completed " | mail -s "Backup of $BOXNAME" $MAILTO
exit 0