Точечное восстановление MySQL/MariaDB: binlog, GTID и контроль точек

Точечное восстановление (PITR, Point-in-Time Recovery) — ваш последний рубеж против человеческих ошибок, дефектных релизов и некорректных миграций. Идея проста: вы регулярно делаете полный бэкап, а все изменения между бэкапами фиксируете в бинарных журналах транзакций (binlog). Когда случается беда, вы поднимаете копию на момент бэкапа и «прокручиваете» только те события, которые нужны, останавливаясь до критической точки. На практике успех зависит от корректной подготовки: формата binlog, GTID, дисциплины «контрольных точек» и аккуратного применения журналов.

Что такое PITR и чем оно отличается от обычного восстановления

Обычное восстановление возвращает базу к моменту последнего полного бэкапа (RPO может быть часа, сутки и больше). PITR позволяет уменьшить RPO до считаных минут или секунд: вы не теряете весь интервал с последнего бэкапа, а только изменения после выбранной точки остановки. Это особенно важно при случайных DROP TABLE, массовом UPDATE без WHERE или при откате проблемного релиза.

Классика PITR: «вчерашний полноразмерный бэкап + поток binlog за последние сутки + остановка на секунду до инцидента».

Архитектура: полный бэкап + binlog

Надёжный пайплайн PITR включает три элемента:

• регулярный полный бэкап (логический или физический),
• непрерывный поток бинарных логов с достаточной ретенцией,
• контрольные точки (запись точного binlog-файла и позиции или GTID-наборов на момент бэкапа).

Политика хранения должна покрывать ваш RPO: если допускаете потерю данных не более 1 часа, гарантируйте наличие binlog за минимум 1–2 суток с запасом и ежедневный полный бэкап.

Почему формат ROW и что с binlog_row_image

Для PITR практичнее binlog_format=ROW. В режиме ROW в журнал попадают конкретные изменения строк, что надёжнее для точечной фильтрации и воспроизведения, особенно при сложных триггерах, нестабильных функциях и смешанных нагрузках. Параметр binlog_row_image рекомендуйте FULL для максимальной восстановимости (в ущерб объёму), либо MINIMAL если уверены в схемах и ключах.

GTID: ускорение согласования и контроль прогресса

GTID (Global Transaction ID) делает управление PITR и репликацией предсказуемым: вы видите, какие транзакции уже применены, а какие — нет. В MySQL GTID выглядит как UUID:interval и управляется переменными gtid_executed и gtid_purged. В MariaDB формат иной (domain_id-server_id-seqno) и используется gtid_slave_pos (исполненный набор). Это нужно учитывать при восстановлении. Для углубления по теме отказоустойчивости посмотрите материал про GTID и semi-sync фейловер: GTID и полуаcинхронный фейловер.

Подготовка сервера: включаем binlog и GTID

Минимальный набор для MySQL 8.x:

[mysqld]
server_id=1
log_bin=mysql-bin
binlog_format=ROW
binlog_row_image=FULL
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_expire_logs_seconds=604800
sync_binlog=1
innodb_flush_log_at_trx_commit=1
log_bin_trust_function_creators=ON

Базовый набор для MariaDB 10.5+:

[mysqld]
server_id=1
log_bin=mysql-bin
binlog_format=ROW
gtid_strict_mode=ON
binlog_expire_logs_seconds=604800
sync_binlog=1
innodb_flush_log_at_trx_commit=1

Проверьте, что server_id уникален, а срок хранения binlog_expire_logs_seconds покрывает ваш RPO с запасом. Для высоконагруженных систем включайте долговечность (sync_binlog=1, innodb_flush_log_at_trx_commit=1), иначе есть риск потерь при сбоях.

Контрольные точки: фиксируем момент бэкапа

Контрольная точка — это запись файла и позиции binlog (или GTID-набора) на момент начала консистентного бэкапа. Она позволяет корректно «стыковать» восстановление базы и применение журналов.

mysqldump: логический бэкап с метаданными

Для InnoDB используйте --single-transaction без блокировок. В MySQL полезен --set-gtid-purged=ON — он помещает в дамп GTID-набор, который потом можно применить на новом сервере.

mysqldump --single-transaction --routines --triggers --events --set-gtid-purged=ON --databases appdb > appdb_2025-10-12.sql

Перед стартом дампа зафиксируйте статус мастера:

mysql -e "SHOW MASTER STATUS\G"

Сохраните File и Position, а в MySQL также Executed_Gtid_Set. Это и есть ваша контрольная точка для стыковки binlog.

Физические бэкапы: XtraBackup/MariaBackup

Физические (горячие) бэкапы быстрее и сохраняют внутренние состояния (включая GTID). Современные инструменты пишут файл с позицией binlog на момент завершения копирования. Для PITR это часто самый надёжный путь, так как бинлоги потом применяются поверх «нативного» состояния данных.

Доставка и хранение binlog

Журналы нужно не только хранить локально, но и оперативно выносить на отдельное хранилище. Утилита mysqlbinlog умеет читать удалённый сервер и писать файлы в «сыром» виде.

mysqlbinlog --read-from-remote-server --raw --stop-never --host=127.0.0.1 --user=replica --password=secret --result-file=/var/backups/binlog/ mysql-bin.000001

Проверяйте контрольные суммы и целостность скачанных файлов, а также следите за ретенцией: удаление журналов раньше срока делает PITR невозможным. Планируйте место с запасом, учитывая пиковые объёмы изменений. Для выноса бэкапов и журналов удобно использовать объектное хранилище: см. S3-бэкапы с Restic/Borg.

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

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

Подробнее

Сценарий PITR: откат до секунды перед инцидентом

Предположим, в 14:07:30 разработчик удалил таблицу. Наша задача — откатиться к 14:07:29. Безопаснее всего восстанавливаться на отдельном экземпляре, проверить результат и только потом переключить трафик. Если под рукой нет стенда, быстро разверните его на VDS.

1. Разворачиваем новый экземпляр СУБД, включаем read_only=ON и super_read_only=ON на время процедуры.
2. Восстанавливаем полный бэкап (логический или физический) на этот экземпляр.
3. Применяем binlog с момента контрольной точки до времени остановки.
4. Проверяем консистентность, снимаем read_only, подключаем приложение.

Применение по позиции/времени (без GTID)

Если у вас есть файл и позиция из SHOW MASTER STATUS, используйте mysqlbinlog и задайте точку остановки по времени. Обратите внимание на часовой пояс: mysqlbinlog интерпретирует --start-datetime и --stop-datetime в локальном времени процесса.

mysqlbinlog --start-position=123456 --stop-datetime="2025-10-12 14:07:29" /var/backups/binlog/mysql-bin.000123 /var/backups/binlog/mysql-bin.000124 | mysql -u root -p

Если нужно увидеть, что именно будет применено, используйте декодирование строк:

mysqlbinlog --verbose --base64-output=DECODE-ROWS --start-position=123456 /var/backups/binlog/mysql-bin.000123 | less

Применение с GTID (MySQL)

В MySQL удобнее оперировать GTID-наборами. После восстановления базового бэкапа убедитесь, что gtid_executed соответствует бэкапу. Если вы разворачиваете пустой инстанс логически, может понадобиться выставить gtid_purged один раз — это «пропущенные» транзакции, которые уже были учтены бэкапом.

mysql -e "SET GLOBAL gtid_purged='a1b2c3d4-e5f6-7890-abcd-ef0123456789:1-456789';"
mysqlbinlog --stop-datetime="2025-10-12 14:07:29" --exclude-gtids='a1b2c3d4-e5f6-7890-abcd-ef0123456789:1-456789' /var/backups/binlog/mysql-bin.000123 /var/backups/binlog/mysql-bin.000124 | mysql -u root -p

--exclude-gtids перескакивает уже применённые транзакции. После процедуры проверьте gtid_executed: ожидаемое расширение диапазонов подтверждает, что вы «догнали» нужный момент.

Применение с GTID (MariaDB)

В MariaDB для фиксации прогресса используется gtid_slave_pos. После восстановления базового бэкапа (физического или логического) удостоверьтесь, что этот набор соответствует состоянию на момент бэкапа. При необходимости:

mysql -e "SET GLOBAL gtid_slave_pos='0-1-100,0-2-50';"
mysqlbinlog --stop-datetime="2025-10-12 14:07:29" /var/backups/binlog/mysql-bin.000123 /var/backups/binlog/mysql-bin.000124 | mysql -u root -p

Если вы используете физические бэкапы, в большинстве случаев gtid_slave_pos приедет «как есть», и ручного вмешательства не потребуется.

