Point-in-Time Recovery, Partial Recovery, Validation, and Data-Loss Boundaries

Learning outcomes

At 14:07 an administrator runs the wrong update and closes every open ServiceHub ticket. Restoring last night's backup would recover the database, but would also discard hours of legitimate work. Applying every binary log would faithfully reapply the damaging update. The task is therefore not “restore the backup”; it is “restore a base and roll forward to the last complete safe transaction before the incident.”

Point-in-time recovery (PITR) is a controlled replay. The base backup supplies an older consistent state. Binary logs supply ordered changes after that state. The recovery operator identifies a safe boundary, replays only complete transactions up to that boundary, validates the isolated target, and only then decides how to return service or extract corrected objects.

1. Create a controlled incident and record coordinates

Run this only on the disposable binlog-enabled instance from Lesson 3. The comments “SAFE” and “DAMAGE” are human labels; the authoritative boundaries are the transaction commit positions/GTIDs in the binlog.

DROP DATABASE IF EXISTS servicehub_pitr_lab;CREATE DATABASE servicehub_pitr_lab;CREATE TABLE servicehub_pitr_lab.tickets (  ticket_id BIGINT PRIMARY KEY,  customer_name VARCHAR(100) NOT NULL,  status ENUM('open','assigned','closed') NOT NULL,  note VARCHAR(255)) ENGINE=InnoDB;INSERT INTO servicehub_pitr_lab.tickets VALUES (4101,'Northwind Clinic','open','base'), (4102,'Alpine Support','assigned','base'), (4103,'Contoso Field Ops','open','base');FLUSH BINARY LOGS;SHOW MASTER STATUS;

Now create a logical PITR base with embedded coordinates:

mkdir -p ./pitr-labmariadb-dump --single-transaction --master-data=2   servicehub_pitr_lab > ./pitr-lab/base.sqlgrep -n "MASTER_LOG_FILE\|MASTER_LOG_POS" ./pitr-lab/base.sql | head

After the base, execute one legitimate transaction and then the damaging transaction:

START TRANSACTION;UPDATE servicehub_pitr_lab.tickets  SET status='assigned', note='legitimate dispatch'  WHERE ticket_id=4101;INSERT INTO servicehub_pitr_lab.tickets  VALUES(4104,'Fabrikam Escalation','open','legitimate new ticket');COMMIT;-- SAFE boundary is after the commit above.START TRANSACTION;UPDATE servicehub_pitr_lab.tickets  SET status='closed', note='INCIDENT: accidental bulk close';COMMIT;-- DAMAGE boundary is after this commit.INSERT INTO servicehub_pitr_lab.tickets  VALUES(4105,'Post Incident','open','must not be assumed safe');SELECT * FROM servicehub_pitr_lab.tickets ORDER BY ticket_id;SHOW MASTER STATUS;

2. Preserve and decode the incident logs before replay

Copy the relevant binary logs to a protected working directory. Do not mutate the source evidence. Start with the file/position recorded by the base dump. Decode the logs and locate the text/table events around the accidental update.

mkdir -p ./pitr-lab/binlogscp /path/to/mariadb-bin.0000* ./pitr-lab/binlogs/mariadb-binlog --verbose --base64-output=DECODE-ROWS   ./pitr-lab/binlogs/mariadb-bin.0000XX   > ./pitr-lab/decoded.sqlgrep -n -B8 -A24 "accidental bulk close\|tickets"   ./pitr-lab/decoded.sql | less

For row-based logging, the literal comment/note may appear in verbose row output; if not, use event positions, table IDs/maps and the known incident time to narrow the region. Identify the commit/Xid ending the legitimate transaction and the GTID/position beginning the damaging transaction. Record both.

3. Restore the base into an isolated target

Start a second local MariaDB 12.3.2 instance on another port or restore volume. Binary logging on the recovery target can be disabled during replay to avoid creating a second unreviewed log stream, but make that an explicit configuration decision; do not alter the incident source.

docker run --name servicehub-pitr-restore -d   -e MARIADB_ROOT_PASSWORD='lab-only-change-me'   -p 3308:3306 mariadb:12.3.2mariadb -h 127.0.0.1 -P 3308 -u root -p   -e "CREATE DATABASE servicehub_pitr_lab;"mariadb -h 127.0.0.1 -P 3308 -u root -p   servicehub_pitr_lab < ./pitr-lab/base.sqlmariadb -h 127.0.0.1 -P 3308 -u root -p   -e "SELECT * FROM servicehub_pitr_lab.tickets ORDER BY ticket_id;"

