It’s a cliche amongst nerds that everyone preaches automated backups, and very few have it.
Partly because it’s surprisingly fiddly to set up “right”. And even fiddlier to do restoration testing.
Still, one thing at a time. I’ve just now finished putting together an automatic backup regime for Ozblogistan, the server I run hosting Skepticlawyer and Andrew Norton (and, at some time in the future, some other sites too).
Herewith my notes.
Tarsnap
I am using the tarsnap backup service. I am satisfied that it’s cheap, efficient, reliable and secure. The trickiest part to wrap my modest brain around is its snapshot-based nature. You don’t follow the full-and-incremental model with tarsnap: you simply list what you want to backup and let it sort out the details of the most efficient way to store that for you.
I have a very simple shell script which cron runs each day:
#!/bin/bash
# Quick and dirty script to backup files, settings and the database.
DATE=`date +%Y-%m-%d`
SQL_FILE="/var/backups/mysqldump/ozblogistan-wordpressmu-$DATE.sql"
BACKUP_ARCHIVE_NAME="ozblogistan-$DATE"
# Dump SQL
mysqldump --defaults-extra-file=/etc/tarsnap/mysqldump.cnf -eltn --dump-date --default-character-set="latin1" wordpressmu > $SQL_FILE
# Perform tarsnap backup
tarsnap -c -f $BACKUP_ARCHIVE_NAME --exclude *cache* --exclude *.svn* /home /etc /var/www/wordpressmu /var/backups/mysqldump
# Delete mysqldump
rm -f $SQL_FILE
This script performs a mysqldump of the database. It tells tarsnap to back that up in addition to /etc, /home and /var/www/wordpressmu, ignoring cache directories and SVN directories. It creates a “new” tarsnap archive each day; in practice tarsnap will only send a delta of the SQL plus any new files uploaded to the WordPress installation.
I’ve chosen not to compress the SQL, as I am unsure whether that will interfere with the delta process used by tarsnap. Compression can change the layout of a file.
Mysqldump settings
The mysqldump commandline in this shell script has two key features to note. Firstly, the use of the –default-character-set option, necessary to circumvent MySQL retardation. Secondly, the use of the –defaults-extra-file option to import settings from a custom configuration file.
The custom configuration file contains the username and password of a particular backup user. This backup user is distinct from the user supplied to WordPress Mu to access the wordpressmu database. The backup user has very limited privileges: essentially it can read but not alter the database, which is all the permissions it needs (ie. it has SELECT, LOCK TABLES on the database and RELOAD globally).
Placing those identifying details in a configuration file means that they will not be visible to someone running top, ps et al. A very modest security improvement, I grant you, but still.
Still to do
I still need to set up something similar for troppo. We have automated backups there but they’re not as efficient or reliable. Ideally I’d prefer to move troppo to the new server, but that may not be possible. This is done.
I also need to develop an automatic restoration testing facility. It’s common to think you have “flawless” backups, then discover after disaster that your backups were no good.
Finally I need to add code to delete old archives. Tarsnap’s snapshotting model is very efficient and the rate of change slow, so for now I will leave it open-ended to get a sense of how far back I can keep backups.