Recovery
Restore accidentally changed rows to any past moment, with row-level point-in-time recovery and human-in-the-loop review
Recovery is dbtrail's row-level point-in-time restore. A deleted customer, a reverted UPDATE, an unintended INSERT: when a single row (or a handful of rows) is wrong, you don't need to restore a whole database. dbtrail indexes every row change, so you can pick any moment in the past and restore that specific row to its state at that moment.
Row-level vs. whole-database restore
- Use recovery (this guide) to restore specific rows, typical for "I just deleted the wrong user" incidents.
- Use PITR to reconstruct whole tables or the whole database at a target time, typical for schema-wide corruption, large accidental batch operations, or cloning for audit.
Both reach into the same underlying binlog index and base snapshots, just at different granularities.
Human-in-the-loop by design
Recovery always operates in dry-run mode: dbtrail generates the SQL but never executes it against your database. A human reviews the statements and applies them. This is deliberate: an automated writer that misreads intent would be a bigger incident than the one it was trying to fix. The review step is the safety feature.
How recovery works
dbtrail indexes the full before/after state of every row change. These before/after row images are captured by the bintrail CLI as it parses MySQL binary log events. When you request recovery, dbtrail uses this data to generate the inverse SQL:
| Original event | Recovery action |
|---|---|
| DELETE | INSERT INTO ... using the row_before values |
| UPDATE | UPDATE ... SET reverting to row_before values |
| INSERT | DELETE FROM ... matching the row_after values |
dbtrail reverses matching events in reverse chronological order (the most recent change is undone first), and wraps the whole script in BEGIN/COMMIT so you can ROLLBACK if a statement fails mid-way. The generated SQL uses primary-key-only WHERE clauses when a schema snapshot is available, falling back to all-columns WHERE otherwise.
Foreign key awareness
The open-source generator does not consult the schema's foreign-key graph; ordering is strictly reverse-chronological. Because MySQL enforces FK order on the original deletes (children before parents), undoing them most-recent-first re-inserts parents before children, as long as all the related deletions fall inside your filters. If a referenced parent row was deleted outside the window you're recovering, the generated INSERTs can violate FK constraints. Widen the time window, or recover the parent row first.
For deletes lost to ON DELETE CASCADE / ON DELETE SET NULL foreign keys, the console's Restore view handles it for you: when the row you're undoing is a foreign-key parent, the generated script also repairs the child rows InnoDB changed below the binlog, and a CASCADE banner on the result says exactly what it restored (child rows re-inserted, cleared references fixed, re-pointed references fixed). Headless deployments get the same synthesis from bintrail recover-cascade; details in the dbtrail repository.
MySQL 8.x CASCADE limitation
ON DELETE CASCADE is invisible to the binlog on MySQL 8.x
When a parent row is deleted and the foreign key uses ON DELETE CASCADE, InnoDB handles the child deletions entirely inside the storage engine: they never appear in the binary log. This is a MySQL/InnoDB limitation, not a bintrail or dbtrail limitation. Since bintrail reads the binlog, cascaded child deletions are invisible to both query and recover.
This means:
- Cascaded child deletions never reach the index: there are no binlog events to capture, so the Events view and a plain undo cannot discover them (the Restore view's cascade repair, and
bintrail recover-cascadeon the CLI, address this by synthesizing the child rows from FK metadata) - Querying the child table for recent DELETEs will not show CASCADE deletions
- Only the parent DELETE is visible to dbtrail
The + Add server preflight warns when monitored schemas carry CASCADE constraints, with the ALTER TABLE remediation ready to copy. Capture proceeds anyway (the FK graph is recorded, which is what makes cascade repair possible later), but the warning is worth acting on.
Workaround: Replace ON DELETE CASCADE with ON DELETE RESTRICT and handle child deletions explicitly in application code or a BEFORE DELETE trigger on the parent table. Explicit DELETEs are logged in the binlog and fully visible to dbtrail.
MySQL 9.6+: This limitation is resolved. Cascade enforcement moved to the SQL layer, making cascaded operations visible to the binlog.
Using recovery
From the console (recommended)
The console's Restore view is the primary interface: filter by schema, table, primary key, and time window, preview the affected rows with before/after diffs, then copy or download the transaction-wrapped script. With a backup configured, the same view also shows the row's state at the chosen moment (and its history), so you see what you're restoring to before you generate anything. Coverage gaps (hours rotated out with no archive) are flagged prominently on the result, so an incomplete undo is never silently presented as complete. The console never executes SQL; you review and apply the script yourself.
The same engine is also reachable as POST /api/recover behind the console's
opt-in API token, useful for integrating recovery into your own runbooks.
With Claude
During an incident, plain English is often faster than filters:
"Generate recovery SQL for the deleted order 12345 in the orders table"
"Undo the UPDATE on user 42 from yesterday around 3 PM"
Claude calls the recover tool and shows you the generated SQL for review.
You apply it (or not): dbtrail never writes to your database itself. See
Connect Claude to wire it up; the MCP
recover tool accepts the same filters as the
console.
From the CLI
Headless and scripted deployments use bintrail recover, which supports the
same filters plus batch options (multiple PKs, per-PK limits, column-equality
matching, GTID-scoped transaction reversal). The command and all its flags
are documented in the repository:
query and recovery reference.
Best practices
- Always review the SQL before executing: recovery SQL can have unintended side effects if the data has changed since the original event
- Use specific filters: narrow down by table, primary key, and time range to avoid generating more statements than needed
- Test in a staging environment first when recovering large batches
- Run inside a transaction: the generated SQL is already wrapped in
BEGIN/COMMIT, so you canROLLBACKif something looks wrong
Access control
Named access profiles (--profile) let you withhold tables and redact flagged columns from query and recovery output. Full role-based access control (per-table and per-column permissions over who sees and recovers what) is part of dbtrail EE.