Problem: MySQL data lost after accidental DELETE - recover with binlog flashback —— Trove problem library

What you will run into

A careless DELETE FROM <table> without WHERE (or a wrong WHERE clause) wipes the data instantly. The table may hold production data and you cannot just roll back.

After accidental deletion, the worst things are continuing to write or restarting the database - the binlog may still have everything, but careless actions can make the data permanently unrecoverable.

Why this happens

When MySQL/MariaDB has binlog enabled (log_bin=ON, binlog_format=ROW), every DML is recorded transactionally in the binlog file. The accidental DELETE is just another transaction - all writes (INSERT/UPDATE) before it are still intact in the binlog.

By truncating the replay at the DELETE transaction's start position, you restore the data to the moment before the deletion.

The binlog is MySQL's sequential append-only log: all DML is written as an event stream in commit order, and each transaction has a definite start position in the file.

An accidental DELETE is just an ordinary transaction in that stream — every write before it (INSERT/UPDATE) is still there.

Flashback is essentially "treat the binlog as a replayable timeline": locate the start of the bad transaction, replay the events before it, and the data returns to the moment before the delete.

Feasibility prerequisites: binlog_format=ROW (row format carries full before/after images; statement format cannot be reconstructed precisely) and log_bin=ON (no log, no timeline).

Deterministic takeaway: before flashback, check both switches (SHOW VARIABLES LIKE 'binlog_format' / log_bin); only ROW + ON is feasible;

always replay on a separate instance (binlog replay executes writes — never run it directly against production); locate the position with mysqlbinlog by time/keyword;

fixed diagnostic: "no binlog" error = the environment never enabled it, flashback is unavailable — fall back to backups.

Step by step

1. Stop the bleeding first (most important) — Required

FLUSH TABLES WITH READ LOCK;   # lock writes
cp -r /var/log/mysql/ /backup/ # back up binlog files

Never restart MySQL - unflushed in-memory changes may be lost; do not keep writing either, new writes pollute the recovery point.

2. Confirm binlog is on and list the logs — Required

SHOW VARIABLES LIKE "log_bin";  # must be ON, otherwise backups are the only option
SHOW BINARY LOGS;               # find the latest binlog file

3. Locate the DELETE transaction with SHOW BINLOG EVENTS — Diagnose

SHOW BINLOG EVENTS IN "mysql-bin.000001";

Look for two rows: the INSERT transaction's Annotate_rows (data write) and the DELETE transaction's Annotate_rows (accident). The Pos column of the DELETE transaction is the recovery cutoff.

4. Flashback export + replay (restore to a new DB first) — Required

# Export the range before the deletion (INSERTs only, no DELETE)
mariadb-binlog --start-position=<INSERT-pos> --stop-position=<DELETE-pos> /var/log/mysql/mysql-bin.000001 > flashback.sql

# Create the recovery DB and replay (rewrite-db avoids touching the broken one)
mariadb-binlog --start-position=<INSERT-pos> --stop-position=<DELETE-pos> --rewrite-db="original-db->recovery-db" /var/log/mysql/mysql-bin.000001 | mysql

# Verify row counts before switching back to production
SELECT COUNT(*) FROM recovery-db.table;

How to confirm it works

SELECT * FROM recovery-db.table;

Row count matches the pre-delete state (measured locally: 5 rows deleted, 5 rows recovered, content and order identical), and grep DELETE on the flashback SQL is 0 (the delete was not replayed).

Other environments

Symptom

Accidental DELETE without WHERE wipes the table (or UPDATE corrupts it); traces still exist in the binlog of the database; restarting the DB or writing more data destroys the recovery chance.

Cause

binlog (ROW format) records all DML transactionally; the accidental DELETE is an independent transaction, and all writes before its start position remain intact. mariadb-binlog/mysqlbinlog --start-position --stop-position exports exactly the pre-delete range; replaying it restores the pre-delete state; --rewrite-db allows verifying in a fresh database first.

Solution

1. Stop the bleeding: FLUSH TABLES WITH READ LOCK + back up binlog; no restart, no writes;
2. SHOW BINARY LOGS + SHOW BINLOG EVENTS to find the DELETE transaction start position;
3. mariadb-binlog --start-position=<INSERT-start> --stop-position=<DELETE-start> to export the flashback SQL (pre-delete writes only);
4. Replay with --rewrite-db into a recovery DB, verify, then switch production.