Частичное восстановление: одна база или таблица

Популярный кейс — вернуть одну таблицу, не трогая остальные. Самый безопасный путь: восстановить базу целиком на отдельном экземпляре в точку перед инцидентом, затем логически выгрузить нужный объект и импортировать его в прод. Прямое применение binlog с фильтрацией по схеме возможно, но есть нюансы.

mysqlbinlog умеет фильтровать по базе через --database, однако DDL и кросс-базовые операции могут просочиться. Для инспекции используйте декодирование строк и явную проверку имен схем и таблиц в выводе.

mysqlbinlog --database=appdb --verbose --base64-output=DECODE-ROWS --stop-datetime="2025-10-12 14:07:29" /var/backups/binlog/mysql-bin.000123 | less

После проверки можно применить отфильтрованный поток на временном экземпляре, убедиться в целостности, затем экспортировать таблицу через mysqldump appdb table_name и импортировать в прод.

Важные проверки после PITR

• Сравните ожидаемые и фактические GTID-наборы (gtid_executed для MySQL, gtid_slave_pos для MariaDB).
• Проверьте счётчики строк, критические агрегаты и бизнес-инварианты.
• Проведите выборочные CHECKSUM TABLE на ключевых таблицах.
• Просмотрите последние операции в binlog, убедитесь, что остановка произошла на правильной секунде.

Ловушки и тонкости

• Часовой пояс: --stop-datetime интерпретируется локально процессом mysqlbinlog, а события хранятся с меткой времени, которую утилита конвертирует. Проверяйте, чтобы не промахнуться на час.
• Нестабильные функции и триггеры: используйте ROW и держите log_bin_trust_function_creators=ON, иначе дампы с детерминированными функциями могут не примениться.
• DDL и нефулл-логгинг: binlog_row_image=MINIMAL экономит место, но усложняет разбор. Для критичных систем держите FULL.
• Шифрование binlog: убедитесь, что ваша версия утилит понимает формат шифрования, иначе mysqlbinlog не прочитает журналы.
• Раннее удаление журналов: контролируйте ретенцию. Любой «провал» в цепочке binlog делает PITR в этот интервал невозможным.
• Повторное логирование при применении: по умолчанию события, поданные через mysql, логируются заново. Если вы восстанавливаете будущий «мастер», это полезно; если это временный стенд, можно отключить для сессии SET sql_log_bin=0.
• Нетрansactional таблицы (MyISAM): консистентность хуже, делайте паузу на запись (FLUSH TABLES WITH READ LOCK) при логическом бэкапе или переходите на InnoDB.

Автоматизация: от практики к рутине

Регулярные проверки важнее самой настройки. Раз в квартал проводите «учения»: поднимайте инстанс из вчерашнего бэкапа, прокручивайте binlog до произвольного момента, валидируйте данные. По итогам фиксируйте RTO/RPO и донастраивайте ретенцию.

Сценарий ежедневной рутины:

• ежедневный полный бэкап с фиксацией контрольной точки (файл, позиция, GTID-набор),
• непрерывная доставка binlog на отдельное хранилище,
• мониторинг возраста последнего binlog и свободного места,
• автоматическое удаление журналов после контрольного срока,
• авто-проверка целостности новых binlog (контрольные суммы, чтение заголовков).

Если вы планируете миграции между хостингами без простоев, посмотрите перенос сайта без простоя — те же принципы «прокрутки» изменений помогут снизить RTO.

Чек-лист перед инцидентом

• binlog_format=ROW, binlog_row_image=FULL на ключевых БД.
• GTID включён и протестирован (MySQL: gtid_mode=ON, MariaDB: gtid_strict_mode=ON).
• Ретенция binlog покрывает RPO с запасом.
• Каждый бэкап сопровождается контрольной точкой (SHOW MASTER STATUS или GTID-набор).
• Процедура восстановления задокументирована и опробована на стенде.

Итог

PITR для MySQL/MariaDB — это дисциплина плюс несколько правильных настроек. Включите binlog в режиме ROW, используйте GTID, фиксируйте контрольные точки и храните журналы с запасом. Восстанавливайтесь на отдельном экземпляре, применяйте только нужный интервал событий, строго валидируйте результат — и у вас будет реальный инструмент против любых «ой» в проде.