Automatic and unattended remote unix backup script for files and MySQL databases using TAR and SSH

I’m paranoid about losing all my hard work in a puff of smoke. I’m paranoid about most things going down the pan when it comes to computers. Operating a development slash staging web server, there is no point in relying on the web hosting company’s backups of my files (or the live versions of the files) should the worst happen, because it will either take ages to get access to them (if they are not freely accessible), but more than likely my development/staging files would be at a stage far more advanced to anything uploaded to the hosting co.

Driven by this fear I searched the Internet for a suitable shell script that didn’t do anything fancy, just tarred and reasonably compressed my files and provided the option of transferring the archive file(s) off site to a remote location. The remote transfer factor is one of great importance - there’s no point creating an archive of files on the same disk, even a separate disk in the same machine, or a machine in the same building if it’s going to end up burning to death (I said I’m paranoid). I came across many and could have come across many, many more if I had persisted, but it got to the point where I had seen enough and it was proving too time consuming just reading and not finding anything good enough. Using the information and bookmarks I had acquired, I decided to write my own unix backup script.

We are fortunate in that our ISP does not meter our upstream usage, though it is limited to 128kbps. We are also fortunate that one of our shared web hosting providers in the USA gives us several gigabytes of space relatively cheaply, I believe they do however count uploading to the server via FTP in working out our bandwidth usage. Those points given, we didn’t want a script that would backup everything and upload it (either via FTP or SSH), every night. Sending barely changed backups night after night is just a waste of time and given the speed, it wouldn’t take long before uploading would continue until the middle of the morning when people wanted to do real work and would encounter slowness. Additionally, despite the large amount of storage space on the hosting servers, we didn’t want to fill it up with just backups.

So requirements (summarised) were as follows:

• Beginning of every month do a full backup and keep permanently (or until manual deletion)
• Every Sunday do a full backup with incremental backups on Monday thru Saturday
• Self remove or overwrite backups over 7 days old to keep disk-usage down
• Back up files on the file system as well as MySQL databases in an easy to restore format
• Script should be deployable onto a shared web hosting service for backup from that end too
• Script should use a commonly found archiving method, such as Tar
• Transfer to a remote or secondary system should be secure via SSH
• Run automatically every night
• Be versatile, in the sense that the script could simply be copied to another machine and run pretty much out of the box
• Some error checking with email notifcation on failure, but similarly notification on success just for reassurance

The result is below, freely available for copying and pasting.

The great thing about this script is that because it backs up entire directories and all sub-directories is that you can (if you want) back up your entire file system as easily as just backing up selected directories. Additionally, use your unix server as a backup server running software such as AMANDA, and you can remotely backup all the systems on your network (albeit very slowly and perhaps a little foolishly in some cases if storing archives on untrusted remote servers).

Brief configuration guide

Create a .my.cnf file in the home directory of the user under whom you will run this script to hold MySQL login details. For example, if you are going to run this as root (recommended), create the following file:

/root/.my.cnf

The format for this file is

[client]
user=myusername
password=mypassword
#host=mysql.host.com # uncomment this line if necessary

It is imperative that you setup SSH so that it can be used unattended for this script. See http://ubuntu-tutorials.com/2007/02/05/unattended-ssh-login-public-key-ssh-authorization-ssh-automatic-login/ for details on setting this up

Add the script to crontab so that your system kicks this off automatically every night.

The file - copy, paste and go

#!/bin/sh
# Based on a script by Daniel O'Callaghan - danny * freebsd dot org
# and modified by Gerhard Mourani - gmourani * videotron dot ca
# further modified by James Caws - james dot caws * folutions dot com
# sourcing it from http://www.faqs.org/docs/securing/chap29sec306.html

# Make sure SSH can run unattended, see:
# http://ubuntu-tutorials.com/2007/02/05/unattended-ssh-login-public-key-ssh-authorization-ssh-automatic-login/

