Comparing MariaDB Enterprise Backup to ClusterControl Backup Management

MariaDB Enterprise Backup is a backup solution from MariaDB Corporation with a number of features such as non-blocking backups, full backup, incremental backup, partial backup and Point in Time Recovery.

We often get questions about the differences between MariaDB Backup and ClusterControl’s backup management features. So this is what this blog is about.

Creating backups vs managing them

MariaDB Backup is a fork of Percona XtraBackup, and is a tool to take physical backups of MariaDB server. It allows you to do things like full, incremental, partial backups. One can also perform point in time recovery with the help of binary logs. According to the documentation, the ‘Enterprise’ version of MariaDB backup provides “DDL statement tracking, which reduces lock-time during backups”.

ClusterControl supports MariaDB Backup as a backup method for MariaDB. It provides a graphical user interface to schedule full backups, incremental backups and partial backups, and perform recovery of backup files or also automates point in time recovery. In addition, ClusterControl provides features like encryption, compression, upload to the cloud storage (Azure, AWS, Google Cloud) and automatic verification of backups to ensure they are recoverable. 

Full Backup and Restore

To perform full backup using MariaDB Enterprise Backup, you can use mariabackup command utilities. There are 4 parameter inputs after the mariabackup command. The parameter are :

• Backup – this is used for backup the database using mariabackup utilities. 
• Prepare – to make a point-in-time consistent backup, you need to prepare the backup after the raw backup was executed.
• Copy-back – used to restore the extracted backup to the default data directory of mysql. It will copy the backup to the mysql directory, without removing the original backup.
• Move-back –  used to restore the extracted backup to the mysql data directory by moving all backup directories.

If you want to backup and restore, you just pass the mandatory parameter after mariabackup command. For a full backup command, below is a sample script using MariaDB Backup.

mariabackup --backup --target-dir=/backup/full/ --user=bkpuser --password=p4sswordb4ckup

There are some options you need to define, such as –target-dir, which is the target location for backup files, –user, used for credential users for backup, and –password for the credential backup password. 

To make the backup become point-int-time consistent, you must run the prepare after the full backup is finished. The data files are not consistent until you run the prepare, it is because when you run the backup, the data files were copied at different points in time during the backup.

To run prepare backup:

mariabackup --prepare --target-dir=/backup/full

After you run prepare, it will make the backup ready to be restored. You will see the message on the last line as below,  when the prepare was successful.

InnoDB: Shutdown completed; log sequence number 9553231

You can run the restore command using copy-back. Here is the sample script to restore the backup:

mariabackup --copy-back --target-dir=/backup/full

You can put the above script in a shell script command and give executable permission, configure it on the operating system scheduler.

Backup and Restore using ClusterControl Backup Management is very easy to use. ClusterControl supports logical backup and physical backup. For logical backup, ClusterControl uses mysqldump and for physical backup uses mariabackup full backup and incremental. 

There are two options on how you want to do the backup; you can create the backup directly or you can schedule the backup.

You can also enable some options like encryption, compression, parallel copy thread as shown below :

Restoring the backup is as easy as the backup was created. You just need to select the full backup file that you want to restore.

There are two options on how you want to restore the backup; you can restore the backup to the nodes where the backup was taken or you can restore the backup to a dedicated standalone host.

Incremental Backup and Restore

Taking a full backup of a very large database will be time consuming and resource intensive. Incremental backup is used to perform backup of the changes after the last full backup was taken. 

When incremental backup is running, MariaDB Enterprise Backup will compare previous full backup or incremental backup to find the last changes.

mariabackup --backup --incremental-basedir=/backup/full --target-dir=/backup/incr --user=bkpuser  --password=p4sswordb4ackup

Before you perform the incremental backup, you need to ensure that full backup has been prepared. After that, you can run the incremental backup, applying to the last full backup.

mariabackup --prepare  --target-dir=/backup/full --incremental-dir=/backup/incr

After the incremental backup has been applied to the full backup, the full backup directory will now have all the backup data prepared.

Restoring the prepared full backup with all the incremental changes can be done through :

mariabackup --copy-back --target-dir=/backup/full

To perform incremental backup in ClusterControl, you can choose the mariabackup incremental. You need to have the full prepared backup before doing the incremental backup.

ClusterControl will automatically find the nearest full backup when you run the incremental backup. And for restoring the backup, you can choose the full prepared backup and restore. It will prompt you how you want to restore the backup, either on the node or standalone host. It will restore the backup including incremental changes.

Partial Backup and Restore

Partial backup specifies which database or table you want to backup. You can either choose a list of databases and tables to back up, or you can exclude some of databases and tables from the backup. The options include : –-databases, –databases-exclude, –tables, –tables-exclude

Below is a sample script to do the partial backup, for the card_data table.

mariabackup --backup --target-dir=/backup/partial --user=bkpuser --password=p4sswordb4ckup --tables=card_data

You still need to prepare the full partial backup to make the backup point-in-time consistent by running the below command : 

mariabackup --prepare --export --target-dir=/backup/partial

Performing partial restore is very different compared to restoring full backup and incremental backup. You need to prepare the tables and database in the running MariaDB Server, and then manually copy the data files into mysql data directory.

For example, you want to do a partial restore for the card_data table (non-partitioned table).

• Create the empty table of card_data with the same structure in the target database 
• Run the DISCARD tablespace on the table card_data.

  ALTER TABLE carddb.card_data DISCARD TABLESPACE;

• Copy the data files into mysql data directory 

  cp /backup/partial/carddb/card_data.* /var/lib/mysql/carddb

• Change the owner of files become mysql

  chown mysql:mysql /var/lib/mysql/carddb/card_data.*

• Last thing, import the tablespace: 

  ALTER TABLE carddb.card_data IMPORT TABLESPACE;

Partial Backup in ClusterControl is really straightforward, you just need to enable the Partial Backup option. It will give you the option to include or exclude database and tables as shown below :

The next part is similar to the full backup and incremental backup, you can choose settings like encryption and compression.

Restoring the partial backup is exactly the same as when we restore full backup. You just need to choose the partial backup, and the rest will be handled by ClusterControl.

Point in Time Recovery

Restoring the full backup or incremental backup does give you a backup from the time the backup was taken, but it does not give you any data that came after the backup was taken. These changes would be in the binary log. When you perform the prepared backup with binlog enabled, there will be a file called xtrabackup_binlog_info. The file contains a binary log file and position of the last sequence number.

You can perform the point in time recovery by extracting the changes to SQL, like after the restore has been done. You can run mysqlbinlog to extract the specific time in the source database node, and apply the SQL in the target/restored database node.

Point in Time Recovery (PITR) in ClusterControl can be enabled as shown below:

You need to define until what point to recover, there are two options supported which are time based or position based. For time based, you just need to fill the exact time when the data will be restored. For the position based, you need to fill the binlog name and position. The rest of the restore is similar.

Conclusion

That’s it for now. As we have seen above, MariaDB Backup is a nice tool with lots of options. ClusterControl provides an easy to use GUI to perform the backup procedures. It also adds a number of features like encryption, compression, scheduling, retention management and automatic backup verification.