Репликация MySQL: настройка master-slave

Администрирование
Репликация MySQL: настройка master-slave

MySQL репликация в схеме master-slave — это способ держать актуальную копию базы на втором сервере в режиме реального времени. Мастер принимает все записи и фиксирует изменения в бинарном логе, а реплика читает этот лог и повторяет те же операции у себя. В результате вы получаете горячую копию данных для быстрого восстановления, сервер для тяжёлых отчётных запросов и площадку для бэкапов без нагрузки на продакшен. В этой статье настроим классическую связку master-slave на MySQL 8.0 с GTID — от конфигов до проверки отставания.

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

  • Отказоустойчивость. Если мастер выходит из строя, реплику можно за минуты повысить до основного сервера. Это на порядки быстрее, чем разворачивать вчерашний дамп.
  • Разгрузка чтения. SELECT-запросы аналитики, отчётов и поиска переносятся на реплику, освобождая мастер под запись.
  • Бэкапы без простоя. Снимать дамп с реплики можно хоть каждый час — продакшен этого не почувствует. Сами схемы дампов разобраны в статье про автоматические бэкапы MySQL.
  • Миграции. Реплика в новом дата-центре — стандартный способ переезда с минимальным даунтаймом: догнали мастер, переключили приложение. Подробнее — в статье о переносе базы на другой сервер.

Важно понимать: репликация — не замена бэкапам. Ошибочный DROP TABLE реплика исполнит так же послушно, как мастер. Копии по расписанию нужны в любом случае.

Что понадобится

Два VPS с MySQL одной версии (реплика может быть новее мастера на минорную версию, но не старее). Между серверами должна быть сетевая связность по порту 3306. В примерах:

РольIP (пример)server_id
Master10.0.0.11
Replica10.0.0.22

Если MySQL ещё не установлен, начните с инструкции установка MySQL на VPS на обоих серверах.

Шаг 1. Настройка мастера

Откройте конфиг на мастере:

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

Добавьте в секцию [mysqld]:

server_id                = 1          # уникален в пределах связки
log_bin                  = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 604800   # хранить бинлоги 7 дней
max_binlog_size          = 512M
gtid_mode                = ON         # глобальные идентификаторы транзакций
enforce_gtid_consistency = ON
bind-address             = 0.0.0.0    # слушать не только localhost

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

Перезапустите MySQL и проверьте, что бинлог пишется:

sudo systemctl restart mysql
sudo mysql -e "SHOW BINARY LOGS;"

Откройте порт 3306 только для IP реплики — наружу для всех его светить нельзя:

sudo ufw allow from 10.0.0.2 to any port 3306 proto tcp

Шаг 2. Пользователь для репликации

На мастере создайте отдельную учётную запись, от имени которой реплика будет читать бинлог:

CREATE USER 'repl'@'10.0.0.2' IDENTIFIED WITH caching_sha2_password BY 'НадёжныйПароль456!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.2';
FLUSH PRIVILEGES;

Права REPLICATION SLAVE — единственное, что нужно этому пользователю; доступа к данным таблиц у него нет.

Шаг 3. Перенос текущих данных на реплику

Реплика должна стартовать с точной копии мастера. Снимите согласованный дамп:

mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --set-gtid-purged=ON \
  --triggers --routines --events \
  | gzip > /tmp/master_full.sql.gz

Ключ --single-transaction делает дамп без блокировки InnoDB-таблиц, а --set-gtid-purged=ON записывает в дамп множество уже выполненных GTID — реплика поймёт, с какого места продолжать.

Передайте файл на второй сервер и загрузите:

scp /tmp/master_full.sql.gz root@10.0.0.2:/tmp/
# далее на реплике:
gunzip < /tmp/master_full.sql.gz | mysql -u root -p

Шаг 4. Настройка реплики

Конфиг на втором сервере:

server_id        = 2
gtid_mode        = ON
enforce_gtid_consistency = ON
read_only        = ON     # запретить запись всем, кроме репликации и root
relay_log        = /var/log/mysql/mysql-relay-bin.log

Параметр read_only защищает от главной болезни master-slave — случайной записи в реплику, после которой данные расходятся. Перезапустите MySQL и укажите реплике её мастера:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.1',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'НадёжныйПароль456!',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1;

START REPLICA;

