The binary log is the authoritative record of every change MySQL has made — every insert, update, delete, schema change. mysqlbinlog is the tool that turns the binary stream back into something human-readable, lets you slice it by time or position, and (carefully) replay a subset. This article is the forensic workflow we follow when a bad statement is reported, when a customer disputes a change, when corruption is suspected, or when point-in-time recovery must reach a precise moment.
How to verify
mysqlbinlog ships with the MySQL client tools and reads the binlog files directly. Confirm the tool is present and the binlog stream is intact.
mysqlbinlog --version
ls -l /var/lib/mysql/binlog.*
mysql -e "SHOW BINARY LOGS;"
mysql -e "SHOW MASTER STATUS\G"
mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/binlog.000123 | head -100
SHOW BINARY LOGS lists every binlog the server still has, with sizes. If the binlog you need is not in that list, the file has been purged by binlog_expire_logs_seconds and forensics is over unless you have backups.
What’s happening
A binlog file is a sequence of events. With binlog_format=ROW (the production default), every change is logged as a Table_map_event (which table) followed by a Write_rows_event, Update_rows_event, or Delete_rows_event containing the before- and after-images of the affected rows. With binlog_format=STATEMENT, the SQL itself is logged. In either case, every transaction has a starting BEGIN and a terminating COMMIT (or XID for InnoDB).
mysqlbinlog decodes the binary into SQL-like text. For row-based events, the default output is base64-encoded blobs — useful for replay, useless for reading. --base64-output=DECODE-ROWS --verbose decodes the row images into readable ### INSERT INTO ... comments, which are what forensic work needs.
Filtering is done by:
--start-datetime/--stop-datetime— time window. Inclusive on start, exclusive on stop.--start-position/--stop-position— byte position within a binlog file.--database/-d— limit to one database (with the sameUSEquirk asbinlog_do_db).--include-gtids/--exclude-gtids— for GTID-enabled servers, slice by GTID range.
The output is shell-pipeable. Combine mysqlbinlog --base64-output=DECODE-ROWS --verbose --start-datetime=... --stop-datetime=... with grep, awk, or less to find the specific event.
The procedure
-
Identify the binlog file and rough position.
SHOW BINARY LOGSlists the files; the bad event happened “around 14:30 yesterday.”mysql -e "SHOW BINARY LOGS;" mysql -e "SELECT @@datadir;" -
Dump a time-windowed slice in decoded form. The output is sometimes huge; pipe it through
less.sudo mysqlbinlog \ --base64-output=DECODE-ROWS \ --verbose \ --start-datetime='2026-06-01 14:25:00' \ --stop-datetime='2026-06-01 14:35:00' \ /var/lib/mysql/binlog.000123 \ | lessThe decoded output shows each row event as
### UPDATE tablewith### WHERE(the before image) and### SET(the after image). TheBEGINandCOMMITboundaries frame each transaction. -
Find the specific bad event. A common pattern: the
WHEREblock shows the value that should not have changed, and theSETblock shows what it became.sudo mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/binlog.000123 \ | grep -B 5 -A 20 'customer_id=42' -
Capture the exact position of the bad transaction. The header above each event shows
# at 19216(start position) and the next event’s# at 19444(the position right after). -
If you need to replay everything except the bad transaction (point-in-time recovery), the workflow is: restore the most recent backup, then replay binlogs up to the position right before the bad transaction, then resume from right after it.
# restore the backup (xtrabackup --copy-back ...) # then replay binlogs to just before the bad event sudo mysqlbinlog \ --start-datetime='2026-06-01 00:00:00' \ --stop-position=19216 \ /var/lib/mysql/binlog.000123 \ | mysql -u root -p # skip the bad transaction; resume from the next one sudo mysqlbinlog \ --start-position=19444 \ /var/lib/mysql/binlog.000123 \ | mysql -u root -p -
For GTID-based replay, use GTID slicing instead of position.
--exclude-gtidsskips a specific transaction.sudo mysqlbinlog \ --exclude-gtids='aaaa-bbbb-...:50' \ /var/lib/mysql/binlog.000123 \ | mysql -u root -p -
To replay against a different server (a forensic sandbox), stream the binlog and apply to a paused replica or a fresh restore.
mysqlbinlog --read-from-remote-server \ --host=primary --user=repl --password=REPL_PASS \ --raw --result-file=/forensic/ \ binlog.000123
Common pitfalls
mysqlbinlogwithout--verbose— for row-based binlogs, the output is base64 blobs and tells you nothing. Always pair--base64-output=DECODE-ROWSwith--verbose.- Time-window filter on a busy server —
--start-datetimeand--stop-datetimeneed the server’s local time. Ifsystem_time_zonediffers from your terminal, you can miss the window by an hour. - Direct
mysqlbinlog | mysqlreplay onto the production primary — if the binlogs contain GTIDs the primary has already executed, the replay errors out; if they do not, you risk duplicating data. Replay onto a freshly restored sandbox first, verify, then promote. - Forgetting binlog retention —
binlog_expire_logs_secondspurges old binlogs on schedule. If forensics arrives the day after the purge, the trail is gone. Retain longer than your maximum dispute or recovery window. - Misreading
before imagevsafter image—### WHEREblock in the decoded output shows what the row was before the change;### SETshows what it became. Mixing them up leads to the wrong conclusion about what happened.
mysqlbinlog is what turns “something changed and we do not know what” into a sequence of events with timestamps and row images. In the engagements we run, the binlog retention is sized for the longest dispute window we cover, forensic replays go to a sandboxed restore not to production, and the precise position of every recovery is recorded in the incident log. For the broader pattern of operating MySQL with backup and recovery this carefully, see /en/services/managed-operations/.