A new layer called Bintrail has been introduced to enable point-in-time queries and row-history lookups for MySQL, a widely used relational database that lacks native temporal querying capabilities. The tool operates by using indexed binary logs (binlogs) behind ProxySQL, without requiring modifications to MySQL or application code.
Bintrail allows users to query data as it existed at a past timestamp and review a record of changes over time. The system is primarily intended for data recovery and audit scenarios, according to its developer, Renato Losio.
How Bintrail Works
Bintrail uses MySQL binary logs, which record all changes to the database, and indexes them to allow fast access. The indexed binlogs are then served via ProxySQL, a MySQL proxy that routes queries without altering the existing database setup.
This design means organizations can implement temporal querying without changing their database configuration or rewriting application code. The tool supports two main functions: retrieving a row as it appeared at a specific past moment, and listing all changes made to a row over a given time period.
Audience and Use Cases
Bintrail is targeted at database administrators, site reliability engineers, and engineers who need to audit historical data or recover from accidental changes. By providing a SQL interface to query past states, it aims to reduce reliance on full backups or complex replication setups for forensic analysis.
The tool addresses a notable gap in MySQL compared to other major relational databases such as PostgreSQL (which supports temporal queries via extensions like pg_bitemporal) and Oracle (which offers Flashback Query). MySQL has no built-in way to run queries against historical data without external tools.
Technical Requirements
Using Bintrail requires enabling binary logging on the MySQL server and installing the Bintrail layer between the application and the database via ProxySQL. The layer then parses and indexes binlogs in near real time, storing the index for later querying.
Indexed binlogs are stored separately, minimizing overhead on the primary database. Queries are executed against the index, not against the live database, which ensures no performance impact on production workloads.
Implications for Database Operations
For organizations that rely on MySQL for transactional systems, Bintrail introduces a way to perform forensic analysis without restoring backups or using log parsing tools. This could reduce recovery time from incidents such as accidental data deletion or corruption.
However, the tool adds an additional component to the database stack and requires managing ProxySQL and binlog indexing infrastructure. The long term viability of Bintrail will depend on its adoption by the MySQL community and its performance under high write loads.
As of the publication date, Bintrail is available as an open source project. No official roadmap for integration with MySQL upstream or commercial support has been announced.
