MySQL Automated Backup Script

mysql_backup.sh

#!/bin/bash

# MySQL Backup util
# Automatic backup utility, run in cron job under root database user
# Example cron line:
# 53 *  *   *   *     /data/scripts/backup_mysql_database.sh

# Defaults - I
PREFIX='hourly_'
HOUR=`date +%H`
DAY=`date +%d`


# if its 1am we will name the tar file differently so it wont get scrubbed
# after a few weeks
if [ $HOUR -eq 1 ]; then
    PREFIX='daily_'
fi

# if its the end of the month we will name the tar file differently so it wont get
# scrubbed after a few months
if [  $DAY -eq 1 -a $HOUR -eq 1 ]; then
    PREFIX='monthly_'
fi

# Defaults - II
FDATE=`date +%Y-%m-`$DAY'_'$HOUR
FPATH='/data/backups/db/mysql/'
logfile=$FPATH$FDATE'-backup.log.out'
logfile2=$FPATH'backup.log'
errors=''
tarfile=$FPATH$PREFIX'db-backup-'$FDATE'.tar.bz2'
outfiles=$FPATH'*.out'

# P - Ignore leading / in path (dont error)
TARCMD='/bin/tar --remove-files -jcvPf '$tarfile' '$outfiles' > /dev/null'

FINDCMD='/usr/bin/find'


echo -e "Dumping databases:: \n" >> $logfile
# Dump the contents of every database
DUMP_CMD='mysqldump --all-databases > '$FPATH$PREFIX$FDATE'-'$db'-dump.out'
eval "$DUMP_CMD" && \
echo -e '  completed db: '$db "\n">> $logfile  || \
errors=$errors"  failed db: '$db' date: \n"`date`

echo -e "Errors: " >> $logfile
echo -e $errors >> $logfile

echo -e "Removing Old Files: " >> $logfile

# Only keep the hourly backups for the last two weeks
echo $($FINDCMD $FPATH -mtime +14 -name "hourly*" | xargs rm -f )"\n" >> $logfile

# and the daily backups for the last 100 days
echo $($FINDCMD $FPATH -mtime +100 -name "daily*" | xargs rm -f ) "\n">> $logfile

# and the daily backups for the last 100 days
echo $($FINDCMD $FPATH -mtime +365 -name "monthly*" | xargs rm -f ) "\n">> $logfile

echo `cat $logfile`>> $logfile2

# Compress results
eval "$TARCMD >> $logfile2"