Incremental Backup and Restore with Mariabackup
Contents
When using Mariabackup, 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, Mariabackup 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 Server
In order to take an incremental backup, you first need to take a full backup. In order to back up the database, you need to run Mariabackup with the --backup
option to tell it to perform a backup and with the --target-dir
option to tell it where to place the backup files. When taking a full backup, the target directory must be empty or it must not exist.
To take a backup, run the following command:
$ mariabackup --backup \ --target-dir=/var/mariadb/backup/ \ --user=mariabackup --password=mypassword
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.
For example:
backup_type = full-backuped from_lsn = 0 to_lsn = 1635102 last_lsn = 1635102 recover_binlog_info = 0
Backing up the Incremental Changes
Once you have created a full backup on your system, you can also back up the incremental changes as often as you would like.
In order to perform an incremental backup, you need to run Mariabackup with the --backup
option to tell it to perform a backup and with the --target-dir
option to tell it where to place the incremental changes. The target directory must be empty. You also need to run it with the --incremental-basedir
option to tell it the path to the full backup taken above. For example:
$ mariabackup --backup \ --target-dir=/var/mariadb/inc1/ \ --incremental-basedir=/var/mariadb/backup/ \ --user=mariabackup --password=mypassword
This command creates a series of delta files that store the incremental changes in /var/mariadb/inc1
. You can find a similar xtrabackup_checkpoints
file in this directory, with the updated LSN values.
For example:
backup_type = incremental from_lsn = 1635102 to_lsn = 1635114 last_lsn = 1635114 recover_binlog_info = 0
To perform additional incremental backups, you can then use the target directory of the previous incremental backup as the incremental base directory of the next incremental backup. For example:
$ mariabackup --backup \ --target-dir=/var/mariadb/inc2/ \ --incremental-basedir=/var/mariadb/inc1/ \ --user=mariabackup --password=mypassword
Combining with --stream
output
When using --stream
, e.g for compression or encryption using external tools, the xtrabackup_checkpoints
file containing the information where to continue from on the next incremental backup will also be part of the compressed/encrypted backup file, and so not directly accessible by default.
A directory containing an extra copy of the file can be created using the --extra-lsndir=...
option though, and this directory can then be passed to the next incremental backup --incremental-basedir=...
, for example:
# initial full backup $ mariabackup --backup --stream=mbstream \ --user=mariabackup --password=mypassword \ --extra-lsndir=backup_base | gzip > backup_base.gz # incremental backup $ mariabackup --backup --stream=mbstream \ --incremental-basedir=backup_base \ --user=mariabackup --password=mypassword \ --extra-lsndir=backup_inc1 | gzip > backup-inc1.gz
Preparing the Backup
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. In MariaDB 10.1, you would also have to use the the --apply-log-only
option.
In MariaDB 10.2 and later, perform the following process:
First, prepare the base backup:
$ mariabackup --prepare \ --target-dir=/var/mariadb/backup
Running this command brings the base full backup, that is, /var/mariadb/backup
, into sync with the changes contained in the InnoDB redo log collected while the backup was taken.
Then, apply the incremental changes to the base full backup:
$ mariabackup --prepare \ --target-dir=/var/mariadb/backup \ --incremental-dir=/var/mariadb/inc1
Running this command brings the base full backup, that is, /var/mariadb/backup
, into sync with the changes contained in the first incremental backup.
For each remaining incremental backup, repeat the last step to bring the base full backup into sync with the changes contained in that incremental backup.
Restoring the Backup
Once you've applied all incremental backups to the base, you can restore the backup using either the --copy-back
or the --move-back
options. The --copy-back
option allows you to keep the original backup files. The --move-back
option actually moves the backup files to the datadir
, so the original backup files are lost.
- First, stop the MariaDB Server process.
- Then, ensure that the
datadir
is empty.
- Then, run Mariabackup with one of the options mentioned above:
$ mariabackup --copy-back \ --target-dir=/var/mariadb/backup/
- Then, you may need to fix the file permissions.
When Mariabackup restores a database, it preserves the file and directory privileges of the backup. However, it writes the files to disk as the user and group restoring the database. As such, after restoring a backup, you may need to adjust the owner of the data directory to match the user and group for the MariaDB Server, typically mysql
for both. For example, to recursively change ownership of the files to the mysql
user and group, you could execute:
$ chown -R mysql:mysql /var/lib/mysql/
- Finally, start the MariaDB Server process.