Репликация Mysql (master-slave, master-master)

28 апреля, 2021 Рубрики: Linux, MySQL/MariaDB
Репликация MySQL

Всем привет. Давненько я не писал. Сегодня будет лонгрид. Некоторое время назад стояла задача развернуть несколько серверов Mysql в конфигурации с репликацией базы данных и описать весь процесс. Данная инсталляция легла в основу статьи. Статья написана на основе официальной документации Mysql. По большей части, является структурированным переводом. Любые дополнения приветствуются. Поехали.

Введение в репликацию Mysql

Репликация позволяет копировать данные Вашей базы данных с одного сервера MySQL (источника) на другой сервер MySQL (реплику). По умолчанию, в MySQL репликация асинхронная. Это позволяет не держать постоянное подключение к серверу-источнику. В зависимости от конфигурации, реплицировать можно как все базы данных, так и выбранные, либо даже просто таблицы БД.

MySQL поддерживает различные методы репликации:

  • Традиционный метод, который основан на репликации событий (events) из бинарного лога источника (binary log replication). В основном именно этот метод и будет рассмотрен в статье. (индикатор – опция --log-bin)
  • Более новый метод репликации – основан на репликации глобальных идентификаторов транзакций (т.н. global transaction identifiers или GTIDs). Данный метод не требует указания координат бинарного лог-файла мастер-сервера и в некотором смысле – проще. Использование GTIDs гарантирует консистентность между источником и репликой. (индикатор – опция gtid_mode)

MySQL умеет различные типы/схемы синхронизации между источником и репликой:

  • однонаправленная, асинхронная репликация, при которой один сервер является источником, а остальные – репликами. При этом, реплика тоже может быть источником. Именно эту схему репликации и рассмотрим (CHANGE MASTER TO...)
  • синхронная репликация – такая конфигурация используется при работе NDB Cluster.
  • полусинхронная (semisynchronous) репликация – commit транзакции будет подтвержден, только тогда, когда хотябы одна из реплик подтвердит, что событие получено и зафиксировано/логировано.
  • репликация с задержкой (delayed) – между данными источника и репликой будет задержка, заданная администратором. Обычно используется для тестирования или для защиты от ошибок на мастере.

Существуют три основных типа формата реплицируемых событий (переменная binlog_format):

  • Statement Based Replication (SBR), при которой реплицируется SQL запрос/SQL выражение
  • Row Based Replication (RBR), реплицируются только измененные строки БД
  • Mixed Based Replication (MBR) это комбинация SBR и RBR

Каждый из данных форматов имеет свои особенности, недостатки и достоинства. Эта тема для отдельной статьи.

Обзор репликации бинарного лога (Binary Log File Position Based Replication)

При данном методе репликации, экземпляр MySQL, как источник (он же source он же master) (на котором происходит изменение базы данных), записывает обновления/изменения БД (запросы) в бинарный лог. Обычно, этот лог размещен в том же каталоге, что и базы данных. А серверы-реплики (slave серверы) настроены таким образом, что читают бинарный лог с мастера и выполняют на своей базе данных те же самые запросы, которые выполнились на мастере.

Каждая реплика получает полную копию бинарного лога и именно реплика отвечает за то, чтобы выполнить все (или не все, а только не/отфильтрованные или только для заданных таблиц/БД) запросы из полученного лог-файла.

Slave-сервер понимает откуда начать читать лог, исходя из заданных координат при настройке репликации:

  • имя файла лога
  • позиция в этом файле

Т.к. реплика хранит/сдвигает эти координаты в процессе прохождения по логу, реплика в любой момент может быть отключена от мастера и подключена снова. При этом, обработка лога продолжится с места на котором произошла остановка.

Резюмируя вышесказанное, а именно, наличие координат и возможность фильтровать/задавать таблицы и БД для чтения лога, позволяет подключить различные slave серверы к одному master и обрабатывать различные части исходной БД. Важно учитывать, что разработчики и приложения должны учитывать данные особенности, чтобы обеспечить консистентность данных/индексов/пр.

Обзор репликации с Глобальными Идентификаторами транзакции (Global transaction identifier (GTID) Based Replication)

GTID – это уникальный идентификатор, который создается и ассоциируется с каждой завершенной (commit) транзакцией на master сервере. Эта транзакция уникальна для всех серверов – участников репликации. Таким образом, когда транзакция клиента выполнена (commit) и записана в бинарный лог на сервере-источнике, ей присваивается новый GTID. Каждый идентификатор GTID монотонно увеличивается, без промежутков в нумерации.

