Incremental Backup and Restore with MariaDB Backup

When using MariaDB Backup, you have the option of performing a full or incremental backup. Full backups create a complete copy in an empty directory while incremental backups update a previous backup with new data. This page documents incremental backups.

InnoDB pages contain log sequence numbers, or LSN's. Whenever you modify a row on any InnoDB table on the database, the storage engine increments this number. When performing an incremental backup, MariaDB Backup checks the most recent LSN for the backup against the LSN's contained in the database. It then updates any of the backup files that have fallen behind.

Backing up the Database

In order to take an incremental backup, you first need to take a full backup. This creates the backup files that MariaDB Backup increments. You can take a full backup by calling MariaDB Backup with the --backup command option. Use the --target-dir option to define where you want to place the backup files. The target directory must be empty.

To take a full backup, run the following command:

$ mariabackup --backup --target-dir /var/mariadb/backup/ \
      --user backup_user --password backup_passwd

This backs up all databases into the target directory /var/mariadb/backup. If you look in that directory at the xtrabackup_checkpoints file, you can see the LSN data provided by InnoDB.

$ vi /var/mariadb/backup/xtrabackup_checkpoints

backup_type = full-backuped
from_lsn = 0
to_lsn = 1635102
last_lsn = 1635102
recover_binlog_info = 0

Incrementing Backups

Once you have created a full backup on your system, you can increment that backup as much as you need to keep it up to date with MariaDB Server.

In order to perform an incremental backup, you need to call MariaDB Backup with the --backup command option. Use the --target-dir option to define the directory where you want to store the delta files. The target directory must be empty. For the --incremental-basedir option, use the path to the full backup taken above.

$ mariabackup --backup --target-dir /var/mariadb/inc1/ \
      --incremental-basedir /var/mariadb/backup/

This command creates a series of backup deltas in /var/mariadb/inc1. You can find a similar xtrabackup_checkpoints file in this directory, with the updated LSN values.

$ vi /var/mariadb/inc1/xtrabackup_checkpoints

backup_type = full-backuped
from_lsn = 1635102
to_lsn = 1635114
last_lsn = 1635114
recover_binlog_info = 0

You can then use the target directory as the base for another incremental backup.

$ mariabackup --backup --target-dir /var/mariadb/inc2/ \
      --incremental-basedir /var/mariadb/inc1/

Restoring from Incremental Backups

Following the above steps, you have three backups in /var/mariadb: The first is a full backup, the others are increments on this first backup. In order to restore a backup to the database, you first need to apply the incremental backups to the base full backup. This is done using the --prepare command option with the --apply-log-only option.

First, prepare the base backup:

$ mariabackup --prepare --target-dir /var/mariadb/backup \
      --user backup_user --password backup_passwd \
      --apply-log-only

Then, apply the increments to the base full backup:

$ mariabackup --prepare --target-dir /var/mariadb/backup \
      --user backup_user --password backup_passwd \
      --incremental-dir /var/mariadb/inc1 \
      --apply-log-only

Running this command brings the base full backup, that is, /var/mariadb/backup, into sync with the changes contained in the first incremental backup. Repeat the above step to apply changes in the /var/mariadb/inc2 backup to the base.

Once you've applied all incremental backups to the base, you can use the --copy-back or --move-back options to restore the backup to the database.

$ mariabackup --copy-back --target-dir /var/mariadb/backup/ \
      --user backup_user --password backup_passwd

Bear in mind, the data directory must be empty before restoring the backup.