Moving to MariaDB Backup

In my previous blog post that was about upgrading to the latest version of Jira together with the latest version of MariaDB, I briefly mention that when upgrading to MariaDB Server 10.3 one should look at how backups are done. With MariaDB Server 10.3 comes MariaDB Backup, which supports the newest features of MariaDB Server. It’s also available on all of the same platforms as MariaDB Server and it’s distributed together with the server.

If you have been backing up with XtraBackup and try to run those same commands against MariaDB Server 10.3, you’ll get an error message:

$ innobackupex ~/backup_to_dir --user=username --password=password
...
InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.9.

The first sentence in the last message is what it is about. XtraBackup doesn’t understand the file(s) in 10.3’s redo log. Notice that we used an old version of XtraBackup and that’s why the command is still innobackupex.

Because of this and some other reasons mentioned below, MariaDB Server, now comes with MariaDB Backup that supports the new redo log format. With MariaDB Backup you have the same functionality that you’re used to with XtraBackup, but with the support for the improved redo log format and support for MariaDB’s data-at-rest encryption. Another very much requested aspect is that MariaDB Backup is available for Windows as well, which XtraBackup isn’t. If you’re interested to know more about the redo log change, read this.

In the beginning of this post I mention that we recently upgraded Jira to the newest version and MariaDB Server to 10.3. In that older environment we used XtraBackup. To get backups for MariaDB Server 10.3 we had to update our backup scripts to use MariaDB Backup.

After switching to MariaDB Backup the backup command looks like this:

$ mariabackup --backup --target-dir /backup/to/dir --user=username --password=password --parallel=4

We had to change the command itself from being innobackupex to mariabackup and we added two options; –backup to tell mariabackup that we want it to create a backup and –target-dir to specify that the given directory is where the backup files should go. It should be noticed that if we would have been using a more recent version of the XtraBackup the command line options would have been completely compatible with XtraBackup, so the only thing to change then would have been the command itself from xtrabackup to mariabackup.

To be sure that the backup works we’ll copy it to another server and try to restore it there. To restore a backup we first have to prepare it:

$ mariabackup --prepare --target-dir full-2018-09-11_09-38-32

Notice that I have a full backup of a MariaDB Server instance which will replace anything that would exist in the instance where I’m restoring to. I’ll therefore stop the server and remove any data files it holds.

$ sudo service mariadb stop
$ sudo rm -rf /var/lib/mysql/*

Now the backup files can be put in the data directory of this server instance. It should be done with mariabackup to get it done right. It does some things related to the redo log format explained above.

$ sudo mariabackup --copy-back --target-dir full-2018-09-11_09-38-32

Make sure permissions are correct. In my case normal user and group are in use. Then start the server.

$ sudo chown mysql:mysql /var/lib/mysql -R
$ sudo service mariadb start

Let’s then connect to the server and verify that it has the data it should have. I decided to query for the maximum issue number in Jira.

# connect to mariadb
$ mysql -uusername -ppassword
# query the database
MariaDB [(none)]> USE jiradb
MariaDB [jiradb]> SELECT MAX(issuenum) FROM jiraissue;

Voila! We now have backups working on MariaDB Server 10.3.