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 mariadb_backup_binlog_info (or xtrabackup_binlog_info in older releases) 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/mariadb_backup_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

See Also

• Point-In-Time Recovery (InnoDB log archiving) — an alternative PITR procedure that replays archived InnoDB write-ahead logs instead of binary logs. Available from MariaDB 13.0.

mariadb-backup does not support the innodb_log_archive=ON log format and fails when the server is running with innodb_log_archive=ON. Use the InnoDB log archiving PITR procedure instead in that configuration.

_{This page is licensed: CC BY-SA / Gnu FDL}