Как работает репликация MySQL (primary/replica)?

Архитектура репликации

MySQL репликация построена на binary log (binlog) — журнале всех изменений данных. Основные компоненты:

• Primary (master): записывает изменения в binlog.
• Replica I/O thread: подключается к primary, читает binlog и записывает в локальный relay log.
• Replica SQL thread: читает relay log и применяет события к данным реплики.

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

-- my.cnf на primary:
-- [mysqld]
-- server-id = 1
-- log_bin = /var/lib/mysql/binlog
-- binlog_format = ROW
-- gtid_mode = ON
-- enforce_gtid_consistency = ON

-- my.cnf на replica:
-- [mysqld]
-- server-id = 2
-- log_bin = /var/lib/mysql/replica-binlog
-- relay_log = /var/lib/mysql/relay-log
-- gtid_mode = ON
-- enforce_gtid_consistency = ON
-- read_only = ON

-- Создать пользователя репликации на primary:
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- На replica настроить подключение к primary (GTID-режим):
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary.db',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='repl_password',
  SOURCE_AUTO_POSITION=1;  -- GTID auto-positioning

START REPLICA;
SHOW REPLICA STATUS\G

Мониторинг состояния репликации

SHOW REPLICA STATUS\G
-- Ключевые поля:
-- Replica_IO_Running: Yes  — I/O thread работает
-- Replica_SQL_Running: Yes — SQL thread работает
-- Seconds_Behind_Source: 0 — лаг в секундах
-- Executed_Gtid_Set — применённые GTID на реплике
-- Retrieved_Gtid_Set — полученные от primary
-- Last_SQL_Error — ошибка применения (например, дедлок)

-- Мониторинг через performance_schema:
SELECT * FROM performance_schema.replication_connection_status\G
SELECT * FROM performance_schema.replication_applier_status_by_worker\G

Параллельная репликация

По умолчанию SQL thread однопоточный. Параллельная репликация ускоряет применение на реплике:

-- my.cnf на replica:
-- replica_parallel_workers = 8
-- replica_parallel_type = LOGICAL_CLOCK  -- MySQL 8 default
-- replica_preserve_commit_order = ON     -- сохраняет порядок транзакций

-- Проверить workers:
SELECT * FROM performance_schema.replication_applier_status_by_worker;

Semi-synchronous репликация

Гарантирует, что хотя бы одна реплика получила событие до подтверждения commit на primary:

-- На primary:
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 1000;  -- fallback через 1 сек

-- На replica:
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

-- Мониторинг:
SHOW STATUS LIKE 'Rpl_semi_sync%';

Group Replication (InnoDB Cluster)

Встроенный multi-primary или single-primary кластер с автоматическим failover:

-- Установка и запуск через MySQL Shell:
dba.createCluster('myCluster');
dba.getCluster().addInstance('root@replica2:3306');
dba.getCluster().status();

Подводные камни

• Асинхронная репликация допускает потерю данных при failover — primary подтверждает commit до того, как реплика получила событие.
• GTID-режим несовместим с CREATE TABLE ... SELECT в MySQL 5.7 (ошибка); в MySQL 8 это ограничение снято.
• При ошибке SQL thread репликация останавливается — нужен мониторинг Replica_SQL_Running=No и алерты.
• Replica в режиме read_only=ON не блокирует пользователей с привилегией SUPER — используйте super_read_only=ON.
• Параллельная репликация с LOGICAL_CLOCK требует, что транзакции на primary коммитились с перекрытием — при низком concurrency на primary параллелизм не даст ускорения.
• Binlog rotation: если реплика отстала и нужный binlog файл удалён с primary — репликацию придётся пересоздавать с нуля.
• CHANGE REPLICATION SOURCE TO без RESET REPLICA накапливает старые relay log — при рестарте применяются старые события.

• Не уточнять storage engine и говорить о primary/replica replication так, будто MyISAM и InnoDB ведут себя одинаково.
• Игнорировать implicit commit, autocommit, isolation level или binlog там, где они меняют ответ.
• Описывать PostgreSQL-поведение как MySQL-поведение.
• Забывать проверить план через EXPLAIN и фактическую версию MySQL.

• Кандидат объясняет primary/replica replication через реальный механизм MySQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.