Using ZFS snapshots to keep MySQL backups

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.

This entry was posted in FreeBSD Administration, MySQL, ZFS and tagged , , , . Bookmark the permalink.

Leave a Reply