Understanding MySQL Flashback: Principles, Tools, and Pseudo GTID

In the previous session we introduced database backup, but as instance size grows, recovery becomes slow. For small‑scale accidental deletions or updates without WHERE clauses, a fast rollback is needed.

MySQL Flashback (闪回) is a technique that leverages the ROW‑format binary log (binlog) to reverse DML operations. It originated from a feature contributed by Peng Lixun to mysqlbinlog and has been implemented in various open‑source tools such as binlog2sql , binlog_inspector , etc.

All these tools share the same principle: they parse the ROW‑format binlog, which records both before‑ and after‑values of each row change. By generating inverse statements (DELETE → INSERT, UPDATE → reverse UPDATE, INSERT → DELETE) the original state can be reconstructed.

The MySQL binlog consists of a 4‑byte header \xFEbin followed by a series of events. Each event contains an event header and event data. Since MySQL 5.0 the default version is V4. Important event types include WriteRowsEvent , UpdateRowsEvent , and DeleteRowsEvent . The structure of a UpdateRowsEvent (shown in the figure) already contains before_values and after_values , which greatly simplifies flashback tool development.

Flashback tools have some limitations:

They can only roll back DML; DDL statements lack sufficient information in the binlog.

Although theoretically any point in time can be restored, locating the exact binlog position often requires manual inspection, which can be time‑consuming.

To precisely delimit the change range, we inject a Pseudo GTID (P‑GTID) – an invisible event such as DROP VIEW IF EXISTS `.*?_pseudo_gtid_hint__` – into the master binlog. This marker allows the DBA to identify the start and end of a change set.

Pseudo GTID was proposed by Shlomi Noach and is used by tools like gh‑ost and orchestrator . By periodically inserting these invisible events, replication streams can carry unique identifiers that aid in topology discovery, replication lag monitoring, and cluster expansion.

In practice we rewrote the binlog2sql tool, combined it with P‑GTID detection, and integrated the solution into our OPS platform. When a rollback is required, operators can simply confirm the operation in the platform, and the system will automatically revert the database to its previous state.