On May 21, 2026, InfoQ published Bintrail: MySQL Time-Travel Queries Using Indexed Binlogs. Bintrail is an open-source layer operating behind ProxySQL that leverages indexed binlogs to retrofit MySQL with point-in-time data querying and row-level history tracking. Because it delivers time-travel queries without requiring any modifications to MySQL core or application code, it dramatically expands options for forensics, auditing, and incident investigations.
For providers supporting core enterprise systems for mid-market clients through custom development, this means that "even MySQL, which lacks temporal tables like PostgreSQL, can support full-fledged audit infrastructure." Beyond the MySQL core upgrades explored in MySQL 9.7 LTS SMB Custom Modernization, it adds the distinct dimension of point-in-time historical data querying. This article outlines the architecture and deployment procedures for building a DB forensics foundation in MySQL using Bintrail.
Why MySQL Came to Need "Time-Travel Queries"
| Dimension | Traditional Methods | Bintrail |
|---|---|---|
| Point-in-Time Queries | Full backup restoration → Spinning up a separate DB | AS OF '2026-05-01 12:00'-style specification in SQL |
| Row-by-row history | Audit tables implemented in application layer | Automatic reconstruction from binlog |
| MySQL modifications | Schema additions and trigger implementation required | Not required (indexing only) |
| Application modifications | Audit logging added at ORM level | Not required (via ProxySQL) |
| Storage | Doubled due to audit tables | Reuse of existing binlog |
| PCI / SOC 2 compliance | Partial compliance | Full compliance achievable |
| Incident investigation speed | Several hours to several days | A few minutes |
In other words, Bintrail is positioned to upend conventional wisdom in audit infrastructure design by "achieving point-in-time reference plus row history without modifying MySQL schemas or applications."
Three structural shifts driven by Bintrail
Structural shift 1: From full backup restoration to instant time travel
Previously, a request like "I want to view the customer master from 2:00 PM last Tuesday" required several hours to half a day of work: restoring a full backup, launching a separate database, and executing queries. With Bintrail, you can retrieve it instantly using SELECT * FROM customers AS OF '2026-05-15 14:00'-style SQL.
Structural shift 2: From implementing audit logs in applications to absorbing them at the ProxySQL layer
To satisfy audit requirements, having the application record before-and-after values in separate tables was a common pattern, but this caused a triple burden of application modifications, testing costs, and bloated audit tables. Because Bintrail absorbs this at the ProxySQL layer, you can design a system where the application remains completely untouched.
Structural shift 3: From migrating to PostgreSQL to meeting audit requirements while staying on MySQL
Until now, migrating to PostgreSQL (temporal tables), Oracle, or SQL Server was often debated to meet audit requirements. With Bintrail, you can achieve equivalent capabilities while staying on MySQL, providing a practical solution for data governance like the one we addressed in Monzo Governed Data Mesh client development.
Five phases of Bintrail + MySQL DB forensics implementation
Phase 1: Current state assessment (2 weeks)
- Inventory of existing MySQL clusters (versions, replica configurations, binlog settings)
- Identification of audit requirements (PCI, SOC 2, ISMS, PrivacyMark)
- Review of incident response history (how many man-hours were spent in the past)
- Inventory of existing audit log implementations (application layer / trigger layer)
- Feasibility of introducing an intermediary layer like ProxySQL or HAProxy
Phase 2: Design (1–2 weeks)
- Configuration design for placing Bintrail behind ProxySQL
- Determining binlog retention period + index retention period
- Storage estimation (S3, EBS, GCS)
- Extracting critical tables (customers, orders, payments, permissions)
- Access control (read-only roles + auditing roles)
- Decommissioning plan for existing audit log implementations
Phase 3: Phased rollout (3–4 weeks)
- Installing Bintrail in validation environments
- Routing existing applications through ProxySQL with zero downtime
- Load testing for indexing (impact on primary)
- Search performance benchmarking
- Production canary deployment (one cluster → all clusters)
Phase 4: Operational foundation building (2–3 weeks)
- Creating a collection of audit query templates (common incidents)
- Time travel SQL training (client SRE / security teams)
- Dashboard construction (Grafana, Metabase)
- Formatting monthly forensics reports
- Escalation procedures during incidents
Phase 5: Monthly forensics review (ongoing)
- Verifying audit requirement fulfillment
- Monitoring mean time to resolution for incident responses
- Optimizing binlog retention periods + storage costs
- Updating audit query templates
- Submitting audit reports
Standard technology stack set for custom development
| Layer | Recommended technology | Alternative |
|---|---|---|
| Core DB | MySQL 8.4 LTS / 9.7 LTS | MariaDB 11.6 |
| Time travel layer | Bintrail | Debezium + ClickHouse |
| Intermediary | ProxySQL | HAProxy + MaxScale |
| Binlog storage | S3 / GCS (lifecycle management) | Dedicated EBS / NFS |
| Indexes | Built-in to Bintrail | OpenSearch / Elasticsearch |
| Visualization | Grafana + Metabase | Looker |
| Audit role management | MySQL native role | HashiCorp Vault |
| Alerting | PagerDuty / Slack | Opsgenie |
Which projects need this and which do not
| Projects requiring this | Projects not requiring this |
|---|---|
| Running MySQL in production + audit requirements exist | Small scale with zero audit requirements |
| Compliance with PCI / SOC 2 / ISMS | Not subject to regulations |
| Frequent incident investigations | Simple read-heavy workload |
| Handles customer master, payments, and permissions | Temporary data only |
| Requests for "point-in-time queries" have emerged | Backup restoration is sufficient |
Six clauses to include in client contracts
| Clause | Details | What the client should verify |
|---|---|---|
| Retention period SLA | Binlog / index retention period | Alignment with statutory retention periods |
| Search performance SLA | Query response time for past N days | Required speed during audit investigations |
| Storage cost allocation | Cost boundaries between client and contractor | Settlement rules upon scaling |
| Incident SLA | Time from detection to investigation start | Response speed by severity level |
| Audit report | Monthly / audit-time deliverables | Legal / auditor review |
| Offboarding handover | Binlog + indexes + roles | Internal operational continuity |
ROI estimate (assumes 6 MySQL clusters with audit requirements)
| Item | Existing operations (current state) | With Bintrail | Difference |
|---|---|---|---|
| Incident investigation time per case | 16 hours on average | 1.5h | -14.5h |
| Incidents per year | 12 incidents | 12 incidents | Same |
| Backup restoration effort per year | 60h | 8h | -52h |
| Audit table operational effort per month | 30h | 5h | -25h |
| Failed audits / lost deal opportunities | Scale of ~1 incident/year | 0 incidents | Tens of millions of yen |
| Annual benefit | — | — | Approx. 18 million yen equivalent + preserved trust |
Calculated at an hourly rate of 8,000 yen, this delivers an annual labor cost reduction of over 14.4 million yen. Factoring in the prevention of lost business opportunities due to failed audits lowers the investment threshold even further. Because implementation and operational costs vary significantly depending on cluster scale, please treat this estimate strictly as a rough benchmark for potential benefits.
Five common pitfalls
Pitfall 1: Leaving the binlog format set to STATEMENT
Bintrail requires the binlog format to be ROW. If it is set to STATEMENT or MIXED, historical row values cannot be fully reconstructed, making changing the binary log format an absolute prerequisite.
Pitfall 2: ProxySQL as a single point of failure
Deploying ProxySQL as a single instance means all queries routed through Bintrail will fail if it goes down. An HA setup + automatic failover is essential.
Pitfall 3: Neglecting index bloat
Bintrail's indexes bloat in proportion to table update frequency. You must incorporate monthly size monitoring + archive procedures for old indexes into your operations.
Pitfall 4: Granting full permissions to the forensics team in access control
The authority to view historical data is a powerful privilege. Unless you build separation between auditing and viewing roles + audit logging for all queries into operations, Bintrail itself becomes an audit risk.
Pitfall 5: Halting PostgreSQL migration projects entirely
Deciding that "PostgreSQL migration is no longer needed since we can keep MySQL with Bintrail" overlooks other PostgreSQL-driven requirements (JSONB, full-text search, PostGIS). This decision should be evaluated solely based on audit requirements.
90-day action plan
| Week | Action |
|---|---|
| Week 1〜2 | Comprehensive MySQL cluster inventory + audit requirements evaluation |
| Week 3〜4 | Binlog format unification + ProxySQL HA design |
| Week 5〜7 | Phased Bintrail rollout (validation → staging → production canary) |
| Week 8〜9 | Development of forensics SQL templates |
| Week 10〜11 | Training for client SRE and security teams |
| Week 12〜13 | Launching monthly review meetings + initial audit report delivery |
Summary — Elevating MySQL to the centerpiece of your audit infrastructure
Bintrail marks a turning point that retrofits MySQL with time travel query capabilities comparable to PostgreSQL. From the perspective of supporting core enterprise databases at mid-sized companies, whether you can holistically assemble inventory, design, phased rollout, operational foundations, and monthly reviews determines the success or failure of your audit infrastructure.
If you want to "meet audit requirements while staying on MySQL," "cut incident investigation time by an order of magnitude," or "avoid migrating to PostgreSQL," we provide tailored estimates for DB forensics infrastructure based on your cluster configuration and audit requirements. Feel free to reach out via our contact form.
Sources
- Bintrail: MySQL Time-Travel Queries Using Indexed Binlogs（InfoQ）
- Updating Client Databases with MySQL 9.7 LTS (GH Media)
- Monzo Governed Data Mesh Client Development (GH Media)
- Continuous Profiling for Clients with Pyroscope 2 (GH Media)
- Source Code Confidential Information Audit Engagements (GH Media)
