MySQL-реплікація — це механізм, який автоматично синхронізує дані між серверами в режимі реального часу. Вона вирішує одразу три завдання: якщо основний сервер впаде, дані не буде втрачено; навантаження на читання можна розподілити між кількома серверами; нарешті, резервні копії можна знімати без зупинки основної бази. У цьому гайді розглядаються два підходи до реплікації. Перший — класичний source-replica (він же master-slave): один головний сервер приймає всі зміни, решта їх копіюють. Другий — двостороння master-master реплікація, де кожен сервер може одночасно і читати, і записувати. Усі приклади та команди актуальні для MySQL 8.0.23+, де Oracle перейменувала застарілі терміни — там, де це важливо, старий синтаксис позначено окремо.

Що потрібно підготувати перед початком

Перш ніж змінювати конфігурацію MySQL, переконайтеся, що інфраструктура готова. Усі подальші кроки передбачають, що у вас є два окремі сервери (фізичні, віртуальні або контейнери) з MySQL 8.0+ однакової версії.

Який тип реплікації краще вибрати

Source-Replica (master-slave) — найкращий вибір для новачків і більшості production-сценаріїв. Підходить для масштабування читання (read replicas), створення резервних копій без навантаження на production-сервер, аналітичних запитів на окремому сервері та простого failover (ручне підвищення репліки до source). Складність налаштування низька, ризик втрати даних мінімальний при sync_binlog=1.

Master-Master (двостороння) — підходить лише тоді, коли потрібен запис на два вузли в різних локаціях або миттєвий failover без просування репліки. На практиці більшість команд використовують master-master у режимі active-passive: записують лише в один вузол, другий тримають як hot standby. Складність висока, ризик конфліктів реальний при одночасному записі.

Group Replication / InnoDB Cluster — рекомендований Oracle варіант для true multi-master. Має вбудований механізм розв’язання конфліктів (certification-based), автоматичний failover і quorum. Потребує щонайменше 3 вузли. Якщо потрібен надійний multi-master — це правильний вибір замість ручного master-master.

Source-Replica (master-slave) реплікація

Це найпростіший і найпоширеніший тип реплікації MySQL. Один сервер (source) приймає всі операції запису, другий (replica) автоматично отримує і застосовує зміни. Репліка зазвичай працює як read-only вузол і використовується для розвантаження читання, резервування та підвищення відмовостійкості.

Якщо ви лише починаєте працювати з реплікацією, краще одразу використовувати схему source-replica разом із GTID (Global Transaction Identifier).

GTID — це унікальний ідентифікатор кожної транзакції в MySQL. Він дозволяє репліці відстежувати не позицію в бінарному логу, а конкретні вже застосовані транзакції.
Виглядає він приблизно так:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

Де:

• 3E11FA47-71CA-11E1-9E33-C80AA9429562 — UUID сервера, на якому транзакцію було створено;
• 23 — порядковий номер транзакції.

Чому краще використовувати GTID

Традиційний спосіб налаштування реплікації використовує координати бінарного логу:

• SOURCE_LOG_FILE
• SOURCE_LOG_POS

Це робочий варіант, але він менш зручний:

• потрібно вручну фіксувати файл і позицію;
• вищий ризик помилки під час первинного налаштування;
• складніше відновлювати реплікацію після збою;
• незручніше виконувати переключення на інший source.

З GTID реплікація стає простішою та надійнішою:

• replica сама визначає, які транзакції вже застосовано;
• не потрібно вручну вказувати SOURCE_LOG_FILE і SOURCE_LOG_POS;
• простіше виконувати відновлення і переключення ролей;
• такий підхід краще підходить для автоматизації та масштабування.

Конфігурація my.cnf на source-сервері

Файл: /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian) або /etc/my.cnf (RHEL/CentOS).

[mysqld]
# Унікальний ідентифікатор сервера
server-id                       = 1

# Увімкнення бінарного логу
log_bin                         = /var/log/mysql/mysql-bin.log

# Рекомендований формат бінлогу
binlog_format                   = ROW

