Introducing Bintrail: Point-in-Time Queries for MySQL

<p>In the evolving landscape of database management, the need for temporal querying is increasingly crucial. Enter <strong>Bintrail</strong>, a groundbreaking layer that introduces point-in-time queries and row-history lookups to MySQL. Although MySQL is a widely-utilized relational database, it has historically lacked native support for temporal querying—something every other major transactional database provides out of the box. Bintrail bridges this gap without requiring modifications to either MySQL or application code.</p>

<h2>How Does Bintrail Work?</h2>

<p>Bintrail operates with the help of indexed binary logs managed by <strong>ProxySQL</strong>. This enables users to execute queries based on historical timestamps and review detailed data change histories. For instance, administrators can conduct audits or recover lost data more efficiently. Bintrail employs two primary queries: <code>AS OF</code> and <code>BETWEEN</code>, facilitating what can be described as 'time travel' queries within standard MySQL setups.</p>

<p>By parsing MySQL ROW-format binary logs and indexing each row event—capturing comprehensive before and after images—Bintrail constructs reversal SQL for point-in-time recovery. Remarkably, this can be done without the original binary logs, which simplifies the operational tasks of recovery and auditing considerably.</p>

<h2>Why Was Bintrail Developed?</h2>

<blockquote>
    <p>As Daniel Guzman-Burgos, the database specialist behind Bintrail, pointed out, "Last month I mapped out how every major OLTP except MySQL gives you point-in-time queries out of the box..."</p>
</blockquote>

<p>This observation highlights a notable disparity between MySQL and other major OLTPs. For instance, Oracle offers <code>AS OF TIMESTAMP</code>, while SQL Server employs <code>FOR SYSTEM_TIME AS OF</code>. Given that MySQL does not natively facilitate this kind of querying, Guzman-Burgos felt compelled to create a solution that fills this operational gap.</p>

<h2>The Advantages of Using Bintrail</h2>

<p>Traditional MySQL setups often rely heavily on binary logs for recovery or historical inspection. With Bintrail, querying past states of data becomes direct and intuitive—as simple as executing a SQL statement.</p>

<p>For example, you can retrieve the state of a specific order at any point in the past:</p>
<pre>

SELECT * FROM _flashback.orders
AS OF '2026-04-15 09:30:00'
WHERE id = 42;

<p>Or you can view every change made to that order within a specified time window:</p>
<pre>

SELECT * FROM _diff.orders
BETWEEN '2026-04-15 00:00:00' AND '2026-04-15 23:59:59'
WHERE id = 42;

<h2>Features of Bintrail</h2>

<p>What makes Bintrail particularly appealing is its ability to integrate seamlessly with existing MySQL architecture. Administrators can effortlessly point their connections to ProxySQL instead of the MySQL port, thus enabling time travel queries without any alterations to the database engine or application code.</p>

<p>Moreover, Bintrail can automatically establish routing rules in ProxySQL to manage historical queries—including patterns like <code>_flashback</code>, <code>_diff</code>, and <code>_snapshot</code>. This flexibility allows users to query data efficiently while keeping standard MySQL traffic unaffected.</p>

<blockquote>
    <p>Peter Zaitsev, founder of Percona, praised the innovation, stating, "Daniel Guzmán Burgos continues to do amazing work with efficient solutions for MySQL recoverability."</p>
</blockquote>

<h2>Limitations and Considerations</h2>

<p>While Bintrail showcases impressive functionality, it does come with some limitations. Currently, it supports only literal timestamp queries and primary-key lookups. Full-table restores are capped, and more complex filtering functions require handling outside of Bintrail's layer. However, this is a small trade-off for the advances it offers in data recoverability.</p>

<p>To sum up, Bintrail is making significant strides in making MySQL a more robust platform for data management by introducing point-in-time queries and enhancing historical data accessibility.</p>

<p>Developers and database administrators interested in implementing Bintrail can find it on GitHub, where it is available under the BUSL, a source-available license.</p>