Time-travel SQL for MySQL, finally

A time ago I argued MySQL is the only major OLTP database without time-travel queries. Here’s what’s changed.

Last month I mapped out how every major OLTP except MySQL gives you point-in-time queries out of the box. Oracle has AS OF TIMESTAMP. SQL Server has FOR SYSTEM_TIME AS OF. MariaDB ships system-versioned tables. PostgreSQL has three extensions that get you there.

Vanilla MySQL: nothing.

There’s now a way to close that gap without forking MySQL, without an ALTER TABLE storm, and without rewriting the application. It’s called bintrail, and once you deploy it behind ProxySQL, your MySQL fleet learns three new query shapes.

What it looks like

-- The state of order #42 at any past instant
SELECT * FROM _flashback.orders
  AS OF '2026-04-15 09:30:00'
  WHERE id = 42;

-- Every change to order #42 in a time window
SELECT * FROM _diff.orders
  BETWEEN '2026-04-15 00:00:00' AND '2026-04-15 23:59:59'
  WHERE id = 42;

No ALTER TABLE to enable system-versioning. No special storage engine. No binary log replay tooling. Same MySQL, same driver: point the connection at ProxySQL instead of the real MySQL port and the rest just works.

The application sends a SQL statement and gets a row back.

How it stacks up against the dialects MySQL has been missing

Database	Syntax
Oracle	SELECT * FROM orders AS OF TIMESTAMP TO_TIMESTAMP('2026-04-15 09:30', 'YYYY-MM-DD HH24:MI') WHERE id = 42;
SQL Server	SELECT * FROM orders FOR SYSTEM_TIME AS OF '2026-04-15T09:30' WHERE id = 42;
MariaDB	SELECT * FROM orders FOR SYSTEM_TIME AS OF '2026-04-15 09:30' WHERE id = 42;
MySQL	❌ not supported
MySQL + bintrail	SELECT * FROM _flashback.orders AS OF '2026-04-15 09:30:00' WHERE id = 42;

The bintrail syntax is shorter than Oracle’s (no TIMESTAMP TO_TIMESTAMP(...) ceremony) and works without per-table setup — there’s no ALTER TABLE to declare anything as ‘system-versioned’. Bintrail starts indexing whatever binlog history MySQL still has on disk the moment you deploy it, plus everything after. The moment bintrail starts indexing your binlog, every table is queryable as of any point in the recorded history.

And _diff returns the change events — event type, GTID, before and after image — over a time range. SQL Server’s FOR SYSTEM_TIME BETWEEN and MariaDB’s equivalent return row versions in the range, which is close but not the same shape. Oracle’s VERSIONS BETWEEN is the closest analogue but requires flashback storage configured on the table and is bounded by the undo retention window.

With bintrail, _diff walks the indexed binlog stream and returns every change to a row over any time range: full audit history with one query.

A real session

Here’s what it looks like in a terminal. An order gets created, updated, then accidentally deleted, and we recover its state from before the delete without touching a backup.

mysql> SELECT * FROM orders WHERE id = 42;
+----+----------+-----+---------------------------+
| id | sku      | qty | note                      |
+----+----------+-----+---------------------------+
| 42 | LIVE-SKU | 999 | live-row-from-passthrough |
+----+----------+-----+---------------------------+
1 row in set (0.00 sec)

mysql> -- Whoops, what was this row 10 minutes ago?
mysql> SELECT * FROM _flashback.orders
    -> AS OF '2026-05-04 13:00:00'
    -> WHERE id = 42;
+----+-------+-----+---------+
| id | sku   | qty | note    |
+----+-------+-----+---------+
| 42 | ABC-1 |   2 | initial |
+----+-------+-----+---------+
1 row in set (0.02 sec)

mysql> -- And the full history of changes?
mysql> SELECT event_timestamp, event_type, row_after
    -> FROM _diff.orders
    -> BETWEEN '2026-05-04 00:00:00' AND '2026-05-04 23:59:59'
    -> WHERE id = 42;
+---------------------+------------+--------------------------------------------------+
| event_timestamp     | event_type | row_after                                        |
+---------------------+------------+--------------------------------------------------+
| 2026-05-04 10:00:00 | INSERT     | {"id":42,"sku":"ABC-1","qty":1,"note":"initial"} |
| 2026-05-04 12:00:00 | UPDATE     | {"id":42,"sku":"ABC-1","qty":2,"note":"initial"} |
| 2026-05-04 14:00:00 | DELETE     | NULL                                             |
+---------------------+------------+--------------------------------------------------+
3 rows in set (0.02 sec)

Three queries, three answers. The first hits the live MySQL: current state. The second reaches back past the DELETE and reconstructs the row’s state at any earlier instant. The third is the audit trail: every event for that row in chronological order.

Recovery without touching backups. Audit trails without instrumenting the application.

Your application doesn’t change

