The migration script had been reviewed by two people. It still shipped an UPDATE without its WHERE clause, and at 16:40 on a Friday the support dashboard lit up: every one of our forty-odd thousand customer rows now had status suspended. The textbook answer is point-in-time recovery — restore last night's backup to a scratch instance, replay nineteen hours of binary log, stop one transaction before the mistake, and somehow get that state back into production. On a database this size that is hours of downtime. We were back in eleven minutes, because MariaDB can run the binary log backwards.
That feature is flashback: mariadb-binlog with the --flashback flag reads row events and emits their inverse, turning a logged mistake into a script that un-applies it. This post is the operational guide I wish we had before that Friday: what flashback is, the prerequisites to set now (not during the incident), its hard limits, and how it fits with classic point-in-time recovery when flashback cannot save you.
What flashback actually is
mariadb-binlog is MariaDB's binlog inspection tool, the mysqlbinlog fork renamed in the 10.5 era. In its normal mode it decodes the binary log into the statements and row images it contains. With --flashback, added in MariaDB 10.2 from work contributed out of Alibaba's AliSQL tree, it walks a range of row events in reverse order and writes compensating DML: an INSERT becomes a DELETE of exactly that row, a DELETE becomes an INSERT of the deleted before-image, and an UPDATE becomes an UPDATE with the before and after images swapped. Apply that output to the server and you have algebraically undone the transaction — not by restoring old files, but by appending new transactions whose net effect cancels the bad ones.
The distinction matters. A restore replaces the whole database and asks you to reconcile everything that happened since. Flashback surgically reverses one transaction and leaves everything else — the good writes before and after — untouched.
The prerequisites, checked before you need them
Flashback works on row events, so binlog_format must be ROW. Statement-format events cannot be inverted; there is no reliable before-image inside a logged UPDATE statement, only the text of the damage.
The second requirement is the one that bites: binlog_row_image must be FULL. With MINIMAL, the before-image of an UPDATE logs only the primary key and the changed columns, and a DELETE logs only the key. A partial before-image cannot reconstruct a deleted row — half the inverse transaction is simply missing. FULL logs every column in every before-image, which costs binlog volume every day so that it can save you on exactly one day. Set it now; you cannot retrofit it onto binlogs already written.
SELECT @@global.binlog_format, @@global.binlog_row_image;
-- what do we have on disk, and how far back does it go
SHOW BINARY LOGS;
-- peek at the start of a binlog file
SHOW BINLOG EVENTS IN 'mariadb-bin.000412' FROM 4 LIMIT 10;The third prerequisite is retention: the bad transaction must still be inside your retained binlogs, governed by binlog_expire_logs_seconds. If you expire binlogs after 24 hours and discover the damage on Monday, flashback is off the table. I treat 72 hours as the floor for any database a human can write to. Finally, the table's shape must be unchanged since the mistake — flashback replays old row images, and an ALTER TABLE in between means the old images no longer map to the current columns.
Finding the damage and generating the undo
First locate the transaction. Decode the binlog around the incident window with mariadb-binlog --base64-output=DECODE-ROWS -v and read it: every row event group is preceded by a Gtid event, and the decoded output shows the exact position where the bad transaction starts and the position where it commits. Those two numbers are the bracket for the undo. In our Friday incident, the whole thing was one transaction — one GTID, about forty thousand row events.
-- generate compensating DML for just that transaction (run in a shell, not the SQL client)
mariadb-binlog --flashback --start-position=18345672 --stop-position=18409155 /var/lib/mysql/mariadb-bin.000412 > /root/undo-users.sqlThen stop and verify before you apply anything. The undo file is executable SQL, but the row payloads inside it are base64 BINLOG blocks, not readable row-by-row DML — the decoded review you did with -v is where you eyeball the actual rows, and MariaDB warns that the verbose decode is for inspection, not for feeding back into the server. So verify what you can: count the inverse row events against the expected blast radius (grep -c '^BINLOG' on the undo file gets you that number), confirm the GTID events at the bracket edges belong to the transaction you meant, and spot-check a few before-images from the decoded output against the live table. This is the step where you catch a wrong position bracket, and it is much cheaper to catch it here than after applying forty thousand inverse rows. When you are confident, apply the file on the primary inside a single transaction, and let it replicate normally — the compensating changes flow to every replica and fix them too, which is almost always what you want. Suppress the binlog when applying and you have fixed the primary while every replica keeps serving the bad data. Note what flashback does to history: it does not rewrite it. The bad GTID stays in the binlog forever; your undo is a new transaction appended on top, which is exactly how time travel should work.
The honest limits
Flashback cannot touch DDL. DROP TABLE, TRUNCATE, and ALTER are logged as statements, not row events, and there is no inverse for a dropped table except a backup. If the incident report starts with the word DROP, skip everything above and go straight to point-in-time recovery.
Second, flashback undoes everything inside your position bracket and nothing outside it. Rows written after the stop position stay. If the application kept working for an hour on top of the corrupted state — new orders referencing the suspended customers, say — the compensating statements can collide with that newer reality: an inverse INSERT re-creating a deleted row will fail with a duplicate key if someone re-created it since. The longer the gap between mistake and undo, the more the fix has to negotiate with the present. Freeze writes to the affected table (or pause the app worker that writes it) the moment you decide to flash back.
Third, size cuts both ways. A two-gigabyte transaction produces a two-gigabyte undo file, which the server then has to apply, binlog, and replicate — and mariadb-binlog holds the flashback events in memory while it builds that file, so a monster transaction can exhaust the tool's RAM before you ever get to apply anything. Eleven minutes was our number for forty thousand rows; do your own math for forty million.
When flashback cannot save you: classic point-in-time recovery
The fallback path deserves a rehearsal before you need it. Provision a scratch instance, restore the most recent full backup with mariabackup, and replay the binlog from the backup's recorded position up to just before the bad transaction using --start-position and --stop-position. From there you have choices: if the damage is confined to one table, export just that table and import it back; if the damage is global, skip the bad GTID, let the scratch instance finish catching up, and promote it.
The cheapest point-in-time recovery appliance, though, is a delayed replica: a normal replica configured to apply the binlog one hour behind. When somebody drops the wrong thing, the mistake has not reached the delayed copy yet — stop it before the bad transaction applies, and you have a clean, recent, already-running instance to extract data from.
-- run one replica permanently one hour in the past
STOP SLAVE;
CHANGE MASTER TO master_delay = 3600;
START SLAVE;A delayed replica costs one instance's worth of hardware and saves you exactly once, on the worst night of the year. Every serious MariaDB estate I have run had one.
Guardrails and where MonPG fits
The full guardrail list is short: binlog_format=ROW and binlog_row_image=FULL as standing configuration, binlog retention that covers a weekend, one delayed replica, and a staging rehearsal of the flashback workflow so the first time you read inverse DML is not during an incident. A missing WHERE clause is a when, not an if.
Guardrails rot when nobody re-checks them, and re-checking is a tooling job. The honest disclosure first: I work on MonPG, which monitors PostgreSQL today — it does not monitor MariaDB yet. MariaDB support is on the way, and the coming-soon page tracks what is being built, with exactly this incident class in mind: binlog_format and binlog_row_image drift surfaced fleet-wide, retention coverage that would fail a Monday-morning discovery, delayed-replica health, and the backup-position bookkeeping PITR depends on. Until it ships, the checklist above is the product. Running PostgreSQL as well? That side is live today — start with the PostgreSQL overview, see how the engines compare, and find the rest of the series on the blog.