MySQL: точечное восстановление (PITR) и delayed replication как страховка от человеческих ошибок

Точечное восстановление (Point-In-Time Recovery, PITR) — один из базовых навыков DBA. Оно спасает, когда в проде прошла неудачная миграция, приложение сделало массовый UPDATE по ошибке, кто-то выполнил DROP TABLE не в той консоли, а репликация лишь аккуратно размножила проблему. В отличие от обычного восстановления «из вчерашнего бэкапа», PITR позволяет вернуться к любому моменту — хоть за секунду до инцидента.

Когда нужен PITR и при чем здесь replication

Репликация — это про доступность и разгрузку чтений. Бэкапы — про восстановление. PITR соединяет их: мы берем базовый бэкап и «догоняем» его бинарными журналами (binlog) до нужного момента. Эта комбинация минимизирует RPO (потерю данных) и помогает держать RTO (время простоя) в рамках SLO.

Delayed replication (задержанная репликация) — полезное дополнение. Реплика отстает от мастера на заданный интервал (например, 1 час), тем самым создавая «страховочную копию настоящего прошлого». Если на мастере произошел инцидент, у вас есть окно, чтобы остановить SQL-поток на задержанной реплике и быстро достать оттуда «чистые» данные или даже временно переключить трафик.

PITR — не только про «восстановить все». Часто практичнее восстановить экземпляр на отдельном хосте, выборочно вытащить нужные таблицы/строки и аккуратно вернуть их в прод.

RPO/RTO: как целиться

Прежде чем настраивать PITR, четко сформулируйте цели:

• RPO: сколько данных допустимо потерять. С включенным binlog это обычно минуты или секунды.
• RTO: как быстро вы готовы поднять восстановленную копию и вернуть сервис. Зависит от размера базы, дисков, сети и автоматизации.

Репликация уменьшает RTO (есть «горячий» источник данных), delayed replication помогает противошоковой терапии при человеческих ошибках, но окончательное «страхование» обеспечивает лишь связка «регулярные бэкапы + binlog + отработанные процедуры PITR».

Подготовка MySQL к PITR: binlog, формат, GTID, retention

Без корректно включенного бинарного лога PITR невозможен. Минимум, что нужно в проде:

• Включить бинарный лог (log_bin), задать уникальный server_id.
• Выбрать безопасный формат репликации: binlog_format=ROW.
• Выставить разумное хранение журналов: binlog_expire_logs_seconds по вашей политике бэкапов и аудитных требований.
• По возможности — включить GTID (gtid_mode=ON, enforce_gtid_consistency=ON) для предсказуемого применения логов.
• Синхронизировать время (NTP/chrony), чтобы метки времени в binlog совпадали с вашими журналами и APM.

Пример конфига для MySQL 8.0

[mysqld]
server_id=101
log_bin=/var/lib/mysql/mysql-bin
binlog_format=ROW
binlog_row_image=FULL
binlog_expire_logs_seconds=604800
sync_binlog=1
innodb_flush_log_at_trx_commit=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON

Пояснения:

• binlog_row_image=FULL повышает шансы на успешный выборочный «откат» конкретных изменений и пригодится, если придется генерировать компенсирующие операции или точно понимать, что изменялось.
• sync_binlog=1 и innodb_flush_log_at_trx_commit=1 — про надежность. Это медленнее, но зато binlog и redo не потеряются при сбое питания.

Если вы эксплуатируете GTID и failover, обратите внимание на разбор практик в статье GTID и полусинхронный failover для MySQL.

Политика хранения binlog

Выбирайте срок хранения так, чтобы перекрыть максимальный интервал между полным бэкапом и потенциальной точкой восстановления, плюс запас на расследование инцидента. Типичный минимум — неделя (604800 секунд), но для высоких требований к audit/disaster recovery нередко держат 14–30 дней, а сами бэкапы — на оффсайт-хранилище дольше.

Для оффсайт-копий пригодится автоматизация. Смотрите практику по объектным хранилищам в материале бэкапы в S3: Restic и Borg.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

Базовые бэкапы: горячие и холодные

Для больших баз используйте горячие физические бэкапы (Percona XtraBackup, MySQL Enterprise Backup, в MariaDB — MariaBackup). Они дают консистентное состояние InnoDB без простоя и быстро восстанавливаются. Небольшие базы допускают логические дампы (mysqldump/mydumper), но помните, что восстановление логического дампа обычно медленнее.

Ключевой принцип PITR: «полный бэкап + цепочка binlog до нужного момента». Если бэкап старый, а binlog уже удален по expire — вы лишаетесь возможности точечного восстановления.

PITR пошагово: от бэкапа к секунде X

Общая процедура выглядит так:

