Spouting Shite

Backup script for a LAMP server

Over the years we have used several backup systems on our various IT systems. I have recently been asked by a few friends to publish these scripts.

Firstly, please allow me to make a few assumptions:

1. We want to run the backup every two hours and we want to keep 400 copies (around 17 days of data)
2. We want to backup some files on the local system….not the whole file system
3. We want to take snapshots of a database on the local system periodically
4. We will hold the backups on a remote server
5. This is a backup system of last resort, if we ever need to recover data from this system it will take manual intervention.

This system is run in two steps:

On the local server:

On the local server we want to dump the local mysql database to disk periodically, perhaps every two hours. To do this we need to follow these steps:

1. create a directory where we will be placing the database dumps
   /root/database_dumps
2. create a directory where we will place our scrips
   /root/scripts
3. save this file into the scripts directory
#!/bin/sh
# Script name : auto_mysql_dump.sh
# Backup the dbname database
date=`date +%F-%k.%M.%S`
DBS=`mysql -Bse ‘show databases’`
for db in $DBS
do
mysqldump –databases $db > /root/database_dumps/$db-$date.sql
done
cd /root/database_dumps
tar -czf mysqldump-$date.tar.gz *.sql
rm -fr *.sql
# delete all database entries that are more than 2 day old
find /root/database_dumps/ -amin +300 -exec /bin/rm -f {} \;

#!/bin/sh
# Script name : mysql_dump.sh
# Backup the dbname database
# Script created by Ross Cooney from Rozmic (www.emailcloud.com)

USER='your-mysql-username'
PASS='your-mysql-password' 

date=`date +%F-%H.%M.%S`
DBS=`mysql -u $USER -p$PASS -Bse 'show databases'`

for db in $DBS
do
       mysqldump -u $USER -p$PASS --databases $db > /root/database_dumps/$db-$date.sql
done

cd /root/database_dumps
tar -czf mysqldump-$date.tar.gz *.sql
rm -fr *.sql

# delete all database entries that are more than 3 hours old
find /root/database_dumps/ -amin +180 -exec /bin/rm -f {} \;

4. setup the following cron job:
   0 1,3,5,7,9,11,13,15,17,19,21,23 * * * /root/scripts/mysql_dump.sh > /dev/null

These four steps will setup a process that dumps the mysql database to disk every two hours and then hold a copy of that database dump for three hours.

On the remote server:

We want to run a script every two hours to copy the changes of any files on the disk. To do this you need to follow these steps:

1. create a directory where we will be placing the backup files
   /root/backup
2. Create a directory that we will use for this particular block of data, we use the IP address of the server but you can use anything that you want.
   /root/backup/10.0.0.6
3. create a directory where we will place our scrips
   /root/scripts
4. Ensure that the SSH key for the remote server is listed in the local server’s ‘authorized_keys’ file so that ssh connections can be made without needing a password.
5. Save this file to your scripts directory:

#!/bin/bash
# this script is called: 10.0.0.6.backup.sh
# Script created by Ross Cooney from Rozmic (www.emailcloud.com)

RSYNC_ARGS="-az --progress --delete --delete-excluded --delete-after"
BACKUP_DIRS="/var/www/html /etc /root/scripts /root/database_dumps"
SERVER="10.0.0.6"
DIR="/root/backup/10.0.0.6"

 rm -rf $DIR/daily.400

 for i in `seq 400 -1  1`; do
        let NEXT=$i+1
        mv $DIR/daily.$i $DIR/daily.$NEXT > /dev/null 2>&1
done

cp -al $DIR/daily.0 $DIR/daily.1

for RDIR in $BACKUP_DIRS; do
   rsync $RSYNC_ARGS ${SERVER}:$RDIR $DIR/$HOST/daily.0/
done

6. Setup the following cron job:
   0 0,2,4,6,8,10,12,14,16,18,20,22 * * * /root/scripts/backup.10.0.0.6.sh > /dev/null

These scripts work for us, I hope that they are of value to you, please dont take my word for their effectiveness, you should test them fully before you use them. I used the following resources when I was writing these scripts:

• http://www.mikerubel.org/computers/rsync_snapshots/
• http://bash.cyberciti.biz/backup/backup-mysql-database-server-2/

Update (2nd October 2009):
Thanks to Oli Wood for pointing out the typo in the above script, this has now been resolved