MariaDB GTID Info & Flashback Plugin

Reversing Transactions Directly from the Binary Log

Some ideas never completely disappear.

They remain somewhere in your brain, waiting for the right opportunity—or the right API—to come back.

For me, recovering rows from the binary log is one of those ideas.

More than ten years ago, I started experimenting with a simple question:

If row-based replication stores the values modified by a transaction, couldn’t we use those values to reverse the transaction?

The answer was yes.

And now, with my new gtid_info plugin for MariaDB Server, I am revisiting that idea with a much cleaner interface: SQL functions operating directly on MariaDB Server GTIDs.

The plugin is available on GitHub:

https://github.com/lefred/mariadb-plugin-gtid-info

Let’s have a look.

A Long-Standing Interest in Binary Log Recovery

This is certainly not the first time I have played with this concept.

In 2015, I presented Undelete rows from the binary log in the MySQL and Friends DevRoom at FOSDEM.

The idea was to inspect row events stored in the binary log and transform them into opposite operations:

• a deleted row could be inserted again;
• an inserted row could be deleted;
• an updated row could be restored to its previous values.

The FOSDEM session was deliberately described as a hacking session. It showed how to locate the appropriate events in a binary log and manipulate them using relatively simple Python code. It covered MySQL 5.5, MySQL 5.6, and MariaDB.

The related code is still available in my old MyUndelete repository:

https://github.com/lefred/MyUndelete

The script reads a row-based binary log between two positions and can reverse DELETE, INSERT, and UPDATE row events. It was an experimental tool—especially the update-reversal part—but it demonstrated that the information required to undo many data changes was already present in the binary log.

One year later, I expanded the subject in another presentation:

Undelete (and more) rows from the binary log

https://www.slideshare.net/slideshow/undelete-and-more-rows-from-the-binary-log/62224281

So yes, I have been interested in this technique for quite some time.

The Principle Is Simple

With row-based binary logging, the server records row events describing the data modifications.

Conceptually, flashback consists of reversing those events:

Original event	Reverse operation
WRITE_ROWS	DELETE the inserted row
DELETE_ROWS	INSERT the deleted row
UPDATE_ROWS	UPDATE the row from its after-image back to its before-image

Of course, the concept is simpler than the implementation.

The binary log does not contain SQL statements ready to copy and paste. It contains encoded events, table maps, column bitmaps, metadata, and row values in the binary log format.

To generate useful SQL, we must:

1. locate the correct transaction;
2. decode its row events;
3. identify the associated database and table;
4. match the binary log column positions with the current table definition;
5. serialize every value correctly;
6. reverse the order of operations when multiple transactions are involved.

And, most importantly, we must be sure that the binary log contains enough information to reconstruct the original rows.

From File Positions to GTIDs

My old MyUndelete script worked with binary log filenames and positions.

For example:

./MyUndelete.py \
  --binlog=/var/lib/mysql/mysql-bin.000008 \
  --start=1514 \
  --end=1725

That worked, but finding the exact boundaries of the transaction was part of the exercise.

MariaDB GTIDs provide a much better way to identify a transaction.

Instead of saying:

Reverse the transaction located between position 1514 and position 1725 in mysql-bin.000008.

we can now say:

Reverse GTID 0-1-123.

That is exactly what the new plugin does.

Why a MariaDB Plugin This Time?

While I was working for MySQL, I also tried to turn this idea into a server-side component.

The concept was the same: access the binary log from inside the server, locate transactions, decode row events, and expose the result through a convenient interface.

However, implementing it as a MySQL component quickly became very complex.

The main difficulty was not the flashback logic itself. I had already experimented with reversing row events in MyUndelete. The difficult part was getting proper access to the binary log machinery from a component.

Binary logging is deeply integrated into the server, but that does not necessarily mean it is easily accessible to an external component. Reusing the internal binary log reader, navigating the retained files, decoding events, and accessing the required server structures involved significantly more complexity than expected.

In practice, the APIs and internal boundaries made the implementation difficult to maintain and tightly coupled to server internals.

I had some working code, but with MySQL Component Architecture updates, it was impossible for a component to keep loading some parts of the server, and with no services available, I had to give up. Once a service was made available for the binary logs, there was nothing for binlog events… back to the beginning of the loop.

With MariaDB, the experience was very different.

MariaDB’s plugin interface made it much easier to integrate the functionality directly into the server. The plugin can register native SQL functions while still using the server’s own binary log classes and internal event-decoding logic.

Meanwhile, MariaDB Server introduced something similar with the flashback option for the mysqlbinlog client.

