Навіщо потрібна реплікація 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.