MariaDB Backups: What is mariabackup?

It is essential that you choose the right technology for backing up your database as you don’t want to take any risk with your important production data. Also, you want to select a backup tool that supports all your needs for backing up the database. If you want to take backups of MySQL database then Percona XtraBackup may be the right choice for you as it supports hot backups and some other good backup features. However, if you are planning to take the backups of the MariaDB database, XtraBackup may not be the good choice for you as it becomes incompatible for the MariaDB version 10.3 or later. In this case, Mariabackup is the right tool for you.

In this article, we will learn about the Mariabackup tool and its features. We will also go through its installation process and some important commands to take backups and restore them using it.

Overview of Mariabackup

From version 10.1 MariaDB launched new features, such as Data-at-Rest Encryption and InnoDB Page compression. All existing backup tools did not support these features, so MariaDB came up with a new backup solution called Mariabackup.

Mariabackup is an open-source tool supported by MariaDB to take backups of MariaDB/MySQL databases with either InnoDB or MyISAM engines. Mariabackup was forked from Percona XtraBackup version 2.3.8. Therefore, it supports all the features of Xtrabackup such as hot backups. On top of that, it also supports all the new features that are exclusive to MariaDB. Mariabackup also comes with the Microsoft Windows support which is not the case with the Percona Xtrabackup.

Installation of Mariabackup

Linux systems

MariaDB Corp. provides a shell script to set up the MariaDB Package Repository in your system. This will be used to install Mariabackup using the package manager. Run the following command to add the MariaDB package repository.

$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

Once done, you can use either of the following commands, based on your Linux system, to install the Mariabackup tool.

$ sudo yum install MariaDB-backup

Or

$ sudo apt-get install mariadb-backup

Windows systems

Mariabackup utility is already included in the MSI and ZIP packages of MariaDB for Windows. While installing MariaDB, you can select Backup utilities to install it.

In any case, it provides two ways of taking backups of your database: Full backup and Incremental backup. The Full backup will create a backup of your whole database whereas the incremental backup will create the backup of only changed data by updating the previous backup.

Before we go any further, we need to create a backup user with all the required permissions. Run the following command in your MariaDB shell.

MariaDB > GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON . TO 'backupuser'@'localhost' IDENTIFIED BY 'backup123';

We will use the above user to perform the backup or restore operations on the MariaDB instance.

Full Backup and Restore

Backup Process

To take the backup of your database, you need to run the Mariabackup with –backup option to indicate it to backup and –target-dir option to specify the location of the backup directory. Make sure the provided target directory is empty before you run the command and it has enough space to store the backup.

$ mariabackup --backup --target-dir=/tmp/mariadb/backup/ --user=backupuser --password=backup123

If you want to create the compressed backup of your database, you can use –stream option as follows.

$ mariabackup --user=backupuser --password=backup123 --backup --stream=xbstream | gzip > backup_22_03_2020.gz

You can use the following command to decompress the files.

$ gunzip -c backup_22_03_2020.gz | mbstream -x

If you want, you can cancel the backup process by terminating the above command. It won’t affect your actual database.

Preparation Process

When you create the backup of your database, it won’t be consistent as all the data files are copied at various times during the backup process. Therefore, you can’t directly restore your database. You have to prepare the data files to make it consistent.

$ mariabackup --prepare --target-dir=/tmp/mariadb/backup/

Now we are ready to restore our database backup.

Restoration Process

To restore our database from a full backup, you can use either of –copy-back or –move-back options. The first one will retain the original files whereas the latter option will move the backup files to the data directory of your database. Before you run the following command, make sure that the MariaDB process is stopped and the data directory is empty.

$ mariabackup --copy-back --target-dir=/tmp/mariadb/backup/

Once the data restore completes, the data directory and its files will be owned by the “backupuser” as we used that user in the backup process. You need to change it back to mysql user along with the group.

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

Now, you can start the MariaDB server with the new data.

Incremental Backup and Restore

Backup Process

Before you take the incremental backup, you have to create a full backup of your database as you can only create the incremental backup on top of the full backup. So first, run mariabackup command with –backup option as follows:

$ mariabackup --backup --target-dir=/tmp/mariadb/backup/ --user=backupuser --password=backup123

Now, you can take incremental backups on top of this backup as many times as you want.

$ mariabackup --backup --target-dir=/tmp/mariadb/backup/increment/ --incremental-basedir=/tmp/mariadb/backup/ --user=backupuser --password=backup123

Here, –target-dir option gives the location of all incremental changes and –incremental-basedir provides the location of the full backup of the database. The target directory should be empty or non-existent before you run this command.

When you want to take the incremental backup again with the previous changes, you can run the backup command as follows:

$ mariabackup --backup --target-dir=/tmp/mariadb/backup/incrementNew/ --incremental-basedir=/tmp/mariadb/backup/increment/ --user=backupuser --password=backup123

Preparation Process

Before you restore the incremental backup in the current database, you need to apply the incremental backup to the full backup which you created before taking an incremental backup. This can be done by using –prepare and –apply-log-only options. Run the following command to prepare the full backup for the restoration process.

$ mariabackup --prepare --apply-log-only --target-dir=/tmp/mariadb/backup/

Now prepare the incremental backup by applying the same options to incremental backup.

$ mariabackup --prepare --apply-log-only --target-dir=/tmp/mariadb/backup/ --incremental-dir=/tmp/mariadb/backup/increment/

Now your full backup is in sync with the first incremental backup. Repeat the same step for syncing it with the second incremental backup we took.

$ mariabackup --prepare --apply-log-only --target-dir=/tmp/mariadb/backup/ --incremental-dir=/tmp/mariadb/backup/incrementNew/

Restoration Process

Once your full backup is in sync with all the incremental changes, you can restore it the same way as you restored the full backup. Once the data is restored, don’t forget to change the permissions of the data directory.

$ mariabackup --copy-back --target-dir=/tmp/mariadb/backup/

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

That’s it! These are the basic commands which you can use to take full or incremental backups using the Mariabackup tool. Overall, it’s a good open-source tool provided by the MariaDB which supports some native features of MariaDB. Therefore, if you want to take backups of the MariaDB server, the recommended choice is Mariabackup.