DEVOURED
Bintrail: MySQL Time-Travel Queries Using Indexed Binlogs
Bintrail introduces time-travel and diff queries to MySQL via ProxySQL and indexed binlogs, enabling point-in-time recovery and audit without schema changes.
What: Daniel Guzman-Burgos's Bintrail project adds AS OF and BETWEEN time-travel queries and row-history lookups to MySQL, a feature traditionally lacking in the database. It works by parsing and indexing MySQL ROW-format binary logs behind ProxySQL, generating reversal SQL for recovery. This allows querying data as it existed at a past timestamp and reviewing row-level changes, independently of MySQL's binlog retention.
Why it matters: Bintrail fills a long-standing gap in MySQL's capabilities, providing native-like temporal querying that is crucial for modern audit, compliance, and disaster recovery needs, especially as automation in database operations increases the demand for precise historical data access.
Takeaway: If you operate MySQL databases and require point-in-time recovery or detailed audit trails, investigate Bintrail as a solution to introduce temporal querying capabilities without modifying your existing MySQL instances or application code.
Deep dive
- Bintrail is a new layer developed by Daniel Guzman-Burgos that brings point-in-time queries and row-history lookups to MySQL.
- It provides
AS OFandBETWEENtime-travel queries to MySQL, a feature available natively in Oracle, SQL Server, MariaDB, and via extensions in PostgreSQL. - The system operates transparently behind ProxySQL, routing historical query patterns (e.g.,
_flashback,_diff,_snapshot) to its own backend while regular MySQL traffic remains untouched. - Bintrail parses MySQL ROW-format binary logs, indexing every row event with full before/after images.
- It generates reversal SQL, allowing point-in-time recovery without needing the original binlog files.
- The indexed history store is maintained independently of MySQL's binlog retention, enabling historical queries over longer periods.
- It can optionally extend historical queries into archived Parquet data stored on S3.
- No
ALTER TABLEor special storage engine is required; it works with existing MySQL instances. - Current limitations include support only for literal timestamp queries, primary-key lookups, and capped full-table restores, with joins and complex filtering handled outside the shim layer.
- Bintrail is available on GitHub under the BUSL (Business Source License).
Decoder
- ProxySQL: A high-performance, high-availability, protocol-aware proxy for MySQL, which can route queries based on rules.
- Binlog (Binary Log): A log of all changes to a MySQL database, used for replication and point-in-time recovery.
ROW-formatmeans it logs changes at the row level. - AS OF query: A type of temporal query that allows retrieving the state of data as it existed at a specific past timestamp.
- BETWEEN query: A type of temporal query that allows retrieving all changes to data within a specified time range.
- GTID (Global Transaction Identifier): A unique identifier for a transaction committed on a MySQL server, used for easier replication and failover.
- BUSL (Business Source License): A source-available license that restricts production use for a certain period, after which the code becomes open source (e.g., Apache 2.0).
Original article
Full article content is not available for inline reading.
Explore all 62 articles in today's digest
Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi