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_mode – ON или 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)		

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

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. Старые версии рассматривают его только как включение бинарного лога, в новых версиях – значение этого параметра воспринимается как базовое имя фала бинарного лога.

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, при существующих данных в БД

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

• [x] мы уже настроили master
• [x] мы уже настроили replica.
• [x] мы уже получили координаты бинарного лога с мастер-сервера И теперь нам нужно сделать следующее:
• [-] запустить сервис mysql на реплике без репликации (указать опцию --skip-slave-start)
• [-] импортировать данные на slave сервер
• [-] выполнить команду CHANGE REPLICATION SOURCE TO (mysql newer than 8.X) или CHANGE MASTER TO (older mysql) на реплике
• [-] Запустить поток репликации командой START {REPLICA | SLAVE}.

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

Если сервер-источник имеет любые запланированные события в шедулере, необходимо убедиться, что эти события не буду запущены на реплике после импорта данных. 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 по шагам:

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 сервера для мастер-мастер репликации на самом деле – это просто настройка мастер-слэйв репликации много раз от одной ноды к другой по кругу. Самый простой пример такой репликации – репликация между двумя нодами. Репликация настраивается в две стороны: от первой ноде ко второй и от второй ноды – к первой. Рассмотрим пример настройки репликации между 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 которые помогают защитить от перехлеста автоматически возрастающих индексов.

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

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

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

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

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

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

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

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

Рекомендации по резервному копированию в режиме 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;

2. Выполнить логическое резервное копирование.
3. Вернуть сервер в нормальный режим.

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

подробнее

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

• SHOW FULL PROCESSLIST\G
• SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]
• SHOW SLAVE HOSTS
• Вызов RESET MASTER на реплике позволит очистить историю GTID

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

Ошибка 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 запросе. Запрос, который корректно может выполнится на источнике, но завершается ошибкой на реплике. Такое может произойти, например, если на реплику импортирован некорректный образ базы данных и он не консистентен с источником.