This means I did not need to reimplement the binary log format in an external tool or create a separate parser that might slowly diverge from the server implementation.

The plugin can use the same internal structures MariaDB itself uses to read events such as:

• GTID events;
• table-map events;
• row events;
• transaction boundaries;
• binary log metadata.

Of course, this still requires knowledge of MariaDB’s internal binary log implementation. It is not a public, stable API where everything is exposed through a few documented calls.

But compared with my earlier attempt to implement similar functionality as a MySQL component, MariaDB’s plugin architecture provided a much more direct and practical path.

This difference is one of the reasons the idea finally evolved from an external Python experiment into a native server plugin.

It is also a good example of why extensibility matters.

A useful plugin interface is not only about adding an authentication method, an information-schema table, or a storage engine. It can also make it possible to experiment with server internals and expose entirely new operational capabilities without maintaining a complete fork of the database server.

Introducing the gtid_info Plugin

The plugin initially started with four SQL functions focused on locating GTIDs and generating flashback operations.

But, as often happens when I start playing with binary logs, I found more information that could be useful to expose.

The plugin now registers eight SQL functions:

- GTID_INFO(gtid)
- GTID_AT(datetime)

- BINLOG_GTID_LIST(filename) 
- BINLOG_GTID_LIST_GAPS(filename) 
- BINLOG_GTID_LIST_RANGES(filename) 
- GTID_LIST_BINLOGS(gtid_set)

- GTID_FLASHBACK(gtid)
- GTID_FLASHBACK_TO(gtid[, execute])

The functions can be divided into three groups.

The first group helps locate and inspect transactions:

GTID_INFO(gtid)
GTID_AT(datetime)

The second group maps GTIDs to retained binary log files and helps analyze the continuity of their sequence numbers:

BINLOG_GTID_LIST(filename)
BINLOG_GTID_LIST_GAPS(filename)
BINLOG_GTID_LIST_RANGES(filename)
GTID_LIST_BINLOGS(gtid_set)

And finally, the flashback functions generate—or optionally execute—the reverse row operations:

GTID_FLASHBACK(gtid[, execute])
GTID_FLASHBACK_TO(gtid[, execute])

They all share a common goal: making the content and history stored in MariaDB binary logs easier to explore and use directly from SQL.

After building the plugin with MariaDB Server, it can be installed with:

INSTALL SONAME 'gtid_info';

The functions then become directly available from SQL.

The plugin currently requires traditional file-based binary logs. It cannot operate when binary logging is disabled or when an engine-backed binary log implementation is configured.

Finding a Transaction with GTID_INFO()

Before reversing anything, it can be useful to inspect the transaction.

SELECT JSON_PRETTY(GTID_INFO('0-1-3'))\G

The function scans the retained local binary logs and returns information about the requested GTID.

A result can look like this:

{
  "gtid": "0-1-3",
  "domain_id": 0,
  "server_id": 1,
  "sequence_no": 3,
  "present": true,
  "binlog": "./mysql-bin.000001",
  "binlog_server_version": "13.1.0-MariaDB-log",
  "start_position": 833,
  "end_position": 1085,
  "size": 252,
  "event_count": 5,
  "event_types": [
    "Xid",
    "Table_map",
    "Write_rows_v1",
    "Annotate_rows",
    "Gtid"
  ]
}

This already replaces much of the manual binary log exploration.

The returned JSON includes the binary log file, transaction boundaries, event types, timestamps, GTID flags, compression information, and an analysis of the row images.

When "present" is false, the GTID was not found in the retained local binary logs.

That may mean that:

• the corresponding binary log was purged;
• the transaction originated on another server;
• or the GTID never existed on this server.

The current implementation deliberately scans the retained binary log files itself. It does not yet use MariaDB’s internal binary log GTID index to jump directly to the most likely file and position.

This is something that could be optimized later.

Exploring the GTIDs Stored in a Binary Log

While working on the flashback functions, I also wanted an easy way to answer some related questions:

• Which GTIDs are stored in this binary log?
• Are there any apparent gaps in their sequence numbers?
• Can consecutive GTIDs be displayed more compactly?
• Which binary logs contain the GTIDs I am interested in?

Usually, answering these questions requires using mariadb-binlog, parsing its output, or writing a small script.

The plugin now exposes that information directly through SQL.

This is the kind of information and function usually required to automate point-in-time recovery by an operator in the Kubernetes environment.

Listing the GTIDs in One Binary Log

The BINLOG_GTID_LIST() function reads one retained binary log and returns the GTIDs found in its actual Gtid events:

SELECT BINLOG_GTID_LIST('mysql-bin.000001');

For example:

