MySQL / MariaDB and ZFS filesystem –  A good combination for backup and Disaster Recovery.

Why snapshot as backup-strategy?

When working with large to huge databases it is critical to take a backup of the database for disaster recovery and data failures that application either cannot recover or regenerate corrected data. For a database of such size it is vital that backup time is as short as possible to minimize application downtime/disruption due to backups. Regular backups can take several hours or more to complete for very large to huge databases.
The fastest way today to take a database backup is using the snapshot technology (sometimes called FlashCopy) either on a filesystem level or if the database has such technology implemented.

ZFS filesystem and snapshots

ZFS uses copy-on-write transactional object model allowing old data to be retained as new data is written and allowing snapshot versions of the filesystem to be maintained.
Here I will demonstrate how you can use the Solaris zfs filesystem (ZFS on FUSE) to backup MySQL/MariaDB database in as short time as possible.

Preparing the VM

For this demonstration I'm using Centos 6.5 64 bit with zfs-fuse in a VirtualBox VM with 1 Core (2.3 MHz AMD A10 ) and 1GB RAM, even though recommended minimum for zfs-fuse is 2 GB.
The VM disks (VDI format) are as follows

• 20 GB disk with the Centos 6.5 system installed.
• Three empty 5 GB disks that will be under the zfs filesystem.  

First thing is to make sure the SELINUX is disabled and reboot the VM after editing the config file as root :

sed -i -e 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config; shutdown -r now
                      

Select all Open in new window

Then  install the zfs-fuse package from the epel repo.

yum install zfs-fuse
                      

Select all Open in new window

After that, start the service and configure it to start on boot

service zfs-fuse start
                      chkconfig zfs-fuse on
                      

Select all Open in new window

and configure it to use the three empty disks (here the disks are /dev/sdb, /dev/sdc and /dev/sdd )
I'm using the MariaDB version 10.1 as I find that version interesting in respect of performance and as the Centos 6.5 installs the MySQL v 5.1 you will need to remove the mysql-server and reinstall it using the MariaDB repos.
To do this you have follow these upgrade steps.

After you have done that, or if you still have the MySQL 5.1 version, your next step is to move the database from the original path to a new path on the zfs pool ( make sure that the database is shutdown before).
Before moving the database to the zfs filesystem my.cnf needs this line in correct place (here I use MariaDB ).

[mariadb]
                      innodb_use_native_aio = 0
                      

Select all Open in new window

 

To test the creation of a database is successful on the zfs filesystem you simply create a database. You should also look for it in the new path on the zfs filesystem.

Snapshot demostration and comparison

Measuring the backup time of regular mysqldump vs zfs snapshot
And now for the interesting part : the comparison of the backup methods.
For this test I use the employee database from Launchpad.net  which installed  is roughly 188 MB in size. The whole database system (MySQL directory ) takes 347 MB of the zfs pool/filesystem.

The mysqldump takes 17.406 seconds while the zfs snapshot takes 0.388 seconds which is 45 times faster ( 98 % improvement). The transfer of the backup to another disk/location takes 21.271 seconds.

Adding 1 GB RAM to the VM makes the snapshot even slight faster. The zfs snapshot takes 0.216 seconds which is 80 times faster ( 99 % improvement) than the mysqldump. And the transfer of the backup to another disk/location takes 13.098 seconds.

Conclusion.

Here I have demonstrated how MySQL / MariaDB and ZFS is a good combination when it comes to backup large to huge databases (even though the sample database used was pretty small ) for disaster recovery as the backup time using zfs snapshot is many times faster than regular backups as these results shows. Keep in mind that even though the database grows the snapshot time increases a lot less than the mysqldump time. In fact, as the mysqldump backup times grow the snapshot time becomes a smaller fraction of it.
Be aware that both the database, VM and zfs filesystem needs little more tweaking for more and optimal performance which can only further improve those backup times.

Using ZFS you have also other nice features that include filesystem mirroring and fast filesystem/database cloning at any time which can come in handy.  Also note that you are able to use gzip to compress the backups on the fly (which requires some more memory to be fast) in both mysqldump and zfs snapshot commands.

For more info on ZFS you could look at this wiki as well as some other googling.