Reading mysqlbinlog: a field guide to row-format events

At 23:41 an agency lead drops a Loom into our shared channel. Two minutes of someone scrolling through a Dutch retailer's WordPress admin. The site is loading assets from the wrong domain. wp_options.siteurl has been swapped for an attacker-controlled URL, and the question on the line is simple: which process did it, when, and what mysqlbinlog can tell us about it.

The site has been running for nine years. Six admin users, three deploy keys, a Jenkins box, a cron worker, and one developer who left in March and may or may not still have a key. None of the application logs show the change. The web access log is clean. The only place the change definitely exists is the MySQL binary log.

This is the post I keep meaning to write for that exact moment. A field guide to the row-format events mysqlbinlog writes out, so you can find the row, the time, the connection, and ideally the human behind it without spending three hours learning the format from scratch.

The four events that carry the change

If your server runs with binlog_format=ROW (the default since MySQL 5.7), every data change is written as a small sequence of event types. They arrive in pairs: one descriptor, one payload.

• TABLE_MAP_EVENT: names the table and the column types of the rows about to follow. Without it, the payload events are anonymous bytes.
• WRITE_ROWS_EVENT: the row image of one or more INSERTs.
• UPDATE_ROWS_EVENT: both the BEFORE and AFTER image of one or more UPDATEs.
• DELETE_ROWS_EVENT: the row image of one or more DELETEs.

Each pair sits inside a transaction wrapper. A GTID event (or an ANONYMOUS_GTID event), then a QUERY event with BEGIN, then the rows, then an XID event committing it. The wrapper is where the timestamps, server id, and thread id live. The payload is where the data lives. You need both halves to make sense of a single write.

The command you actually want

The mysqlbinlog reference lists a dozen flag variations. In practice you want one base recipe and adjust the window. Assume the binlog files live in /var/log/mysql/ and rotate as mysql-bin.000142:

mysqlbinlog \
  --base64-output=DECODE-ROWS \
  --verbose \
  --start-datetime="2026-06-10 23:30:00" \
  --stop-datetime="2026-06-10 23:45:00" \
  /var/log/mysql/mysql-bin.000142 \
  > /tmp/window.sql

Two flags do most of the work. --base64-output=DECODE-ROWS tells mysqlbinlog not to print the raw base64 blobs that ROW events are stored as; without it the file is unreadable. --verbose tells it to emit pseudo-SQL comments alongside each event, so you can see which row changed and what the before and after values were.

Skip --start-datetime at your peril on a busy server. A single binlog can be a gigabyte, and the pseudo-SQL it expands to is much larger. If you do not know the window, run SHOW BINARY LOGS first and narrow by file before you narrow by time.

Reading the pseudo-SQL

Inside the window file you will see blocks like this:

#260610 23:41:18 server id 1  end_log_pos 38421 CRC32 0x9af13c20
#       Update_rows: table id 142 flags: STMT_END_F
### UPDATE `wp_db`.`wp_options`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='siteurl' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###   @3='https://klant.example/' /* VARSTRING(255) meta=255 ... */
###   @4='yes' /* ENUM(1 byte) meta=63233 ... */
### SET
###   @1=1
###   @2='siteurl'
###   @3='https://wp-content-cdn.click/'
###   @4='yes'

A few things to notice.

The column names are @1, @2, @3, @4. mysqlbinlog does not know your schema. The TABLE_MAP_EVENT only gives it ordinal positions and types, so you map them back yourself with a quick DESCRIBE wp_options;. Here, @1 is option_id, @2 is option_name, @3 is option_value, @4 is autoload.

The WHERE block is the BEFORE image. By default MySQL writes the full row image, which is what you want during forensics. If you ever see only the primary key in WHERE, your server is running with binlog_row_image=MINIMAL and most of your evidence is gone. Check it with SHOW VARIABLES LIKE 'binlog_row_image'; and set it to FULL on anything customer-facing.

The timestamp on the header is the commit time, in the server's local timezone. The server id is the MySQL replication id, which matters when chasing a write that landed via a replica rather than from your application server.

Narrowing down the suspect

The row event tells you what changed. To tell you who, you look at the transaction wrapper a few lines above:

#260610 23:41:18 server id 1  end_log_pos 38302
#       GTID last_committed=812 sequence_number=813 ...
SET @@SESSION.GTID_NEXT= 'a3f1c2d4-...:1042'/*!*/;
#260610 23:41:18 server id 1  end_log_pos 38380
#       Query   thread_id=4471  exec_time=0   error_code=0
SET TIMESTAMP=1812844878/*!*/;
BEGIN
/*!*/;

That thread_id=4471 is the gold. If your MySQL is running with performance_schema on (the default), and you caught the incident inside the connection's lifetime, you can correlate it with performance_schema.threads and see the connecting user, host, and program name. In practice the connection is usually long gone by the time anyone is reading binlogs, so you fall back to the slow query log or the general log if either was running.

The exec_time is wall time of the statement on the source. A single-row UPDATE with exec_time=0 from a thread that only ever ran that one statement is the signature of a script, not an admin clicking around in phpMyAdmin. That detail alone narrowed our 23:41 incident from six admins to one deploy key, which had been copy-pasted into an unrelated GitHub Action eighteen months earlier and forgotten.

When ROW format isn't enough

Two situations where the row events leave you short.

First, binlog_row_image=MINIMAL. Some hosting providers ship MySQL configs tuned for replication throughput rather than auditability. The WHERE block on every update is just the primary key and the SET block is just the changed columns. You can see what the new value is, but you cannot see what it replaced. If you run a legacy site for someone else's money, flip this to FULL.

Second, DELETE_ROWS_EVENT on a soft-deleted table. The row image is in the binlog, so you can recover the data, but you have to reconstruct the INSERT statement by hand from the @1, @2, @3 assignments. Worth practising once on a staging dump before you need it at 02:00.

For schema changes, ROW events are silent. Those go through as QUERY events containing the literal DDL string, so grep -E 'ALTER|DROP|CREATE' window.sql still works as a first pass when someone says "the table just disappeared".

What this looks like in our own work

When we built Pier, this exact scene was the reason the MySQL editor ships with a row-level version history. We wanted the agency lead at 23:41 to be able to scroll back through a single row's writes without a terminal and without an SSH hop. The binlog is still the canonical source of truth; Pier just makes the common case (one row, last thirty days) one click instead of three commands.

Today's small thing: run SHOW VARIABLES LIKE 'binlog_row_image'; on the production database of your busiest legacy site. If it returns MINIMAL, change it to FULL in my.cnf tonight and bounce the server during your next maintenance window. The next time someone asks who changed what, you will have an answer instead of a shrug.