Shkerka.ruзаметки админа
Linux и FreeBSD

Настройка репликации баз данных MySQL

Опубликовано: Обновлено: 5 мин чтенияАвтор: Редакция Shkerka.ru

Репликация MySQL поддерживает на одном или нескольких серверах точную копию базы с ведущего сервера. Разбираем, зачем она нужна, как настроить классическую асинхронную схему по позиции в бинарном журнале и чем отличается синтаксис в MySQL 8.

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

Ведущий сервер (в старой терминологии master, в новой — source) записывает все изменения данных в бинарный журнал. Ведомые серверы (slave, replica) читают этот журнал и повторяют изменения у себя. По умолчанию репликация асинхронная: ведущий не ждёт подтверждения от реплик и не знает, успевают ли они за ним. Есть также полусинхронный режим (ведущий ждёт, пока хотя бы одна реплика получит транзакцию) и синхронные кластерные решения вроде Group Replication и Galera.

Типичные задачи:

  1. Распределение нагрузки. Запись идёт на ведущий сервер, тяжёлые запросы на чтение и отчёты — на реплики.
  2. Отказоустойчивость и обслуживание. Реплику можно быстро сделать основным сервером, на ней можно снимать резервные копии, не останавливая приложения.
  3. Географическое распределение. Копия данных может находиться в другом дата-центре.

MySQL поддерживает два способа указывать, откуда реплике читать журнал:

Метод Как реплика отслеживает положение
Классический Имя файла бинарного журнала и позиция в нём
GTID Глобальные идентификаторы транзакций

Реплицировать можно все базы целиком или отдельные базы и таблицы.

Настройка ведущего сервера

В конфигурации MySQL (/etc/mysql/mysql.conf.d/mysqld.cnf в Ubuntu, my.cnf или my.ini в других системах) в секции [mysqld] нужны уникальный идентификатор сервера и включённый бинарный журнал:

[mysqld]
server-id    = 1
log-bin      = mysql-bin
bind-address = 0.0.0.0

server-id — число от 1 до 2³²−1, уникальное для каждого сервера в схеме. Для InnoDB ради надёжности рекомендуются ещё два параметра (в современных версиях это значения по умолчанию):

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Убедитесь, что не задан skip-networking, а bind-address позволяет подключаться с адреса реплики. После правки перезапустите MySQL.

Создаём пользователя для репликации:

CREATE USER 'replica'@'11.11.11.%' IDENTIFIED BY 'СложныйПароль';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'11.11.11.%';

Снимок данных и координаты

Если на ведущем сервере уже есть данные, реплика должна начать с их согласованной копии и точно знать, с какого места журнала продолжать.

Для таблиц InnoDB самый удобный способ — mysqldump с записью координат прямо в дамп:

mysqldump -u root -p --all-databases --single-transaction \
  --master-data=2 --routines --events > dbdump.sql
  • --single-transaction снимает согласованный снимок InnoDB без длительной блокировки таблиц;
  • --master-data=2 записывает в дамп закомментированную команду CHANGE MASTER TO с именем файла журнала и позицией (с =1 команда будет выполнена при импорте).

Если в базе есть таблицы MyISAM, согласованность нужно обеспечить блокировкой. В одном сеансе выполняем:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Запоминаем значения File и Position — это координаты репликации. Сеанс не закрываем: при выходе блокировка снимается. В другом терминале снимаем дамп, после чего в первом сеансе выполняем:

UNLOCK TABLES;

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

В конфигурации реплики указываем свой уникальный server-id. Бинарный журнал на реплике нужен, если она сама будет источником для других серверов или вы планируете схему master-master:

[mysqld]
server-id = 2
log-bin   = mysql-bin
read_only = ON

read_only не даст приложениям случайно писать на реплику (пользователи с привилегией SUPER это ограничение обходят; для полной защиты есть super_read_only).

Чтобы репликация не стартовала до загрузки данных, запустите сервер с параметром skip-slave-start (в MySQL 8 — skip-replica-start), затем загрузите дамп:

mysql -u root -p < dbdump.sql

Подключаем реплику к ведущему серверу, подставив координаты из дампа или из SHOW MASTER STATUS:

CHANGE MASTER TO
  MASTER_HOST = '11.11.11.10',
  MASTER_PORT = 3306,
  MASTER_USER = 'replica',
  MASTER_PASSWORD = 'СложныйПароль',
  MASTER_LOG_FILE = 'mysql-bin.000002',
  MASTER_LOG_POS = 1151664,
  MASTER_CONNECT_RETRY = 10;

START SLAVE;

Проверка состояния

SHOW SLAVE STATUS\G

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

Поле Что означает Норма
Slave_IO_State Что сейчас делает поток чтения журнала Waiting for master to send event
Slave_IO_Running Читается ли журнал с ведущего сервера Yes
Slave_SQL_Running Применяются ли события на реплике Yes
Seconds_Behind_Master Отставание реплики Близко к 0
Last_IO_Error, Last_SQL_Error Текст последней ошибки Пусто

Простая проверка: создайте на ведущем сервере тестовую таблицу и убедитесь, что она появилась на реплике.

Схема master-master

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

# сервер 1
auto_increment_increment = 2
auto_increment_offset    = 1

# сервер 2
auto_increment_increment = 2
auto_increment_offset    = 2

Даже так одновременная запись одних и тех же строк на оба сервера приводит к конфликтам. На практике чаще пишут на один узел, а второй держат в горячем резерве.

Актуальное положение дел

В MySQL 8 терминология и синтаксис изменились. Начиная с 8.0.22–8.0.23 появились новые команды, а в MySQL 8.4 часть старых удалена, поэтому для новых серверов используйте такой вариант:

Было Стало
CHANGE MASTER TO MASTER_HOST=... CHANGE REPLICATION SOURCE TO SOURCE_HOST=...
START SLAVE / STOP SLAVE START REPLICA / STOP REPLICA
SHOW SLAVE STATUS SHOW REPLICA STATUS
SHOW MASTER STATUS SHOW BINARY LOG STATUS (MySQL 8.2+)
mysqldump --master-data mysqldump --source-data

Кроме того, в MySQL 8 пользователи по умолчанию создаются с плагином caching_sha2_password, и при подключении реплики без TLS нужно добавить GET_SOURCE_PUBLIC_KEY = 1 в CHANGE REPLICATION SOURCE TO или настроить шифрованное соединение. Для новых схем удобнее GTID: включите на всех серверах gtid_mode = ON и enforce_gtid_consistency = ON и подключайте реплику с SOURCE_AUTO_POSITION = 1 вместо имени файла и позиции.

В MariaDB сохранился классический синтаксис CHANGE MASTER TO, а GTID реализован иначе, чем в MySQL, — сверяйтесь с документацией вашей СУБД.

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

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

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

Почему Slave_SQL_Running стал No?

На реплике не удалось применить событие: например, строка уже существует или таблицы нет. Текст ошибки виден в поле Last_SQL_Error. Пропуск события возможен, но после этого данные стоит сверить, например утилитой pt-table-checksum.

Что лучше — репликация по позиции или по GTID?

Для новых установок предпочтителен GTID: реплика сама знает, какие транзакции уже применены, и переключение на другой источник проще. Позиционная схема остаётся рабочей и проще для понимания.

Можно ли реплицировать только одну базу?

Да, на реплике используют фильтры replicate-do-db или replicate-wild-do-table. С фильтрами по базе нужно быть осторожным при формате statement: они ориентируются на текущую базу сеанса, а не на базу в запросе.