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 protected]"
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
## 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 protected]"
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