Rob's Blog

Sitting in a tree in our front yard:

Incidentally, I just noticed that WordPress has a category called “uncategorized”.

A few of my clients have businesses which rely on entirely on their website, and therefore on the database that powers it. What would happen to them if their database was lost or corrupted? Most likely, it would be a complete disaster for them. Of course, we could ask the web hosts to perform daily database backups of the database, but that usually adds significant cost to the price of hosting (one hosting company quoted us $100 per backup!).

Perhaps an easier way to implement an offset backup would be to dump the database, compress it and email it offsite somewhere. You’d need an email account with plenty of storage, obviously. Something like GMail’s 7GB of storage perhaps? Since you’re sending possibly sensitive data offsite, you might want to encrypt the data as well. Here’s a simply shell script which will do all of this:

#!/bin/bash
#

mysqldump -u USERNAME --password=DATABASE_PASSWORD DATABASE_NAME | gzip | gpg -a -c -o - --no-use-agent --passphrase 'YOUR_ENCRYPTION_PASSPHRASE' | uuencode "Database Backup `date`.gz.asc" | mail -s "Database Backup `date`" YOUR_EMAIL_ADDRESS

Let’s break this down a little to explain:

mysqldump -u USERNAME --password=DATABASE_PASSWORD DATABASE_NAME

This dumps the complete database “DATABASE_NAME” as SQL.

gzip

This compresses your database dump (no point using up your email storage unnecessarily)

gpg -a -c -o - --no-use-agent --passphrase 'YOUR_ENCRYPTION_PASSWORD'

This uses GPG’s symmetric encryption to keep your compressed database dump safe from prying eyes.

uuencode "Database Backup `date`.gz.asc"

(Possibly a bit “belt ‘n’ braces” since we’ve specified the “-a” option with GPG, but) uuencode the encrypted, compressed database backup so it’s suitable for emailing as an attachment. You might need to install the sharutils package before this program is available.

mail -s "Database Backup DB `date`" YOUR_EMAIL_ADDRESS

Finally, send the database backup to your nominated email account.

To automate the process, simply set up a cron job to run this script every n hours, where n is a number of your choosing according to a) the number of transactions on your database, b) the likely impact of losing data, and c) the amount of your storage at your disposal.

There you go: a basic strategy for automatically creating, encrypting and storing database backups on a remote server.