The target should contain only the base rows—before the legitimate post-backup work. That proves the base import, not PITR. Keep the target isolated from application traffic until replay and validation finish.

4. Replay by exact file/position boundary

Assume the base starts at mariadb-bin.000042:187654 and investigation shows the damaging transaction begins at position 231900 in that same file. Substitute the values from your lab; never copy these example numbers.

mariadb-binlog   --start-position=187654   --stop-position=231900   ./pitr-lab/binlogs/mariadb-bin.000042   > ./pitr-lab/replay-safe.sql# Inspect the tail and confirm the last transaction is complete.tail -80 ./pitr-lab/replay-safe.sql

If the interval spans multiple files, provide them in rotation order. Apply --start-position only to the first required file and stop in the final file at the verified safe boundary. A practical technique is to generate a replay file first, inspect it, checksum it, then execute it against the isolated target.

mariadb -h 127.0.0.1 -P 3308 -u root -p   < ./pitr-lab/replay-safe.sqlmariadb -h 127.0.0.1 -P 3308 -u root -p   -e "SELECT * FROM servicehub_pitr_lab.tickets ORDER BY ticket_id;"

Expected recovered state: ticket 4101 is assigned with “legitimate dispatch”; ticket 4104 exists; the accidental bulk-close is absent; ticket 4105 is absent unless you separately prove it is a safe transaction you intend to replay. That absent ticket is an explicit data-loss boundary, not a hidden defect.

5. Time-based recovery is a locator, not a substitute for transaction analysis

mariadb-binlog supports --start-datetime/--stop-datetime. This is useful when incident responders know approximately when damage occurred:

mariadb-binlog   --start-datetime="2026-08-20 13:55:00"   --stop-datetime="2026-08-20 14:10:00"   ./pitr-lab/binlogs/mariadb-bin.000042   > ./pitr-lab/window.sql

Use the output to identify the transaction, then refine to positions/GTIDs. Event timestamp ordering can differ from the business meaning of “the last safe operation,” clocks can drift, and multiple transactions can share timestamp granularity. MariaDB's own PITR guidance notes that --stop-datetime stops generation at the first transaction at or after the specified time; boundary semantics must be validated with your exact logs.

6. GTID-aware replay: verify target-version support

MariaDB GTIDs can express the recovery state independent of a particular filename. Current mariadb-binlog supports GTID-aware --start-position/--stop-position on supported versions (introduced from Community 10.8). A GTID list such as 0-1301-9842 identifies the last state already applied for a domain. Use --gtid-strict-mode when appropriate to detect out-of-order domain sequences.

mariadb-binlog --versionmariadb-binlog --help | grep -E "gtid|start-position|stop-position"# Example shape only; replace with verified GTID state from your logs.# mariadb-binlog --gtid-strict-mode #   --start-position='0-1301-9842' #   --stop-position='0-1301-9850' #   ./pitr-lab/binlogs/mariadb-bin.000042 > replay-gtid.sql

Do not mix MySQL GTID tutorials with MariaDB syntax. MariaDB's domain/server/sequence model and replay tooling are distinct.

7. Partial/object recovery is safer as an isolated merge

Suppose only one table was damaged while the rest of production continued. “Replay only binlog events for that table back into production” sounds efficient but can violate cross-table transactions, foreign keys, generated side effects and application invariants. A safer pattern is:

1. perform whole recovery into an isolated target to the desired point;
2. validate the recovered object's state;
3. export the required rows/table from the isolated target;
4. compare with current production and design an idempotent, reviewed merge;
5. apply the merge inside a controlled transaction/change window;
6. verify business invariants.

mariadb-dump -h 127.0.0.1 -P 3308 -u root -p   --single-transaction   servicehub_pitr_lab tickets   > ./pitr-lab/recovered-tickets.sql

That file is not automatically safe to import into production because it may contain DROP/CREATE and full-table data. Treat it as recovery evidence/input to a merge plan, not as a blind command.

8. Validation and data-loss declaration

Recovery Point Objective (RPO) is not “zero because PITR worked.” If the last safe transaction ended at 14:06:54 and legitimate transactions at 14:07:02–14:07:20 cannot be separated safely from the incident, that interval is the actual recovery loss boundary. Document it.

Cleanup only the disposable target after retaining the lab evidence you want to study:

docker rm -f servicehub-pitr-restore  # only if this lab created it# Keep or delete ./pitr-lab according to your disposable lab policy.

Lesson 5 turns this manual recovery into an operational program: scheduled backups, remote/immutable copies, measurable restore drills, RPO/RTO tracking and corruption simulations.