GTID представляет из себя пару координат, разделенных дветочием. Например: GTID = source_id:transaction_id или GTID = 3E00FA47-22CA-01E1-9E33-C80UU9429452:23 , где

  • source_id обычно, это значение server_uuid мастер-сервера
  • transaction_id – порядковый номер транзакции

стоит знать, что в новых версиях MySQL/MariaDB формат GTID изменился и упростился

GTID сохраняется в телице БД mysql.gtid_executed только когда значение параметра gtid_modeON или ON_PERMISSIVE. Хотя , при включенном режиме gtid_mode, на мастер-сервере журналирование должно быть включено обязательно для возможности реплицировать завершенные транзакции, реплики могут работать без включения бинарного лога. Выключить бинарный лог можно, запустив сервер с опциями --skip-log-bin и --log-slave-updates=OFF

Допущения в статье

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

  • в статье не рассматриваются вопросы установки сервера (обычно, это просто apt install mysql-server mysql-client)
  • правила netfilter/iptables так же не рассмтариваются в данной статье (используются стандартные порты mysql – tcp/3306)
  • версия СУБД и дистрибутив Linux, на котором развернут стенд: Ubuntu 20.04 / Mysql 8.0.23-0ubuntu0.20.04.1
  • TLS/SSL настройки так же исключены и для авторизации используется стандартный плагин аутентификации mysql_native_password
  • GTID репликация рассмотрена поверхностно (тема достойна отдельной статьи)

Топология репликации

master1(Master/Source/10.0.2.2)  - async ->  slave1(Slave/Replica/10.0.2.4)
       /|\   |          \                            |
        |    |          |                       Multi-source
        |    |          |                          async
     master-master      |                            |   
     ( circular )       |                           \|/  
        |    |           \- async -> slave2(Second Slave/Replica/10.0.2.5)
        |   \|/
master2(Second Master/Source/10.0.2.3)		

Настройка репликации бинарного лога (aka Master-slave)

1. Редактирование конфигов на мастер сервере и репликах

ОБЯЗАТЕЛЬНЫЕ НАСТРОЙКИ

server_id = 1

Этот параметр задает идентификатор сервера. Для всех серверов, которые включены в топологию репликации server_id должен быть установлен в диапазоне от 1 (по-умолчанию) до 4294967295 и он должен быть уникальным в рамках данной топологии. Значение параметра может быть изменено динамически командой mysql SET GLOBAL server_id = 2;. При этом, стоит знать, что если установить значение в ноль, то это отключит любые отношения репликации (отключит репликацию), изменение параметра в 0 требует перезагрузки/перезапуска сервера.

# на источнике
binlog_do_db    = db_name
# на получателях/репликах
replicate_do_db    = db_name

ОПЦИОНАЛЬНЫЕ НАСТРОЙКИ

Необходимо задать базы данных, которые будут вовлечены (или исключены) в/из репликации. При старте репликации, реплика проверяет, есть ли база данных в параметрах --replicate-do-db или --replicate-ignore-db. Такой же параметр есть для мастера --binlog-do-db и --binlog-ignore-db. Поведение опций – как черные и белые списки. Опция -do- заставит сервер создавать бинарный лог только для баз данных из этой опции, опция -ignore- заставит сервер создавать бинарный лог для всех баз, кроме тех что указаны в опции.

log_bin = base_name_of_log

Примечание: В разных источниках в интернете есть небольшая путаница с log-bin параметром. Где-то он указан без аргумента, где-то с аргументом. Это происходит из-за разной интерпретации данного аргумента разными версиями MySQL/MariaDB. Старые версии рассматривают его только как включение бинарного лога, в новых версиях – значение этого параметра воспринимается как базовое имя фала бинарного лога.

Эта опция обязательна на мастере. На самом деле, в старых версиях MySQL требовалось задавать/включать параметр log_bin, но с версии ~5.6 он включен по умолчанию. Для выключения этой опции, необходимо при запуске сервиса указать параметр --skip-log-bin или --disable-log-bin. Как только эта опция указана, все запросы к БД (которые вносят изменения) логируются в бинарный лог. Если задан параметр как в примере, все имена файлов будут иметь вид:

/var/lib/mysql/base_name_of_log.000001 
/var/lib/mysql/base_name_of_log.000002
 ...  

