MySQL binlog: point-in-time recovery step by step

Why binlog matters and what point-in-time recovery is

The MySQL binary log (binlog) records every data change as a sequence of events: inserts, updates, deletes, and DDL structure changes. A full backup alone restores a database only to the moment the copy was taken, and everything that happened afterward is lost. The binlog closes that gap: restore the last full backup, replay binlog events up to the needed second, and you can roll the database back to the instant right before a bad DELETE or a disk failure.

This approach is called point-in-time recovery, PITR. It saves the day in the scenario \"an hour ago a developer ran UPDATE with no WHERE\" — without binlog the only option is to lose that hour of data.

How to enable binlog in the server configuration

Binlog is enabled in my.cnf and requires a service restart:

[mysqld]
log_bin = /var/lib/mysql/binlog
binlog_expire_logs_seconds = 604800
max_binlog_size = 512M
server_id = 1

The server_id parameter is required even without replication — without it the server refuses to write the binlog. Keep the retention period set by binlog_expire_logs_seconds no shorter than the window between full backups, otherwise some events needed for PITR get purged too early. General my.cnf tuning advice is collected in the article on my.cnf optimization.

Binlog formats: ROW, STATEMENT, MIXED

The format affects both log volume and recovery reliability:

For reliable point-in-time recovery, use ROW — it avoids surprises from functions like NOW() or RAND() that could execute differently on a replica.

Full point-in-time recovery scenario

Steps to take after a failure caused by a bad query at 14:32:

mysql < /backup/full_2026-09-28.sql

mysqlbinlog --stop-datetime="2026-09-29 14:31:59" \
  /var/lib/mysql/binlog.000045 | mysql -u root -p

First restore the last full backup, then use mysqlbinlog to replay binlog events up to the second before the mistake. If the failing query is known exactly, a position can be given instead of a timestamp via --stop-position — it is more reliable than a time value under a high transaction rate.

How to find the right position or moment in the binlog

Before restoring, locate the exact moment of the failure by inspecting the log contents:

mysqlbinlog /var/lib/mysql/binlog.000045 | grep -B5 'DELETE FROM orders'

The output shows the event's position and timestamp, which then go into the --start-position and --stop-position parameters. A one-byte mistake in the position either skips needed data or reapplies the destructive query — check the values twice.

Common mistakes when working with binlog

A few things break PITR in practice:

• Binlog was not enabled before the failure — recovery is only possible up to the backup moment.
• The log retention period expired before the needed recovery point.
• STATEMENT format combined with non-deterministic functions in queries.
• The recovery procedure was tested for the first time during a real failure, not in advance.

Checklist for a recovery point

To make PITR actually work when you need it, keep on hand:

• A regular full backup, for example via mysqldump, with a known capture time.
• Binlog enabled in ROW format with a sufficient retention period.
• A mysqlbinlog plus mysql procedure already tested on a staging database.
• Recorded positions of the last backup to stitch together with the binlog with no gaps.
• Monitoring of free disk space under the log directory.