mySQL Backup Script.

The following script will backup all of the specified databases, create a report and throw them in a tar.gz. Script features error handling, e-mail notifications and easily customizable. I wrote this several years ago, so it’s probably due for an update soon…

#!/bin/sh
## MySQL Makeover Backup Script v1.0
## Authored By Russ Thompson 2008 @ viGeek.net

# Establish destination directory
DESTDIR="/pwd/whatever"

# Define log file ensure writable
LOGFILE="/var/log/dbbackup.log"

# Establish output file name
FONAME="db_$(hostname)_$(date +%m-%d-%Y).tar.gz"

# Establish Databases To Backup
# For multiple databases seprate via space
DBACKUP="db1 db2"

# mySQL Database Credentials (can store in users my.cnf)
# Only user with 'read only' ensure this file script chmoded correctly
# Better security... use shcrypt to secure or keyless entry.
DUSER="username"
DPASS="password"

# Define e-mail attributes for error handling messages
EADDY="email@whatever.com"
ESUB="mySQL Backup Error on $(hostname)"

# Seperation
echo -e "--------------------------------------------------------------------------------------------------------" >> $LOGFILE

# Pregame check if we are the active  (for clustered DRBD systems)
# Simply checks to ensure we are running.
if [ -e /var/lib/mysql/mysql.sock ]
  then
     echo -e "$(hostname) is active beginning backup procedure..." >> $LOGFILE
  else
     echo -e "$(hostname) is not active, aborting..." >> $LOGFILE
     exit 1
fi

# Kickoff
echo -e "$(date +%D) $(basename $0) has started at: $(date +%l:%M%p)" >> $LOGFILE

# Define Error Handling.
function clean_up {
        #Perform actions on errors.
        EMESSAGE="tmpout.txt"
        echo -e "$(hostname) caught error on line $LINENO at $(date +%l:%M%p) via script $(basename $0)" >> $EMESSAGE
        /bin/mail -s "$ESUB" "$EADDY" < $EMESSAGE
        echo -e "Caught error on $LINENO at $(date +%l:%M%p)" >> $LOGFILE
        rm -f $EMESSAGE
        exit 1
}
# Define our trap conditions
trap clean_up ERR SIGHUP SIGINT SIGTERM

# Change into working directory
cd $DESTDIR

echo -e "Changed into directory $(pwd)" >> $LOGFILE

# Before we continue ensure we're in the right place

if echo $(pwd)| grep $DESTDIR >> /dev/null
        then
        echo -e "Good" >> /dev/null
else
        echo -e "ERROR: expecting directory $DESTDIR however in $(pwd)" >> $LOGFILE
        clean_up
fi

# Remove the previous backup
rm -f *.gz

# Begin creating SQL backup files.
for i in $DBACKUP
        do
        mysqldump --user $DUSER --password=$DPASS $i > $i.sql
        echo -e "Created mySQL dump for database $i" >> $LOGFILE
done

# Create simple report to include in backup
REPNAME="backup-report.txt"
echo -e "Database backups expecting the following files:" > $REPNAME
echo -e "$DBACKUP" >> $REPNAME
echo -e "\nGetting the following files:" >> $REPNAME
echo -e "$(ls)" >> $REPNAME
echo -e "The following files are getting backed up:" >> $LOGFILE
echo -e "$(ls)" >> $LOGFILE
# Create our GNU Zip
tar -czvf $FONAME *
echo -e "Created backup file with a size of $(du -h $FONAME)" >> $LOGFILE

# Cleanup the mess.
for i in $DBACKUP
        do
        rm -f $i.sql
done

# Finish
echo -e "$(basename $0) has finished at: $(date +%l:%M%p)" >> $LOGFILE

Comments are closed.