Recover
Generate point-in-time recovery SQL from binlog history
Generate recovery SQL to reverse or replay changes for specific rows. Requires the recover:execute permission.
Endpoint
| Method | Endpoint | Permission | Description |
|---|---|---|---|
POST | /recover | recover:execute | Generate recovery SQL (always dry-run) |
Request body
{
"server_id": "uuid",
"schema": "mydb",
"table": "orders",
"primary_keys": [12345],
"target_time": "2026-03-01T12:00:00Z",
"resolve_fk": true,
"include_dependents": false
}| Field | Required | Default | Description |
|---|---|---|---|
server_id | yes | — | Server to recover from |
schema | yes | — | Database schema name |
table | yes | — | Table name |
primary_keys | no | [] | Primary key values to recover |
target_time | no | — | ISO 8601 point-in-time to recover to |
resolve_fk | no | true | Auto-resolve missing FK parent rows. When enabled, if a parent row referenced by a foreign key was also deleted, it is automatically included in the recovery SQL before the child row. |
include_dependents | no | false | Also recover deleted child rows that reference the recovered parent. For example, recovering a customer can also restore their orders. |
Response
{
"success": true,
"sql": "BEGIN;\nINSERT INTO `mydb`.`orders` ...;\nCOMMIT;\n",
"affected_rows": 1,
"operations": [{"type": "INSERT", "table": "orders", "pk": "12345"}]
}When FK resolution is active, the response includes additional fields:
{
"success": true,
"sql": "-- FK-aware recovery ...\nBEGIN;\n...\nCOMMIT;\n",
"affected_rows": 2,
"operations": [{"type": "INSERT", "table": "customers", "pk": "42"}, {"type": "INSERT", "table": "orders", "pk": "12345"}],
"recovery_steps": [
{"order": 1, "schema": "mydb", "table": "customers", "primary_key": {"id": 42}, "reason": "required by foreign key on mydb.orders.customer_id", "sql": "INSERT INTO ..."},
{"order": 2, "schema": "mydb", "table": "orders", "primary_key": {"id": 12345}, "reason": "requested recovery target", "sql": "INSERT INTO ..."}
],
"warnings": []
}Always dry-run
Recovery is always dry-run. The generated SQL must be reviewed and executed manually by a DBA. AI and automated systems never execute recovery SQL directly.
Access rules
Recovery is subject to access rules. If the requesting user's role has no access to the table, the request returns 403. When FK resolution discovers related tables the user cannot access, those tables are skipped with a warning. Forensic SQL text is removed when column restrictions apply.
Recovery logic
Recovery SQL is generated from before/after row images captured by the bintrail CLI during binlog streaming.
| Original event | Recovery action |
|---|---|
| DELETE | INSERT INTO ... (using row_before values) |
| UPDATE | UPDATE ... SET (old values from row_before) |
| INSERT | DELETE FROM ... (using row_after values) |
FK-aware recovery
By default (resolve_fk: true), dbtrail inspects INFORMATION_SCHEMA.KEY_COLUMN_USAGE to discover foreign key relationships in the schema. It then:
- Resolves missing parents — recursively walks up the FK chain (up to 5 levels) to find deleted parent rows that must be restored first
- Optionally includes dependents — when
include_dependents: true, walks down the FK chain to include deleted child rows - Topologically sorts all recovery statements so parents are inserted before children
- Handles cycles — circular FKs and self-referencing FKs (e.g.
employees.manager_id → employees.id) are handled by wrapping the SQL withSET FOREIGN_KEY_CHECKS=0/SET FOREIGN_KEY_CHECKS=1
When FK resolution is active, the response includes additional fields:
recovery_steps— an ordered list of recovery steps, each with areasonexplaining why it was included (e.g."required by foreign key on mydb.orders.customer_id"or"dependent of mydb.customers via customer_id")warnings— notes about skipped tables (access denied), depth limits reached, or circular dependencies detected
Set resolve_fk: false to disable FK resolution and get single-table recovery only.