Point-in-Time Recovery Backups

Point-in-time recovery (PITR) reconstructs the full state of your database — or specific tables — at any past moment. It combines periodic baseline snapshots with the continuous binlog event stream to produce a complete, importable SQL dump.

How PITR works

PITR builds a database snapshot in three steps:

1. Find the nearest baseline — dbtrail looks for the most recent Parquet baseline snapshot taken at or before your target time.
2. Replay binlog events — starting from the baseline's binlog position, dbtrail replays every INSERT, UPDATE, and DELETE up to the target time, applying them to the baseline state.
3. Output mydumper SQL — the result is a set of SQL files (schema DDL + data) that you can import into any MySQL instance.

[Baseline T0] ─── replay binlog events ───> [Target Time] ───> mydumper SQL output
     │                                            │
     │  Parquet snapshot of your tables           │  Your requested point in time
     │  (closest one before target time)          │
     └────────────────────────────────────────────┘

Baselines

A baseline is a Parquet snapshot of your tables with the binlog position and GTID set embedded — which is how PITR knows exactly where to start replaying events. You produce baselines with two commands, typically on a cron schedule:

# 1. Logical dump with mydumper
bintrail dump \
  --source-dsn "user:pass@tcp(source-db:3306)/" \
  --output-dir /tmp/mydumper-weekly

# 2. Convert to Parquet baselines (and optionally upload to S3)
bintrail baseline \
  --input  /tmp/mydumper-weekly \
  --output /data/baselines \
  --upload s3://my-bucket/baselines/

If the upload step was skipped at baseline time (network down, no credentials), bintrail upload pushes the existing Parquet files to S3 later. See the dump and baseline guide for scheduling and upload details.

More frequent baselines mean less event replay time and faster PITR.

Coverage window

The PITR coverage window is the time range where recovery is possible — from the oldest indexed binlog event to the most recent one. Check coverage before triggering PITR: bintrail status shows indexed binlog files and partition time ranges, and --baseline-dir adds the binlog positions of your baseline snapshots.

PITR requires both:

• A baseline taken before the target time
• Binlog events covering the gap between that baseline and the target time

If your target time is before the oldest available baseline, PITR will fail with "no baseline snapshot found."

Requirements and compatibility

Binlog format: ROW only

dbtrail requires binlog_format = ROW. STATEMENT and MIXED are explicitly rejected — the agent validates this on startup and refuses to stream if the source is not in ROW format. Row-format binlogs contain the full before/after image of every changed row, which is what makes per-event reconstruction possible.

Row image: FULL only

dbtrail also requires binlog_row_image = FULL. MINIMAL and NOBLOB are rejected at startup. FULL row images ensure that every event contains the complete row state — both the values that changed and the values that didn't. Without FULL images, dbtrail couldn't reconstruct the exact state of a row at an arbitrary point in time.

-- Required MySQL configuration
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_row_image = 'FULL';

GTID support

dbtrail fully supports GTID-based streaming and recovery. When your MySQL server has gtid_mode = ON, dbtrail:

• Tracks the accumulated GTID set during streaming, not just the latest GTID. Every indexed event stores its GTID for later querying.
• Embeds the GTID set in baseline snapshots — Parquet baselines include the exact GTID set at the time of the dump, so PITR knows which transactions are already reflected in the baseline.
• Supports per-transaction recovery — you can reverse or inspect a specific transaction by its GTID (e.g. --gtid "3e11fa47-71ca-11e1-9e33-c80aa9429562:42").
• Detects and fills gaps on restart — if the agent restarts and the checkpoint falls behind @@gtid_purged, dbtrail detects the gap and auto-advances past purged transactions (unless --no-gap-fill is set).

GTID mode is strongly recommended for managed MySQL instances (RDS, Aurora, Cloud SQL) where binlog file names can change after failover.

Streaming: how real-time is it?