1. Поднимите отдельный экземпляр MySQL для восстановления (не трогайте прод до проверки результата). Это может быть новый сервер, временная VM или облачный VDS.
2. Разверните актуальный полный бэкап на этот экземпляр.
3. Определите точное время инцидента. Берите метки из логов приложения, аудитных журналов, ALB/NGINX-логов, Prometheus/Tracing — важно не ошибиться с часовым поясом.
4. Подготовьте применение binlog: дождитесь, пока восстановленная база стартует и будет готова принимать SQL.
5. Примените binlog до момента за секунду до инцидента. Убедитесь, что попадаете в правильный интервал.
6. Проверьте целостность данных, затем решайте: переключать трафик на восстановленный узел или избирательно выгрузить нужные объекты (таблицы/строки) и вернуть их в прод.

Применение binlog по времени

Если журналы сохранены локально, а вы знаете границы инцидента, удобно использовать mysqlbinlog со временем старта/остановки:

# Определите файлы и позиции, если нужно
mysql -e "SHOW BINARY LOGS;"

# Посмотреть события с метками времени
mysqlbinlog --verbose --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000123 | less

# Применить события от T0 до T1
mysqlbinlog --start-datetime="2025-03-10 12:30:00" --stop-datetime="2025-03-10 12:34:59" /var/lib/mysql/mysql-bin.000123 /var/lib/mysql/mysql-bin.000124 | mysql -u restore -p restored_db

В MySQL 8.0 с GTID цепочку удобно применять целиком, а границы времени задавать параметрами. Внимательно проверяйте таймзону: mysqlbinlog оперирует временем сервера, в который писались журналы.

Применение по позициям

Границы по позициям надежнее, если есть конкретные GTID или координаты:

# Применить c позиции в файле до другой позиции
mysqlbinlog --start-position=123456 --stop-position=234567 /var/lib/mysql/mysql-bin.000124 | mysql -u restore -p restored_db

По GTID в MySQL 8.0 можно использовать SET @@GLOBAL.gtid_purged на пустой инстанс (или после физического восстановления), чтобы корректно продолжить цепочку транзакций. Не пересекайте уже примененные GTID — получите ошибки дублирования.

Пример сценария «DROP TABLE в 12:35»

Допустим, полный бэкап сделан в 02:00, а инцидент произошел в 12:35. Действуем так:

1. Развернуть бэкап на отдельном сервере.
2. Найти точный момент инцидента и убедиться, что все binlog с 02:00 до 12:35 доступны.
3. Применить binlog до 12:34:59.
4. Проверить консистентность, сравнить ключевые агрегаты, count(*) таблиц, прогнать smoke-тесты приложения.
5. Принять решение: переключаемся на восстановленный узел или аккуратно экспортируем восстановленные таблицы и импортируем обратно в прод (под новым именем, с проверкой и атомарным переименованием).

Delayed replication как «тайм‑машина»

Задержанная репликация — быстрый способ «откатиться», если ошибка замечена оперативно. Реплика сознательно отстает на заданное время. В MySQL 8.0 параметр задается как SOURCE_DELAY (раньше MASTER_DELAY), в MariaDB такую конфигурацию часто называют slave_delay. Суть одна: SQL-поток применяет события позже, создавая безопасное «окно».

-- На реплике MySQL 8.0
STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600; -- 1 час
START REPLICA;

Как использовать при инциденте:

1. Как только ошибка замечена, остановите SQL-поток на задержанной реплике до того, как до нее «дойдет» вредная транзакция: STOP REPLICA SQL_THREAD;.
2. Проверьте состояние данных на момент «за секунду до». Если все ок — либо делайте быстрый дамп нужных объектов и возвращайте их в прод, либо, если архитектура позволяет, временно переключайте чтение/запись на эту реплику (с изменением ролей).
3. После ликвидации последствий аккуратно верните топологию в норму и пересоберите задержанную реплику, чтобы снова иметь «окно» безопасности.

Плюсы: очень быстрый «rollback» в реальном времени, минимальный RTO. Минусы: защищает только в пределах окна (например, 1 час), требует дисциплины и мониторинга, а при больших нагрузках и длинных транзакциях потери окна могут увеличиваться. В любом случае delayed replication — дополнение, а не замена полноценному PITR с бэкапами и binlog.

Выборочный откат: как вернуть только нужную таблицу

Типичный запрос бизнеса: «откатите только заказы, созданные между 12:30 и 12:35». Надежный способ — side-restore:

1. Восстановите отдельный экземпляр до времени до инцидента (PITR на стенде).
2. Экспортируйте требуемые таблицы/строки (например, mysqldump --single-transaction --where для выборки по условию или логический дамп нужных таблиц).
3. Импортируйте в прод под временными именами (например, orders_restore), проверьте целостность, затем атомарно переименуйте таблицы на проде.

Использование автоматических «flashback» из binlog в чистом MySQL ограничено: стандартный mysqlbinlog не генерирует компенсирующие DML. Поэтому side-restore остается самым предсказуемым методом для избирательного восстановления.

Практические нюансы и подводные камни