На самом деле, есть еще одна опция, которая влияет на работу источника:

  • для большей надежности и стабильности репликации БД, использующих движок InnoDB, рекомендуется включить опции innodb_flush_log_at_trx_commit=1 и sync_binlog=1 на мастер-сервере.
  • чтобы разрешить сетевые взаимодействия с сервером БД, необходимо убедиться, что выключена skip_networking и bind-address слушает правильный сетевой интерфейс.

2. Создание пользователя для репликации

Каждая реплика подключается к мастеру с помощью MySQL пользователя и пароля. Соответственно, на каждом мастере должен быть создан пользователь, который может использоваться репликой для подключения. Если учетная запись создается только для репликации, она должна иметь привелегии REPLICATION SLAVE. Например, для создания нового пользователя replication, который будет подключаться с любого хоста в домене k-max.name, необходимо выполнить следующее:

mysql> CREATE USER 'replication'@'%.k-max.name' IDENTIFIED IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.k-max.name';

Пример настройки сервера mysql master-slave

master1(Master/Source/10.0.2.2)

#/etc/mysql/my.cnf
server-id       = 2
log_bin
binlog_do_db    = db_name
#mysql CLI
mysql> CREATE USER 'replication'@'%.k-max.name' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.k-max.name';

slave1(Slave/Replica/10.0.2.4)

#/etc/mysql/my.cnf
server-id           = 4
log_bin
replicate-do-db	    = db_name

Примечание! Slave сервер не обязан иметь включенную опцию log_bin. Однако, ее включение на реплике означает, что бинарные логи помогут серверу более стабильно работать при бэкапах, восстанавливать репликацию после сбоев и участвовать в более сложной топологии репликации в качестве мастера.

3. Получение координат бинарного лога с Мастер сервера

Следущий шаг – это получить информацию о том, откуда (с какого места, с какой транзакции) слейв-серверу начинать репликацию бинарного лога.

  1. Если сервер-источник использует движок MyISAM и выполняет большое количество запросов на запись, желательно заблокировать эту активность (на самом деле, там немного все сложнее, да): mysql> FLUSH TABLES WITH READ LOCK;

  2. Получите текущих координат бинарного лога на мастер-сервере:

mysql> SHOW MASTER STATUS;                                                                        
+--------------------+----------+--------------+------------------+-------------------+   
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |   
+--------------------+----------+--------------+------------------+-------------------+   
| master1-bin.000001 |      156 | db_name      |                  |                   |   
+--------------------+----------+--------------+------------------+-------------------+   
1 row in set (0.00 sec)                                                                           

Пояснения к выводу команды: поле File показывает имя файла бинарного лога (обычно, в каталоге /var/lib/mysql/), Position показывает позицию в файле. Именно эти данные нужны для реплики.

Следующие шаги зависят от того, имеете ли Вы данные в БД на сервере-источнике:

  • Если сервер-источник имеет существующие базы данных с данными, необходимо эти данные перенести на реплику перед стартом процесса репликации.
  • Если исходный сервер чист, то можно приступить к запуску процесса репликации и разблокировать СУБД командой mysql> UNLOCK TABLES;.

4. Перенос данных MySQL с Мастер-сервера на реплику

Итак, если сервер-источник содержит данные, их можно перенести несколькими способами:

  • mysqldump (рекомендуемый метод для InnoDB)
  • перенос сырых данных (то есть просто скопировать каталог /var/lib/mysql). Даный способ требует дополнительных приседаний, они описаны в документации к технике "холодного" MySQL бэкапа. Способ несколько рискованней, но быстрее, чем mysqldump за счет отсутствия необходимости обновления индексов.
  • MySQL плагин для клонирования данных

Создание копии данных MySQL с помощью mysqldump

Следующая команда сделает дамп всех баз данных MySQL (--all-databases) в файл, который называется dbdump.db и включит в дамп текущие координаты (--master-data) бинарного лога. Если вы не используете опцию --master-data, необходимо заблокировать все таблицы БД вручную командой mysql> FLUSH TABLES WITH READ LOCK;.

master1> mysqldump --all-databases --master-data > /backup/path/dbdump.db

Необходимо держать в уме, что при использовании опции --all-databases, будет перенесена база данный mysql, что на реплике приведет к переносу всех данных о пользователях. Соответственно, все пользователи при импорте будут заменены.

Создание копии данных MySQL с помощью сырых файлов

