ProxySQL Time-Travel SQL

dbtrail-shim is a MySQL wire-protocol server that turns time-travel queries into dbtrail agent calls. Pair it with ProxySQL routing rules and any MySQL client — mysql, DBeaver, Metabase, an ORM, a BI tool — gets time-travel SQL with zero client integration.

SELECT * FROM _flashback.orders AS OF '2026-04-27 09:00:00' WHERE id = 42;
SELECT * FROM _diff.orders BETWEEN '2026-04-27 09:00' AND '2026-04-27 10:00' WHERE id = 42;
SELECT * FROM _snapshot.orders AT GTID '3E11FA47-71CA-11E1-9E33-C80AA9429562:42' WHERE id = 1;
SELECT /*+ DBTRAIL_AT='2026-04-27 09:00:00' */ * FROM orders WHERE id = 42;

When to use it

• Forensics over SQL. Investigators write SELECT … FROM _flashback.t AS OF '<ts>' WHERE id = ? instead of learning dbtrail's CLI/API.
• Pre-flight recovery. A "what did this row look like 5 minutes ago?" check becomes a one-liner against your existing mysql shell.
• BI and ad-hoc tools. Metabase, DBeaver, JetBrains DataGrip — anything that speaks MySQL — can query historical state without a plugin.
• Composable with live SQL. With a follow-up release, you'll be able to JOIN a flashback row against a live lookup table; the beta keeps virtual-schema queries isolated for safety.

How it works

mysql client ──► ProxySQL ──► dbtrail-shim ──► dbtrail agent
                  │                │             /api/v1/query
                  │ regex match    │ parses      /api/v1/recover
                  │ on _flashback  │ AS OF /
                  │  _diff /       │ BETWEEN /
                  │  _snapshot     │ AT GTID
                  │
                  └─► your production MySQL
                       (everything else flows untouched)

ProxySQL's mysql_query_rules route any reference to a virtual schema (_flashback, _diff, _snapshot) — or a /*+ DBTRAIL_AT='…' */ hint comment — to the shim's hostgroup. Every other query flows untouched to your production MySQL backend.

The shim parses the time-travel clause, extracts the primary key from your WHERE predicate, and calls the dbtrail agent's recover or query endpoint. Results come back over the wire as a normal MySQL result set, so the client never knows it talked to anything other than MySQL.

SQL dialect

¹ The beta parser only recognizes a primary-key column literally named id. Tables with customer_id, order_id, uuid, etc. are not supported yet — see Limitations.

² Single equality (= <literal>) is the only predicate shape served end-to-end. IN (...) and BETWEEN <a> AND <b> are accepted by the safety check but the row resolver currently handles single equality only.

AS OF NOW() - INTERVAL N MINUTE works — the shim evaluates the expression before calling the agent.

_diff.<t> returns one row per change event with synthetic metadata columns (_event_id, _event_timestamp, _event_type, _gtid) followed by before_<col> / after_<col> pairs for every column in the table.

Setup

You will install the shim on the same host as your ProxySQL, drop in a config file, add a hostgroup and a few mysql_query_rules, and restart nothing. The shim listens on :3308 by default.

1. Get a dbtrail API key

From the dashboard, Settings → API Keys → Create. The shim sends this on every outbound call as Authorization: Bearer … — same shape as the MCP and REST API use today. See API Keys for scope and rotation details.

2. Install the shim

The shim binary is currently distributed by the dbtrail team during beta — email support@dbtrail.com and we will share a signed linux/arm64 build along with a checksum. Drop it on the same host as ProxySQL and confirm it runs:

./dbtrail-shim --version
# dbtrail-shim 0.1.0 (or similar)

Public release builds will land on the dbtrail GitHub releases page once the feature exits beta.

3. Write shim.yaml

listen: 0.0.0.0:3308

# Operator safety knobs. Defaults are applied when fields are omitted.
limits:
  # Cap on `WHERE pk IN (...)` list size. Each value becomes one
  # agent call. Default 1000.
  max_pk_batch: 1000

  # Cap on the BETWEEN span for _diff queries. Default 24h.
  # Investigators can override per-connection by setting
  # @dbtrail_allow_wide_diff = ON.
  max_diff_window: 24h

tenants:
  - mysql_user: acme_app
    agent_url: http://your-agent.example.com:8600   # the dbtrail agent's HTTP endpoint
    agent_token: "bt_live_REPLACE_ME"               # API key from step 1
    server_id: 11111111-2222-3333-4444-555555555555
    index_dsn: "bintrail:****@tcp(127.0.0.1:3306)/idx_acme_main"
    logical_schema: shop                            # your real schema name
    # source_dsn: "app_ro:****@tcp(127.0.0.1:3306)/acme_prod"
    # ^ Optional. Used to read column metadata for virtual tables from
    #   INFORMATION_SCHEMA.COLUMNS. Defaults to index_dsn — correct for
    #   shared-tier setups where the bintrail index lives on the same
    #   MySQL instance as the source data.

One tenants[] entry per MySQL user that should be allowed to issue time-travel queries.

4. Start the shim

./dbtrail-shim --config shim.yaml
# dbtrail-shim listening on 0.0.0.0:3308

5. Configure ProxySQL