# The following variables need checking and changing according to your set-up
EMAIL=youremail@domain.com		# email address where notifications should be sent to
COMPUTER=`hostname -s`			# name of this computer
MYSQL_BACKUP_DIR=/tmp/mysql_backups	# path to where temporary MySQL dump files should reside
MYSQL_SECRETS=/root/.my.cnf		# location of .my.cnf file containing MySQL login credentials
DIRECTORIES="/home $MYSQL_BACKUP_DIR"	# directories to backup
BACKUPDIR=/local-backups		# where to store the backups
TIMEDIR=/local-backups			# where to store time of full backup
REMOTE_BACKUPDIR=/remote-backups	# location on remote system where backups should be placed
SSH_USER=someuser			# username to use when connecting to remote system via SSH
SSH_HOST=your.remotehost.com		# hostname or IP address of remote system

# Tool locations - for a number of reasons, you may not wish to use the system defaults
# You will find the version of the utility in place on my server when this script was
# written shown in [ ] - just for your reference
TAR=tar					# name and locaction of tar [tar (GNU tar) 1.14]
MKDIR=mkdir				# path to mkdir utility [mkdir (coreutils) 5.2.1]
RM=rm					# path to rm utility [rm (coreutils) 5.2.1]
CP=cp					# path to cp utility [cp (coreutils) 5.2.1]
MYSQL_DUMP=mysqldump			# path to mysqldump utility [Ver 10.9 Distrib 4.1.20, for redhat-linux-gnu (i686)]
MYSQL=mysql				# path to mysql client [Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3]
SSH=ssh					# path to ssh utility
REMOTE_MKDIR=mkdir			# path to mkdir on REMOTE system

### Should be no need to modify anything below this line unless you don't want the MySQL backup ###

PATH=/usr/local/bin:/usr/bin:/bin
DOW=`date +%a | tr '[A-Z]' '[a-z]'`	# Three letter day of the week in lower case e.g. mon
DOM=`date +%d`				# Date of the Month e.g. 27
YM=`date +%Y-%m`			# Year and Month e.g. 2007-04
ISO_DATE=$(date +%Y-%m-%d)		# ISO date format e.g. 2007-04-02

### FUNCTIONS BELOW THIS LINE ###

mysqlBackup() {
# uncomment the following line if you do not want MySQL backups
# return 1;

if [ ! -d $MYSQL_BACKUP_DIR ]; then
$MKDIR -p $MYSQL_BACKUP_DIR
fi

# DUMP ENTIRE DB
# $MYSQL_DUMP --defaults-file=$MYSQL_SECRETS --add-drop-table --allow-keywords --all-databases > $MYSQL_BACKUP_DIR/full-mysql-dump.sql

# DUMP INDIVIDUAL DBs & THEIR TABLES

# DETERMINE DB NAMES
for DATABASE in `echo show databases | $MYSQL --defaults-file=$MYSQL_SECRETS -s` ; do
if [ $DATABASE == 'mysql' ]; then
continue;
fi

$MKDIR -p $MYSQL_BACKUP_DIR/$DATABASE
# DETERMINE AND DUMP EACH TABLE IN DB
for TABLE in `echo show tables | $MYSQL --defaults-file=$MYSQL_SECRETS -s $DATABASE`; do
$MYSQL_DUMP --defaults-file=$MYSQL_SECRETS --add-drop-table --allow-keywords -q -a -c $DATABASE $TABLE > $MYSQL_BACKUP_DIR/$DATABASE/$TABLE.sql
done
done

return 1;
}