Итак, данный способ должен учитывать соблюдение дополнительных требований:

  • Этот способ может работать некорректно, если мастер и слэйв имеют различные значения для настроек ft_stopword_file, ft_min_word_len, или ft_max_word_len и копируются таблицы с полнотекстовыми индексами.
  • необходимо использовать технологию холодного резервного копирования MySQL для получения консистентных бинарных файлов при использовании InnoDB: перед копированием, выполните т.н. slow shutdown
  • на реплике, каталог назначения $datadir/(обычно /var/lib/mysql/) должен быть предварительно очищен.

Следующие файлы не нужны для репликации и могут бы исключены:

  • файлы данных БД mysql (обычно лежат в /var/lib/mysql/mysql/)
  • файл master.info с данными к подключению к мастер-серверу (обычно /var/lib/mysql/master.info )
  • файлы бинарных логов с мастер-сервера (указаны в параметре log-bin, relay_log и relay_log_index)

Передача файлов на реплику

Тут можно задействовать любую любимую утилиту Linux nc, rsync, scp, etc…

root@master1:~# scp /dev/shm/dbdump.db root@slave1:/dev/shm/ 
[email protected]'s password:
dbdump.db                             100%   13MB 128.3MB/s   00:00
root@master1:~#

Настройка slave сервера MySQL, когда на источнике нет данных (чистый)

Повторюсь еще раз, этот метод приемлем только, если Вы устанавливаете репликацию с нового (чистого) MySQL сервера. И после запуска репликации Вы можете импортировать данные дампов в мастер. Таким образом, т.к. импорт будет осуществляться при установленной репликации, импортированные дампы будут скопированы (реплицированы) на реплику автоматически.

Список шагов следующий:

  • [x] мы уже настроили master
  • [x] мы уже настроили replica. И теперь нам нужно сделать следующее:
  • [-] запустить сервис mysql на реплике
  • [-] выполнить команду CHANGE REPLICATION SOURCE TO (mysql newer than 8.X) или CHANGE MASTER TO (older mysql) на реплике
  • [-] Если есть данные, которые можно импортировать на master сервере, то нужно их импортировать.

Поехали:

slave1(Slave/Replica/10.0.2.4)

mysql> CHANGE MASTER TO		
  MASTER_HOST='10.0.2.2',
  MASTER_USER='replication',	
  MASTER_PASSWORD='password',	
  FOR CHANNEL 'master1_channel';	

Указывать координаты, когда настраивается репликация с чистого мастера не нужно.

master1(Master/Source/10.0.2.2)

master1# mysql < fulldb.dump

Проверить статус репликации можно в выводе команд SHOW REPLICA STATUS\G (mysql новее 8.X) или SHOW SLAVE STATUS\G (старые версии mysql) в значениях SQL thread and replication I/O thread.

Настройка slave сервера MySQL, при существующих данных в БД

Список шагов следующий:

На что нужно обратить внимание:

Если сервер-источник имеет любые запланированные события в шедулере, необходимо убедиться, что эти события не буду запущены на реплике после импорта данных. Event Scheduler управляется переменной event_scheduler, которая по умолчанию – ON с версии MySQl 8.0. Тем самым, запланированные события запустятся при запуске реплики после импорта данных. Это вызовет ошибки. Чтобы отключить все события, необходимо перед импортом установить эту переменную в OFF \ DISABLED командой SET event_scheduler = 'OFF';.

Поехали:

slave1(Slave/Replica/10.0.2.4)

# bash
slave1> mysql < fulldb.dump
# mysql CLI
mysql> CHANGE MASTER TO		/* from MySQL 8.0.23 - CHANGE REPLICATION SOURCE TO */
  MASTER_HOST='10.0.2.2',      /* адрес мастер сервера */
  MASTER_USER='replication',	/* имя пользователя, которого мы создали */
  MASTER_PASSWORD='password',	/* пароль для пользователя */
  MASTER_PORT=3306,	/* опционально-только если он отличается от дефолтного */
  MASTER_LOG_FILE='master1-bin.000001',	/* имя бинарного лога, который мы получили с мастера */
  MASTER_LOG_POS=156,		/* позиция в бинарном логе */
  MASTER_CONNECT_RETRY=10		/* опционально */
  FOR CHANNEL 'master1_channel';		/* опционально - если реплика настраивается на несколько источников */
mysql> START SLAVE FOR CHANNEL 'master1_channel'; /* from MySQL 8.0.23 - START REPLICA */

START {REPLICA | SLAVE} не сообщит Вам о каких-либо проблемах. Необходимо проверять статус потоков подключения и репликации командой SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]. Как минимум, успешно-запущенная репликация должна показывать Replica_SQL_Running=Yes и Replica_IO_Running=Yes.

