Навіщо потрібен 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 без пропусків.
Моніторинг вільного місця на диску під каталог з журналами.

Binlog MySQL: відновлення бази на точку у часі