Database Backups – Comparing MariaDB Mariabackup and Percona Xtrabackup

Your database server stores some of your enterprise’s most valuable information. Guaranteeing reliable database backups to prevent data loss in the event of an accident or hardware failure is a critical checkbox.

Whether it is a 24×7 highly loaded server or a low-transaction-volume environment, you will be in the need of making backups a seamless procedure without disrupting the performance of the server in a production environment.

In this blog, we are going to review two of the most used tools to accomplish this task, namely Percona XtraBackup and Mariabackup. We will review the similarities and differences between the two of them, and also how to use them.

What is Percona XtraBackup?

Percona XtraBackup is an open source tool for performing backups of MariaDB, MySQL and Percona Server databases. It performs online non-blocking (for the supported engines^*), tightly compressed and secure full backups on transactional systems so that applications remain fully available for the duration of the backup window.

By using this tool you can:

• Create hot InnoDB backups, that complete quickly and reliably, without pausing your database or adding load to the server
• Make incremental backups
• Move tables between MySQL servers on-line
• Create new MySQL replication slaves easily
• Stream compressed MySQL backups to another server
• Save on disk space and network bandwidth

What is Mariabackup?

Mariabackup is an open source tool provided by MariaDB for performing physical online backups. It is a fork of Percona XtraBackup designed to work with encrypted and compressed tables, and is the recommended backup method for MariaDB databases.

MariaDB Server 10.1 introduced MariaDB Compression and Data-at-Rest Encryption, but the existing backup solutions did not support full backup capability for these features. So MariaDB decided to extend XtraBackup (version 2.3.8) and named this solution Mariabackup.

Differences Between Percona XtraBackup and Mariabackup

As we noted earlier, Mariabackup is the recommended backup tool for MariaDB, and the main difference from XtraBackup is that it works with encrypted and compressed tables.

Anyway, if for any particular reason you want to use XtraBackup for your MariaDB database, there are some points to take into account depending on the MariaDB server version you have:

• MariaDB 10.1: With uncompressed and unencrypted MariaDB data, you can use XtraBackup. If encryption or compression is used, or when innodb_page_size is set to some value other than 16K it will not work.
• MariaDB 10.2: You might also want to try to use XtraBackup, but be aware that problems are likely due to the MySQL 5.7 undo log format incompatibility bug that was fixed in MariaDB 10.2.2. Due to this bug, backups prepared with XtraBackup may fail to recover some transactions. Only if you run the server with the setting innodb_undo_logs=1 this would not be a problem.
• MariaDB 10.3 and later: This case is more simple. XtraBackup is not compatible.

Also, there are some limitations to take into account when using Mariabackup:

• MyRocks: Starting with MariaDB 10.2.16 and MariaDB 10.3.8, Mariabackup will backup MyRocks Storage Engine data. Partial backup of MyRocks data is currently not supported. Incremental backup will store a full copy of MyRocks data.
• Export file functionality: Before MariaDB 10.2.9, Mariabackup did not support the –export functionality (it creates an export file to export data from the database). You can workaround this limitation by preparing the backup as usual (without the –export flag), then start the server and execute FLUSH TABLES FOR EXPORT.
• Log files: Mariabackup versions until 10.2.8 do not create empty log files and relies on the –copy-back action executed by user (which deletes old innodb log files, if any). If the user doesn’t use –copy-back or makes sure that the data directory is empty before restoring, the backups created with these versions may well become inconsistent/corrupted (because of the presence of leftover InnoDB logs).
• Gcrypt: Backup tool based encryption (gcrypt) is not supported on Mariabackup.
• Innobackupex option: No symlink to innobackupex (use the –innobackupex parameter instead). The innobackupex tool patches and provides additional features over the innobackup tool for backing up InnoDB and MyISAM tables.
• Compact option: –compact option is not supported.
• Rebuild indexes option: –rebuild_indexes option is not supported.
• Tar for backup files: Support for –stream=tar was removed in Mariabackup 10.1.24 (The –streams options streams backup files to stdout).

At last but not least, Mariabackup can be installed on Windows.

How to – Percona XtraBackup and Mariabackup

Let’s see how we can install and use it.

Installation

You have different methods to install both XtraBackup and Mariabackup. Let’s try the installation from repositories.