После проделанных шагов, slave сервер подключится к master серверу и начнет реплицировать любые обновления произошедшие на сервере-источнике с момента создания снапшота. ошибки репликации так же можно отслеживать в error.log MySQL сервера.

Снапшот, который мы получили с мастера может быть применен к любому количеству реплик. То есть можно настроить репликацию один-ко-многим с использованием единого дампа MySQl, просто повторив шаги данного раздела.

Добавление Slave сервера в существующее окружение репликации

Можно легко добавить новую реплику в существующую топологию репликации без остановки мастер-сервера. Для этого можно просто остановить существующую реплику, скопировать каталог данных MySQL на новый сервер и задать новый ID и UUID сервера.

Добавление нового Slave сервера в существующее окружение репликации Mysql по шагам:

ДействиеСуществующий Slave сервер (донор)Новый slave сервер (реципиент)
Проверить статус существующей репликации mysql> STOP SLAVE;
mysql> SHOW SLAVE STATUS\G
Остановка донораsystemctl stop mysql
Копирование данных на новый серверtar, cp/scp или rsync – ваши помощники
Запуск донораsystemctl start mysql
Изменение конфигурации получателя/etc/my.cnf задать новый server_id
Запуск реципиента без репликации/etc/my.cnf задать skip_slave_start
или при старте сервиса указать --skip-slave-start
Запуск процесса репликацииmysql> START {REPLICA | SLAVE}
Проверка статуса репликации на серверахmysql> SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]mysql> SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]

Multi-source репликация MySQL/MariaDB

При настройке репликации MySQL из нескольких источников (т.н. FAN-IN репликация), Slave сервер может быть настроен несколькими способами:

  1. Запуск нескольких инстансов реплик на разных портах и настройка каждого инстанса на отдельный мастер сервер.
  2. Запуск одной реплики и настройка отдельного канала репликации на отдельный сервер.

Я опишу второй путь, т.к. первый не отличается от настройки обычной репликации. Итак, вспомним, что в нашей топологии есть 3 ключевых сервера:

master1(Master/Source/10.0.2.2)  - async ->  slave1(Slave/Replica/10.0.2.4)
                      \                            |
                      |                       Multi-source
                      |                          async
                      |                            |   
                      |                           \|/  
                       \- async -> slave2(Second Slave/Replica/10.0.2.5)

Два сервера-источника: master1(Master/Source/10.0.2.2) и slave1(Slave/Replica/10.0.2.4) (это slave, на котором включен бинарный лог, так что он может быть и мастером) Один реплика-сервер:slave2(Second Slave/Replica/10.0.2.5). Реплика-сервер будет реплицировать две базы данных: test с master1 и test_s1 с slave1.

Итак, шаги настройки такие:

  • [x] необходимо, чтобы у нас были настроены каждый источник и подготовлена реплика
    • бинарный лог включен, заданы ID серверов (server_id, etc)
    • имеется пользователь с правами репликации
    • известны координаты бинарного лога с двух серверов-источников
  • [-] Осталось запустить команду CHANGE REPLICATION SOURCE TO (mysql новее 8.X) или CHANGE MASTER TO (старые mysql) на slave сервере для настройки репликации с master1(Master/Source/10.0.2.2)
  • [-] запустить команду CHANGE REPLICATION SOURCE TO (mysql новее 8.X) или CHANGE MASTER TO (старые mysql) на slave сервере для настройки репликации с slave1(Slave/Replica/10.0.2.4)
  • [-] Запустить процессы/потоки репликации на slave2(Second Slave/Replica/10.0.2.5) с помощью вызова START {REPLICA | SLAVE}.
  • [-] (опционально) Установить фильтр для того, чтобы только заданные базы данных реплицировались со строго заданных серверов-источников test с master1 и test_s1 с slave1.

Поехали:

slave2(Second Slave/Replica/10.0.2.5)

mysql> CHANGE MASTER TO		
  MASTER_HOST='10.0.2.2',
  MASTER_USER='replication',	
  MASTER_PASSWORD='password',	
  MASTER_PORT=3306,	
  MASTER_LOG_FILE='master1-bin.000014',	
  MASTER_LOG_POS=156,		
  MASTER_CONNECT_RETRY=10		
  FOR CHANNEL 'master1_channel';		/* Это ключевой параметр, который говорит серверу использовать отдельный поток */
