Настройка асинхронной репликации MySQL: пошаговое руководство

Асинхронная репликация MySQL по-прежнему является одним из самых полезных строительных блоков в настройке базы данных. Вы можете использовать ее для реплик чтения, более безопасных резервных копий, копий для отчетов, промежуточных этапов миграции и аварийного восстановления. Ключевое слово — "асинхронная": источник фиксирует транзакцию, не дожидаясь, пока реплика применит ее. Это предотвращает блокировку источника при каждой записи реплики, но также означает, что реплика может отставать.

Старая терминология во многих командах MySQL использует "master" и "slave". В новых версиях MySQL используются "source" и "replica" в таких командах, как SHOW REPLICA STATUS и CHANGE REPLICATION SOURCE TO. Вы все еще можете встретить старые команды в старых системах, примерах и скриптах. В этом руководстве используются source и replica в объяснениях, а также сначала показывается новая форма команды с примечаниями, где старый синтаксис отличается.

В примере используются два сервера:

• Источник: 192.168.1.100
• Реплика: 192.168.1.101
• Пользователь репликации: repl_user
• Область действия: все базы данных, если вы намеренно не фильтруете

Сначала сделайте это в тестовой среде, если вы раньше не выполняли эту процедуру. Настройка репликации проста, когда все чисто. Она становится стрессовой, когда источник занят, дамп несогласован или реплика уже содержит старые данные.

Прежде чем трогать конфигурацию

Убедитесь в основах:

• На обоих серверах работают совместимые версии MySQL.
• Реплика может связаться с источником через порт MySQL, обычно 3306.
• У вас есть административный доступ к обоим экземплярам MySQL.
• Вы можете редактировать файл конфигурации MySQL и перезапускать MySQL при необходимости.
• Реплика пуста, или вы точно знаете, какие существующие данные необходимо заменить.
• На источнике достаточно места на диске для бинарных логов.

На хосте реплики проверьте базовый сетевой доступ:

nc -vz 192.168.1.100 3306

Если nc недоступен, используйте telnet или инструменты подключения вашего облачного провайдера. Исправьте брандмауэры, группы безопасности, адреса привязки и маршрутизацию перед настройкой репликации. Пользователь репликации не поможет, если TCP-путь заблокирован.

Настройка сервера-источника

Источник должен записывать изменения в бинарные логи. Реплика читает эти события и сохраняет их в релейных логах перед применением.

Отредактируйте файл конфигурации MySQL на источнике. Обычные расположения: /etc/mysql/mysql.conf.d/mysqld.cnf, /etc/my.cnf или файл, включенный из этих путей. Добавьте или проверьте эти настройки в разделе [mysqld]:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW

server-id должен быть уникальным для каждого сервера в топологии репликации. log-bin включает бинарное логирование. binlog_format=ROW — практическое значение по умолчанию для большинства современных настроек репликации, поскольку оно записывает изменения строк, а не полагается на поведение повторного выполнения операторов.

Будьте осторожны с binlog-do-db и binlog-ignore-db. Фильтрация кажется удобной, но может преподнести сюрпризы, поскольку поведение на основе операторов зависит от базы данных по умолчанию, выбранной сессией. Если вам нужна фильтрованная репликация, проектируйте и тестируйте ее намеренно. Для первой надежной настройки реплицируйте все.

Перезапустите MySQL на источнике:

sudo systemctl restart mysql
# или, на некоторых системах
sudo systemctl restart mysqld

Проверьте настройки:

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

log_bin должен быть ON. server_id должен быть ненулевым и уникальным.

Создание пользователя репликации

Создайте выделенную учетную запись на источнике, которую будет использовать реплика. Ограничьте хост адресом реплики, если позволяет ваша сетевая архитектура:

CREATE USER 'repl_user'@'192.168.1.101' IDENTIFIED BY 'use_a_real_secret_here';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.101';

Имя привилегии по-прежнему REPLICATION SLAVE в грантах MySQL, хотя в новой документации часто используется "replica".

Проверьте вход с хоста реплики:

mysql -h 192.168.1.100 -u repl_user -p

Если это не удается, исправьте аутентификацию и сеть сейчас. Распространенные причины: bind-address на источнике, правила брандмауэра, несоответствие хоста пользователя, разрешение DNS на другой адрес и несовместимость плагина аутентификации с более старым клиентом.

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

На реплике настройте другой server-id. Релейные логи обычно включаются автоматически для репликации, но их явное именование может сделать операции более понятными:

[mysqld]
server-id=2
relay_log=mysql-relay-bin
read_only=ON