# Увімкнення GTID
gtid_mode                       = ON
enforce_gtid_consistency        = ON

# Дозволити підключення ззовні
bind-address                    = 0.0.0.0

# Безпечний запис бінлогу на диск
sync_binlog                     = 1

# Термін зберігання бінарних логів
binlog_expire_logs_seconds      = 604800

# Максимальний розмір файлу бінлогу
max_binlog_size                 = 100M

# (Опціонально) Реплікувати лише конкретні бази:
# binlog_do_db                  = mydb
# binlog_do_db                  = another_db

Конфігурація my.cnf на replica-сервері

[mysqld]
# Унікальний ідентифікатор сервера, відмінний від source
server-id                       = 2

# Relay log для зберігання подій від source
relay-log                       = /var/log/mysql/mysql-relay-bin.log

# Захист від випадкових записів на репліці
read_only                       = ON
super_read_only                 = ON

# Бінарний лог на репліці
log_bin                         = /var/log/mysql/mysql-bin.log

# Записувати репліковані події в бінлог репліки
log_replica_updates             = ON

# Увімкнення GTID
gtid_mode                       = ON
enforce_gtid_consistency        = ON

Після редагування конфігурації на обох серверах перезапустіть MySQL:

sudo systemctl restart mysql

Налаштування реплікації з GTID

Крок 1 — Створити користувача для реплікації на source

На source-сервері потрібно створити окремого користувача, через якого replica підключатиметься і читатиме зміни з бінарного логу. Для цього користувачу видаються права реплікації. Рекомендується обмежити доступ за IP-адресою replica-сервера, а не дозволяти підключення з будь-якого хоста.

-- Варіант A: caching_sha2_password (рекомендується для MySQL 8.0)
CREATE USER 'repl_user'@'192.168.0.20' IDENTIFIED BY 'StrongP@ss123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.0.20';
FLUSH PRIVILEGES;

Альтернативно, якщо потрібна сумісність зі старою конфігурацією:

-- Варіант B: mysql_native_password
CREATE USER 'repl_user'@'192.168.0.20'
IDENTIFIED WITH mysql_native_password BY 'StrongP@ss123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.0.20';
FLUSH PRIVILEGES;

Якщо використовується caching_sha2_password без SSL, на replica потрібно буде вказати GET_SOURCE_PUBLIC_KEY=1 у команді CHANGE REPLICATION SOURCE TO.

Крок 2 — Підготувати консистентний знімок бази на source

Перед створенням дампа потрібно зафіксувати стан даних, щоб snapshot вийшов узгодженим. Це особливо важливо, якщо на source у цей момент продовжуються зміни. Блокування дозволяє уникнути ситуації, коли частина даних потрапила в дамп до зміни, а частина — уже після неї.

Для отримання коректного дампа виконайте:

FLUSH TABLES WITH READ LOCK;

Крок 3 — Зробити дамп бази

На цьому етапі створюється початкова копія даних, з якої replica отримає поточний стан бази. Після імпорту цього дампа replica зможе наздоганяти source вже через реплікацію бінарних логів. Для стартової ініціалізації це базовий і найбільш зрозумілий спосіб.

В окремому терміналі виконайте:

mysqldump -u root -p \
  --databases mydb \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  > /tmp/source_dump.sql

Пояснення:

• --single-transaction дає консистентний знімок для InnoDB-таблиць;
• --routines, --triggers, --events включають пов’язані об’єкти;
• при використанні GTID немає потреби спиратися на SOURCE_LOG_FILE і SOURCE_LOG_POS як на основний механізм налаштування.

Після завершення дампа зніміть блокування:

UNLOCK TABLES;

Передайте дамп на replica:

scp /tmp/source_dump.sql user@192.168.0.20:/tmp/

Крок 4 — Імпортувати дамп на replica

Тепер потрібно завантажити отриманий дамп на replica, щоб вона стартувала не з порожньої бази, а з актуальної копії даних source. Після цього сервер replica буде готовий приймати лише відсутні транзакції через механізм GTID-реплікації.

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