dbtrail connects to MySQL using the native replication protocol (COM_BINLOG_DUMP_GTID) — the same mechanism MySQL replicas use. It registers as a replica and receives events in real time as they're committed on the source.

Events are batched (default: 1000 events per batch) and checkpointed every 10 seconds to the index database. In practice, this means changes are visible in the index within ~10 seconds of commit.

On graceful shutdown (SIGTERM), the agent flushes the current batch and saves the checkpoint before exiting. On crash, worst-case data loss is one checkpoint interval (~10 seconds of events), which are automatically re-indexed and deduplicated on restart.

Recovery precision

PITR operates at per-event, per-second precision:

The --at parameter accepts second-level precision (YYYY-MM-DD HH:MM:SS, interpreted as UTC, or RFC 3339). All events with timestamps up to and including the target are applied; later events are skipped.

Triggering PITR

PITR is the full-table mode of bintrail reconstruct, enabled with --output-format=mydumper:

bintrail reconstruct \
  --at "2026-04-10 14:30:00" \
  --output-format=mydumper \
  --output-dir ./pitr-output \
  --tables mydb.orders,mydb.customers \
  --baseline-s3 s3://my-bucket/baselines/ \
  --index-dsn "user:pass@tcp(127.0.0.1:3306)/binlog_index"

The command runs synchronously: it selects the most recent baseline at or before --at, replays indexed events on top, writes the mydumper output locally, and prints a one-line summary per table.

Choosing tables

--tables takes a comma-separated list of schema.table names and is required in full-table mode:

--tables mydb.orders,mydb.customers

Reconstructing only the tables you need is faster and produces a smaller output — use a narrow list when you know exactly which tables you're recovering. For full-database recovery, pass the complete list of tables you want in the dump.

Allow gaps

By default, PITR fails if there are gaps in the indexed event stream between the baseline and the target time — missing hours that were rotated out of the index without a Parquet archive, or lost to agent downtime or binlog purging. Pass --allow-gaps to proceed anyway:

bintrail reconstruct ... --allow-gaps

Using the result

PITR writes a mydumper-compatible dump directory to --output-dir:

pitr-output/
├── mydb.orders-schema.sql      # CREATE TABLE DDL
├── mydb.orders.00000.sql       # INSERT data, chunked at --chunk-size
├── mydb.customers-schema.sql
├── mydb.customers.00000.sql
└── metadata                    # target time + baseline binlog position and GTID set

Import into MySQL

mysql -h your-host -u your-user -p your_database < pitr-output/mydb.orders-schema.sql
mysql -h your-host -u your-user -p your_database < pitr-output/mydb.orders.00000.sql

Or use myloader for parallel import:

myloader -h your-host -u your-user -p your-password -d ./pitr-output/ -o

Best practices

1. Keep baselines fresh. Every baseline gives PITR a more recent starting point, which means fewer events to replay and faster recovery. Run the dump → baseline pipeline on a cron schedule; on dbtrail Cloud, each scheduled backup produces one automatically.

2. Check coverage before triggering. Verify that a baseline exists before your target time and that the event index covers the gap up to it — bintrail status --baseline-dir ... shows both; Cloud users can use the dashboard or the coverage endpoint.

3. Use a tables filter when possible. Full-database PITR reconstructs every table, which takes longer. If you only need specific tables, a narrow --tables list is significantly faster.

4. Keep your snapshot schedule healthy. PITR requires a baseline before your target time. If the cron job (self-hosted) or backup schedule (Cloud) is disabled or failing silently, your PITR window will slowly shrink. Monitor it — see the backup strategy guide — and test an end-to-end restore periodically.

5. Review before importing. Always inspect the generated SQL files before importing into a production database. PITR output reflects the state at the target time, which may include data you don't want.

Next steps

• Backup strategy — how dbtrail implements backups and why they matter
• Recovery guide — generate SQL to reverse specific row-level changes
• Backups & PITR API reference — REST endpoints for dbtrail Cloud PITR jobs, history, and coverage