Бинарный журнал MySQL

Бинарный журнал MySQL (binary log, binlog) — это файл, в котором сервер системы управления базами данных MySQL последовательно записывает все события, изменяющие данные или структуру базы данных (DDL и DML-операции, за исключением запросов SELECT и SHOW, не изменяющих данные). Бинарный журнал является основным механизмом для репликации данных, восстановления после сбоев (point-in-time recovery) и аудита изменений в MySQL.

Назначение и функции

Основное назначение бинарного журнала — обеспечение надёжности и масштабируемости базы данных. Он выполняет три ключевые функции:

• Репликация: ведущий сервер (master) записывает изменения в binlog, а ведомые серверы (slaves) читают эти записи и применяют их к своим копиям данных, синхронизируясь с ведущим.
• Восстановление на момент времени (PITR): после восстановления из резервной копии (дампа) бинарный журнал позволяет «докатить» изменения до заданного момента времени, восстанавливая данные с точностью до секунды или даже до конкретной транзакции.
• Аудит: анализ содержимого binlog даёт возможность отследить, какие изменения вносились в базу данных, когда и каким пользователем (при условии включения соответствующего логирования).

Формат и содержимое

Бинарный журнал не является текстовым файлом, доступным для чтения человеком. Он состоит из записей (events) определённой структуры, которые могут быть прочитаны только с помощью специальных утилит (например, mysqlbinlog) или самим сервером MySQL. Каждая запись содержит:

• Тип события: например, QUERY_EVENT (выполнение SQL-запроса), WRITE_ROWS_EVENT (вставка строк), UPDATE_ROWS_EVENT (обновление строк), DELETE_ROWS_EVENT (удаление строк), XID_EVENT (фиксация транзакции).
• Идентификатор сервера (server_id) — номер, назначаемый каждому серверу в кластере репликации.
• Позицию в журнале (offset) — байтовое смещение, по которому можно однозначно найти запись.
• Временную метку (timestamp) — время выполнения события.
• Собственно данные — изменённые строки, SQL-запрос или идентификаторы.

Формат записи может быть одним из трёх:

• STATEMENT (устаревший, начиная с MySQL 5.7.7 не рекомендуется): в журнал записывается текст SQL-запроса. Недостаток — недетерминированные запросы (например, с NOW(), RAND(), UUID()) на разных серверах могут дать разный результат.
• ROW (рекомендуемый, по умолчанию с MySQL 5.7.7): в журнал записываются точные значения изменённых строк (до и после изменения). Гарантирует детерминированность репликации, но генерирует больше данных.
• MIXED: сервер автоматически выбирает между STATEMENT и ROW в зависимости от типа запроса. Для детерминированных запросов используется STATEMENT, для недетерминированных — ROW.

Управление и настройка

Бинарный журнал включается и настраивается через конфигурационный файл MySQL (обычно my.cnf или my.ini) или через системные переменные. Основные параметры:

• log_bin — включает бинарный журнал и задаёт базовое имя файлов (например, log_bin = /var/log/mysql/mysql-bin).
• binlog_format — задаёт формат записи (STATEMENT, ROW, MIXED).
• expire_logs_days — количество дней, после которого старые файлы журнала автоматически удаляются (начиная с MySQL 8.0 вместо этого используется binlog_expire_logs_seconds).
• max_binlog_size — максимальный размер одного файла журнала (обычно 1 ГБ). При достижении этого размера создаётся новый файл.
• sync_binlog — определяет, как часто сервер синхронизирует буфер журнала с диском. Значение 1 (по умолчанию) гарантирует, что каждая запись будет сброшена на диск перед отправкой подтверждения клиенту (наиболее надёжно, но снижает производительность).
• binlog_do_db и binlog_ignore_db — фильтры, позволяющие включать или исключать из журнала определённые базы данных.

Файлы и индексы

Бинарный журнал состоит из набора последовательных файлов, имена которых образуются из базового имени и порядкового номера (например, mysql-bin.000001, mysql-bin.000002). Текущий активный файл имеет наибольший номер. Для отслеживания порядка файлов используется индексный файл (например, mysql-bin.index), содержащий список всех файлов журнала.

Просмотр и анализ

Для просмотра содержимого бинарного журнала используется утилита mysqlbinlog, входящая в состав дистрибутива MySQL. Она позволяет:

• Вывести содержимое журнала в читаемом виде (SQL-запросы или псевдо-SQL для формата ROW).
• Фильтровать записи по времени, позиции, базе данных, таблице.
• Восстанавливать данные из журнала (например, mysqlbinlog mysql-bin.000001 | mysql -u root -p).

Пример команды для просмотра последних 10 записей: `` mysqlbinlog --offset=100 --limit=10 mysql-bin.000001 ``

Влияние на производительность

Ведение бинарного журнала создаёт дополнительную нагрузку на сервер MySQL, особенно на дисковой подсистеме. Каждая операция записи (INSERT, UPDATE, DELETE) должна быть сначала зафиксирована в журнале, а затем применена к таблицам. Наиболее существенное влияние оказывает параметр sync_binlog=1, который требует синхронной записи на диск каждой транзакции. В системах с высокой нагрузкой на запись может быть оправдано увеличение sync_binlog до 0 или 100 (синхронизация раз в 100 транзакций), но это снижает надёжность при сбое питания.

Безопасность и очистка

Бинарный журнал может содержать конфиденциальные данные (например, пароли, если они передаются в SQL-запросах, или сами данные строк). Поэтому доступ к файлам журнала должен быть ограничен. Рекомендуется:

• Хранить журналы в отдельном защищённом каталоге.
• Использовать шифрование на уровне файловой системы (например, LUKS).
• Регулярно очищать устаревшие файлы с помощью PURGE BINARY LOGS или автоматического механизма expire_logs_days.

Команда для ручной очистки: `` PURGE BINARY LOGS TO 'mysql-bin.000010'; `` Удаляет все файлы журнала с номером меньше, чем у указанного.

Особенности в различных версиях MySQL

• MySQL 5.5 и 5.6: формат STATEMENT был по умолчанию. Введена поддержка групповой фиксации (group commit) для улучшения производительности.
• MySQL 5.7: формат ROW стал значением по умолчанию. Добавлена поддержка сжатия журнала (binlog_transaction_compression) и улучшена обработка больших транзакций.
• MySQL 8.0: введена поддержка шифрования бинарного журнала на уровне сервера (binlog_encryption). Добавлена возможность атомарной записи DDL-операций. Улучшена производительность при параллельной репликации.
• MySQL 8.4 и новее: продолжается развитие механизмов сжатия и шифрования, а также улучшение интеграции с Group Replication.

Альтернативы и замечания

В некоторых случаях вместо бинарного журнала могут использоваться другие механизмы:

• General query log — текстовый журнал всех запросов (включая SELECT), но он не предназначен для репликации и восстановления.
• Slow query log — журнал медленных запросов, используется для оптимизации производительности.
• InnoDB redo log — журнал повторного выполнения, используемый для восстановления после сбоя на уровне движка InnoDB, но не заменяет binlog для репликации.

Бинарный журнал является обязательным компонентом для работы репликации MySQL и рекомендуется к включению на всех серверах, где требуется надёжное восстановление данных. Отключение binlog (установка log_bin = 0) допустимо только на тестовых или изолированных серверах, не участвующих в репликации и не требующих PITR.

Источники

• MySQL 8.0 Reference Manual: The Binary Log
• MySQL 5.7 Reference Manual: Binary Logging
• Официальная документация Oracle по MySQL: Replication and Binary Logging
• Статья «Understanding MySQL Binary Log» на ресурсе Percona Database Performance Blog