XtraBackup Installation

On Debian/Ubuntu

$ wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb
$ sudo dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb
$ sudo apt-get update
$ sudo apt-get install percona-xtrabackup-24

On RedHat/CentOS

$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
$ sudo yum install percona-xtrabackup-24

Mariabackup Installation

On Debian / Ubuntu

Mariabackup is part of MariaDB Server starting with MariaDB 10.1.23.

$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
$ sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu bionic main'
$ sudo apt-get update
$ sudo apt-get install mariadb-server-10.1

On CentOS / RedHat

$ sudo vi /etc/yum.repos.d/MariaDB.repo
[mariadb]
name=MariaDB
baseurl=http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
$ sudo yum install MariaDB-backup

Configuration

Both Xtrabackup and Mariabackup read the [mysqld] and [xtrabackup] sections of any MySQL configuration file, in that order. In this way, it can read MySQL parameters, such as datadir or InnoDB parameters.

We can modify the parameters included in the [mysqld] section by modifying their value in [xtrabackup], as we mentioned before, they are read in order, so the last thing we have in [xtrabackup] will have priority.

[mysqld]
datadir=/data/datadir
[xtrabackup]
target_dir=/backups/

The user with the minimum privileges required to full backups would be RELOAD, LOCK TABLES, PROCESS and REPLICATION CLIENT:

mysql> CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'Password';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';

And then, you can add this user in the MySQL configuration files:

[xtrabackup]
user=backupuser
password=Password

Also, you can use Xtrabackup or Mariabackup to perform the State Snapshot Transfers when you are using a Percona XtraDB Cluster or MariaDB Galera Cluster. You must set the wsrep_sst_method and the wsrep_sst_auth variables in the configuration files:

[mysqld]
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=backupuser:Password

Or

[mysqld]
wsrep_sst_method=mariabackup
wsrep_sst_auth=backupuser:Password

Usage

Since Mariabackup is based on XtraBackup, it can be used similarly.

Now let’s see an example using both methods to create, prepare and restore a full backup.

Creating a backup

To create a new backup with XtraBackup or Mariabackup, you need to add the –backup and –target-dir options to the command line:

$ xtrabackup --backup --target-dir=/backups/

Or

$ mariabackup --backup --target-dir=/backups/

The target dir, where the backup will be stored, can be specified in the MySQL configuration files. The backup process will not overwrite existing files. If the file exists the backup will fail.

Transaction log of lsn (9755450) to (9755467) was copied.
181122 23:02:44 completed OK!

If everything went fine, the last line that you see should be “completed OK!“. You can cancel the backup at any time, as it doesn’t modify the database’s content.

[root@MySQL1 ~]# ls -l /backups/
total 102448
-rw-r----- 1 root root       488 Nov 22 23:02 backup-my.cnf
-rw-r----- 1 root root       482 Nov 22 23:02 ib_buffer_pool
-rw-r----- 1 root root 104857600 Nov 22 23:02 ibdata1
drwxr-x--- 2 root root      4096 Nov 22 23:02 mysql
drwxr-x--- 2 root root      4096 Nov 22 23:02 performance_schema
drwxr-x--- 2 root root      4096 Nov 22 23:02 sakila
drwxr-x--- 2 root root     12288 Nov 22 23:02 sys
-rw-r----- 1 root root        64 Nov 22 23:02 xtrabackup_binlog_info
-rw-r----- 1 root root       113 Nov 22 23:02 xtrabackup_checkpoints
-rw-r----- 1 root root       533 Nov 22 23:02 xtrabackup_info
-rw-r----- 1 root root      2560 Nov 22 23:02 xtrabackup_logfile

This should be the content of your backup. It could change depending on your databases.

Preparing a backup

When you have your backup created with XtraBackup or Mariabackup, you need to prepare it to be restored. Data files aren’t consistent until they’ve been prepared, because they were copied at different times during the duration of the backup. If you try to restore it and start your database, it will detect corruption and crash itself to prevent you from running on inconsistent data.

To prepare the backup, you need to run the command xtrabackup or mariabackup with –prepare option and specify the target dir where is stored the backup.

$ xtrabackup --prepare --target-dir=/backups/

Or

$ mariabackup --prepare --target-dir=/backups/
InnoDB: Shutdown completed; log sequence number 9757224
181122 23:05:29 completed OK!

