MySQL point-in-time recovery with the binary log: undo one bad statement from an agent

The short answer

Restore your last full backup, then replay the binary logs written since, stopping just before the bad statement and resuming just after it. MySQL’s docs show it with mysqlbinlog --start-position and --stop-position piped into mysql.² Find the positions by searching the log near the time of the incident, and use positions, not timestamps, for the replay: the docs warn that timestamp ranges risk missing events.²

In MySQL 8.4, binary logging is on by default, in row-based format, and binary log files expire after 30 days by default.³ If those defaults hold on your server and you have a full backup from within that window, you can recover.

Check you can do this

Before an incident, confirm three things:

The binary logs written after the full backup are the source of point-in-time recovery.¹ A backup older than your oldest binary log can’t be rolled forward to the incident.

On managed MySQL services, binary log access and retention are set by the provider; check its docs before you need them.

Step 1: find the bad event

Narrow by time, then search for the statement. The docs’ example, for a table dropped around 20:06:²

Code Commands typed in a terminal. The text around it says what happens.

mysqlbinlog --start-datetime="2020-03-11 20:05:00" \
            --stop-datetime="2020-03-11 20:08:00" --verbose \
  /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"

In the output, the statement sits between two position markers, # at 232 and # at 355 in the docs’ example: the bad event starts after position 232, and the log is at 355 after it.²

For an agent’s DELETE rather than a DROP, search for the table name with --verbose, which decodes row events into readable pseudo-SQL. The agent’s transcript usually gives you the time; the binary log gives you the exact position.

Step 2: restore the backup somewhere safe

Restore the full backup to a server that isn’t production: a new instance, or a copy you can throw away. Replaying into production directly makes any mistake in the positions a second incident.

Step 3: replay up to the event

From the backup’s binary log position (155 in the docs’ example) to the position just before the bad event (232):²

Code Commands typed in a terminal. The text around it says what happens.

mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \
  | mysql -u root -p

This recovers every transaction from the start position until just before the stop position. If the range spans several binary log files, apply them in one connection, for example mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p.¹ You can also write the output to a file, read it, and only then apply it, which we recommend the first time.¹

Step 4: resume after the event

To keep everything after the bad statement, start again from the position after it (355 in the example):²

Code Commands typed in a terminal. The text around it says what happens.

mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 | mysql -u root -p

The database is now at the latest event in the log, with the bad event skipped.²

One caution the docs don’t need to state but you should: later statements may depend on the rows the agent deleted, or on their absence. An application that saw the rows missing may have inserted replacements. Read what comes after the skipped event before replaying it.

Restore beside production and copy rows

Steps 2 to 4 rebuild a whole server. When an agent deleted rows in one table and production has kept running, it’s often simpler to:

1. Restore the backup to a side server and replay only up to the bad event (step 3).
2. Copy the deleted rows from the side server, for example with mysqldump --where on that table or a SELECT ... INTO OUTFILE.
3. Insert them into production with INSERT IGNORE, so rows that still exist are skipped.

That leaves every legitimate write since the incident untouched. It’s the same pattern we tested for PostgreSQL in restoring one Postgres table to a point in time.

Two limits specific to MySQL, covered in our other posts: rows removed by ON DELETE CASCADE in InnoDB may not appear as separate row events in the binary log (the cascade post), so check child tables too; and the binary log tells you what changed, not which agent session changed it (auditing MySQL changes by agents).

Practise once, on purpose

A recovery you’ve never run is a guess. On a test server, the whole drill takes under an hour:

1. Take a full backup and note its binary log position.
2. Insert a few hundred rows, then run a deliberately bad DELETE without a WHERE, then insert a few more rows.
3. Find the DELETE with mysqlbinlog --verbose, write down its start and end positions.
4. Restore the backup to a second server, replay up to the DELETE, skip it, replay the rest.
5. Compare row counts with what you expect.

Write the exact commands into your runbook as you go. During a real incident, with an agent’s damage on screen and people asking questions, you’ll want to copy and paste, not remember.

A checklist

• log_bin on, and binlog_expire_logs_seconds longer than the time between full backups.
• Every full backup records its binary log position.
• The incident time taken from the agent’s transcript; the event position from mysqlbinlog --verbose.
• Replays use --start-position and --stop-position, not timestamps.
• Replays run on a side server first, output reviewed before applying.
• Statements after the skipped event read before replaying.
• Cascaded child rows checked separately.

Before connecting an agent to MySQL at all, give it a narrow user, and see what your agents can reach: paste an MCP config into the check page or run npx deedglass scan (how the grade works); to try tool-call rules in your browser, open the playground.

Written with AI assistance and checked against our claims register.