Потім підключіться до MySQL на replica:

mysql -u root -p

Крок 5 — Налаштувати реплікацію через GTID на replica

На цьому кроці replica отримує параметри підключення до source і переводиться в режим автоматичного позиціонування через GTID. На відміну від старої схеми, тут не потрібно вручну вказувати файл бінарного логу і позицію — MySQL сам визначить, які транзакції вже є на replica, а які ще потрібно отримати.

Виконайте:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.0.10',
    SOURCE_PORT=3306,
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='StrongP@ss123!',
    SOURCE_AUTO_POSITION=1,
    GET_SOURCE_PUBLIC_KEY=1;

Пояснення:

• SOURCE_AUTO_POSITION=1 увімкнює реплікацію через GTID;
• у цьому режимі MySQL автоматично визначає, які транзакції вже застосовано на replica;
• GET_SOURCE_PUBLIC_KEY=1 потрібен при використанні caching_sha2_password без SSL.

Крок 6 — Запустити і перевірити реплікацію

Після налаштування з’єднання реплікацію потрібно запустити і одразу перевірити її стан. На цьому етапі важливо переконатися, що replica успішно підключилася до source, читає бінарний лог і застосовує зміни без помилок. Саме ця перевірка показує, що схема справді працює, а не лише формально налаштована.

START REPLICA;
SHOW REPLICA STATUS\G

Ключові поля для перевірки:

Replica_IO_Running:  Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0
Last_IO_Error:
Last_SQL_Error:

Що це означає:

• Replica_IO_Running: Yes — replica підключилася до source і читає бінарний лог;
• Replica_SQL_Running: Yes — replica застосовує отримані зміни;
• Seconds_Behind_Source: 0 — відставання відсутнє;
• Last_IO_Error і Last_SQL_Error мають бути порожніми.

Якщо обидва потоки працюють і помилок немає, реплікацію налаштовано коректно.

Двостороння (master-master) реплікація

Двостороння реплікація — це два сервери, кожен із яких одночасно є source і replica для іншого. Зміни на Node1 реплікуються на Node2, і навпаки. Це суттєво складніше за звичайну source-replica, головна причина — ризик конфліктів при одночасному записі на обидва вузли. MySQL не має вбудованого механізму розв’язання конфліктів для стандартної InnoDB-реплікації.

Що змінюється порівняно з master-slave

Основних відмінностей п’ять. По-перше, обидва вузли потребують увімкненого log_bin, оскільки кожен виступає source. По-друге, log_replica_updates має бути ON на обох вузлах — без цього репліковані події не потрапляють у локальний бінлог, і circular replication не працює. По-третє, auto_increment_increment і auto_increment_offset необхідні для уникнення колізій первинних ключів. По-четверте, обидва вузли потребують користувача для реплікації. По-п’яте, обидва вузли є writable (не read-only).

Конфігурація my.cnf для двох вузлів

Node 1 (IP: 192.168.1.10):

[mysqld]
server-id                = 1
log_bin                  = /var/log/mysql/mysql-bin.log
binlog_format            = ROW
relay_log                = /var/log/mysql/mysql-relay-bin
log_replica_updates      = ON
bind-address             = 0.0.0.0

# Критично: розділення AUTO_INCREMENT між вузлами
auto_increment_increment = 2
auto_increment_offset    = 1
# Node 1 генерує ID: 1, 3, 5, 7, 9...

binlog_expire_logs_seconds = 604800
max_binlog_size          = 100M

Node 2 (IP: 192.168.1.20):

[mysqld]
server-id                = 2
log_bin                  = /var/log/mysql/mysql-bin.log
binlog_format            = ROW
relay_log                = /var/log/mysql/mysql-relay-bin
log_replica_updates      = ON
bind-address             = 0.0.0.0

# Node 2 генерує ID: 2, 4, 6, 8, 10...
auto_increment_increment = 2
auto_increment_offset    = 2

binlog_expire_logs_seconds = 604800
max_binlog_size          = 100M

