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. dbtrail writes a set of SQL files (schema DDL + data) that you can import into any MySQL instance.

Baselines

A baseline is a Parquet snapshot of your tables with the replication position embedded, which is how PITR knows exactly where to start replaying events.

The easiest way to produce one is from the console: on the Backups page, Create backup runs the whole pipeline (parallel mydumper dump → Parquet conversion → upload to your backup destination) in-process for the selected server, and the new snapshot appears in the page's backup listing when it finishes. The button is on by default in the bundled Compose stack; the server needs a backup destination (local directory or S3) configured under Manage servers → Edit → Advanced.

For cron-scheduled baselines and headless deployments, the same pipeline runs from the CLI (bintrail dump → bintrail baseline). See the dump and baseline guide for scheduling, upload retries, and every flag.

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: the console's Status view shows indexed coverage and continuity, and the Backups page lists your baseline snapshots with the coordinates their deltas start from.

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 (for example --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.

Single rows: the Restore view

For the common case ("what did this row look like at 14:30?"), you don't need a full PITR run. With a baseline configured, the console's Restore view reconstructs a single row's full state at any instant (baseline merged with the deltas after it) and shows the row's history, right above the undo-SQL form for the same row. It cleanly distinguishes three outcomes: the row's state, deleted as of that time, and no baseline row for that PK. A coverage gap between the baseline and the target is a hard error rather than a silently-wrong answer.

Full tables: build a .sql backup for any moment

Full-table PITR runs from the console's Backups page. Open Build a .sql backup for any moment, enter the target time (UTC), and click Build: the daemon selects the most recent baseline at or before that time, replays indexed events on top, and packages a mydumper-compatible dump. When it finishes, Download .sql backup (.tar.gz) hands you every table as of the chosen moment, ready for myloader or plain mysql. Coverage gaps abort the build rather than silently producing a wrong state.

Headless and scripted deployments run the same engine from the CLI: bintrail reconstruct --output-format=mydumper, which adds a --tables filter (reconstructing only the tables you need is faster and produces a smaller output) and the full flag set documented in the repository: query and recovery reference.

Using the result

Both paths produce the same artifact: the console's download is a .tar.gz of the dump directory, and the CLI writes it to --output-dir. Inside, it's a mydumper-compatible layout:

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. Create backups regularly from the Backups page, or put the CLI dump → baseline pipeline on a cron schedule for unattended runs.

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. The console's Status view and Backups page show both.

3. Use a tables filter when possible (CLI). Full-database PITR reconstructs every table, which takes longer. If you only need specific tables, the CLI's --tables list is significantly faster; the console build always covers every table with a backup.

4. Keep your snapshot schedule healthy. PITR requires a baseline before your target time. If the snapshot cron job 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