Для более надежной защиты рассмотрите super_read_only=ON после настройки. read_only не останавливает все привилегированные учетные записи от записи. super_read_only безопаснее для реплик, которые никогда не должны принимать записи приложений, но вам может потребоваться временно отключить его для некоторых административных задач.

Перезапустите MySQL на реплике и проверьте:

sudo systemctl restart mysql

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'read_only';

Создание согласованного начального снимка

Реплика должна начинать с копии данных, соответствующей определенной позиции бинарного лога. Если снимок и позиция не совпадают, репликация может запуститься, но быть неправильной.

Для баз данных с большим количеством InnoDB mysqldump --single-transaction обычно является самым простым согласованным методом. Он избегает длительной глобальной блокировки чтения для транзакционных таблиц. Включите --source-data=2 в новых версиях MySQL, чтобы дамп записывал файл бинарного лога источника и позицию в виде закомментированной строки. В старых версиях используется --master-data=2.

Запустите это на доверенном хосте, который может подключиться к источнику:

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --source-data=2 \
  > source_dump.sql

Если ваша версия MySQL не поддерживает --source-data, используйте:

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  > source_dump.sql

--single-transaction безопасен для согласованности InnoDB, но он не делает нетранзакционные таблицы MyISAM согласованными таким же образом. Если у вас все еще есть таблицы MyISAM, запланируйте окно обслуживания или используйте другой метод резервного копирования, который дает согласованный снимок.

Проверьте дамп на наличие записанных координат репликации:

grep -m 1 -E "CHANGE (MASTER|REPLICATION SOURCE)" source_dump.sql

Вы должны увидеть закомментированную строку, содержащую файл бинарного лога и позицию. Сохраните ее. Вы будете использовать ее при указании реплике на источник, если только вы не выберете репликацию на основе GTID.

Импорт снимка на реплику

Передайте дамп на реплику, используя ваш обычный безопасный метод:

scp source_dump.sql db-replica:/tmp/source_dump.sql

На реплике убедитесь, что вы случайно не записываете в существующий набор данных, похожий на производственный. Если эта реплика должна быть чистой копией, удалите и пересоздайте только то, что ваш план миграции предписывает заменить. Затем импортируйте:

mysql -u root -p < /tmp/source_dump.sql

Для больших дампов запускайте импорт в screen или tmux и следите за дисковым пространством. Неудачный импорт из-за заполнения /var/lib/mysql или /tmp тратит время и может оставить вас с наполовину загруженной репликой.

Указание реплике на источник

В MySQL 8.0.23 и новее используйте CHANGE REPLICATION SOURCE TO:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='use_a_real_secret_here',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=1234;

Замените файл и позицию значениями из дампа. Если вы используете старый синтаксис, эквивалент:

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='use_a_real_secret_here',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;

Если ваша среда использует GTID, настройка отличается. Обычно вы настраиваете режим GTID на обоих серверах, восстанавливаете дамп, сохраняющий состояние GTID, и используете SOURCE_AUTO_POSITION=1 вместо файла и позиции. Не смешивайте GTID и инструкции по файлу-позиции casually; выберите один подход и протестируйте его.

Краткое замечание о репликации GTID

Репликация GTID часто проще в эксплуатации после настройки, потому что MySQL отслеживает транзакции по глобальным идентификаторам транзакций вместо того, чтобы заставлять вас управлять файлом бинарного лога и позицией вручную. Это особенно полезно при отработке отказа, смене источника и перестройке реплик.

Это не означает, что вы должны включать его casually в середине миграции. Оба сервера должны иметь совместимые настройки GTID, и ваш процесс резервного копирования или дампа должен сохранять правильное состояние GTID. Распространенный шаблон — настроить:

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON

Затем, после восстановления дампа, учитывающего GTID, настройте реплику с помощью:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='use_a_real_secret_here',
  SOURCE_AUTO_POSITION=1;

Используйте это только в том случае, если история GTID источника и реплики чиста и понятна. Если вы не уверены, репликация на основе файла и позиции проще для понимания при первой настройке. Худший выбор — смешивать примеры из обоих подходов, пока репликация не запустится, но история транзакций будет не такой, как вы думаете.

Запустите репликацию:

START REPLICA;

Старый синтаксис:

START SLAVE;

Проверьте статус:

SHOW REPLICA STATUS\G

Старый синтаксис:

SHOW SLAVE STATUS\G

Ключевые поля:

Поле	Здоровое значение	Что оно говорит вам
Replica_IO_Running	Yes	Реплика может подключиться и получать события бинарного лога.
Replica_SQL_Running	Yes	Реплика может применять события релейного лога.
Last_IO_Error	пусто	Проблемы с сетью, учетными данными или логами источника отображаются здесь.
Last_SQL_Error	пусто	Конфликты данных и ошибки применения отображаются здесь.
Seconds_Behind_Source	низкое или падает	Грубый индикатор задержки.