0-1-1,0-1-2,1-1-1

The filename can be the value displayed by:

SHOW BINARY LOGS;

It can also be the indexed path returned by GTID_INFO():

./mysql-bin.000001

Only files that are still present in the server’s binary log index can be inspected.

This is important because the plugin does not simply open any arbitrary file from the filesystem. It operates on the binary logs currently retained and known by the server.

Looking for Gaps in a GTID Sequence

MariaDB GTIDs contain three parts:

domain_id-server_id-sequence_no

For a given domain and server pair, sequence numbers are expected to increase.

The new BINLOG_GTID_LIST_GAPS() function reports missing sequence numbers between the lowest and highest values observed in one binary log:

SELECT BINLOG_GTID_LIST_GAPS('mysql-bin.000001');

Imagine that the file contains:

0-1-1,0-1-2,0-1-4,0-1-5

The result would report:

0-1-3

This is especially useful when investigating binary logs, copied files, unusual replication histories, or the output of test environments.

However, the result needs to be interpreted correctly.

The function only detects internal gaps in the GTIDs physically present in that file. It does not report values before the first observed GTID or after the last one.

For example, if a binary log contains:

0-1-10,0-1-11,0-1-12

the function does not claim that 0-1-1 through 0-1-9 are missing. They may simply be stored in an older binary log.

An empty result means that no internal gap was found for the domain and server pairs present in the inspected file.

It does not necessarily mean that the server’s complete history has no gaps.

Displaying GTIDs as Ranges

A long list of consecutive GTIDs can quickly become difficult to read. And as I come from the MySQL world where GTIDs are different and old habits are difficult to change, I implemented this new function:

BINLOG_GTID_LIST_RANGES(filename)

The function sorts the GTIDs by domain, server, and sequence number and compresses consecutive values into ranges.

For example, this list:

0-1-3,0-1-5,1-1-1,1-1-2,1-1-3

becomes:

0-1-3,0-1-5,1-1-1:1-1-3

Single values remain unchanged, while consecutive sequences are represented using:

first_gtid:last_gtid

This output is much easier to read when a binary log contains many transactions:

SELECT BINLOG_GTID_LIST_RANGES('mysql-bin.000001');

It also provides a quick visual indication of the continuity—or discontinuity—of GTID sequences inside the file.

Finding the Binary Logs for a GTID Set

The reverse question is also useful.

Instead of asking which GTIDs are in a file, we may want to ask which retained binary logs contain a set of specific GTIDs.

For this, the plugin provides:

GTID_LIST_BINLOGS(gtid_set)

For example:

SELECT GTID_LIST_BINLOGS('0-1-2,1-1-4');

The result is a JSON array:

[
  "./mysql-bin.000001",
  "./mysql-bin.000003"
]

The files are returned in binary log index order, from the oldest matching file to the newest.

A filename is included only once, even when it contains several of the requested GTIDs.

When using InnoDB-Based Binary Logs, the list is of course different:

If none of the exact GTIDs are still retained locally, the function returns an empty JSON array:

[]

Flashing Back One GTID

Now we arrive at the function that originally motivated most of this plugin:

GTID_FLASHBACK(gtid[, execute])

By default, the function locates the requested GTID in the retained binary logs and returns the reverse row DML without executing it.

Suppose we have this table:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

And somebody inserts a row:

INSERT INTO customers
VALUES (42, 'Fred', 'Brussels');

The transaction is written to the binary log as a WRITE_ROWS event.

If its GTID is 0-1-123, we can ask the plugin to generate the reverse operation:

SELECT GTID_FLASHBACK('0-1-123')\G

The returned SQL could look like this:

DELETE FROM `test`.`customers`
WHERE `id` <=> 42
  AND `name` <=> 'Fred'
  AND `city` <=> 'Brussels';

The plugin uses MariaDB’s null-safe equality operator, <=>, when generating predicates.

This is important because a normal equality comparison does not match NULL:

NULL = NULL

does not evaluate to true, while:

NULL <=> NULL

does.

The generated predicates therefore work correctly with nullable columns.

Reversing a DELETE

Let’s now delete that row:

DELETE FROM customers WHERE id = 42;

The row-based binary log contains a DELETE_ROWS event with the deleted row image.

Calling GTID_FLASHBACK() for that transaction generates an INSERT:

INSERT INTO `test`.`customers`
       (`id`, `name`, `city`)
VALUES (42, 'Fred', 'Brussels');

The data required to undelete the row comes directly from the row image stored in the binary log.

This is the same basic idea demonstrated by MyUndelete more than ten years ago, but it is now exposed as a MariaDB SQL function and identified using a GTID rather than manually supplied binary log positions.

