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
/etc/my.cnf and add the line:
binlog directory owned by
$ cd /var/lib/mysql $ sudo mkdir binlog $ sudo chown mysql:mysql binlog
$ sudo /sbin/service mysqld restart
$ 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
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"