MySQL Backup and Rsync Script

#!/bin/bash
#unset PATH
 
# USER VARIABLES
MYSQLUSER=root                         # The mysql user
MYSQLPWD='[INSERT YOUR PASSWORD]'                    # The mysql root password -- Note, might not be needed if you are using .my.cnf, but you'll need to change the script if that's the case.
MYSQLHOST=localhost                    # This should stay localhost
MYSQLBACKUPDIRTAR="backups/mysql/$HOSTNAME"            # The leading / is to be removed for TAR, otherwise it sends an annoying email each time it compresses the files.
MYSQLBACKUPDIR=/${MYSQLBACKUPDIRTAR}            # A folder where the backupped databases will be put.
EMAILADDRESS='[INSERT YOUR EMAIL ADDRESS]'                # The email address to send the backed up database to. e.g [email protected]
EMAILBODY="Attached should be the latest zipped backup of the MySQL database on $HOSTNAME"   # Where the body of the main email comes from, or the content itself.
RSYNCHOST='[[email protected]]' # This should be set to however you'd SSH into the host you want to RSYNC to. e.g [email protected]
echo "Saving compressed MySQL database backup to $MYSQLBACKUPDIR, please wait"
 
#Other Variables
CURRENTDATE=$(date '+%d-%m-%Y') #The date (e.g 23-02-2008) for use in the zip file name
MYSQLBACKUPFILENAME="MySQL_${HOSTNAME}_[$CURRENTDATE].sql" # Should look something like 'MySQL_office.anat_[23-10-2010].sql'
 
##                                                      ##
##      --       DO NOT EDIT BETWEEN THIS HERE     --   ##
##                                                      ##
 
# CREATE MYSQL BACKUP
# Create backup dir (in case it doesn't exist yet) and remove old .gz files to make way for new ones.
mkdir -p  $MYSQLBACKUPDIR
rm -v $MYSQLBACKUPDIR/*.gz
 
#Dump individual sql files... The important bit!
for i in $(echo 'SHOW DATABASES;' | mysql -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST | grep -v '^Database$' ); do
  mysqldump                                                    \
  -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST                         \
  -Q -c -C --add-drop-table --add-locks --quick --lock-tables   \
  $i > $MYSQLBACKUPDIR/$i.sql
 echo -n "."
done;
echo ""
 
##                                         ##
##      --       AND THIS HERE     --      ##
##                                         ##
## Hint, you can edit below this line      ##
 
echo "Done, dumping files to $MYSQLBACKUPDIR"
 
# This will Tarball (compress) the sql files up individually as they should get rsnapshotted, so if they haven't changed then you don't have to create a new file for them
# Note, the change directory and -C in the Tar command is to stop it from generating an annoying error saying that it's removing the leading / from the tar names.
 
cd $MYSQLBACKUPDIR/
#find * -name '*.sql' -exec tar -vcf $MYSQLBACKUPDIR/{}.tar -C / $MYSQLBACKUPDIRTAR/{} \;
# another way is to run :: find $MYSQLBACKUPDIR/ -name '*.sql' -exec tar -zvcf {}.tar.gz -C / {} \;
# The above would  tar and gzip at the same time, but I'm not sure if it's in an rsyncable way
 
## Compress the SQL files ##
find $MYSQLBACKUPDIR/ -name '*.sql' -exec gzip --best --rsyncable {} \; 
 
TARLIST=''
###  CREATE A MASTER .TAR FILE OF ALL GZIPS ####
cd $MYSQLBACKUPDIR/
# This will create a listing of all the .tar.gz files in the directory
for i in $( find * -name '*.gz' );
do
TARLIST="$TARLIST $MYSQLBACKUPDIRTAR/$i"
done;
 
# Now do that actual tarballing of the files we've listed above #
tar -vcf $MYSQLBACKUPDIR/$MYSQLBACKUPFILENAME.tar -C / $TARLIST
echo "Compressed $MYSQLBACKUPDIR/$MYSQLBACKUPFILENAME.tar"
echo
 
#### Rsync push the backed up files somewhere ####
rsync -avh $MYSQLBACKUPDIR/*.gz -e ssh $RSYNCHOST:/backup/mysql/$HOSTNAME/
## Note, you might need to create the directory on the target server, or maybe change to something like /backup/mysql/$HOSTNAME and hopefully the parent directory already exists.
echo "---"
echo "Rsync'd daily backup"
 
### Mail the zipped file as an attachment... If your database is too big you'll have to replace this with rsync, or FTP backup ##
echo $EMAILBODY | mutt -s "[$HOSTNAME] DailyMysql backup" -a $MYSQLBACKUPDIR/$MYSQLBACKUPFILENAME.tar -- $EMAILADDRESS
echo "emailed the zipped file"
 
### All databases in one file, Emergency Backup in case they all die ###
# mysqldump --add-drop-database --all-databases > $MYSQLBACKUPDIR/allMySQL-current.sql
# gzip --rsyncable $MYSQLBACKUPDIR/allMySQL-current.sql
# echo "Finished the all in one backup $MYSQLBACKUPDIR/allMySQL-current.sql.gz"
 
# Before Rsnapshot decides to copy all of this lets remove the unwanted files
#rm $MYSQLBACKUPDIR/*.sql # Don't need to remove them as they've already been converted to .tar.gz files
rm $MYSQLBACKUPDIR/$MYSQLBACKUPFILENAME.tar
rm $MYSQLBACKUPDIR/*.sql
 
echo
echo "Listing of compressed files in $HOSTNAME:$MYSQLBACKUPDIR"
echo "------------------------------------------------------------------"
ls -Asch1 $MYSQLBACKUPDIR/
echo "---"
echo "DONE!"