Binary Logs, Retention, Coordinates/GTIDs, and PITR Prerequisites

Learning outcomes

ServiceHub now has restore-tested full backups, but a nightly backup alone can still lose almost a day of committed changes. Point-in-time recovery (PITR) closes that gap by combining a base backup with the ordered changes recorded after it. In MariaDB, the primary source of those changes is the binary log (binlog).

The binary log is not the InnoDB redo log. InnoDB redo exists for storage-engine crash recovery. The binary log records server-level change events used by replication and PITR. A relay log belongs to a replica's apply pipeline. Confusing these files leads to impossible recovery plans.

1. Build the recovery timeline mental model

Think of recovery as a timeline. The base backup establishes state at coordinate B. The binlogs must then contain every committed event from immediately after B through the desired recovery boundary R. If a required file between B and R disappears, later files do not repair the gap.

2. Enable binary logging on a disposable server

Before editing configuration, record the current state. MariaDB option-file locations vary by OS/package. Add the following only to the disposable instance's server option group, using a server ID unique inside any replication topology.

[mariadb]log_bin=mariadb-binserver_id=1301binlog_format=ROW# Example only: derive retention from your recovery policy.# binlog_expire_logs_seconds=604800

Do not copy the commented retention value into production as a “best practice.” Retention must exceed the maximum age of a base backup that may need log replay, plus transfer/verification delay and operational margin. Disk capacity and off-host archival are part of the design.

SHOW VARIABLES WHERE Variable_name IN ('log_bin','log_bin_basename','server_id','binlog_format',  'expire_logs_days','binlog_expire_logs_seconds');SHOW BINARY LOGS;SHOW MASTER STATUS;SHOW GLOBAL VARIABLES LIKE 'gtid%';

Expected evidence includes log_bin=ON, a nonzero unique server_id, at least one binary-log file and current file/position. Some status/terminology evolves across releases; use the commands available on your target MariaDB version. The GTID variables show MariaDB's transaction-position model; they do not make MySQL GTID procedures interchangeable.

3. Generate known transactions and observe file/position movement

DROP DATABASE IF EXISTS servicehub_pitr_lab;CREATE DATABASE servicehub_pitr_lab;CREATE TABLE servicehub_pitr_lab.recovery_events (  event_id BIGINT PRIMARY KEY,  note VARCHAR(100) NOT NULL,  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;INSERT INTO servicehub_pitr_lab.recovery_events VALUES (1,'base marker',CURRENT_TIMESTAMP);SHOW MASTER STATUS;START TRANSACTION;INSERT INTO servicehub_pitr_lab.recovery_events VALUES (2,'transaction A row 1',CURRENT_TIMESTAMP), (3,'transaction A row 2',CURRENT_TIMESTAMP);COMMIT;SHOW MASTER STATUS;FLUSH BINARY LOGS;INSERT INTO servicehub_pitr_lab.recovery_events VALUES (4,'after rotation',CURRENT_TIMESTAMP);SHOW BINARY LOGS;SHOW MASTER STATUS;

A transaction can contain multiple row events but should be treated as one atomic replay group. Positions increase by event byte offsets, not by “one per transaction.” After rotation, the filename changes and the new file begins its own coordinate space. Therefore a numeric position without its filename is incomplete.

4. Decode logs safely with mariadb-binlog

Binary logs are not plain-text SQL files. Use mariadb-binlog to decode them. Work on copied log files or a disposable server and avoid piping remote live logs back into the same source server; doing so can create replay loops if the destination is also logging the re-executed changes.

mariadb-binlog --verbose --base64-output=DECODE-ROWS   /path/to/mariadb-bin.000001 | less# Focus investigation without executing anything:mariadb-binlog /path/to/mariadb-bin.000001   > ./decoded-binlog.sqlgrep -n "GTID\|COMMIT\|Xid\|servicehub_pitr_lab" ./decoded-binlog.sql | head -80

For row-based logging, decoded output contains table-map and row events; verbose decoding makes row changes understandable. The important recovery boundary is a complete committed transaction, not a visually convenient line number in the text rendering.

5. Connect backup coordinates to the retained log set

A logical dump made with --master-data=2 embeds the corresponding file/position in comments. A physical mariadb-backup can produce xtrabackup_binlog_info containing a binlog file, position and possibly GTID state. Capture that metadata with the backup manifest.

cat /var/mariadb/backups/full-001/xtrabackup_binlog_info# Example shape only; your values differ:# mariadb-bin.000042  187654  0-1301-9842mariadb -e "SHOW BINARY LOGS;"

If the backup says it corresponds to mariadb-bin.000042 position 187654, the recovery set must retain that file from that position forward and every rotated log needed to reach the target. Merely keeping “the last N files” is unsafe when transaction volume changes dramatically.

6. MariaDB GTIDs: transaction identity, not a magic backup

A MariaDB Global Transaction Identifier (GTID) is formatted as domain_id-server_id-sequence_no. The domain separates independent replication streams, server ID records the generating server, and sequence number advances within a domain. GTIDs help describe replication/replay state across renamed/rotated files, but the actual events still have to exist somewhere.

SHOW GLOBAL VARIABLES LIKE 'gtid_binlog_pos';SHOW GLOBAL VARIABLES LIKE 'gtid_current_pos';SHOW GLOBAL VARIABLES LIKE 'gtid_domain_id';SELECT @@GLOBAL.server_id;

Current mariadb-binlog versions can use GTID-aware start/stop positions in supported versions (GTID filtering support was added in MariaDB Community 10.8). Treat that syntax as a version-gated capability and test against copied logs before relying on it in an incident.

7. Deliberate failure: purge the bridge and prove PITR is broken

The dangerous operator pattern is “disk is filling, purge old binlogs” without checking the oldest backup still inside the recovery window. Reproduce the reasoning without risking real logs:

1. Copy the lab binlog files to a separate recovery directory.
2. Record the base backup's starting file/position.
3. List the copied files in order.
4. Move the required starting file out of the recovery directory rather than deleting it.
5. Attempt to construct the replay sequence.

mkdir -p ./pitr-copy/quarantinecp /path/to/mariadb-bin.0000* ./pitr-copy/ls -1 ./pitr-copy/mariadb-bin.*# Suppose the backup starts in mariadb-bin.000042:mv ./pitr-copy/mariadb-bin.000042 ./pitr-copy/quarantine/ls -1 ./pitr-copy/mariadb-bin.*

The later log files may all be intact, yet the recovery chain now has a hole immediately after the base. The repair is not to guess a later position. Recover the missing log from off-host archival or choose a newer validated base backup whose recorded coordinate starts inside the retained set.

8. Retention, encryption and disk-pressure production judgment

Retention is a three-way contract among recovery window, storage capacity and log offloading. Alert on binary-log volume/growth, oldest retained coordinate, archival lag and free disk. Purging must be tied to validated backup inventory—not just file age. If binlog encryption is enabled through MariaDB encryption/key-management capabilities, retain the correct keys/plugins for the entire PITR window; encrypted logs without keys are lost recovery evidence.

Lesson 4 uses this evidence to perform the actual roll-forward restore and to stop immediately before a known damaging transaction.