Skip to content

mysqlbinlog forensics — reading the binary log to reconstruct events

How to use mysqlbinlog to inspect, time-window, and replay MySQL binary logs — the forensic workflow we follow after a bad statement, a corruption, or a customer dispute.

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 same USE quirk as binlog_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

  1. Identify the binlog file and rough position. SHOW BINARY LOGS lists the files; the bad event happened “around 14:30 yesterday.”

    mysql -e "SHOW BINARY LOGS;"
    mysql -e "SELECT @@datadir;"
  2. 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 \
      | less

    The decoded output shows each row event as ### UPDATE table with ### WHERE (the before image) and ### SET (the after image). The BEGIN and COMMIT boundaries frame each transaction.

  3. Find the specific bad event. A common pattern: the WHERE block shows the value that should not have changed, and the SET block shows what it became.

    sudo mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/binlog.000123 \
      | grep -B 5 -A 20 'customer_id=42'
  4. 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).

  5. 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
  6. For GTID-based replay, use GTID slicing instead of position. --exclude-gtids skips a specific transaction.

    sudo mysqlbinlog \
      --exclude-gtids='aaaa-bbbb-...:50' \
      /var/lib/mysql/binlog.000123 \
      | mysql -u root -p
  7. 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

  • mysqlbinlog without --verbose — for row-based binlogs, the output is base64 blobs and tells you nothing. Always pair --base64-output=DECODE-ROWS with --verbose.
  • Time-window filter on a busy server — --start-datetime and --stop-datetime need the server’s local time. If system_time_zone differs from your terminal, you can miss the window by an hour.
  • Direct mysqlbinlog | mysql replay 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_seconds purges 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 image vs after image — ### WHERE block in the decoded output shows what the row was before the change; ### SET shows 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/.