mysql> CHANGE MASTER TO		
  MASTER_HOST='10.0.2.4',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',	
  MASTER_PORT=3306,
  MASTER_LOG_FILE='slave1-bin.000014',	
  MASTER_LOG_POS=156,		
  MASTER_CONNECT_RETRY=10		
  FOR CHANNEL 'slave2_channel';		/* Это ключевой параметр, который говорит серверу использовать отдельный поток */
mysql> START SLAVE FOR CHANNEL 'master1_channel';
mysql> START SLAVE FOR CHANNEL 'slave2_channel';
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('test.%') FOR CHANNEL 'master1_channel';
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('test_s1.%') FOR CHANNEL 'slave2_channel';

Репликация мастер-мастер (или круговая репликация)

Настройка репликации MySQL в режиме мастер-мастер подразумевает то, что в случае отказа одного из серверов – другие участники репликации прозрачно подхватят работу. То есть не нужно будет делать ручных шагов для перевода сервера роли Slave-сервера в Master (что вызовет перерыв сервиса). Круговая репликация (или circular replication) MySQL может быть использована для масштабирования MySQL нодов, доступных на запись (изменение базы данных). !Но есть нюансы. В данной конфигурации, MySQL не выполняет разрешение конфликтов, то есть нет реализованного протокола, который отслеживает блокировки таблиц\баз между нодами. То есть, например, если мы используем внешние ключи (FOREIGN KEY) в нашей базе данных INSERT может завершится ошибкой, если ссылка на внешний ключ не успела реплицироваться.

Мастер-мастер репликация Mysql
Master-master репликация между двумя нодами
Master-master репликация между четырьмя нодами

Настройка MySQL сервера для мастер-мастер репликации на самом деле – это просто настройка мастер-слэйв репликации много раз от одной ноды к другой по кругу. Самый простой пример такой репликации – репликация между двумя нодами. Репликация настраивается в две стороны: от первой ноде ко второй и от второй ноды – к первой. Рассмотрим пример настройки репликации между master1 и master2.

master1(Master/Source/10.0.2.2) 
       /|\   |         
        |    |        
        |    |          
     master-master     
     ( circular )       
        |    |          
        |   \|/
master2(Second Master/Source/10.0.2.3)		

master1(Master/Source/10.0.2.2)

#/etc/mysql/my.cnf
server-id       = 2
log_bin
binlog_do_db    = db_name
auto_increment_increment = 2
auto_increment_offset = 1
# systemctl restart mysql
#mysql CLI
mysql> stop slave;
mysql> SHOW MASTER STATUS;                                                                        
+--------------------+----------+--------------+------------------+-------------------+   
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |   
+--------------------+----------+--------------+------------------+-------------------+   
| master1-bin.000001 |      400 |              |                  |                   |   
+--------------------+----------+--------------+------------------+-------------------+   
1 row in set (0.00 sec)                                                                           
                                                                                                  
mysql>
mysql> CREATE USER 'replication'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
mysql> flush privileges;
mysql> CHANGE MASTER TO	
  MASTER_HOST='10.0.2.3',
  MASTER_USER='repication',	
  MASTER_PASSWORD='password',	
  MASTER_LOG_FILE='master2-bin.000001',	
  MASTER_LOG_POS=300,		
  FOR CHANNEL 'master2_channel';	
  
mysql> start slave;

master2(Master/Source/10.0.2.3)

#/etc/mysql/my.cnf
server-id       = 3
log_bin
binlog_do_db    = db_name
auto_increment_increment = 2
auto_increment_offset = 2
# systemctl restart mysql
#mysql CLI
mysql> SHOW MASTER STATUS;                                                                        
+--------------------+----------+--------------+------------------+-------------------+   
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |   
+--------------------+----------+--------------+------------------+-------------------+   
| master2-bin.000001 |      300 |              |                  |                   |   
+--------------------+----------+--------------+------------------+-------------------+   
1 row in set (0.00 sec)                                                                           
mysql> CREATE USER 'replication'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
mysql> flush privileges;
mysql> CHANGE MASTER TO	
  MASTER_HOST='10.0.2.2',
  MASTER_USER='replication',	
  MASTER_PASSWORD='password',	
  MASTER_LOG_FILE='master1-bin.000001',	
  MASTER_LOG_POS=400,		
  FOR CHANNEL 'master1_channel';
mysql> start slave;

Важный нюанс, на который стоит обратить внимание – это параметры auto_increment_increment и auto_increment_offset которые помогают защитить от перехлеста автоматически возрастающих индексов.