The last line that you see should be “Shutdown completed; log sequence number xxxxxxx” and “completed OK!” if everything went fine. It’s not recommended to cancel the prepare process because it may cause data file corruption and the backup will become unusable.

If you want to use this backup with an incremental backup later, you should use the –apply-log-only option when preparing it, or you will not be able to do it.

Restoring a Backup

After preparing the backup, you can use the restore option with the parameters –copy-back or –move-back, to copy or move the backup to the datadir. If you don’t have enough disk space, you probably should use the move option. Also, we need to specify the target dir where the backup is stored. Keep in mind that the datadir must be empty and the database service should be down before restoring the backup.

$ xtrabackup --copy-back --target-dir=/backups/

Or

$ mariabackup --copy-back --target-dir=/backups/

It will first copy/move the MyISAM tables and indexes, InnoDB tables and indexes next and the log files at last. It will preserve file’s attributes when copying them, you may have to change the files’ ownership to mysql before starting the database server, as they will be owned by the user who created the backup.

$ sudo chown -R mysql:mysql /var/lib/mysql

There are several parameters to use with Xtrabackup and Mariabackup. You can check these parameters here for XtraBackup, and here for Mariabackup.

Managing Your Backups on ClusterControl

As we saw above, running a backup is not rocket science. It can also be scheduled with cron (but beware of silent failures!). However, a script to regularly create backups is not a backup management solution. You need a way to report on your backups, and alert on failures. Now, configuring backups in your environment and seeing the backups work without error does not mean everything is good. You might have heard about Schrödinger’s backup, which states that the condition of any backup is unknown until a restore is attempted. Because the worse thing that can happen is a disaster and you realize the backups are wrong for some reason. You try to restore the files that were backed up, and it does not restore what you think you backed up, or it does not restore at all! Then there are things like moving backup files offsite, e.g. to external cloud storage, for disaster recovery. Encryption and handling of keys are important for security. Retention of local as well as external/archived backups also need to be managed.

Let’s see how ClusterControl can help.

If you want to use the ClusterControl Backup feature, go to ClusterControl -> Select Cluster -> Backup, and there you can see your current backups, create or schedule a new one.

Using the create or schedule option, we can choose both, XtraBackup or Mariabackup method. In the same section, we can choose the server from which to take the backup, enable partial backup, choose where you want to store the backup and if you want to upload the backup to the cloud (AWS, Azure or Google Cloud).

Then, we can select backup parameters like compression, encryption and retention.

And these should be the commands that ClusterControl will run for you:

[16:37:58]: 192.168.100.120: Launching ( LC_ALL=C /usr/bin/innobackupex --defaults-file=/etc/my.cnf --galera-info --parallel 1 --stream=xbstream --no-timestamp . | gzip -6 - > /root/backups/BACKUP-13/backup-full-2018-11-14_193757.xbstream.gz ) 2>&1.

Or

[16:29:57]: 192.168.100.131: Launching ( LC_ALL=C /usr/bin/mariabackup --defaults-file=/etc/my.cnf --backup --galera-info --parallel 1 --stream=xbstream --no-timestamp | gzip -6 - > /root/backups/BACKUP-11/backup-full-2018-11-14_192957.xbstream.gz ) 2>&1.

This command could be different depending on which parameters you chose.

As we could see, ClusterControl is a good friend if we want to use XtraBackup or Mariabackup. We can run complex backup commands in an easy way, by selecting the options from the ClusterControl UI.
ClusterControl support both full or incremental backup, so we can configure all our backup strategy from a friendly UI.

Conclusion

When backing up a MariaDB server, it is recommended to use the Mariabackup tool. However, if for some reason you prefer to use XtraBackup, you still can. But you need to keep in mind the restrictions that apply, as we have noted in this blog. And finally, we discussed how a script to backup a database is not the same thing as a backup management solution, and had a quick look at ClusterControl.

Let us know if we missed any differences between the XtraBackup and MariaBackup.

---

^* The non-blocking backups are supported for InnoDB, XtraDB, and HailDB storage engines. The following storage engines can be backed up by briefly pausing writes at the end of the backup: MyISAM, Merge, and Archive, including partitioned tables, triggers, and database options.