Reversing an UPDATE

Updates are more interesting because a row event can contain both a before-image and an after-image.

Imagine this change:

UPDATE customers
   SET city = 'Ghent'
 WHERE id = 42;

The original row was:

42, Fred, Brussels

The new row is:

42, Fred, Ghent

The reverse SQL must restore the before-image while ensuring that it modifies the row matching the after-image:

UPDATE `test`.`customers`
   SET `id`   = 42,
       `name` = 'Fred',
       `city` = 'Brussels'
 WHERE `id`   <=> 42
   AND `name` <=> 'Fred'
   AND `city` <=> 'Ghent';

This detail is important.

The generated query does not simply use the primary key. It uses the available after-image as the predicate.

This reduces the risk of overwriting a row that has already changed again since the original transaction.

However, this is still not magic. You must always review the generated SQL and understand the current state of the data before executing it.

Preview First, Execute Later

By default, the plugin only generates and returns the reverse SQL.

SELECT GTID_FLASHBACK('0-1-123');

This is the mode I recommend using first.

You can inspect the generated statements, save them, modify them, test them on another server, or wrap them in your own recovery procedure.

The plugin also supports an execution mode:

SELECT GTID_FLASHBACK('0-1-123', 1);

When execution is enabled, the reverse SQL is run through an internal local SQL-service connection.

The function still returns the generated SQL so that it remains visible for review and auditing.

As this is a scalar function called from a SELECT, the client receives a result row rather than a traditional protocol-level OK packet. The returned text therefore includes an execution summary such as:

Query OK, 1 row affected

followed by the generated reverse statement.

This feature is powerful. Use it with care!

Or, to reuse the warning from my original script:

*** WARNING *** USE WITH CARE ****

Flashback to a Previous GTID

Undoing a single transaction is useful, but sometimes the goal is to return a replication domain to an earlier state.

For this, the plugin provides:

GTID_FLASHBACK_TO(gtid[, execute])

Suppose the current GTID position for domain 0 is:

0-1-7

And we execute:

SELECT GTID_FLASHBACK_TO('0-1-5');

The function generates reverse DML for:

0-1-7
0-1-6

GTID 0-1-5 itself is preserved.

Notice the order.

Transactions must be undone from newest to oldest.

If the original execution order was:

0-1-5
0-1-6
0-1-7

the reverse order must be:

0-1-7
0-1-6

This follows the same principle as rolling back operations on a stack: the last change performed is the first one that must be undone.

To generate the SQL only:

SELECT GTID_FLASHBACK_TO('0-1-5');

To generate and execute it, same way as before:

SELECT GTID_FLASHBACK_TO('0-1-5', 1);

The function reads the current GTID position, finds the current GTID for the requested domain, and processes every later retained GTID up to that position.

Finding the GTID for a Point in Time

The plugin also contains another function that fits very well with flashback:

GTID_AT(datetime)

This function scans the retained local binary logs and returns the GTID position that would have been visible at a given time.

For example:

SELECT GTID_AT('2026-07-02 21:03:05');

could return:

0-1-3

You could then use that result as the starting point for a flashback operation.

Conceptually:

SET @target_gtid = GTID_AT('2026-07-02 21:03:05');

SELECT GTID_FLASHBACK_TO(@target_gtid);

This starts to resemble a transaction-oriented point-in-time rewind mechanism.

There is an important limitation: binary log event timestamps currently have one-second precision.

When multiple transactions commit during the same second, GTID_AT() cannot determine a more precise ordering based on the timestamp alone.

GTID-based recovery is therefore preferable whenever the exact transaction is known.

Full Row Images Are Essential

Flashback depends entirely on the information available in the binary log.

For safe and complete reconstruction, the row events must contain complete row images.

In practice, this means using row-based binary logging with full row images.

For example:

binlog_format=ROW
binlog_row_image=FULL

With minimal row images, MariaDB may omit columns that are not required for replication.

That is perfectly valid for replication, but it creates a problem for flashback: a value that was not written to the binary log cannot be reconstructed later.

For this reason, the plugin refuses transactions containing incomplete row images rather than silently producing potentially unsafe SQL.

This is an important design decision.

A recovery tool should fail loudly when it cannot guarantee that it has all the required data.

The Table Definition Still Matters

The binary log contains table identifiers and column positions, but it does not provide the SQL column names required to produce readable statements.

The plugin uses the current live table definition to map the row-event columns to their names.

Consequently, the table must still exist, and its column ordering must remain compatible with the binary log event.

For example, if the transaction was originally written for:

