Using ZFS to snapshot your database

What if I told you you can backup your database in under a second? Awesome right? Well it's not entirely true, but you can create a snapshot of your database in under a second and decide later on how to backup this snapshot. In this post I will show you how to get the ZFS filesystem running on Ubuntu and how to backup and restore a MariaDB database from a snapshot.

Snapshot?

So, what's a snapshot? ZFS is a copy-on-write filesystem. This basically means only the changes to the filesystem are recorded. A snaphot is nothing more than a reference to the state of the filesystem at a certain point in time. Therefore an initial snapshot doesn't take up any space.

Install ZFS on Ubuntu

Let's roll up our sleeves and install ZFS right away!

I used Ubuntu 14.04 in a Vagrant box with the following Vagrantfile:

Vagrant.configure(2) do |config|
  config.vm.box = "ubuntu/trusty64"
end

Run the following commands in your Vagrant box:

sudo apt-get install python-software-properties
sudo apt-add-repository ppa:zfs-native/stable
sudo apt-get update
sudo apt-get install ubuntu-zfs

This may take a while so grab a cup of coffee...

Or watch some funny cat videos...

This is our favorite:

Now test your installation:

sudo zpool status

It will tell you there are no pools available.

Volumes, devices and pools

You can play with ZFS without having to format a physical drive. Just create a virtual device (vdev) of 1 GB on your current filesystem:

truncate -s 1GB /tmp/vdev1.img

While we're at it, let's create two more virtual devices for a RAID-Z3 setup:

truncate -s 1GB /tmp/vdev2.img
truncate -s 1GB /tmp/vdev3.img

Now create a virtual volume (pool) called yolo with your new virtual devices:

sudo zpool create -f yolo /tmp/vdev1.img /tmp/vdev2.img /tmp/vdev3.img

Now check your newly created pool:

sudo zpool status

This will output something like:

Now create a dataset within your pool:

sudo zfs create -o mountpoint=/mnt/mariadb yolo/mariadb

This will mount the new dataset on /mnt/mariadb.

Setting up MariaDB

If you haven't installed MariaDB yet, please do so using the instructions found on the MariaDB Repository Configuration Tool page. In my case I did:

sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://ams2.mirrors.digitalocean.com/mariadb/repo/10.0/ubuntu trusty main'
sudo apt-get update
sudo apt-get install mariadb-server

One thing I ran into is that the ZFS I used does not support AIO so I had to tell MariaDB not to use it for the InnoDB engine. Just add the following line to /etc/mysql/my.cnf:

innodb_use_native_aio = 0

Also, because we're on a Vagrant box with limited resources I tuned down the InnoDB buffer pool size:

innodb_buffer_pool_size = 64M

Now let's continue and copy the data directory to the ZFS volume:

sudo service mysql stop
sudo cp -R /var/lib/mysql/* /mnt/mariadb
sudo chown -R mysql:mysql /mnt/mariadb
sudo mv /var/lib/mysql /var/lib/mysql.original
sudo ln -s /mnt/mariadb /var/lib/mysql
sudo service mysql start

Note: you can also create a fresh data directory on your ZFS volume:

sudo mysql_install_db --user=mysql --base-dir=/usr/bin --data-dir=/mnt/mariadb

Your first snapshot

Let's create a dummy table in the test database and insert a single record:

CREATE DATABASE test;
CREATE TABLE test.yolo (`foo` CHAR(3));
INSERT INTO test.yolo (foo) VALUES('bar');
SELECT foo FROM test.yolo;

Now take a snapshot of our pool called yolo:

sudo zfs snapshot yolo/mariadb@bar

Update the record in the database:

UPDATE test.yolo SET foo='baz';
SELECT foo FROM test.yolo;

Take another snapshot:

sudo zfs snapshot yolo/mariadb@baz

When you list the snapshots with sudo zfs list -t snapshot something similar is shown:

You can even have a look around in your snapshot. By default the snapshots are hidden but you can make them visible with the following command:

sudo zfs set snapdir=visible yolo/mariadb

Now you can list the snapshots with:

ls -all /mnt/mariadb/.zfs/snapshot/

Backup and restore a snapshot

As I said earlier, a snapshot is just a reference, not an actual filesystem. Using zfs send you can create a stream of the snapshot and backup the data:

sudo zfs send yolo/mariadb@bar > /backup/yolo/mariadb/bar
#or
sudo zfs send yolo/mariadb@bar | gzip > /backup/yolo/mariadb/bar.gz
#or even
sudo zfs send yolo/mariadb@bar | ssh backuphost zfs recv yolo/mariadb

Now restore the first snapshot:

sudo service mysql stop
sudo rm -rf /mnt/mariadb/*
sudo cp -R /mnt/mariadb/.zfs/snapshot/bar/* /mnt/mariadb/
sudo chown -R mysql:mysql /mnt/mariadb/*
sudo service mysql start

The data in the database should be the data we originally inserted:

Yay!

Final remark

As you may know, MariaDB does not write every transaction to disk immediately. While MyISAM does write to disk when you issue a FLUSH TABLES WITH READ LOCK statement, InnoDB does not guarantee it will. However it does write to the binary log files. So when you include these in your snapshot you're fine.