Backing up MySQL and the source code to your websites is absolutely critical as a systems administrator. I have been learning that while often you can rely on the MIS mechanisms that are in place for many large organizations, you never want to fully rely on them. Unfortuneately, we recently lost a small amount of data because the Oracle backups being sent to tape where not properly configured. I will take credit for the query that lost the small amount of data; however, it should have been easily recoverable.
In order to beef up the backup processes for our Linux server, I have now created a cronjob using a BASH script to backup the critical files and ftp them to an external server. In creating this script, I made use of the the scripts found on these two websites, and then added some additional functionality.
http://bash.cyberciti.biz/backup/backup-mysql-database-server-2/comment-page-1/#comment-500
http://linux.derkeiler.com/Newsgroups/alt.os.linux.suse/2005-12/msg01959.html
The script from this first website was incredible. Without having to update the script everytime you add a new database, this script will do a mysqldump and save them all in .gz format. My addition simply tarballed the source code for the websites including the http.conf file. Lastly, the script sends the files to a remote server using FTP.
Save the below code in a file called backupscript.sh. Then set your crontab to run it every evening – sh backupscript.sh.
#!/bin/bashMyUSER=”databaseusername”
MyPASS=”databasepassword”
MyHOST=”databasehost”# Linux bin paths, change this if it can’t be autodetected via which command
MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”
CHOWN=”$(which chown)”
CHMOD=”$(which chmod)”
GZIP=”$(which gzip)”# Backup Dest directory, change this if you have someother location
DEST=”/backupdirectory”# Main directory where backup will be stored
MBD=”$DEST/mysql”# Get hostname
HOST=”$(hostname)”# Get data in dd-mm-yyyy format
NOW=”$(date +”%d-%m-%Y”)”# File to store current backup file
FILE=”"
# Store list of databases
DBS=”"# DO NOT BACKUP these databases
IGGY=”test”[ ! -d $MBD ] && mkdir -p $MBD || :
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST# Get all database list first
DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ’show databases’)”for db in $DBS
do
skipdb=-1
if [ "$IGGY" != "" ];
then
for i in $IGGY
do
[ "$db" == "$i" ] && skipdb=1 || :
done
fiif [ "$skipdb" == "-1" ] ; then
FILE=”$MBD/$db.$HOST.$NOW.gz”
# do all inone job in pipe,
# connect to mysql using mysqldump for select mysql database
# and pipe it out to gz file in backup dir
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
fi
doneFILE=”$MBD/hostway_$NOW.gz”
tar -cvf $FILE /var/www/html/*
FILE=”$MBD/httpd_conf_$NOW.gz”
tar -cvf $FILE /etc/httpd/*host=”ftpserverhost”
user=”ftpusername”
password=”ftppassword”
file1=”*.gz”
srcdir=”/backupdirectory/mysql”
FTPLOG=”ftp.log”
ftp -d -v -n $host < $FTPLOG; ERR=$?
user $user $password
lcd $srcdir
prompt off
mput $file1
bye
END
if [[ $ERR -ne 0 ]]; then
echo “Failure …$?”
echo “Debug – File $FTPLOG”
fi
exit
#End