CREATE TABLE t1 (
    id INT,
    name VARCHAR(100)
);

but the table has since been dropped and recreated as:

CREATE TABLE t1 (
    comment VARCHAR(200),
    id INT,
    name VARCHAR(100)
);

the binary log column positions no longer correspond to the same live definition.

The plugin must not pretend that this is safe.

Schema changes between the original transaction and the flashback operation therefore need to be considered carefully.

What About DDL?

The current flashback implementation focuses on row DML:

• INSERT;
• DELETE;
• UPDATE.

It does not attempt to reverse arbitrary DDL.

Undoing:

DROP TABLE important_data;

is fundamentally different from reversing a row event.

The binary log may tell us that the statement happened, but it does not necessarily contain everything required to reconstruct the previous table definition and all its contents.

This is why backups remain essential.

Flashback is an additional recovery tool. It is not a replacement for a proper backup strategy.

What Happens When a Binary Log Was Purged?

The plugin only works with retained local binary logs.

For GTID_FLASHBACK_TO(), the requested target GTID must still be present.

This requirement is intentional.

Suppose the server still contains transactions 0-1-100 through 0-1-110, but older binary logs containing 0-1-90 through 0-1-99 have been purged.

Requesting:

SELECT GTID_FLASHBACK_TO('0-1-90');

would be unsafe.

The plugin could generate reverse operations for the transactions it still sees, but it could not prove that the generated sequence was complete.

Instead of silently generating a partial rollback, it refuses the operation when the anchor GTID cannot be found.

Again, failing is better than pretending.

Not a Replacement for Backups

Let’s be very clear.

Binary log flashback is not a backup.

It relies on several conditions:

• binary logging must have been enabled;
• the relevant binary logs must still exist;
• row-based events must have been recorded;
• the row images must contain sufficient data;
• the tables must still exist with compatible definitions;
• supported data types must be used;
• the current data must not conflict with the generated reverse operations.

A backup can restore an entire server, database, table, or dataset independently of the current live schema and binary log retention.

Flashback is more surgical.

It is useful when you know which transaction—or which sequence of transactions—must be reversed and the required row events are still available.

The two techniques complement each other.

From a Python Hack to a MariaDB Plugin

Looking back, the evolution is interesting.

The first version was a Python script operating directly on a binary log file and a pair of positions.

It was useful for demonstrating the concept:

DELETE_ROWS → INSERT
WRITE_ROWS  → DELETE
UPDATE_ROWS → reverse UPDATE

The new implementation moves the same idea much closer to the server.

Instead of parsing positions manually, we use GTIDs.

Instead of running an external script, we call SQL functions.

Instead of only producing a best-effort recovery operation, the plugin verifies row-image completeness and rejects transactions that cannot be reversed safely.

And instead of reversing only one known event range, we can generate a reverse sequence from the current GTID position back to a retained GTID anchor.

This is the part I find particularly exciting.

GTIDs are usually discussed in the context of replication topology and transaction tracking.

But they are also stable transaction identifiers.

Once a transaction can be identified, located, inspected, and decoded, it becomes a natural recovery boundary.

Conclusion

More than ten years after my first experiments with undeleting rows from binary logs, I am still convinced that row events are useful for much more than replication.

They contain a detailed history of data changes.

The new gtid_info plugin starts exposing that history through eight SQL functions:

SELECT GTID_INFO('0-1-123'); 
SELECT GTID_AT('2026-07-02 21:03:05'); 
SELECT BINLOG_GTID_LIST('mysql-bin.000001'); 
SELECT BINLOG_GTID_LIST_GAPS('mysql-bin.000001'); 
SELECT BINLOG_GTID_LIST_RANGES('mysql-bin.000001'); 
SELECT GTID_LIST_BINLOGS('0-1-123,1-1-45'); 
SELECT GTID_FLASHBACK('0-1-123'); 
SELECT GTID_FLASHBACK_TO('0-1-120');

The flashback implementation is intentionally conservative.

It requires binary logs to be enabled (traditional files or stored in InnoDB), complete row images, compatible live table definitions, and transactions containing reversible row DML.

The code is available here:

https://github.com/lefred/mariadb-plugin-gtid-info

This is still experimental work, and there is room for improvement—especially around faster GTID lookup, broader type coverage, and additional safety and auditing features.

But for me, it is also a nice continuation of an experiment that started long ago with a Python script, some binary log positions, and a simple question:

Can we undo this transaction using only the binary log?

Today, with MariaDB GTIDs and the flexibility of MariaDB’s plugin interface, the answer is not only becoming easier to use, but also easier to inspect and understand.