Binary Log Formats, GTIDs, Retention, and Recovery Prerequisites

Learning outcomes

A full backup gives ServiceHub a historical starting point. The binary log supplies the ordered changes after that point. If the log is disabled, incomplete, purged too early, or misunderstood, point-in-time recovery becomes impossible no matter how good the full backup is. This lesson makes the recovery stream observable before it is needed.

Redo is crash recovery; the binary log is a server change stream

InnoDB redo exists so the storage engine can recover durable page changes after a crash. The MySQL binary log records server data-changing events for replication and incremental/point-in-time recovery. Their retention, file formats, and consumers differ. A database can recover from a crash with redo yet still lack enough historical binary logs to rewind to yesterday at 14:31.

Inventory the recovery stream

-- Run on a disposable local MySQL 8.4 LTS instance.CREATE DATABASE IF NOT EXISTS servicehub_recovery_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.sites (  site_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_code VARCHAR(20) NOT NULL UNIQUE,  site_name VARCHAR(120) NOT NULL) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.work_orders (  work_order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_id BIGINT UNSIGNED NOT NULL,  status ENUM('OPEN','IN_PROGRESS','DONE','CANCELLED') NOT NULL DEFAULT 'OPEN',  summary VARCHAR(180) NOT NULL,  opened_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  closed_at TIMESTAMP(6) NULL,  CONSTRAINT fk_recovery_work_order_site    FOREIGN KEY (site_id) REFERENCES servicehub_recovery_lab.sites(site_id)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.recovery_markers (  marker_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  marker_name VARCHAR(80) NOT NULL UNIQUE,  marker_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  note VARCHAR(255) NOT NULL) ENGINE=InnoDB;INSERT IGNORE INTO servicehub_recovery_lab.sites(site_code,site_name)VALUES ('BAKU-01','Baku Central Service Site'),       ('BAKU-02','Baku East Service Site');INSERT IGNORE INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES ('CH13_BASELINE','Known recovery invariant before Chapter 13 drills');SELECT COUNT(*) AS sites FROM servicehub_recovery_lab.sites;SELECT COUNT(*) AS markers FROM servicehub_recovery_lab.recovery_markers;

SELECT VERSION() AS server_version;SHOW GLOBAL VARIABLES WHERE Variable_name IN (  'log_bin','binlog_format','binlog_row_image',  'binlog_expire_logs_seconds','binlog_expire_logs_auto_purge',  'gtid_mode','enforce_gtid_consistency');SHOW BINARY LOGS;SHOW BINARY LOG STATUS;SELECT @@GLOBAL.gtid_executed AS gtid_executed,       @@GLOBAL.gtid_purged AS gtid_purged;

MySQL 8.4 enables binary logging by default for ordinary initialized installations, but there are documented initialization/startup cases where it can be disabled. Therefore the lab checks log_bin rather than assuming it. The default automatic expiration period is 2,592,000 seconds (30 days), but recovery requirements—not defaults—should determine the production window.

Statement, row, and mixed formats describe how changes are represented

STATEMENT records SQL statements; this can be compact but requires deterministic behavior and has classes of unsafe statements. ROW records row-change events and avoids many statement nondeterminism concerns at the cost of potentially larger logs. MIXED lets the server choose statement or row representation depending on safety. This chapter does not ask learners to switch a production source merely to observe the difference.

Create an identifiable transaction and rotate the log

INSERT INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES (CONCAT('BINLOG_MARKER_',DATE_FORMAT(NOW(6),'%Y%m%d%H%i%s%f')),        'Safe transaction for mysqlbinlog inspection');SELECT LAST_INSERT_ID() AS marker_id;SHOW BINARY LOG STATUS;

Record the file/position before and after the transaction in your lab notebook. If your disposable account can do so, FLUSH BINARY LOGS can rotate to a new log file, making boundaries easier to inspect; it requires administrative privileges and should not be used as a casual production troubleshooting command.

FLUSH BINARY LOGS;SHOW BINARY LOGS;SHOW BINARY LOG STATUS;

Decode events with mysqlbinlog

mysqlbinlog can read local binary-log files when you have filesystem access or read them from a running server with the appropriate remote-read options and privileges. For row-based events, --base64-output=DECODE-ROWS -vv provides human-oriented row annotations. Treat the output as sensitive because it can contain application values.

# Replace the filename with the actual log from SHOW BINARY LOGS.mysqlbinlog --verify-binlog-checksum \  --base64-output=DECODE-ROWS -vv \  binlog.000123 > decoded_binlog.txt# Search the decoded output for the lab schema/table and surrounding positions.

The important evidence is the event position, timestamp, transaction boundary, and, when GTIDs are enabled, the GTID. A position is a byte location in a binary-log file—not “transaction number 27.”

GTIDs: transaction identity and the meaning of purged history

A Global Transaction Identifier (GTID) uniquely identifies a committed transaction in a replication topology when GTID mode is enabled. gtid_executed represents executed GTIDs. gtid_purged represents executed transactions that no longer exist in any current binary-log file on the server. Therefore a transaction can remain known to GTID metadata after the binary log bytes required for a detailed PITR replay have been purged.

SELECT @@GLOBAL.gtid_mode AS gtid_mode,       @@GLOBAL.gtid_executed AS gtid_executed,       @@GLOBAL.gtid_purged AS gtid_purged;

Do not “fix” GTID mismatches by resetting GTID history casually. Commands that reset binary logs/GTIDs are topology-changing operations and are intentionally deferred to controlled recovery/provisioning contexts.

Retention failure: the missing interval cannot be reconstructed from wishful thinking

Suppose the last tested full backup is Sunday 02:00 and the accidental DELETE is Thursday 14:00. To achieve near-zero RPO, every required binary-log event after the backup boundary through 13:59:59 must still be available. If Wednesday's log was purged, the chain has a hole. A later log cannot recreate transactions that were only present in the missing file.

SHOW GLOBAL VARIABLES LIKE 'binlog_expire_logs_seconds';SHOW GLOBAL VARIABLES LIKE 'binlog_expire_logs_auto_purge';SHOW BINARY LOGS;

MySQL automatically purges expired binary logs when automatic purge is enabled; manual PURGE BINARY LOGS also exists. This lesson intentionally does not purge the learner's active recovery chain. A safe failure drill can instead copy a set of log files into a disposable directory, remove one copied file, and prove the sequence is incomplete before any restore begins.

Recovery prerequisites checklist

Summary and bridge to Lesson 4

You now know whether a usable post-backup change stream exists and how to identify events within it. Lesson 4 combines the two halves: restore the full backup, locate an accidental change, then replay the binary log only through the last valid position before that change.

Authoritative references

• MySQL 8.4 — The Binary Log
• MySQL 8.4 — Binary Logging Options and Variables
• MySQL 8.4 — mysqlbinlog
• MySQL 8.4 — GTID Format and Storage
• MySQL 8.4 — GTID Life Cycle