Point-in-time recovery in MySQL

Introduction

Point-in-time recovery helps you restore your database to a specific moment, so you can fix mistakes or recover lost data without losing everything.

You accidentally deleted important data and want to restore it just before the deletion.

A wrong update changed many records and you want to undo it by going back to the time before the update.

Your database crashed and you want to recover it up to the last good moment before the crash.

You want to test how your database looked at a certain time in the past.

You want to recover from a ransomware attack by restoring data to a safe point.

Syntax

MySQL

mysqlbinlog [options] binlog-files | mysql -u user -p database_name

You use mysqlbinlog to read binary log files that record all changes.

You apply these logs to your database to replay changes up to a chosen time.

Examples

This replays changes from the binary log between 10:00 and 10:30 on June 1, 2024, restoring the database to that point.

MySQL

mysqlbinlog --start-datetime='2024-06-01 10:00:00' --stop-datetime='2024-06-01 10:30:00' binlog.000001 | mysql -u root -p mydatabase

This replays all changes up to 9:45 on June 1, 2024, stopping before any later changes.

MySQL

mysqlbinlog --stop-datetime='2024-06-01 09:45:00' binlog.000001 | mysql -u root -p mydatabase

Sample Program

This command restores the database by replaying all changes recorded in the binary log from 8:00 to 8:15 on June 1, 2024.

MySQL

mysqlbinlog --start-datetime='2024-06-01 08:00:00' --stop-datetime='2024-06-01 08:15:00' /var/log/mysql/mysql-bin.000003 | mysql -u root -p mydatabase

Important Notes

Make sure binary logging is enabled on your MySQL server to use point-in-time recovery.

Keep your binary logs safe and backed up, as they are essential for recovery.

You need to know the exact time range to replay changes correctly without losing or duplicating data.

Summary

Point-in-time recovery lets you restore your database to a specific moment using binary logs.

Use mysqlbinlog with start and stop datetime options to replay changes.

This helps fix mistakes or recover data without restoring the entire backup.