Whilst at a customer site the other day the question was raised over how they could keep a fair volume of backups of a MySQL database off site, while minimising bandwidth and disk space. So the usual ‘moon on a stick’ enquiry. My thought was to use MySQL replication combined with ZFS snapshotting. This is my test setup to ensure it all works nicely.
Stage 1 – Replication server setup
I’m using FreeBSD 9.1 (how retro!) with a UFS boot drive and a second ZFS data drive. I created the main /DATA zpool and a sub directory (dataset) called mysql:
# zfs list NAME USED AVAIL REFER MOUNTPOINT DATA 34.9M 19.5G 32K /DATA DATA/mysql 34.7M 19.5G 32.1M /DATA/mysql
Stage 2 – MySQL setup
Now we must install MySQL server and ensure the data is stored in the non default directory of /DATA/mysql. To do this, add the following line to the /etc/rc.conf file:
mysql_enable="YES" mysql_dbdir="/DATA/mysql"
Stage 3 – MySQL Replication
The next stage is to get the data replicating into the database replication server. I use the standard procedure documented on the MySQL website and ensure I have a dedicated ‘Replication’ user. When its all done, complete the ‘show slave status’ to ensure its all running correctly:
show slave status\G --snip-- Slave_IO_Running: Yes Slave_SQL_Running: Yes --snip--
Stage 4 – Set a cron task to snapshot each hour
I wrote a little script to take the snapshot and set it to run every hour:
#! /bin/sh DATESTRING=`date +'%Y%m%d%H'` zfs snapshot DATA/mysql@$DATESTRING
The snapshots can be seen in ‘very’ hidden directory:
ls -lah /DATA/mysql/.zfs/snapshot/ total 2 dr-xr-xr-x 3 root wheel 3B Oct 15 22:25 . dr-xr-xr-x 4 root wheel 4B Sep 24 20:23 .. drwxr-xr-x 5 mysql mysql 16B Oct 15 21:50 2013101522
So far, thats it! I’ll let it build for while and then try using de-dupe and compression to see what works best.