Apply this against your ProxySQL admin interface (default port 6032). The snippet assumes you do not yet have a production hostgroup configured — if you already do, skip the first INSERT and reuse your existing hostgroup ID in default_hostgroup below.

-- Register your production MySQL backend (skip if you already have one).
-- Replace `mysql-prod` and the port with your real values.
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (10, 'mysql-prod', 3306);

-- Register the shim as a separate backend in hostgroup 99.
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (99, '127.0.0.1', 3308);

-- Register every user that should be allowed time-travel queries.
-- The username here MUST match a `mysql_user` entry in shim.yaml.
INSERT INTO mysql_users (username, password, default_hostgroup, active)
VALUES ('acme_app', 'plaintext-or-hashed', 10, 1);

-- Route virtual-schema queries to the shim. Earlier rule_id wins.
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (10, 1, '(?is)\b(?:from|join|update|into)\s+`?_flashback`?\.', 99, 1),
  (11, 1, '(?is)\b(?:from|join)\s+`?_diff`?\.',                 99, 1),
  (12, 1, '(?is)\b(?:from|join)\s+`?_snapshot`?\.',             99, 1),
  (13, 1, '(?is)/\*\+\s*DBTRAIL_AT\s*=',                         99, 1);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6. Test

Connect through ProxySQL with one of the users you registered:

mysql -h <proxysql-host> -u acme_app -p

SELECT * FROM _flashback.orders AS OF '2026-04-27 09:00' WHERE id = 42;

If the row existed at that timestamp, you get its full state back. If the row had been deleted (or had not yet been created) at that timestamp, you get zero rows back — the shim does not currently surface a tombstone marker, so you can't distinguish "deleted by then" from "didn't exist yet" from the result alone. To check whether the row was ever deleted, use SELECT * FROM _diff.orders BETWEEN '<earlier>' AND '<later>' WHERE id = 42 and look for an event with _event_type = 'delete'. A first-class deletion marker on _flashback is on the post-beta roadmap.

Authentication

There are two authentication hops, and they use different mechanisms — by design.

ProxySQL → shim. ProxySQL authenticates your client against mysql_users, then opens a backend connection to the shim hostgroup as the same username. The shim looks the username up in shim.yaml's tenants[] and binds the matching tenant to the session. The shim does not verify the MySQL password — ProxySQL already did, and storing a second copy would just complicate rotation.

Shim → dbtrail agent. The shim authenticates outbound to the dbtrail agent using the API key in agent_token, sent as Authorization: Bearer ….

The implication for operators: every user authorized to issue time-travel queries must appear in both mysql_users (ProxySQL) and tenants[] (shim). A user listed in ProxySQL but missing from shim.yaml fails fast with unknown tenant for MySQL user "<name>" rather than crashing the connection.

Errors you might see

The shim translates upstream failures into specific MySQL error codes so your client sees something meaningful:

The connection stays open after any of these — your next query on the same connection can succeed if the issue was transient.

The shim itself does not impose a per-query timeout. ProxySQL is the source of truth via mysql-default_query_timeout and mysql-connect_timeout_server, so timeout policy lives in one place.

Limitations during beta

• PK column must literally be id — the parser hardcodes the PK column name during beta. Tables whose PK is customer_id, order_id, uuid, etc. are not supported yet. Information-schema-driven PK lookup is on the post-beta roadmap.
• Single equality predicate today: WHERE id = <literal>. IN (...) and BETWEEN are recognized by the safety check but the row resolver only handles single equality.
• Deleted rows return zero rows, not a tombstone — _flashback filters out deletion events before sending the result back. Use _diff.t BETWEEN ... WHERE id = ? and look for _event_type = 'delete' to detect deletions explicitly. A _state column is on the post-beta roadmap.
• No prepared statements — the parser only handles literal queries. Most ORMs that emit ? placeholders will be rejected. The hint-comment form (/*+ DBTRAIL_AT='…' */) is the workaround.
• No mixed JOINs between a virtual schema and a real table (_flashback.orders f JOIN customers c …). The shim rejects these explicitly. Federated planning is on the roadmap.
• No INSERT/UPDATE/DELETE against virtual tables — recover is always read-only. This matches dbtrail's overall guarantee that the system never executes writes against your database.
• No multi-statement queries — one statement per round-trip.
• _diff window cap of 24h by default. Investigators can raise it via limits.max_diff_window in shim.yaml or, eventually, per-connection with SET @dbtrail_allow_wide_diff = ON.
• Single-tenant schema cache — running multiple tenants[] entries works for routing, but the column-metadata cache currently uses the first tenant only. Multi-tenant schema resolution lands in a follow-up release.
• Schema drift — if a column was added after the target timestamp, the reconstructed row will have NULL for that column.

If you hit a limitation that's blocking your use case, open an issue or email support — beta is the right time for us to hear about it.

Why a shim and not a ProxySQL plugin

ProxySQL has a Lua-like rewrite engine but no way to call out to a remote service mid-query. Routing to a backend that speaks MySQL is the well-trodden path — the same shape Vitess, PlanetScale, and other MySQL-protocol gateways use. The shim is a small Go binary and adds one extra hop on the time-travel path only; production traffic flows directly to your real MySQL through ProxySQL's normal routing.