I realised today after reading this blog post that I although I pay for the backup facility on my Linode I don't have backups of my databases.

If it was just my own site on the Linode then it wouldn't matter too much but I'm hosting the site for my girlfriends charity. If I lost that then I would be very popular. So I set about adding a simple backup of the databases. In about an hour I had something that would dump out the databases to raw sql and copy them off the server.

A couple of shell scripts and cronjobs and jobs a good'un.

database_backup.sh is the script that dumps everything to a folder

#!/bin/sh
date=`date -I`
mysqldump -u root --all-databases | gzip > /var/backups/dbs/mysql-backup-$date.sql.gz
pg_dumpall | gzip > /var/backups/dbs/pg-backup-$date.sql.gz

The backup_databases.sh script is on my local machine and just runs rsync over the folder on the server to grab any files there.

rsync -av jon@www.spandexbob.co.uk:/var/backups/dbs/ /home/storage/backup/databases/

The two scripts are then set to run using a cronjob. At the moment they both run once a week as the content on the sites don't change massively each week, but it would be a simple job to increase the frequency if need be with a small tweak to the cronjobs.