Point-in-Time Recovery from Full Backup plus Binary Logs

Learning outcomes

A ServiceHub operator runs an accidental broad update at 14:31. The 02:00 full backup is healthy, and the binary logs are intact. Restoring only the full backup loses twelve hours of valid work; replaying every log event repeats the bad update. Point-in-time recovery (PITR) solves this by restoring the full backup and replaying only the valid prefix of the change stream.

The recovery timeline

For the core lab, we recover to the instant immediately before the bad event. A more advanced “skip one event, then replay later events” workflow is possible but riskier because later transactions may depend on the unwanted change. That should be an incident-specific data-reconciliation decision, not a copy-paste recipe.

Preflight: use only a disposable binary-logging instance

-- 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;

SHOW GLOBAL VARIABLES WHERE Variable_name IN  ('log_bin','binlog_format','gtid_mode');SHOW BINARY LOG STATUS;SHOW BINARY LOGS;

If log_bin=OFF, stop the PITR lab here. You can still practice full-backup restore, but there is no post-backup change stream to replay. Do not restart or reconfigure a valuable database just to make a tutorial command run.

Step 1 — create the full restore point and record coordinates

INSERT IGNORE INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES ('PITR_FULL_BACKUP_POINT','Must exist after full restore');SELECT COUNT(*) AS before_backup_work_ordersFROM servicehub_recovery_lab.work_orders;SHOW BINARY LOG STATUS;

mysqldump -h 127.0.0.1 -P 3306 -u root -p \  --single-transaction \  --source-data=2 \  --set-gtid-purged=OFF \  --databases servicehub_recovery_lab \  > servicehub_pitr_base.sql# Record a SHA-256 hash immediately after creation.

Copy the commented CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE=..., SOURCE_LOG_POS=... values into the recovery worksheet. That is the replay start boundary for the lab. Keep the backup file immutable.

Step 2 — create valid changes, then one bad change

START TRANSACTION;INSERT INTO servicehub_recovery_lab.work_orders(site_id,status,summary)SELECT site_id,'OPEN','PITR valid event A - cooling fan inspection'FROM servicehub_recovery_lab.sites WHERE site_code='BAKU-01';INSERT INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES ('PITR_VALID_A','This marker must survive PITR');COMMIT;SHOW BINARY LOG STATUS;

START TRANSACTION;UPDATE servicehub_recovery_lab.work_ordersSET status='DONE', closed_at=NOW(6)WHERE summary='PITR valid event A - cooling fan inspection';INSERT INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES ('PITR_VALID_B','Second valid post-backup transaction');COMMIT;SHOW BINARY LOG STATUS;

-- Record the time and SHOW BINARY LOG STATUS immediately before executing.SELECT NOW(6) AS just_before_bad_event;SHOW BINARY LOG STATUS;START TRANSACTION;UPDATE servicehub_recovery_lab.work_ordersSET status='CANCELLED', summary=CONCAT('[BAD] ',summary);INSERT INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES ('PITR_BAD_EVENT','This marker must NOT exist in the recovered target');COMMIT;SELECT NOW(6) AS just_after_bad_event;SHOW BINARY LOG STATUS;

The broad update is intentionally harmful only to the disposable lab. Its marker makes validation deterministic. In a real incident, the bad event is discovered from application evidence, audit/log evidence, or data symptoms rather than because somebody conveniently labeled it “BAD.”

Step 3 — locate the event, then choose exact positions

Oracle recommends datetime filters as a discovery aid, not as the final replay boundary, because time-based filtering carries a greater risk of missing events. First narrow the window around the bad transaction, inspect the output, then record the exact event start position to use as the --stop-position.

# Substitute the actual copied binary-log file and a narrow time window.mysqlbinlog --start-datetime="2026-08-16 14:25:00" \            --stop-datetime="2026-08-16 14:35:00" \            --base64-output=DECODE-ROWS -vv \            binlog.000123 > pitr_window.txt# Inspect pitr_window.txt and identify the event/transaction positions# immediately before the unwanted transaction.

In row format, the human-readable output may show row images rather than the original SQL text. The business marker and timing/position evidence are therefore useful in this lab. The actual stop position must be copied from your own binary log; this lesson never invents a universal numeric position.

