ProxySQL Time-Travel SQL

dbtrail-shim is a MySQL wire-protocol server that turns time-travel queries into reads against a local index DB populated from your MySQL binlog. 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;

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

ProxySQL's mysql_query_rules route any reference to a virtual schema (_flashback, _diff, _snapshot) 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 reads change events from the local bintrail_index DB to reconstruct row state. 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 reading the index.

_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 bintrail, initialize a local index, run a streamer that populates it from your MySQL binlog, drop in a shim config file, add a hostgroup and a few mysql_query_rules to ProxySQL, and restart nothing. The shim listens on :3308 by default.

1. Get a dbtrail API key

From the dashboard, Settings → API Keys → Create. The agent (if you run one) sends this as Authorization: Bearer …. See API Keys for scope and rotation details. The shim itself does not need an API key — it reads the local index directly.

2. Install the shim

The shim is bundled in the bintrail CLI as a subcommand — there is no separate binary to download. Install bintrail on the same host as ProxySQL and confirm the subcommand is available:

bintrail shim --version

If you do not have bintrail yet, the dbtrail team will share a signed linux/arm64 build during beta — email support@dbtrail.com. Public release builds land on the dbtrail GitHub releases page once the feature exits beta.

3. Initialize the local index

Pick a MySQL instance to hold the index (commonly the same MySQL that serves your application — see the Local Agents guide for sizing) and run bintrail init once against it:

bintrail init --index-dsn 'bt_index:****@tcp(127.0.0.1:3306)/bintrail_index'

This creates the binlog_events, schema_snapshots, index_state, and supporting tables that both the streamer and the shim depend on. Without it, both bintrail stream and bintrail agent in BYOS mode crash on startup with:

schema migration: add binlog_events.connection_id column:
  Error 1146 (42S02): Table 'bintrail_index.binlog_events' doesn't exist

bintrail init is idempotent — safe to re-run after upgrades.

4. Start the binlog streamer

bintrail stream is the process that tails your MySQL binlog and writes change events into bintrail_index. It must be running before any _flashback query will return data.

bintrail stream \
  --source-dsn 'bintrail:****@tcp(127.0.0.1:3306)/' \
  --index-dsn  'bt_index:****@tcp(127.0.0.1:3306)/bintrail_index' \
  --server-id  201 \
  --start-file mysql-bin.000001 \
  --start-pos  4 \
  --schemas    shop

Notes:

• --server-id must be different from any other replica (including a bintrail agent running on the same host — both processes connect to MySQL as replicas, and a duplicate ID will get one of them disconnected).
• --schemas filters the binlog to the schemas you actually care about and keeps the streamer from trying to index bintrail_index itself. Without it you will see a FK cascade constraint found warning at startup (see Troubleshooting).
• --start-file / --start-pos point at the first binlog position to read. For a brand-new setup, mysql-bin.000001 / 4 is the beginning of the first binlog file; for a running database use the values from SHOW MASTER STATUS at the moment you start.

Run it under systemd or your supervisor of choice — it stays in the foreground and must restart on crash.

5. 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
  # index lookup. 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
    mysql_password: "cleartext-password"            # MUST be cleartext — see below
    source_dsn: "app_ro:****@tcp(127.0.0.1:3306)/acme_prod"
    server_id: 11111111-2222-3333-4444-555555555555  # dbtrail SaaS server UUID — NOT a uint32

The shim reads only these fields per tenant: mysql_user, mysql_password, source_dsn, and server_id. One tenants[] entry per MySQL user that should be allowed to issue time-travel queries.

6. Start the shim

bintrail shim \
  --shim-config /etc/bintrail/shim.yaml \
  --index-dsn   'bt_index:****@tcp(127.0.0.1:3306)/bintrail_index'
# → logs a "shim listening" line on 0.0.0.0:3308 and stays in foreground

Both flags are required. The flag is --shim-config (older drafts of this guide said --config, which the CLI rejects with unknown flag: --config). --index-dsn must match what you passed to bintrail init and bintrail stream.

7. 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.
-- ProxySQL accepts either cleartext or a *HEX… hash for password here;
-- shim.yaml needs the cleartext regardless (see "mysql_password must be cleartext").
-- On MySQL 8.0+ / Percona 8.4 backends, this password needs special handling —
-- see step 7.5 below for the two options.
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);

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;

7.5. Auth plugin compatibility (MySQL 8.0+ / Percona 8.4)

The ProxySQL setup above hides a subtle handshake. ProxySQL authenticates your client against mysql_users, then opens a backend connection to your MySQL server as the same user. That backend handshake uses whatever auth plugin MySQL is configured for the user — and MySQL 8.0 and newer default to caching_sha2_password, which ProxySQL can only complete if it has the cleartext password for the backend user.

Follow the inline SQL above (or the SQL that bintrail proxysql-config generates) verbatim on a MySQL 8.x backend and you'll hit a silent failure on the first connect:

$ mysql -h 127.0.0.1 -P 6033 -u bintrail -p<PW> mydb
ERROR 1045 (28000): Access denied for user 'bintrail'@'localhost' (using password: YES)

ProxySQL is using a SHA1 hash (the mysql_native_password format) where it needs the cleartext for the backend handshake. See dbtrail/bintrail#310. Which path you take depends on the MySQL version you're connecting to:

Option A: switch the bintrail user to mysql_native_password (MySQL 8.0 only)

mysql_native_password is deprecated in 8.0 but still supported. Some 8.0 installs ship without the plugin loaded — INSTALL it first, then ALTER. Run this against MySQL directly (not through ProxySQL), as a privileged user:

INSTALL PLUGIN mysql_native_password SONAME 'auth_native_password.so';
-- ERROR 1125 (plugin already installed) is fine — ignore it.

ALTER USER 'bintrail'@'%'         IDENTIFIED WITH mysql_native_password BY '<password>';
ALTER USER 'bintrail'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
FLUSH PRIVILEGES;

After this, the SHA1 hash that bintrail proxysql-config (or the inline SQL above) stored in mysql_users works end-to-end — no further ProxySQL changes needed.

Option B: keep caching_sha2, store the cleartext password in ProxySQL

Run this against the ProxySQL admin interface (port 6032), after the inline SQL or bintrail proxysql-config output has been loaded:

DELETE FROM mysql_users WHERE username = 'bintrail';

-- default_hostgroup must match what you used when you first registered the user:
--   * 10 if you followed the inline SQL in step 7 above
--   * whatever `bintrail proxysql-config` generated (commonly 990) if you used that path
INSERT INTO mysql_users (username, password, default_hostgroup, active, use_ssl)
VALUES ('bintrail', '<cleartext-password>', 10, 1, 0);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

ProxySQL now holds the cleartext password and can complete the caching_sha2 backend handshake. The exposure is the same as the cleartext tenants[].mysql_password in shim.yaml — lock down the ProxySQL admin interface (and whichever bootstrap store seeded it) the same way you'd lock down the shim config.

Don't forget the shim itself

Once ProxySQL is talking to MySQL over caching_sha2, ProxySQL also reuses caching_sha2 when it opens its backend connection to the shim for any _flashback / _diff / _snapshot query. The shim's listener defaults to advertising mysql_native_password, so the handshake fails again — this time with 1045 Access denied from the shim, not from MySQL.

Add --auth-method=caching_sha2_password to the bintrail shim command from step 6 so the shim's listener advertises the matching plugin:

bintrail shim \
  --shim-config /etc/bintrail/shim.yaml \
  --index-dsn   'bt_index:****@tcp(127.0.0.1:3306)/bintrail_index' \
  --auth-method caching_sha2_password

See dbtrail/bintrail#286. If you took Option A and left the user on mysql_native_password, leave this flag off — the default listener auth method is correct for that path.

8. 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, plus a synthetic _state column. See the DELETE-event handling note below for what the result looks like when the row was deleted before <ts>.

If you also need the time of the deletion or the deleter's identity, use SELECT * FROM _diff.orders BETWEEN '<earlier>' AND '<later>' WHERE id = 42 and look for an event with _event_type = 'delete' — _diff carries the full event metadata.

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 then re-authenticates against shim.yaml's tenants[].mysql_password (which must be cleartext — see the callout above) and binds the matching tenant to the session.

Shim → MySQL (read-only). The shim's only outbound connection is to the local MySQL index, plus an optional source_dsn read-only connection for INFORMATION_SCHEMA.COLUMNS lookups. There is no outbound HTTP call.

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.

Troubleshooting

• Table 'bintrail_index.binlog_events' doesn't exist on bintrail stream or bintrail agent startup. You skipped step 3 — run bintrail init --index-dsn '<dsn>' against the index DSN.
• unknown flag: --config from bintrail shim. The flag is --shim-config, not --config.
• --index-dsn is required from bintrail shim. The shim does not read the index DSN from yaml — you must pass it on the CLI.
• _flashback queries always return empty. bintrail stream is not running, or it has a different --index-dsn than the one you gave the shim. Check SELECT COUNT(*) FROM bintrail_index.binlog_events directly — if it's 0, the streamer never wrote anything.
• FK cascade constraint found ... schema=bintrail_index warning at bintrail stream startup. Benign — these are the index DB's own internal FKs (on access_rules), not your schema's. With a --schemas <your_schema> filter that excludes bintrail_index, the warning goes away.
• Duplicate server-id in MySQL replica list. bintrail stream and bintrail agent both connect to MySQL as replicas — give each a unique --server-id, and make sure neither conflicts with other replicas you operate.

Limitations during beta

• Hint-comment form /*+ DBTRAIL_AT='…' */ is not currently supported. The shim rejects these with ERROR 1235: this server only handles _flashback / _snapshot / _diff virtual-schema queries. Tracked at #1401. Until that ships, use … FROM _flashback.<t> AS OF '<ts>' WHERE … instead.
• DELETE events return the pre-delete row state, not a tombstone. A SELECT … FROM _flashback.<t> AS OF '<ts>' WHERE id = <id> against a row that was deleted at or before <ts> currently returns the row's last known state — there is no _state = 'deleted' marker. Older versions of this guide claimed the result was empty; that is also wrong. Tracked at #1400. Until that ships, cross-reference _diff.<t> BETWEEN <a> AND <b> WHERE id = <id> for an event with _event_type = 'delete' to determine whether the row was actually present at the target time.
• Composite primary keys are not yet supported. Tables whose PK spans more than one column reject up front with "composite primary keys are not yet supported". Workaround: use _diff.<t> and filter client-side, or split the query.
• Dropped tables degraded for time-travel. Once a table is fully dropped from the source, the shim falls back to the bintrail index DB's schema_snapshots for column metadata. That fallback doesn't carry primary-key information, so time-travel queries against dropped tables reject with "table has no primary key". _diff queries that don't require PK lookup are unaffected.
• Single equality predicate today: WHERE id = <literal>. IN (...) and BETWEEN are recognized by the safety check but the row resolver only handles single equality.
• No prepared statements — the parser only handles literal queries. Most ORMs that emit ? placeholders will be rejected.
• 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 per-connection with SET @dbtrail_allow_wide_diff = ON (accepts ON|OFF|1|0; the override is cleared on COM_RESET_CONNECTION).
• 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.