• Формат binlog. STATEMENT опасен для PITR из-за недетерминированности (NOW(), UUID(), rand()). Рекомендуется ROW.
• binlog_row_image. Для аудитной читаемости и потенциальных компенсирующих операций полезен FULL. Да, больше места, но меньше сюрпризов.
• Retention. Не чистите binlog агрессивно. Срок хранения должен перекрывать период между полными бэкапами с запасом.
• Часовые пояса. Фиксируйте TZ инстансов и APM. Смешение TZ — частая причина «мы восстановили не тот интервал».
• Длинные транзакции. При --stop-datetime учтите, что транзакция атомарна и применится полностью, если она началась до, а завершилась после указанного времени. В сложных кейсах лучше границы по позициям.
• GTID. Удобно в эксплуатации, но требовательно при смешивании восстановлений. Следите за gtid_purged и уникальностью доменов транзакций.
• Валидируйте восстановление. Сверьте агрегаты, уникальные ключи, внешние ключи. Держите быстрые smoke-тесты приложения.
• Не применяйте binlog прямо в прод. Всегда на стенде или временном экземпляре, пока не проверите результат.

Автоматизация: как не забыть ничего

Хорошая практика — регулярно воспроизводить процедуру восстановления автоматически, например раз в неделю в песочнице:

• Развернуть свежий полный бэкап в чистый инстанс.
• Применить binlog до текущего момента минус X минут.
• Прогнать проверочные запросы, smoke-тесты, метрики целостности.
• Отчитаться в чат/почту: успех/ошибка, размер и время восстановления, список примененных файлов binlog.

Параллельно мониторьте заполнение диска под binlog, время жизни файлов, наличие свежих бэкапов, лаг репликации и «окно» delayed replication. Любой тревожный сигнал должен поднимать уведомление заранее, а не в момент инцидента.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Мини‑гайд по delayed replication

-- Проверить статус
SHOW REPLICA STATUS\G

-- Установить задержку (MySQL 8.0)
STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 1800; -- 30 минут
START REPLICA;

-- Быстро стопнуть применение событий, если заметили ошибку на мастере
STOP REPLICA SQL_THREAD;

-- Вернуть обычный режим
STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 0;
START REPLICA;

В MariaDB часто встречается терминология «slave delay» (по сути, то же самое), а параметр задается через CHANGE MASTER TO MASTER_DELAY = N. Для SEO многие говорят «slave_delay», хотя правильнее оперировать текущей терминологией версии, которую вы используете.

Чеклист для DBA по PITR

• Включен log_bin, уникальный server_id, binlog_format=ROW, sync_binlog=1.
• binlog_expire_logs_seconds покрывает период между бэкапами + запас.
• Для больших баз — физические горячие бэкапы; проверка скорости восстановления.
• Отработана процедура применения binlog по времени и по позициям; оформлена документацией.
• Есть регулярная «учебная тревога»: автоматическое тестовое восстановление.
• Заведены дашборды по lag реплик, «окну» delayed replication и заполнению диска под binlog.
• Скоординированы TZ и аудитные логи приложения.
• Понимание, как делать selective restore без риска для прод: side-restore, импорт под временным именем, атомарный swap.

Частые вопросы

Можно ли «откатить» DDL?

DDL атомарны и необратимы по месту. Реалистичный путь — PITR на стороне до момента до DDL и избирательный возврат структуры/данных. Если речь о DROP TABLE, side-restore спасает чаще всего.

Нужен ли ROW для PITR?

Да, предпочтительно. STATEMENT усложняет детерминизм и анализ. ROW даёт точные изменения строк. Для аудитной ясности — binlog_row_image=FULL.

Как выбрать величину задержки в delayed replication?

Оцените, за сколько обычно обнаруживаются ошибочные операции (минуты? десятки минут?). Добавьте запас и учтите время длинных транзакций. Типичные значения — 15–60 минут.

Чем отличается PITR от «rollback»?

«Rollback» — откат незакоммиченной транзакции на уровне СУБД. PITR — восстановление состояния базы к моменту в прошлом уже после фиксации вредных транзакций, используя бэкап и binlog.

Что делать, если binlog уже истёк?

Без цепочки журналов до нужного момента PITR невозможен. Придется восстанавливаться к последнему бэкапу и мириться с RPO. Это тот случай, когда правильный retention спасает.

Итоги

PITR — фундамент для disaster recovery в MySQL. Он требует дисциплины: корректной конфигурации binlog, надежных бэкапов, устоявшейся процедуры восстановления и регулярных учений. Delayed replication добавляет «подушку безопасности» против человеческих ошибок, сокращает RTO и позволяет быстро извлечь «чистые» данные. Вместе эти практики превращают разовый инцидент в управляемую операцию: вы знаете, что и как делать, — и делаете это быстро.

Если вы отвечаете за прод, не откладывайте: проверьте свои настройки binlog и бэкапов, оформите пошаговую инструкцию PITR и обкатывайте её регулярно. Это инвестиция, которая окупается в самый неожиданный день.