Table of contents of the article:
In the database world, backup is never a trivial topic. Many administrators think they're safe because they have a daily dump, a volume snapshot, or a nightly copy of the data directory. In reality, when working with production systems, the issue isn't just "having a backup," but being able to return to exactly the right time, minimizing data loss and limiting service downtime. This is where PITR comes in, an acronym for Point-in-Time Recovery.
PITR allows you to restore a MariaDB database not simply to the state of the last available backup, but to a specific point in time after the backup.This is crucial when human error, a destructive query, a faulty application update, an accidental deletion, or an application compromise occurs that modifies data in an unintended manner. In all these cases, restoring just the backup may be insufficient: if the backup is from 02:00 AM and the error occurs at 14:36 PM, reverting to 02:00 AM would mean losing over twelve hours of legitimate changes.
With a properly designed PITR, however, you can restore the 02:00 AM backup and then reapply all subsequent changes up to, say, 14:35:59 PM, which is one second before the malicious query. The concept is simple, but requires prior configuration: you cannot improvise a Point In Time Recovery after the incident if the necessary logs have not been produced and preserved.
What is PITR in MariaDB?
MariaDB doesn't perform a "magical" PITR from scratch. The mechanism is based on the union of two elements: a consistent backup and binary logs. The backup represents a snapshot of the database at a specific point in time. The binary logs, on the other hand, record modification events that occurred after that snapshot: inserts, updates, deletes, alter tables, create tables, and, more generally, all operations that change the state of the data.
The logical flow is therefore as follows: take a valid backup, restore it to a clean data directory, identify the binlog location corresponding to the time of the backup, and reapply the binlogs up to the desired point. The stopping point can be defined by date and time, by position in the binary log, or, in more advanced architectures, by GTID. In the most common operational context, especially on single servers or less complex managed environments, restoring by binlog file name, location, and stop-datetime is more than sufficient.
From a practical point of view, the most suitable physical backup tool for MariaDB is mariadb-backup, previously known as mariabackupIt is a tool designed to perform online physical backups, reducing the impact on the service and producing a copy of the data directory that can then be prepared and restored. For reading and replaying binary logs, instead, mariadb-binlog, or in some environments the compatible command mysqlbinlog.
Prerequisite: Enable and maintain binary logs
The first requirement for PITR is to have binary logs enabled. Without binary logs, MariaDB can only be restored to the time of backup, not to a later intermediate point. This is the most common mistake: you implement a backup policy, perhaps even a correct one, but forget that PITR requires persistent change logs.
On a MariaDB server installed on Linux, the configuration can be entered into the server's main configuration file. In AlmaLinux environments with MariaDB packages, for example, it is common to work in /etc/my.cnf.d/server.cnfA minimal example is the following:
[mysqld] server_id=1 log_bin=/var/lib/mysql/mariadb-bin binlog_format=ROW sync_binlog=1 expire_logs_days=14
The parameter server_id This is required when enabling binary logging and is still good practice to set it explicitly. log_bin defines the path and prefix of binlog files. binlog_format=ROW It is generally preferable for recovery purposes, because it records changes at the row level and reduces the ambiguities typical of statement-based logging. sync_binlog=1 improves log durability, at a possible performance cost. expire_logs_days, finally, establishes how many days to keep the binlogs before they automatically expire.
On newer versions it may be preferable to use retention expressed in seconds:
[mysqld] server_id=1 log_bin=/var/lib/mysql/mariadb-bin binlog_format=ROW sync_binlog=1 binlog_expire_logs_seconds=1209600
The value 1209600 corresponds to 14 days. The retention choice must be consistent with the backup frequency and the desired RPO. If you maintain a full daily backup but delete the binlogs after a few hours, the useful window for Point-in-Time Recovery becomes unnecessarily short. In production, it is also advisable to copy the binlogs off the main server, because storing them only on the same disk as the database exposes you to the risk of losing them along with the data directory.
After changing the configuration, the service must be restarted:
systemctl restart mariadb
You can verify that binary logs are enabled with:
SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW BINARY LOGS;
Se log_bin results ON e SHOW BINARY LOGS show at least one binlog file, the technical basis for the PITR is present.
Creating a physical backup with mariadb-backup
A serious PITR strategy starts with consistent physical backups. A SQL dump can be useful in many scenarios, but on large databases it tends to be slow both during export and import. A physical backup with mariadb-backup, instead, copies the actual database files and allows for generally faster restores, especially on large instances.
First of all, it is a good idea to create a user dedicated to backup, avoiding using the main administrative user:
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'PasswordMoltoForteQui'; GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost'; FLUSH PRIVILEGES;
Full backup can be performed in a dedicated directory:
mkdir -p /backup/mariadb/full-$(date +%F) mariadb-backup --backup \ --target-dir=/backup/mariadb/full-$(date +%F) \ --user=backup \ --password='PasswordMoltoForteQui'
The newly produced backup should not be considered immediately ready for restore. As with physical InnoDB backups, a prepare phase is required, which makes the backup consistent by applying the necessary information from internal logs and bringing the files to a restorable state.
mariadb-backup --prepare \ --target-dir=/backup/mariadb/full-2026-06-10
Once the prepare is complete, inside the backup directory you will find an extremely important file: xtrabackup_binlog_infoThis file contains the name of the binary log and the exact location corresponding to the backup.
cat /backup/mariadb/full-2026-06-10/xtrabackup_binlog_info
A typical output might be:
mariadb-bin.000123 456789
This means that after restoring that backup, re-running the binlogs will have to start from the file mariadb-bin.000123 and from the position 456789This information should be kept with the backup, because it represents the link between the snapshot of the datadir and the subsequent flow of changes.
Restore the backup to a clean data directory
Recovery shouldn't be performed blindly on the production server. In the event of a real incident, the most prudent procedure is to first restore to a separate machine or isolated environment, validate the recovered data, and only then decide how to return to production. This approach avoids permanently overwriting data that may still be useful for analysis, partial extractions, or further recovery attempts.
On a restore server, after stopping MariaDB, you can discard the existing datadir and prepare an empty one:
systemctl stop mariadb mv /var/lib/mysql /var/lib/mysql.pre-pitr.$(date +%F-%H%M%S) mkdir /var/lib/mysql chown mysql:mysql /var/lib/mysql
At this point, copy the prepared backup into the data directory:
mariadb-backup --copy-back \ --target-dir=/backup/mariadb/full-2026-06-10 chown -R mysql:mysql /var/lib/mysql systemctl start mariadb
After booting, MariaDB will be in the exact state of the full backup. We haven't reached the desired point in time yet; we've simply rebuilt the baseline. The next step is to reapply the binary logs.
Apply binary logs until desired time
Suppose we want to recover the database up to June 10, 2026, at 14:35:59 PM. Let's also assume that the error occurred at 14:36:12 PM, perhaps due to a DROP TABLE or of an DELETE without clause WHEREOur goal is to reapply all valid events that occurred after the backup, stopping short of the destructive operation.
If the file xtrabackup_binlog_info indicated:
mariadb-bin.000123 456789
and the original binlogs have been preserved in /var/lib/mysql.pre-pitr.2026-06-10-150000/, we can generate a recovery SQL file like this:
mariadb-binlog \ --start-position=456789 \ --stop-datetime="2026-06-10 14:35:59" \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000123 \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000124 \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000125 \ > /root/pitr-recovery.sql
The resulting file can then be applied to the restored database:
mariadb < /root/pitr-recovery.sql
This operation replays the events contained in the binlogs up to the specified stop date. The final database will not be the one from the backup, but the one resulting from the backup plus all subsequent changes up to the selected time.
In sensitive scenarios, however, it's often best not to rely solely on the time. Two transactions may be very close together, or the server clock may introduce ambiguity. For this reason, whenever possible, it's best to inspect the binlog and pinpoint the precise location of the event to be avoided.
mariadb-binlog \ --base64-output=DECODE-ROWS \ -vv \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000124 \ | less
With this mode, you can read row-based events in a more understandable form, search for the malicious operation, and note the immediately preceding position. You can then use --stop-position in place of --stop-datetime:
mariadb-binlog \ --start-position=456789 \ --stop-position=987654 \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000123 \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000124 \ > /root/pitr-recovery.sql mariadb < /root/pitr-recovery.sql
Binlog position is generally more precise than time, because it identifies an exact point in the flow of events.
Technical example: recovery before accidental deletion
Let's imagine an application database called ecommerceAt 02:00 a full backup is performed with mariadb-backupAt 14:36 PM an operator mistakenly executes:
DELETE FROM ordini;
or, in an even more serious scenario:
DROP TABLE ordini;
A full backup alone would allow you to go back to 02:00 AM, but you would lose all the orders registered that morning. With PITR, however, you proceed as follows: isolate the server or prepare a restore machine, restore the 02:00 AM backup, recover from xtrabackup_binlog_info the starting position, read the binlogs up to a few seconds before the error and apply the result.
cat /backup/mariadb/full-2026-06-10/xtrabackup_binlog_info
Output:
mariadb-bin.000120 884422
Replay generation:
mariadb-binlog \ --start-position=884422 \ --stop-datetime="2026-06-10 14:35:59" \ /safe-binlogs/mariadb-bin.000120 \ /safe-binlogs/mariadb-bin.000121 \ /safe-binlogs/mariadb-bin.000122 \ > /root/ecommerce-pitr.sql
Application:
mariadb ecommerce < /root/ecommerce-pitr.sql
After importing, you need to verify application consistency: number of orders, related tables, constraints, payment data, application logs, and the status of any associated queues. The PITR rebuilds the database at the MariaDB event level, but it doesn't replace functional validation of the application.
Operational attention and best practices
The first mistake to avoid is storing backups and binlogs on the same filesystem without any external replication. In the event of a disk failure, ransomware, or accidental system-side deletion, you risk losing both the snapshot and the log of changes. A proper policy should include local backups for quick restores, remote copies for disaster recovery, and consistent binlog storage.
The second point concerns periodic testing. An untested backup strategy is just hope.It's necessary to periodically test the restore on a separate environment, measure actual times, verify that the binlogs are present, and verify that the procedure actually reaches the desired time point. This allows you to estimate RTOs and RPOs realistically, not theoretically.
The third aspect concerns security. MariaDB backups contain sensitive data, application passwords, personal information, and transactional data. They must be protected with strict permissions, encryption where necessary, limited access, and rotation procedures. The PITR is a business continuity measure, but it must not become a new risk area.
Finally, it's important to remember that binlogs aren't resource-free. They take up space, generate I/O, and must be monitored. In environments with heavy writes, binlog growth can be significant. Therefore, it's a good idea to configure disk space alerts, automate remote log copying, and define retention periods compatible with the backup cycle.
Conclusion
Point-in-time recovery in MariaDB is one of the most important procedures for those managing production databases. It's not enough to be able to restore a "backup": you need to be able to restore the right data, at the right time, avoiding both excessive data loss and a return to an already compromised state.
The combination of mariadb-backup, binary log and mariadb-binlog It allows you to build a robust and relatively simple to automate recovery strategy. Physical backup provides a consistent foundation, xtrabackup_binlog_info indicates the point from which to start again and the binlogs allow you to reapply the changes up to the desired time.
The real difference, however, is not made by the command used in the emergency, but by the previous preparation: binary logs already active, correct retention, remote copies, prepared backups, documented procedures and periodic testing. The PITR is only effective if it was designed before the accident. Afterwards, at best, you can only find out whether the backup strategy was actually adequate.
For further details on the implementation, it is also useful to consult the official MariaDB documentation at Point-In-Time Recovery with mariadb-backup, the guide on backup and restore with mariadb-backup and the reference of mariadb-binlog.
