DBTrail

Limitations

What DBTrail does not capture, cannot undo, or refuses to rebuild. Read this before you rely on it.

By design means intended; not yet means a known gap. DBTrail refuses out loud, with an error, a warning or a status word. A silent partial result is a bug: report it.

The analytical replica

  • Not high availability. Nothing fails over to the copy. A tool pointed at DBTrail's port gets no answers while DBTrail is down; your MySQL keeps working.
  • Not your backup. History starts the day you install it. Keep your own backups.
  • Seconds behind, not the last second. Answers from the copy include the changes captured since its last update (fresh mode, the default since 0.129.0): 1.2 seconds on average with about 1,200 rows changed per second, and in a heavier benchmark a median of 9 seconds and a worst of 289. In fast mode they are as old as the copy's last update. Updates are off until you turn them on, then as often as you choose, from every 5 minutes to once a day.
  • The MySQL port is beta and read-only by default. It offers TLS and does not require it. Without a certificate of your own, a client that asks for TLS gets it with a certificate DBTrail makes up at start, which no client can verify. Give the router yours (--tls-cert and --tls-key, or BINTRAIL_ROUTER_TLS_CERT and BINTRAIL_ROUTER_TLS_KEY) and clients can connect with --ssl-mode=VERIFY_IDENTITY: TLS on the MySQL port. With Engine Routing each statement goes to your MySQL or to the copy; without it the copy answers everything, in DuckDB's SQL: Query Parquet with MySQL. It does not tune a slow query: a query that needs an index still needs the index.
  • Read it through views.sql. Another tool reading the files directly can miss recent changes, with no error. For Spark, Trino or Athena: Export to Iceberg.
  • Spark misreads text in the Parquet files. Its default reader gets a text value wrong after an empty one, with no error. Set spark.sql.parquet.enableVectorizedReader=false, or read the Iceberg tables, which Spark reads correctly.
  • Row history needs an S3 bucket. Without one it is kept 48 hours. The copy itself stays. Retention.

SQL on the copy

The SQL card and a MySQL client run your statement in DuckDB. Most text and number answers match MySQL's: 'Paid' equals 'paid', 'café' equals 'cafe', 'ß' equals 'ss', a _bin column compares byte by byte, ROUND(SUM(amount), 2) prints 117329550.00, and NULLs sort where MySQL puts them. These still differ, with no error (by design):

Same statementMySQLThe copy
AVG(amount) on a DECIMAL(12,2)1.8000001.8
qty / 31.33331.3333333333333333
'Paid' LIKE 'paid'truefalse
count(DISTINCT status) over paid, Paid12
'a' = 'A' on a _cs columnfalsetrue
'bob ' = 'bob' on a utf8mb4_general_ci columntruefalse
ORDER BY an ENUM('z','a') columnz, aa, z
SELECT * on a table with a generated columnhas itone column fewer

REGEXP, instr and the duplicate removal of UNION see case too. On a MariaDB source the trailing-space row applies to every text column not declared nopad. Fine for a report a person reads; a program that compares the text it gets back can break. The full list.

Sources

SourceStatus
MySQL and Percona Server 8.0, 8.4 and 9.7, not the 9.0 to 9.6 Innovation releases (RDS and Aurora; Cloud SQL should work)Supported
PostgreSQL 15 to 18 (14 may work, best effort)Beta: limitations
MariaDB 10.11+Supported: what is untested

The index, where DBTrail keeps the changes, is always MySQL 8.0, 8.4 or 9.7 (by design).

An installation serves one database family: MySQL and MariaDB, or PostgreSQL. To use both, run a second installation.

What capture does not see

Captured: every INSERT, UPDATE and DELETE with before and after image, on InnoDB tables with a primary key, when the source uses binlog_format=ROW and binlog_row_image=FULL. Not in the binlog: TRUNCATE, DROP and RENAME; cascaded child rows before MySQL 9.6; PARTIAL_JSON updates; commits lost when sync_binlog is not 1. Nothing to undo for those; the way back is a backup taken before, and Restore repairs cascades.
  • The binlog must be ROW with full row images (by design). Otherwise capture does not start.
  • Tables without a primary key, or not InnoDB, are skipped (by design). Find them.
  • TRUNCATE, DROP, RENAME, CREATE OR REPLACE TABLE leave no rows in the binlog (by design, MySQL). Investigate → Schema changes lists them; the way back is a backup from before.
  • Rows deleted by a cascade (MySQL before 9.6, and MariaDB) are not in the binlog. Restore rebuilds most of them. Details. On MySQL 9.6 and later they are in the binlog, and recover-cascade undoes them since 0.133.0. Details.
  • JSON updates with binlog_row_value_options=PARTIAL_JSON are skipped (not yet). Fix: SET PERSIST binlog_row_value_options = '';
  • Tagged GTIDs (MySQL 8.3+, SET gtid_next = 'AUTOMATIC:<tag>') are not tracked (not yet). After a reconnect or restart their rows can be saved twice, and a purged binlog that held them shows as lost data. Only sources that set a tag on purpose are affected; capture those in position mode. Details.
  • sync_binlog other than 1: an operating-system crash can lose commits before they reach the binlog (by design, MySQL).
  • After a schema change, DBTrail reads the new schema before the next row. If it cannot, capture stops and retries; nothing is skipped. What happens on ALTER, CREATE, DROP.

Undo

  • DBTrail never runs the undo (by design). You review the script and run it.
  • Triggers fire again when you run it (by design, MySQL).
  • AUTO_INCREMENT is not reset. The script says so.
  • Caps: 1,000 changes per undo script by default, 10,000 at most.

Rebuilding a table at a moment

Backups kept and changes kept are two ranges ending now. The rebuildable window starts at the later of the two starts: the oldest rebuildable moment is the later of the oldest backup and the oldest change. Overview shows restore coverage per table.
  • Needs a snapshot from before that moment. Without one, a full-table rebuild is refused, never partial: the .sql build, reconstruct and _snapshot alike (since 0.130.0 for reconstruct --output-format mydumper, which wrote the changed rows only; --allow-changes-only still does).
  • Refused: a primary key of type FLOAT, DOUBLE, TIME, BIT, JSON, spatial or generated; a key value that changed in the window; a column added, dropped or retyped after the snapshot.
  • Can be refused for lack of disk or memory.
  • One-second resolution (not yet). A full-table rebuild can apply half of a transaction that straddles the requested second.
  • Time-travel SQL port (beta): its limitations.

Verification

A MATCH is a strong signal, not a certificate. What a MATCH proves.

Running it

  • You operate the index MySQL (by design): disk, backups, upgrades. Support scope.
  • A snapshot is read by the DBTrail that wrote it, or a newer one. Copy the small change files together with each table file.
  • DBTrail only reads your database. It installs nothing in it and never runs a script it generated. One exception you turn on yourself: Engine Routing passes a client's writes to your MySQL only when the router is started with --route-read-only=false (BINTRAIL_CONSOLE_ROUTE_READ_ONLY=0). By default the port only reads.
  • The web interface listens only on this machine in the bundled stack. To open it up: Web interface on a hostname.
  • The time-travel port takes no certificate of its own. It offers TLS with one it makes up at start, which no client can verify. Keep it on this machine, or put ProxySQL in front and set up TLS there.
  • Claude only reads. It cannot point DBTrail at another database.

Missing a limit, or blocked by a "not yet"? Open an issue.

On this page