Step 4 — restore the full backup into a clean target

Before restoring, verify the target is disposable and empty of unrelated data. If @@GLOBAL.gtid_mode is ON on the source, record that fact and use a target whose GTID configuration is compatible; do not reset or rewrite GTID history on a production server for this exercise.

Recover into a separate disposable MySQL instance so the damaged source remains available for evidence and comparison. In the local course lab, keep the source on port 3306 and use a second disposable target on port 3307 (another local service/container/instance). Configure the target compatibly with the source's character-set and, when GTIDs are enabled, GTID expectations. This positional PITR lab is about data recovery; it is not a recipe for rejoining a replication topology.

DROP DATABASE IF EXISTS servicehub_recovery_lab;

# Bash / cmd.exemysql -h 127.0.0.1 -P 3307 -u root -p < servicehub_pitr_base.sql# PowerShell:cmd.exe /c "mysql -h 127.0.0.1 -P 3307 -u root -p < servicehub_pitr_base.sql"

SELECT marker_nameFROM servicehub_recovery_lab.recovery_markersWHERE marker_name IN ('PITR_FULL_BACKUP_POINT','PITR_VALID_A','PITR_VALID_B','PITR_BAD_EVENT')ORDER BY marker_name;

At this moment only the full-backup marker should exist from those four names. If the valid post-backup markers are already present, you restored the wrong artifact or the baseline was taken later than documented; stop instead of continuing.

Step 5 — replay from the backup boundary to just before the bad transaction

Use the exact source log file/position recorded in the backup plus the exact stop position identified from the unwanted transaction. If replay spans multiple binary-log files, process all required files in a single mysqlbinlog invocation piped to a single mysql connection. Oracle documents this as the safe method because state such as temporary tables can otherwise be lost between separate client connections.

# Replace placeholders with YOUR recorded coordinates.mysqlbinlog \  --start-position=<BACKUP_START_POSITION> \  --stop-position=<BAD_TRANSACTION_START_POSITION> \  binlog.000123 \  | mysql -h 127.0.0.1 -P 3307 -u root -p# If the interval spans files, list them in order in the same mysqlbinlog command.

The stop position is exclusive for events beginning at or after that position, which is why the start position of the bad transaction is a useful boundary when identified correctly. Always inspect transaction boundaries rather than cutting through an event or transaction blindly.

Step 6 — verify before any cutover

SELECT marker_name,noteFROM servicehub_recovery_lab.recovery_markersWHERE marker_name LIKE 'PITR_%'ORDER BY marker_name;SELECT COUNT(*) AS bad_rowsFROM servicehub_recovery_lab.work_ordersWHERE summary LIKE '[BAD] %';SELECT status,COUNT(*) AS nFROM servicehub_recovery_lab.work_ordersGROUP BY status ORDER BY status;SELECT COUNT(*) AS orphan_work_ordersFROM servicehub_recovery_lab.work_orders wLEFT JOIN servicehub_recovery_lab.sites s ON s.site_id=w.site_idWHERE s.site_id IS NULL;

The intended result is that PITR_VALID_A and PITR_VALID_B exist, PITR_BAD_EVENT does not, bad_rows=0, and referential invariants remain valid. A production recovery also needs application smoke tests, account/role checks, routines/events/triggers verification, and backup/recovery metadata review.

Failure case: missing binary-log file

Remove one copied log file from a disposable recovery directory and attempt to construct the full replay sequence. The correct diagnosis is “recovery chain incomplete.” The correct repair is to retrieve the missing verified log copy from backup/archive/another protected source—not to guess changes or skip the gap silently.

Summary and bridge to Lesson 5

PITR is no longer abstract: it is full backup + verified binary-log continuity + a precise stop boundary + validation. The final lesson operationalizes that workflow so recovery is rehearsed, measured, automated, and monitored rather than rediscovered during an incident.

Authoritative references

• MySQL 8.4 — Point-in-Time Recovery Using Binary Log
• MySQL 8.4 — Point-in-Time Recovery Using Event Positions
• MySQL 8.4 — mysqlbinlog
• MySQL 8.4 — SHOW BINARY LOG STATUS