Переключение сервера MySQL из режима репликации в отдельный сервер (отключение репликации MySQL)

Если установить server ID в ноль, то бинарный лог продолжит работать, но в режиме server_id = 0 сервер MySQL отбрасывает любые подключения от реплик. так же, реплика с server_id = 0 не пытается установить соединения с мастером. Стоит помнить, что этот параметр может быть изменен на работающем сервере, но изменения будут приняты только после рестарта MySQL!

Переключение мастер-сервера при отказах мастер-сервера (переключение на новый источник репликации)

Ок. Давайте посмотрим на исходную топологию репликации:

Топология репликации MySQL до сбоя

Предположим, что мастер сервер умер и недоступен. Давайте сделаем нашу реплику1 новым мастером и получим следующую топологию репликации:

Сбой мастер-сервера

Для назначение нового мастер-сервера, необходимо выбрать реплику, которая станет новым мастером. В нашем случае – это Replica1. А оставшимся (2 и 3) необходимо просто запустить команды CHANGE REPLICATION SOURCE TO (from MySQL 8.0.23) или CHANGE MASTER TO с необходимыми параметрами. Все, реплика просто начнет читать бинарный лог с нового источника, выполнять запросы на своих базах данных и не будет проверять, совместима ли база данных на источнике.

Новый мастер сервер (который Replica1) желательно запустить с параметром --log-slave-updates=OFF или изменить его онлайн.

Так же, необходимо не забыть про клиентов – они должны быть перенаправлены на нового мастера, который теперь Replica1. Часто, для этого используют такое решения, как демон keepalived.

Итак, давайте пройдемся по шагам:

Действие Replica1 Replica2,3
Убедиться, что все реплики завершили все запросы, которые получили со старого мастера STOP REPLICA | SLAVE IO_THREAD STOP REPLICA | SLAVE IO_THREAD
Проверить вывод команд на наличие сообщения Has read all relay log SHOW PROCESSLIST SHOW PROCESSLIST
Настроить новый сервер быть мастером STOP REPLICA | SLAVE and RESET MASTER
Направить реплики на нового мастера Replica1 STOP REPLICA | SLAVE and CHANGE REPLICATION SOURCE TO SOURCE_HOST='Replica1'
нет необходимости указывать координаты, т.к. реплики помнят, где закончилось чтение.
Запустить потоки репликации `START REPLICA

Более подробно – тут

Рекомендации по резервному копированию в режиме MySQL репликации

Резервное копирование и восстановление в режиме репликации использует те же принципы, что и отдельный (standalone) MySQL сервер. Резервное копирование может быть логическим (например, с помощью утилиты mysqldump) или физическим (копирование каталога данных /var/lib/mysql). mysqldump имеет опцию --single-transaction, которая создает образ и позволяет избежать блокирования работы других клиентов.

Копию базы данных возможно получать как с мастер-сервера, так и с реплики. Поэтому очень часто репликацию используют для аккумулирования всех баз на одном slave сервере и в едином месте делают логические копии и архивацию.

Есть так же рекомендация для бОльшей надежности и консистентности резервной копии – использовать переменную read_only и выполнять резервное копирование в следующей последовательности:

  1. Перевести сервер в режим только-чтение.
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;
  1. Выполнить логическое резервное копирование.
  2. Вернуть сервер в нормальный режим.
mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

подробнее

Диагностика репликации MySQL

Полезные команды для траблшутинга MySQL

Типичные ошибки репликации (и способы устранения)

Ошибка Last_IO_Error equal MySQL server UUIDs

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Ошибка может появляться, если вы перенесли каталог данных MySQL на новый сервер и запустили сервер со старым файлом auto.cnf. Для устранения ошибки – необходимо удалить файл и при следующем запуске сервер сгенерирует новый UUID.

cat /var/lib/mysql/auto.cnf 
[auto]
 server-uuid=fea4e713-9552-11e6-a093-0341a44379d3

Ошибка MY-010584 – MY-002061

2021-03-18T09:20:56.956824Z 5 [ERROR] [MY-010584] [Repl] Slave I/O for channel ‘master1_channel’: error reconnecting to master ‘[email protected]:3306’ – retry-time: 10 retries: 6185 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection. Error_code: MY-002061

а так же

Last_IO_Error: error connecting to master ‘[email protected]:24011’ – retry-time: 60 retries: 51

Ошибка возникает при подключении реплики к мастер-серверу более старой/новой версии. Для устранения – необходимо корректно настроить шифрование или использовать mysql_native_password в качестве плагина при создании пользователя репликации. подробнее тут

Ошибка MY-010604

2021-03-18T11:38:25.048055Z 0 [Warning] [MY-010604] [Repl] Neither –relay-log nor –relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-log=slave1-relay-bin’ to avoid this problem.

Ошибка возникает при переносе (импорте) базы данных с мастер-сервера на реплику. Для устранения – необходимо задать новое имя файла в параметрах relay-log и relay-log-index.

Основные шаги диагностики

(c)

  1. Проверить содержимое лога (/var/log/mysql/error.log)
  2. Включен ли бинарный лог на источнике? Проверить с помощью команды SHOW MASTER STATUS. Обычно лог включен по умолчанию. Команда покажет позицию – ноль, если лог не включен. Проверить, что сервер запущен без опции --skip-log-bin.
  3. Переменная server_id должна быть установлена и уникальна для каждого члена репликации.
  4. Запущена ли реплика? Проверить командой START {REPLICA | SLAVE}. В выводе должно содержаться Replica_IO_Running и Replica_SQL_Running со значением Yes. Если нет, то проверить следующее: не запущен ли сервер с опцией --skip-slave-start или установлена переменная skip_slave_start, которые запрещают запуск потоков репликации.
  5. Если потоки запущены, проверить установлено ли соединение с мастером командой SHOW PROCESSLIST, найти статус потоков I/O и SQL в выводе. Если статус Connecting to master, Проверить следующее:
    • Пароли и привилегии пользователя репликации на сервере-источнике (можно попробовать временно настроить репликацию от root)
    • Проверить порт и IP. (попробовать подключиться mysql клиентом по адресу и порту в выводе команды SHOW PROCESSLIST)
    • Проверить системную переменную skip_networking на источнике и реплике. Если переменная установлена – выключить.
    • проверить правила iptables и межсетевой экран.
  6. Если реплика была запущена и работала, но потом упала, то скорей всего проблема в SQL запросе. Запрос, который корректно может выполнится на источнике, но завершается ошибкой на реплике. Такое может произойти, например, если на реплику импортирован некорректный образ базы данных и он не консистентен с источником.

Полезные ссылки

  • https://dba.stackexchange.com/questions/11487/mysql-master-master-replication-auto-flush-old-logs/11488#11488
  • https://icicimov.github.io/blog/high-availability/database/MySQL-Circular-Replication/
  • https://spec-zone.ru/RU/mysql/5.6/
  • https://dev.mysql.com/doc/refman/8.0/en/replication.html
  • https://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html – SHOW SLAVE STATUS
  • https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html – SHOW REPLICA STATUS
  • https://dev.mysql.com/doc/refman/8.0/en/change-replication-source-to.html – CHANGE REPLICATION SOURCE TO
  • https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html – CHANGE MASTER TO
  • https://dev.mysql.com/doc/refman/8.0/en/start-replica.html – START REPLICA | SLAVE
  • https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html – SHOW {REPLICA | SLAVE} STATUS
  • https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html – SHOW [FULL] PROCESSLIST
  • https://dev.mysql.com/doc/refman/8.0/en/show-slave-hosts.html – SHOW SLAVE HOSTS | SHOW REPLICAS
  • https://dev.mysql.com/doc/refman/8.0/en/reset-master.html – RESET MASTER [TO binary_log_file_index_number]



Теги: ,

4 комментария к “Репликация Mysql (master-slave, master-master)”

  1. Сергей
    25 мая, 2021 at 00:45
    1

    Это мощно…
    У тебя восхитительный стиль подачи инфы.

    • 26 мая, 2021 at 20:51
      2

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

  2. Caisy
    21 января, 2022 at 14:59
    3

    Спасибо за статью. Пару вопросов :
    1. Если сейчас стоит мастер-мастер бинлог (МарияДБ 10.6.5), То для переключения на реплкацию по GTID необходимы дополнительные действия и можно ли это сделать на ходу, не прерывая процесс пользователей ?
    2. Галера кластер с МарияБД 10.5.2 – так же можно ли на ходу включить репликацию по GTID ?
    Заранее Спасибо за ответ

  3. Дмитрий
    18 июля, 2022 at 23:54
    4

    Спасибо большое за статью. Достаточно подробно объясняете как что работает и зачем нужны конкретные параметры. Будем тестить)

Написать комментарий