Зачем нужна репликация MySQL

Репликация MySQL позволяет поддерживать точную копию базы данных на втором сервере в реальном времени. Используется для: читающей нагрузки (read replicas), аварийного восстановления (failover), бэкапов без нагрузки на master.

Архитектура Master-Slave

Master записывает все изменения в Binary Log (binlog). Slave читает binlog и применяет те же изменения у себя. Репликация асинхронная — небольшая задержка (lag) нормальна.

Настройка Master-сервера

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size = 100M

sudo systemctl restart mysql

# Создать пользователя для репликации
mysql -u root -p -e "
CREATE USER 'replicator'@'%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;"

# Узнать позицию binlog (запомните File и Position)
mysql -u root -p -e "SHOW MASTER STATUS\G"

Настройка Slave-сервера

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay.log
read_only = 1
log_slave_updates = 1

sudo systemctl restart mysql

# Подключить к master
mysql -u root -p -e "
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replicator',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;
START SLAVE;"

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

mysql -u root -p -e "SHOW SLAVE STATUS\G"

-- Ключевые параметры:
-- Slave_IO_Running: Yes  (должно быть Yes)
-- Slave_SQL_Running: Yes  (должно быть Yes)
-- Seconds_Behind_Master: 0  (задержка репликации)

Если Slave_IO_Running: No или Slave_SQL_Running: No — репликация сломана. Проверьте Last_Error в выводе SHOW SLAVE STATUS для диагностики.

GTID-репликация (MySQL 5.6+ рекомендуется)

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = ON

# На slave вместо MASTER_LOG_FILE/POS:
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replicator',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_AUTO_POSITION=1;

GTID-репликация предпочтительнее традиционной binlog-репликации: автоматически определяет позицию, легче управлять failover и не нужно вручную отслеживать File/Position.

Типичные ошибки репликации

Ошибка	Причина	Решение
Last_IO_Error: error connecting to master... Access denied	Неверный пароль пользователя репликации или он не подключается с IP реплики	Проверить `GRANT REPLICATION SLAVE` и хост, с которого подключается slave
Last_SQL_Error: Duplicate entry '5' for key 'PRIMARY'	На реплике уже есть данные, не совпадающие с дампом, с которого начали репликацию	Пересоздать реплику с чистого дампа мастера с зафиксированной позицией binlog
Last_IO_Error: Got fatal error 1236 from master... binary log is purged	Мастер удалил binlog-файл раньше, чем реплика успела его прочитать (маленький `binlog_expire_logs_seconds`)	Увеличить срок хранения binlog на мастере и пересоздать реплику

Почему задержка репликации (lag) растёт не линейно

Асинхронная репликация MySQL по умолчанию однопоточная на стороне SQL-потока (до MySQL 5.7 многопоточная репликация опциональна и требует явной настройки slave_parallel_workers). Если на мастере один тяжёлый UPDATE выполняется 10 секунд, на реплике та же операция блокирует весь поток репликации на 10 секунд — при этом остальные, более лёгкие изменения мастера просто накапливаются в очереди. Именно поэтому лаг иногда растёт скачками, а не равномерно: одна долгая транзакция на мастере откладывает применение всех последующих на реплике.

Когда read-реплика не годится для чтения

Приложение, которое сразу после записи читает те же данные (типичный паттерн: создать заказ и тут же показать страницу подтверждения), не должно читать эту запись с реплики — из-за задержки репликации данные там могут появиться на секунды позже, и пользователь увидит ошибку «заказ не найден». Правило простое: сразу после своей же записи читать с мастера, а не с реплики; на реплику направлять только чтение независимых от текущей сессии данных — отчёты, аналитику, ленты других пользователей.

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

# Основные показатели состояния (MySQL 5.7 / MariaDB < 10.5)
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error"

# В MySQL 8.0.22+ команда переименована
mysql -u root -p -e "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source"

# Сверить контрольную сумму данных между мастером и репликой (pt-table-checksum)
pt-table-checksum --host=master_host --databases=mydb

# Проверить состояние InnoDB на реплике под нагрузкой репликации
SHOW ENGINE INNODB STATUS\G

Seconds_Behind_Master, равный 0, не гарантирует полную идентичность данных — он показывает разницу во времени между обработанным событием и текущим моментом, а не факт сверки данных. Для настоящей проверки консистентности нужен именно pt-table-checksum из Percona Toolkit, который построчно сравнивает контрольные суммы. Более устойчивый вариант репликации без ручного отслеживания File/Position описан в статье про GTID-репликацию, а восстановление на конкретный момент времени — в статье про point-in-time recovery через binlog.