Next-Gen Backup with BTRFS snapshots for MySQL/Mariadb and root filesystems

btrfs_logoThis article is about using BTRFS snapshots as backup solution, usable for database and full root partition backup. This is not a detailed Step-By-Step guide and requires solid linux skills. This Post aims to share my expiriences and save you some hassle.
We use it in production, if you planning to do that too, do it at your own risk.

Here are some use-cases of BTRFS Snapshots:

  • Backup Root Partition of one or more Linux-Servers to a central backup Server
    • Its possible to directly boot the backup server into one of the snapshots, allowing manual failover
    • The snapshots can also be transfered into virtual machines. this becomes very handy if you want your “dev vm” to be up to date with the live system and update it with incremental snapshots regulary.
  • Backup MySQL or whatever data partitions seperately
  • You can use BTRFS Snapshots on the Root partitions to return to a functional linux after a failed system upgrade
  • If you are a developer, you can use BTRFS Snapshots locally with MySQL to test your data-modifying application again and again against the same starting dataset.
    • you can also share your dev database snapshots with your coworkers and give incremental updates to them.

When thinking about MySQL backups, you have some options to consider:

  1. use mysqldump to create a logical backup (also on per-table basis)
    1. its slow and creates noticeable load on the server
    2. hard to create consistent state (requires locks or a desync replication slave)
    3. not incremental, even when backing up only modified tables; if those tables are large, this is a problem.
    4. restore: import the dump somewhere, can tage ages with large datasets.
  2. use mysql binary logs for incremental backup
    1. allows point-in-time recovery
    2. replaying binary logs can take a long time on write-intensive setups
    3. you have to do full backups regulary because of this
  3. use innobackupex incremental method
    1. while its more advanced, it seems to have the same characteristics as with mysql binary logs.
    2. Its also resource intensive and requires a full database lock at the end of the transfer.
  4. use filesystem snapshots
    1. no point-in-time recovery
    2. endless incremental, do one full backup and then only incremental ones.
    3. fast and resource-saving backup process
    4. restore: you can launch a instance of your database from every snapshot directly on the backup system or move the full mysql directory back to the source server if needet.
    5. only downside is that you have to relie on the databases own power-outage recovery mechanism.

While doing mysqldump backups in the past, we now use the filesystem snapshot way. It has proven to be a very reliable, fast and resource-saving way of doing backups and also provides easy restore of the backed up data.

A filesystem snapshot is a consistent state of a filesystem that is not modified anymore. From data-integrity point of view, a snapshot features a unclean state like you have after an power failure, a kill -9 or an “Out of Memory” error. Because MySQL/Mariadb and other database engines are designed to survive power failures, you are always able to recover from a snapshot too.
BTRFS has a powerful snapshot capability that allows to incrementally transfer snapshots to remote systems. Its just so simple: create a snapshot, transfer it to a remote system, create a 2nd snapshot and only transfer the changed blocks from the previous one. Solaris/FreeBSD users are doing this for almost a decade with ZFS. A ZFS port is also available for linux, so you can also use it instead of BTRFS if you prefer it for some reason.

The stability and reliability of BTRFS has been discussed a lot. While it still features some bugs and problems, they are said to be unlikely to cause data loss. BTRFS improves a lot with every new linux kernel release, we are using it since linux 3.17 in production and for more than two years on our workstations. If BTRFS is proven to be stable, the snapshot backup methode discussed here can become very popular.

  • As said before, BTRFS is improving with every linux kernel release, so it is recommended to use the latest kernel version, or the version before that.
  • Obvious: because we want to use btrfs snapshotting, we need to put the data we want to save on a btrfs volume.
  • You need to have BTRFS filesystems running on both source (production)  and destination (backup) server.
  • if you dont have btrfs already, you can resize your current filesystem to make some space on the hard drive and create a btrfs there. If you just want to test, you can create a image file and mount it using losetup.
  • if you are already using btrfs as /, you can use btrfs subvolume create <path> to create a new subvolume for mysql. why should you? Because you may want to backup the mysql data dir more often than the rest of the system. Btrfs snapshots work on per-volume basis, so if you want to backup files independently (or exclude them form backup) you have to put them into a subvolume. you can backup multiple subvolumes from one machine, you just have to create a backup job for each.
  • you are free to use the btrfs compress options on the source, the destination or both.
  • install BTRFS-SxBackup from on the backup server. This cli-program makes it very easy to create, transfer and manage btrfs snapshots. with btrfs-sxbackup on the destination, the backup will be “pulled” and centally managed by the backup server (the client only requires to have ssh running). you also can setup btrfs-sxbackup on the source system and do a “push” backup if you want, both is supported.
  • on the destination (Backup) server, i strongly recommend to use LVM and create one btrfs volume for every backup task. Do not make the volumes too large, you can easily extend them if one needs more space. Storing and managing a lot of snapshots on one btrfs volumes can cause bugs that render one filesystem unwriteable. creating one btrfs volume per backup job makes btrfs faster and safer – and in case of btrfs problems, only one backup is affected. (and you have space left to create a fresh one and continue backups there). after doing it this way on the backup server, all the trouble we previously had with a single btrfs-backup-fs went away.
  • create a ssh key for root on the backup server and add it to the authorized_keys file on the source. also for the root user. btrfs-sxbackup requires to create, transfer and delete snapshots and only root can do that.
  • You have to use the btrfs-sxbackup init and run commands to setup and start the backup process. see the BTRFS-SxBackup git front page for more information. you can also turn on transfer compression
  • BTRFS-SxBackup has a retention feature; i.e. if you do a database backup every hour, you can keep the latest two on the source, and configure rules like “after two weeks, keep only 4 backup per day”
  • Restore: if you are using btrfs for (root) filesystem backups, you can directly read the plain files from the backup servers filesystem. if you require a full restore, you can use btrfs send and btrfs receive commands to restore your backup. there is also a commandline option to boot directly into a btrfs subvolume.
  • if you are using btrfs for mysql backups, you most likely want to investigate the data from your mysql tables or restore a single table or database.
    • in this case, install the same major mysql version as you have on the source system
    • the snapshots on the backup server are read-only, mysql will be unable to start on a read-only filesystem. to resolve that, create a new snapshot of the read-only snapshot, which will be writeable by default: “btrfs sub snap sx-<backupname> <restorename>
    • most likely, the source system will have a different user id for mysql than the backup system, so you have to do a chown -R mysql:mysql on the writeable volume.
    • configure the my.cnf data dir to point to the restore volume and start up mysql. watch the <hostname>.err logfile within the restore volume for startup process.
    • after the startup/recovery completed successfully, you can login with your production user/passwords on the backup instance, inspect data and use mysqldump to transfer tables back to the production system. you can also use btrfs send/receive to transfer the complete snapshot back to the source.
    • if you have a backup instance running, its a fully functional and writeable snapshot of the production data, which can be also used for testing before deployment.


Written by Bernd Michael Helm

Helm & Walter IT Solutions GbR © 2017 • Impressum