Backing up MySQL to Amazon S3

I recently got round to backing up my applications' databases to S3. Previously I had a script archiving them each night onto the same server. This helped if a client deleted something by mistake and wanted to retrieve it, but didn’t guard against server failure.

Fortunately in the four years I’ve been with Rails Machine I’ve never had a server failure.

Having pushed my luck I’m now finally backing up the databases to S3. Better yet, I’m incrementally backing up every 15 minutes. It’s not as good as full-blown replication but it’s much easier to set up and good enough for now.

The steps are pretty straightforward. This note is really an aide-mémoire for the next server I do this on.

Enable MySQL’s binary logging

Edit /etc/my.cnf and add the line:

log_bin=/var/lib/mysql/binlog/mysql-bin

Create binlog directory owned by mysql

$ cd /var/lib/mysql
$ sudo mkdir binlog
$ sudo chown mysql:mysql binlog

Restart mysqld

$ sudo /sbin/service mysqld restart

Install myql_s3_backup gem

Marc-André Cournoyer wrote this gem and it does the job admirably. I encountered a strange problem though, which I fixed in my fork.

$ sudo gem install airblade-mysql_s3_backup

Configure the backups (one file per database)

Adapt the gem’s sample config and stick it somewhere like ~/configure.

Set up cron for nightly full backups

$ crontab -l
PATH=/opt/ree/bin:$PATH
0 0 * * * fyi "mysql_s3_backup -c=/home/deploy/config/mephisto.yml full"

Note this uses my fyi gem to find out what cron is doing.

Set up cron for incremental backups

We have to do this as root because of the file permissions on MySQL’s binary logs.

$ sudo su -
# crontab -l
PATH=/opt/ree/bin:$PATH
5,35 * * * * fyi "mysql_s3_backup -c=/home/deploy/config/mephisto.yml inc"