Binary Log and Point-in-Time Recovery

MySQL binary logs record every data-changing event. Combined with a full backup, binary logs enable point-in-time recovery (PITR) — the ability to restore a database to any specific moment, not just to the last backup.

How Binary Logs Work

When binary logging is enabled (log_bin = ON in my.cnf), MySQL writes every INSERT, UPDATE, DELETE, and DDL statement to a binary log file. You can replay these logs to apply changes that occurred after the last full backup.

Point-in-Time Recovery Steps

Restore the last full mysqldump backup
Identify the binary log files and the target stop time or position
Replay binary logs using mysqlbinlog up to the desired point

This approach is the standard recovery technique for production databases and allows recovery from accidental data modification right up to the moment before the mistake occurred.

Example

-- Enable binary logging (in my.cnf)
-- [mysqld]
-- log_bin = /var/log/mysql/mysql-bin.log
-- server-id = 1

-- List binary log files
SHOW BINARY LOGS;

-- Replay binary logs up to a specific time
-- mysqlbinlog --stop-datetime="2025-05-02 14:30:00" \
--   /var/log/mysql/mysql-bin.000042 \
--   | mysql -u root -p myapp_db

-- Show binary log events
SHOW BINLOG EVENTS IN 'mysql-bin.000042' LIMIT 20;

SQL

Full Editor

Pro Tip

Enable binary logging on every production MySQL server — it is required for point-in-time recovery.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned