Зачем нужен binlog и что такое point-in-time recovery
Бинарный лог (binlog) MySQL записывает все изменения данных в виде последовательности событий: вставки, обновления, удаления и структуру DDL. Сам по себе полный бэкап восстанавливает базу только на момент снятия копии, а всё, что произошло после, теряется. Binlog закрывает этот разрыв: восстановив полный бэкап и доиграв события из binlog до нужной секунды, можно откатить базу на точку прямо перед ошибочным DELETE или сбоем диска.
Такой подход называют point-in-time recovery, PITR. Он спасает в сценарии «час назад разработчик выполнил UPDATE без WHERE» — без binlog единственный вариант это потерять час данных.
Как включить binlog в конфигурации сервера
Binlog включается в my.cnf и требует перезапуска сервиса:
[mysqld]
log_bin = /var/lib/mysql/binlog
binlog_expire_logs_seconds = 604800
max_binlog_size = 512M
server_id = 1Параметр server_id обязателен даже без репликации — без него сервер откажется писать binlog. Срок хранения через binlog_expire_logs_seconds стоит держать не меньше окна между полными бэкапами, иначе часть событий для PITR будет удалена раньше времени. Общие рекомендации по настройке my.cnf собраны в статье про оптимизацию my.cnf.
Форматы binlog: ROW, STATEMENT, MIXED
Формат влияет и на объём логов, и на надёжность восстановления:
| Формат | Что пишется | Когда выбрать |
|---|---|---|
| ROW | Фактически изменённые строки | По умолчанию, для точного PITR |
| STATEMENT | Сами SQL-команды | Компактнее, но ломается на недетерминированных функциях |
| MIXED | STATEMENT с переключением на ROW при необходимости | Компромисс для смешанной нагрузки |
Для надёжного point-in-time recovery используйте ROW — он избавляет от сюрпризов с функциями вроде NOW() или RAND(), которые на реплике могли бы выполниться иначе.
Полный сценарий восстановления на точку во времени
Порядок действий при аварии из-за ошибочного запроса в 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Сначала разворачивается последний полный бэкап, затем через mysqlbinlog проигрываются события binlog вплоть до секунды перед ошибкой. Если сбойный запрос известен точно, вместо времени можно указать позицию через --stop-position — она надёжнее временной метки при высокой частоте транзакций.
Как найти нужную позицию или момент в binlog
Прежде чем восстанавливать, находят точное место аварии просмотром содержимого лога:
mysqlbinlog /var/lib/mysql/binlog.000045 | grep -B5 'DELETE FROM orders'Вывод показывает позицию события и метку времени, которые затем идут в параметры --start-position и --stop-position. Ошибка в один байт позиции приводит либо к пропуску нужных данных, либо к повторному применению разрушительного запроса — проверяйте значения дважды.
Частые ошибки при работе с binlog
Несколько вещей ломают PITR на практике:
- Binlog не был включён до аварии — восстановить получится только на момент бэкапа.
- Срок хранения логов истёк раньше, чем нужная точка восстановления.
- Формат STATEMENT вместе с недетерминированными функциями в запросах.
- Восстановление тестировали впервые уже во время реальной аварии, а не заранее.
Чек-лист для точки восстановления
Чтобы PITR действительно сработал, когда понадобится, держите под рукой:
- Регулярный полный бэкап, например через mysqldump, с известным временем снятия.
- Включённый binlog в формате ROW с достаточным сроком хранения.
- Отработанную на тестовой базе процедуру mysqlbinlog + mysql.
- Записанные позиции последнего бэкапа для склейки с binlog без пропусков.
- Мониторинг свободного места на диске под каталог с логами.