Automatic offsite WordPress backups

Yesterday, I decided to set up a an automated backup solution for my site here.  Basically, it does an sqldump then sends the dump to another server over SCP.

First, I had to enable SSH login without a password in order for SCP to work in a cronjob.  I followed this howto and it worked perfectly.

Then, I created a backup script to be called by cron:

#!/bin/sh

cd /home/backups/collegegeek

FNAME=collegegeek-`date +%F`.sql

mysqldump –add-drop-table -uroot -pPASSWORDHERE collegegeek > $FNAME
bzip2 $FNAME
rm $FNAME
scp $FNAME.bz2 zach@192.168.1.82:backups/
echo “Nightly Backup Successful: $(date)” >> /home/backups/blogbackup.log

This will also leave a copy on the local server on the /home partition.  Note that /home is on a different physical drive than /var, so if the drive with the SQL database goes down, I should have a local copy as well.