Point-In-Time Recovery (InnoDB Log Archiving)

This functionality is available from MariaDB 13.0.

When InnoDB log archiving is enabled, the server retains a continuous history of write-ahead log records across multiple ib_lsn.log files. You can use this history to perform point-in-time recovery (PITR) to a specific Log Sequence Number (LSN), by replaying the archived InnoDB write-ahead logs at server startup.

This is an alternative to PITR via mariadb-backup and binary logs, useful in InnoDB-only deployments or in recovery scenarios where binary logs are not available.

No shipped backup tool yet generates or restores backups in the innodb_log_archive=ON format — mariadb-backup does not support it and fails when the server is running with innodb_log_archive=ON. A backup tool that uses this format is being worked on. Until it ships, this procedure assumes you already have an externally-prepared restore that contains a consistent set of ib_lsn.log files alongside the InnoDB data files.

Recovery Parameters

Two startup parameters define the range of the log replay:

innodb_log_recovery_start — the LSN at which recovery begins. Set this to the end LSN of the previous restore when applying an incremental restore. The default 0 starts from the latest completed checkpoint, which is guaranteed to live in one of the last two ib_lsn.log files.
innodb_log_recovery_target — the LSN at which recovery ends (the recovery point objective). When this is non-zero, persistent InnoDB tables become read-only and no log writes are allowed during the recovery session. The default 0 replays the log to its end.

You can identify candidate LSNs from the Innodb_lsn_archived status variable on the source server, and from the file names in the data directory (each ib_lsn.log file name encodes the LSN at file offset 0x3000).

The data files in the restore must correspond to an LSN that lies between Innodb_lsn_archived (the first checkpoint in the first archived log file) and the end LSN of the last archived log file. To extend the available recovery range, copy additional archived log files in from the source server — no further copying of data files is required.

Procedure

Prepare the restore.

Place the restored InnoDB data files and the corresponding ib_lsn.log archive files in the data directory. With innodb_log_archive=ON, the server refuses to start if a legacy ib_logfile0 exists, so make sure it is not present.

Stop the MariaDB Server (if it is running).

$ sudo systemctl stop mariadb

Start the server with recovery parameters.

Pass innodb_log_recovery_start and innodb_log_recovery_target either on the command line or in the configuration file, alongside innodb_log_archive=ON. While innodb_log_recovery_target is non-zero, the server replays the archived log up to the target LSN and then keeps the InnoDB tables read-only.

Verify the state.

Once the server reaches the target LSN, the recovery process stops. Inspect Innodb_lsn_archived, Innodb_lsn_current, and the contents of the recovered tables to confirm the data is at the expected logical point.

Resume normal operation.

To return to normal read-write operation, restart the server without innodb_log_recovery_target set.

No data file may carry an LSN newer than innodb_log_recovery_target. Crash recovery's role is to bring every database page to the same LSN. If any data file is newer than the target, recovery completes in an inconsistent state where some pages carry an LSN past the target — the database is corrupted. The server cannot validate every such impossible target, and the resulting corruption may not surface until the affected pages are accessed (see MDEV-34830). See innodb_log_recovery_target for details.

Point-in-time recovery of DDL operations is limited even for an InnoDB-only deployment, because part of the data dictionary is stored in .frm files whose creation is not covered by the InnoDB write-ahead log.

Recovery Parameters

Procedure

See Also