Ключова змінна — auto_increment_increment=2 з різним auto_increment_offset на кожному вузлі. Це гарантує, що Node 1 генерує непарні AUTO_INCREMENT-значення (1, 3, 5…), а Node 2 — парні (2, 4, 6…), повністю уникаючи конфліктів первинних ключів. Для N вузлів використовуйте auto_increment_increment=N з offset від 1 до N.

Змінна log_replica_updates (або log_slave_updates до MySQL 8.0.26) забезпечує, що події, отримані від партнера, записуються у власний бінлог. Без неї circular replication неможлива — Node1 відправить зміну на Node2, Node2 застосує її, але не запише у свій бінлог, тому Node1 ніколи не «побачить» зворотну реплікацію. MySQL запобігає нескінченним циклам завдяки server-id: кожен вузол автоматично ігнорує події зі своїм власним server-id.

Покрокове налаштування circular replication

Після зміни конфігурації перезапустіть MySQL на обох вузлах: sudo systemctl restart mysql.

Крок 1 — Створити користувачів для реплікації на обох вузлах:

На Node 1:

CREATE USER 'repl_user'@'192.168.1.20' IDENTIFIED BY 'StrongP@ss123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.20';
FLUSH PRIVILEGES;

На Node 2:

CREATE USER 'repl_user'@'192.168.1.10' IDENTIFIED BY 'StrongP@ss123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.10';
FLUSH PRIVILEGES;

Крок 2 — Налаштувати Node2 як репліку Node1:

На Node 1 виконайте SHOW MASTER STATUS; і запишіть File/Position.

На Node 2:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.1.10',
    SOURCE_PORT=3306,
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='StrongP@ss123!',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=<позиція_з_node1>,
    GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

Крок 3 — Налаштувати Node1 як репліку Node2:

На Node 2 виконайте SHOW MASTER STATUS; і запишіть File/Position.

На Node 1:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.1.20',
    SOURCE_PORT=3306,
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='StrongP@ss123!',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=<позиція_з_node2>,
    GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

Крок 4 — Перевірити реплікацію на ОБОХ вузлах:

SHOW REPLICA STATUS\G

На кожному вузлі Replica_IO_Running і Replica_SQL_Running мають показувати Yes. Протестуйте: вставте рядок на Node1 (отримає непарний ID), потім на Node2 (отримає парний ID), переконайтеся, що обидва рядки видно на обох вузлах.

Конфлікти і як їх уникати

MySQL не має вбудованого розв’язання конфліктів для стандартної InnoDB master-master реплікації. Якщо обидва вузли одночасно змінять один і той самий рядок, реплікація зламається з помилкою 1062 (duplicate key) або 1032 (record not found). Найбезпечніші стратегії для уникнення конфліктів: направляти всі записи лише на один вузол (другий — як hot standby), розділяти запис на рівні застосунку (різні таблиці або діапазони даних на різні вузли), або використовувати UUID замість AUTO_INCREMENT для первинних ключів.

Для повноцінного multi-master з автоматичним розв’язанням конфліктів Oracle рекомендує MySQL Group Replication (доступна з MySQL 5.7.17, суттєво покращена в 8.0) або InnoDB Cluster, який об’єднує Group Replication з MySQL Router і MySQL Shell для простого керування.

Висновок

Реплікація MySQL 8.0 стала значно зручнішою завдяки оновленій термінології, GTID за замовчуванням і бінарному логу, увімкненому «з коробки». Головне правило для новачків — починайте з простої source-replica з GTID, завжди встановлюйте read_only=ON на репліці, використовуйте GET_SOURCE_PUBLIC_KEY=1 для caching_sha2_password, і тримайте binlog_expire_logs_seconds достатньо високим, щоб репліка встигала прочитати всі логи. Master-master варто розглядати лише як active-passive failover, а для справжнього multi-master — переходити на Group Replication. І найважливіше: якщо ви клонували VM — завжди видаляйте /var/lib/mysql/auto.cnf перед запуском реплікації.