That’s the part worth lingering on. No new driver. No connection string trick. No SDK to integrate. If ProxySQL already sits in front of your MySQL fleet, bintrail goes behind it as a sidecar:

The same connection serves both worlds. The ORM doesn’t know anything changed. The DBA team doesn’t have to retrain. Every tool that talks MySQL keeps talking MySQL.

The routing, in three rules

ProxySQL only needs to know about two backends and three query rules:

-- Two backend hostgroups
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
  (990, 'mysql.your-network.internal', 3306),  -- your real MySQL
  (991, '127.0.0.1',                   3308);  -- bintrail-shim sidecar

-- Route any query mentioning _flashback / _diff / _snapshot to the
-- shim hostgroup. Everything else falls through to the passthrough.
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (990001, 1, '\b_flashback\.', 991, 1),
  (990002, 1, '\b_diff\.',      991, 1),
  (990003, 1, '\b_snapshot\.',  991, 1);

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

You don’t have to write that by hand. bintrail proxysql-config reads the connection details from .bintrail.env plus the tenant list from shim.yaml and emits the full script, including the mysql_users entries from the tenants you list in shim.yaml — pick the same credentials your app already uses so the connection string only changes host:port:

$ bintrail proxysql-config --out -
BEGIN;

DELETE FROM mysql_servers WHERE hostgroup_id IN (990, 991);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (990, 'mysql.your-network.internal', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (991, '127.0.0.1', 3308);

DELETE FROM mysql_users WHERE default_hostgroup = 990;
INSERT INTO mysql_users (username, password, default_hostgroup, active) VALUES ('appuser', '*<sha1-hash>', 990, 1);

DELETE FROM mysql_query_rules WHERE rule_id IN (990001, 990002, 990003);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (990001, 1, '\b_flashback\.', 991, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (990002, 1, '\b_diff\.', 991, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (990003, 1, '\b_snapshot\.', 991, 1);

COMMIT;

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

Pipe it straight into the ProxySQL admin port and you’re routing:

bintrail proxysql-config --out - | mysql -h proxysql -P 6032 -u admin -padmin

Three rules. Two backends. ProxySQL becomes a transparent time-travel router; the application sees one MySQL connection, bintrail sees only the queries that need its help.

The time horizon is whatever you keep indexed, not your MySQL binlog retention.

Bintrail’s index is a regular MySQL table, partitioned by hour, with its own retention knob (bintrail rotate --retain 30d keeps a month, --retain 365d keeps a year). Events stream into the index in real time, so MySQL is free to roll its binlogs underneath without affecting how far back _flashback reaches. Partitions that age out of the live index can be archived to Parquet on S3, and _flashback queries merge live + archived results transparently.

Honest scope

A few caveats so you know what you’re getting:

• AS OF takes a literal timestamp string. Oracle-style SYSDATE - 5/1440 arithmetic isn’t supported. For now: AS OF '2026-04-15 09:30:00'.
• The syntax uses a virtual schema prefix (_flashback.<table>), not the inline <table> AS OF ... form. This is a deliberate trade-off: ProxySQL routes by a regex match on the schema prefix, which keeps the deployment dead simple. Three rules, no SQL parsing in the proxy layer.
• Auth scheme is currently mysql_native_password only. caching_sha2_password (the MySQL 8.0+ default) isn’t supported yet. On a fresh 8.0+ instance the application user has to be created with IDENTIFIED WITH mysql_native_password BY '<password>'. SHA2 support is on the roadmap.
• Point-lookup only. Today _flashback and _snapshot require a WHERE <pk_col> = <value> filter: they’re built for “what did this specific row look like at time X?” rather than full-table reconstruction. Walking every PK that ever existed in a table to rebuild the whole table at a past instant is a separate operation (bintrail recover, offline, generates SQL). Wiring full-table reconstruction into the shim as an interactive query is on the roadmap.

Try it

Bintrail is open source. The deployment is a few commands:

bintrail init   --index-dsn '...'        # create the index tables
bintrail stream --source-dsn '...' ...   # index your binlog in real time
bintrail shim   --index-dsn '...' ...    # serve the time-travel queries

Plus a one-shot bintrail proxysql-config to generate the ProxySQL routing SQL.

The rig in the repo is tested against ProxySQL 2.6.x LTS, the line the ProxySQL maintainers recommend for production deployments.

The repo also includes a docker-compose-driven end-to-end test that boots the full stack (MySQL, ProxySQL, bintrail-shim) on your laptop in under a minute, so you can run the queries above against a real deployment before committing to anything.

→ github.com/dbtrail/bintrail

For the skeptics:the chain mysql client → ProxySQL → bintrail → MySQL index is exercised end-to-end by a docker-compose-driven test that asserts wire-protocol behaviour, ProxySQL routing, and time-travel semantics together as one integrated unit. Run it locally with cd e2e/shim && ./run.sh.

MySQL spent decades being the major OLTP without point-in-time queries. That gap is closed.