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

Find the binary log position to restore to.

When MariaDB Backup runs on a MariaDB Server with binary logs 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

2

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

3

Restore the backup.

Restore from the backup as explained here.

4

Start the database server.

Start MariaDB Server.

systemctl start mariadb

5

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

6

Run the script.

In the new data directory, run the script created in the previous step:

$ mariadb < mariadb-binlog.sql

Point-in-Time Recovery Using InnoDB Log Archiving

This functionality is available from MariaDB 13.0.

While binary logs are the standard method for point-in-time recovery, you can also use InnoDB log archiving. This feature allows for recovery to a specific Log Sequence Number (LSN) by replaying archived InnoDB write-ahead logs. This is particularly useful for InnoDB-only deployments or for recovery scenarios where binary logs may not be available.

To perform a point-in-time recovery using archived logs:

1

Enable log archiving.

Before a recovery is necessary, ensure that log archiving is active. This is done by setting the innodb_log_archive system variable to ON :

SET GLOBAL innodb_log_archive=ON;

2

Identify the recovery target.

Determine the target LSN you wish to recover to. You can find the latest archived LSN by checking the INNODB_LSN_ARCHIVED status variable:

SELECT VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'INNODB_LSN_ARCHIVED';

3

Stop the MariaDB Server.

Terminate the mariadbd process before initiating the recovery – for example:

$ sudo systemctl stop mariadb

4

Start the server with recovery parameters.

Invoke the server with the innodb_log_recovery_start and innodb_log_recovery_target parameters to define the recovery window.

• innodb_log_recovery_start: Set this to the LSN of your last known good backup.

• innodb_log_recovery_target: Set this to the LSN of your recovery point objective.

$ mariadbd --innodb_log_recovery_start=12288 --innodb_log_recovery_target=4194304

5

Verify the state.

Once the server reaches the target LSN, the recovery process stops. You can then verify the data integrity.

Point-in-time recovery for DDL (Data Definition Language) operations may be limited as .frm files are not tracked by the InnoDB log.