Старый вывод использует Slave_IO_Running, Slave_SQL_Running и Seconds_Behind_Master.

Тест с небольшой записью

Не объявляйте победу после того, как потоки скажут Yes. Создайте небольшую тестовую таблицу или вставьте безвредную строку в существующую тестовую схему на источнике, затем проверьте, появится ли она на реплике.

Пример на источнике:

CREATE DATABASE IF NOT EXISTS repl_check;
CREATE TABLE IF NOT EXISTS repl_check.heartbeat (
  id INT PRIMARY KEY,
  checked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
REPLACE INTO repl_check.heartbeat (id) VALUES (1);

На реплике:

SELECT * FROM repl_check.heartbeat;

Это выявляет простые ошибки, такие как указание на неправильный источник, фильтрация базы данных или использование устаревших координат.

Защита канала репликации

Если трафик репликации проходит через ненадежную сеть, требуется TLS. Даже внутри частной сети многие команды теперь предпочитают шифрованный трафик базы данных, потому что сетевые границы со временем меняются.

Как минимум, создайте пользователя репликации и канал, чтобы учетные данные не передавались учетным записям приложений. Для каналов на основе TLS настройте сертификаты в соответствии с вашей версией MySQL, а затем включите параметры SSL в конфигурации источника репликации. Точные параметры различаются в зависимости от версии и политики сертификатов, но цель одна: реплика должна проверять, что она подключается к ожидаемому источнику, и защищать учетные данные и изменения строк при передаче.

Также держите привилегии пользователя репликации узкими. Ему не нужен широкий доступ DDL или DML. Если кто-то получит этот пароль, радиус поражения должен быть ограничен чтением логов репликации, а не записью данных приложения.

Распространенные проблемы настройки

Если Replica_IO_Running равно No, реплика не может получать события. Проверьте:

• SOURCE_HOST правильный.
• Источник прослушивает ожидаемый адрес и порт.
• Брандмауэры и группы безопасности разрешают трафик.
• Хост пользователя репликации соответствует IP-адресу источника реплики.
• Пароль и плагин аутентификации работают с версией клиента/сервера реплики.
• Запрошенный файл бинарного лога все еще существует на источнике.

Если Replica_SQL_Running равно No, реплика получила события, но не смогла их применить. Проверьте Last_SQL_Error. Дублирующиеся ключи часто означают, что реплика не была инициализирована из точно соответствующего снимка или кто-то напрямую записал данные на реплику. Отсутствующие строки часто означают расхождение данных. Пропуск транзакции с помощью SQL_SLAVE_SKIP_COUNTER может заставить поток двигаться, но также может сделать реплику неправильной. Используйте его только тогда, когда вы понимаете неудачную транзакцию и принимаете риск расхождения.

Если задержка высока сразу после настройки, дайте реплике догнать и посмотрите, падает ли Seconds_Behind_Source. Импорт большого дампа с последующим запуском репликации может оставить отставание. Если задержка растет, а не уменьшается, проверьте дисковый ввод-вывод на реплике и объем записи на источнике.

Поддержание здоровья реплики после настройки

Настройте хранение бинарных логов на источнике, чтобы реплики могли пережить обслуживание и сбои. Современный MySQL использует binlog_expire_logs_seconds:

[mysqld]
binlog_expire_logs_seconds=604800

Этот пример хранит логи около 7 дней. Выберите значение, исходя из ваших потребностей в восстановлении и емкости диска. Старые системы могут использовать expire_logs_days.

Мониторьте состояние репликации и задержку. Как минимум, предупреждайте, когда любой поток репликации останавливается, когда задержка превышает ваш допуск и когда использование диска источника растет, потому что бинарные логи не очищаются. Для проверок согласованности данных многие команды используют инструменты Percona Toolkit, такие как pt-table-checksum и pt-table-sync, но тщательно тестируйте их перед запуском на данных производственного размера.

Наконец, держите трафик приложений подальше от реплики, пока вы не убедитесь, что она доступна только для чтения, догнала и мониторится. Реплика, которая принимает случайные записи, хуже, чем отсутствие реплики, потому что ущерб может оставаться скрытым до отработки отказа или восстановления.

Асинхронная репликация работает хорошо, когда начальный снимок, координаты бинарного лога, привилегии и мониторинг совпадают. Большинство неудачных настроек происходит из-за того, что одно из этих условий предполагается, а не проверяется.