sendBackupAndVerify() {
# Checks if any params.
if [ -z $1 -o -z $2 ]; then
echo "Not enough parameters passed to sendBackupAndVerify() -- exiting"
return 0
fi

# $1 = local filename
# $2 = remote filename

LOCALCHECKSUM=`md5sum $1 | cut -f1 -d " "`
REMOTECHECKSUM=`cat $1 | $SSH $SSH_USER@$SSH_HOST "$REMOTE_MKDIR -p $REMOTE_BACKUPDIR; cd $REMOTE_BACKUPDIR; cat  > $2; md5sum $2 | cut -f1 -d ' '"`

if [ $LOCALCHECKSUM != $REMOTECHECKSUM ]; then
echo "Checksums of local and remote backup file do not match -- you probably want to check this out soon.
Local file: $1
Remote file: $REMOTE_BACKUPDIR/$2" | mail -s "Automated backup: ERROR" $EMAIL
else
echo "Automated backup from $COMPUTER to $SSH_HOST has completed successfully." | mail -s "Automated backup: COMPLETE" $EMAIL
fi

return 1;
}

### END OF FUNCTIONS ###

# On the 1st of the month a permanet full backup is made
# Every Sunday a full backup is made - overwriting last Sundays backup
# The rest of the time an incremental backup is made. Each incremental
# backup overwrites last weeks incremental backup of the same name.

# The original state of this script is to only backup MySQL during full
# and Sunday backups - locate and uncomment the call to mysqlBackup if
# you want to backup everyday

if [ ! -d $BACKUPDIR ]; then
$MKDIR -p $BACKUPDIR
fi

# Monthly full backup - this case must be tested/run before the remainder
if [ $DOM = "01" ]; then
mysqlBackup

$TAR -czf $BACKUPDIR/$COMPUTER-FULL-$YM.tar.gz $DIRECTORIES > /dev/null 2>&1

# CHECKSUM=`md5sum $BACKUPDIR/$COMPUTER-$YM.tar.gz | cut -f1 -d " "`
# Send full monthly backup to remote host
sendBackupAndVerify $BACKUPDIR/$COMPUTER-$YM.tar.gz $COMPUTER-$YM.tar.gz
fi

# Weekly full backup
if [ $DOW = "sun" -o ! -f $TIMEDIR/.$COMPUTER-last-full-update ]; then
mysqlBackup

# If Sunday is also first of the month, then as we've already done a full backup,
# just copy the full monthly to the full weekly locally and remotely and cleanup.
if [ $DOM = "01" ]; then
$CP $BACKUPDIR/$COMPUTER-$YM.tar.gz $BACKUPDIR/$COMPUTER-$DOW.tar.gz
$SSH $SSH_USER@$SSH_HOST "cd $REMOTE_BACKUPDIR; cp $COMPUTER-$YM.tar.gz $COMPUTER-$DOW.tar.gz"

if [ -d $MYSQL_BACKUP_DIR ]; then
$RM -fr $MYSQL_BACKUP_DIR
fi

# Update full backup date
echo $ISO_DATE > $TIMEDIR/.$COMPUTER-last-full-update

exit ;
# Otherwise let's just run the full backup as normal
else
$TAR -czf $BACKUPDIR/$COMPUTER-$DOW.tar.gz $DIRECTORIES > /dev/null 2>&1
fi

# Update full backup date
echo $ISO_DATE > $TIMEDIR/.$COMPUTER-last-full-update

# Make incremental backup - overwrite last weeks
else
# mysqlBackup # uncomment to backup MySQL everyday

# Get date of last full backup
NEWER="--newer `cat $TIMEDIR/.$COMPUTER-last-full-update`"
$TAR $NEWER -czf $BACKUPDIR/$COMPUTER-$DOW.tar.gz $DIRECTORIES > /dev/null 2>&1
fi

# Send [full] daily backup to remote host
sendBackupAndVerify $BACKUPDIR/$COMPUTER-$DOW.tar.gz $COMPUTER-$DOW.tar.gz

# Remove MySQL back-ups - they were only temporary and there's no point in keeping duplicates
if [ -d $MYSQL_BACKUP_DIR ]; then
$RM -fr $MYSQL_BACKUP_DIR
fi

# THE END #

…

You can leave a response, or trackback from your own site.