Point-In-Time Recovery (PITR, mariadb-backup)

Explains how to restore (recover) to a specific point in time. Point-in-time recovery is often referred to as PITR.

Recovering from a backup can restore the data directory at a specific point in time, but it does not restore the binary log. In a point-in-time recovery, start by restoring the data directory from a full or incremental backup, then use the mysqlbinlog utility to restore the binary log data to a specific point in time.

Run the following commands as root unless indicated otherwise.

1

Prepare the backup.

Prepare the backup as you normally would for a full or incremental backup:

mariabackup --prepare --target-dir=/data/backups/full

2

Find the binary log position to restore to.

When MariaDB Backup runs on a MariaDB Server with binary logs is enabled (which is a prerequisite for PITR), it stores binary log information in the xtrabackup_binlog_info file. Consult this file to find the name of the binary log position to use. In the following example, the log position is 321.

cat /data/backups/full/xtraback_binlog_info

mariadb-node4.00001     321

3

Configure a new data directory.

Update the configuration file (for instance, my.cnf) to use a new data directory.

[mysqld]
datadir=/var/lib/mysql_new

4

Restore to the new data directory.

Restore from the backup to the new data directory:

mariabackup --copy-back --target-dir=/data/backups/full

5

Change the owner to the system user.

Change the owner to the MariaDB Server system user:

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

6

Start the database server.

Start MariaDB Server.

systemctl start mariadb

7

Create a script using mysqlbinlog.

Use the mysqlbinlog utility to create an SQL script, using the binary log file in the old data directory, the start position in the xtrabackup_binlog_info file, and the date and time you want to restore to. Issue the following command as a regular user:

$ mysqlbinlog --start-position=321 \
      --stop-datetime="2019-06-28 12:00:00" \
      /var/lib/mysql/mariadb-node4.00001 \
      > mariadb-binlog.sql

8

Restore the backup.

Run the binary log SQL to restore the databases. Issue the following command as a regular user:

mysql -u root -p < mariadb-binlog.sql