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

> How to enable MySQL binlog and restore a database to the moment before a failure: a full backup plus mysqlbinlog, positions, and timestamps.

## 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](https://www.zevshost.net/en/kb/mysql-my-cnf-optimizatsiya/).

## Binlog formats: ROW, STATEMENT, MIXED

The format affects both log volume and recovery reliability:

| Format | What gets logged | When to choose it |
| --- | --- | --- |
| ROW | The actual changed rows | Default, for precise PITR |
| STATEMENT | The SQL commands themselves | More compact, but breaks with non-deterministic functions |
| MIXED | STATEMENT with a switch to ROW when needed | A compromise for mixed workloads |

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](https://www.zevshost.net/en/kb/mysqldump-backup-restore/), 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.

---

Source: https://www.zevshost.net/en/kb/mysql-binlog-point-in-time/