SOURCE_AUTO_POSITION = 1 включает автопозиционирование по GTID — не нужно указывать имя файла бинлога и смещение вручную. В версиях до MySQL 8.0.22 команды называются CHANGE MASTER TO и START SLAVE — синтаксис аналогичен.

Шаг 5. Проверка работы

Смотрим состояние репликации:

SHOW REPLICA STATUS\G

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

ПолеНормаЧто значит
Replica_IO_RunningYesПоток чтения бинлога с мастера работает
Replica_SQL_RunningYesПоток применения транзакций работает
Seconds_Behind_Source0 или близкоОтставание реплики в секундах
Last_ErrorпустоНет ошибок применения

Практический тест: создайте на мастере таблицу и запись —

CREATE DATABASE repl_test;
CREATE TABLE repl_test.ping (id INT PRIMARY KEY, note VARCHAR(50));
INSERT INTO repl_test.ping VALUES (1, 'hello from master');

— и через секунду-две проверьте на реплике:

SELECT * FROM repl_test.ping;

Если запись на месте — связка работает.

Мониторинг и типичные проблемы

Отставание реплики удобно проверять скриптом по крону:

#!/bin/bash
# /usr/local/bin/check_replica.sh — алерт при отставании или остановке
LAG=$(mysql -N -e "SHOW REPLICA STATUS\G" | awk '/Seconds_Behind_Source/ {print $2}')
if [ "$LAG" = "NULL" ] || [ "${LAG:-999}" -gt 60 ]; then
    echo "Replica problem: lag=$LAG" | mail -s "MySQL replica alert" admin@example.com
fi

Частые сценарии сбоев:

  • Ошибка дублирующегося ключа (Last_Error с кодом 1062) — обычно значит, что кто-то писал в реплику напрямую. Проверьте read_only и найдите источник записи; расхождение надёжнее всего лечится пересозданием реплики из свежего дампа.
  • Реплика сильно отстаёт — включите параллельное применение транзакций: replica_parallel_workers = 4 в конфиге реплики. Также помогает более быстрый диск: репликация чувствительна к задержкам записи.
  • Обрыв связи — потоки IO/SQL переподключаются сами; если Replica_IO_Running: Connecting висит долго, проверьте firewall и доступность порта 3306 с реплики: nc -zv 10.0.0.1 3306.

Ускорить реплику и мастер помогает и общая настройка СУБД — буферный пул, журналы InnoDB и лимиты подключений разобраны в статье оптимизация настроек MySQL.

Частые вопросы

Репликация заменяет резервные копии?

Нет. Реплика повторяет всё, включая ошибочные удаления и DROP DATABASE. Она защищает от отказа сервера, но не от человеческой ошибки или повреждения данных на уровне приложения. Держите и реплику, и регулярные дампы.

Можно ли писать в реплику?

В классической схеме master-slave — нельзя: любая прямая запись приводит к расхождению данных и ошибкам репликации. Именно поэтому на реплике включают read_only = ON. Схемы с записью в несколько узлов (master-master, групповая репликация) существуют, но требуют заметно более аккуратного проектирования приложения.

Какое отставание реплики считается нормальным?

На здоровой связке в одной сети Seconds_Behind_Source держится на нуле почти всё время и кратковременно подскакивает при массовых записях. Постоянное отставание в десятки секунд — повод включить параллельных воркеров, проверить диск реплики и поискать тяжёлые транзакции на мастере.

Сколько реплик можно подключить к одному мастеру?

Технически — много: каждая реплика просто читает бинарный лог. На практике 2–5 реплик покрывают большинство задач; при большем числе имеет смысл каскадная схема, где промежуточная реплика раздаёт лог остальным.

Как переключиться на реплику при падении мастера?

Убедитесь, что реплика применила все полученные транзакции (SHOW REPLICA STATUS\G), остановите репликацию командой STOP REPLICA, снимите read_only и переключите приложение на новый адрес базы. С GTID обратное подключение старого мастера в роли реплики делается той же командой CHANGE REPLICATION SOURCE TO без ручного поиска позиций.


Для мастера и реплики под нагрузкой важны запас по ядрам и быстрый диск: репликация упирается в скорость применения транзакций. Тариф Ultra от Cheap-Host за 1500 ₽/мес — это KVM-VPS в Москве с 20 vCPU, 32 ГБ RAM и 192 ГБ NVMe; сервер выдаётся за 45–60 секунд. Разверните связку master-slave на cheap-host.onl.

Читайте также