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.
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
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
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
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
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
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.
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:
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.
Come and join us in our quest to build the best development team in the universe...and beyond!