Настройка репликации баз данных MySQL
Репликация MySQL поддерживает на одном или нескольких серверах точную копию базы с ведущего сервера. Разбираем, зачем она нужна, как настроить классическую асинхронную схему по позиции в бинарном журнале и чем отличается синтаксис в MySQL 8.
Зачем нужна репликация
Ведущий сервер (в старой терминологии master, в новой — source) записывает все изменения данных в бинарный журнал. Ведомые серверы (slave, replica) читают этот журнал и повторяют изменения у себя. По умолчанию репликация асинхронная: ведущий не ждёт подтверждения от реплик и не знает, успевают ли они за ним. Есть также полусинхронный режим (ведущий ждёт, пока хотя бы одна реплика получит транзакцию) и синхронные кластерные решения вроде Group Replication и Galera.
Типичные задачи:
- Распределение нагрузки. Запись идёт на ведущий сервер, тяжёлые запросы на чтение и отчёты — на реплики.
- Отказоустойчивость и обслуживание. Реплику можно быстро сделать основным сервером, на ней можно снимать резервные копии, не останавливая приложения.
- Географическое распределение. Копия данных может находиться в другом дата-центре.
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: они ориентируются на текущую базу сеанса, а не на базу в запросе.