Mysql репликация: Репликация MySQL в виде Master/Slave

Содержание

Репликация MySQL в виде Master/Slave

Что такое репликация MySQL?

Репликация MySQL — это процесс, который позволяет копировать данные с одного сервера на другой (поддерживать несколько копий данных MySQL) путем их автоматического копирования из базы данных master (ведущей) в slave (ведомую).

Я в своей статье «Репликация MySQL в виде Master/Slave» расскажу как можно установить данную репликацию mysql в виде master/slave, в котором БД master передает информацию на сервер с БД slave.

Имеется:

192.168.13.128- Master
192.168.13.130- Slave

Репликация MySQL в виде Master/Slave

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

Если используете deb’s ОС (Debian/Ubuntu):

# apt-get install mysql-server mysql-client

Если используете rpm ОС (CentOS/Fedora/RedHat):

# yum install mysql-server mysql-client

Вот некоторые полезные статьи:

Настройка безопасности MYSQL в Unix/Linux

Сменить кодировку в Mysql/MariaDB в Unix/Linux

Сколько MySQL соединений в Unix/Linux

Проверить тип баз данных MySQL для хранения данных в Linux

Тюнинг MySQL в Unix/Linux

Просмотр привилегий пользователя MySQL

Настройка Master

Открываем конфигурационный файл на master-сервере:

# vim /etc/mysql/my. cnf

Сейчас я его немного видоизменю и для самого начала заменим ИП адрес который слушает mysql (он связывает сервер с локальным хостом):

[...]
bind-address = 127.0.0.1
[...]

Замените стандартный IP-адрес IP-адресом сервера.

[...]
bind-address = 192.168.13.128
[...]

PS: если он не прописан, то добавьте строку что выше себе в конфигурационный файл.

Идем далее, нужно прописать директиву «server-id»:

[...]
server-id = 1
[...]

Она может принимать любое значение, но как по мне — проще начинать с 1. Данная величина должна быть уникальной и не совпадать ни с одним из другим «server-id» в этой группе репликации.

Находим» log_bin» — эта переменная будет нести в себе некоторые детали о самой репликации ( сервер slave будет копировать все изменения, зарегистрированные в указанном лог файле):

[...]
log_bin = /var/log/mysql/mysql-bin.log
[...]

PS: Возможно, у вас не хватит прав на запись этого файла в указанной директории, по этому, измените путь или добавьте запись на папку.

И напоследок, стоит задать БД, которую необходимо будет копировать на slave-сервер( допускается добавлять более 1 БД, но для этого стоит прописывать данную строку, но с нужно базой), напимер:

[...]
binlog_do_db = magento_db
[...]

И так, все было сделано и мой конфиг выглядит следующим образом:

# cat /etc/my.cnf

Перезапускаем master с MySQL:

# service mysql restart

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

# mysql -u root -p

Создаем пользователя, который будет передавать данные с мастера на слейв:

> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'Slave_password666';

Сбросим все привелегии (чтобы все заработало):

> FLUSH PRIVILEGES;

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

И так, у нас настроен сервер с мастером и уже работает некоторая БД (у меня это magento_db), выберем ее для использования:

> USE magento_db;

Поле чего, я заблокирую данную базу для того, чтобы не записывались никакие данные в нее:

> FLUSH TABLES WITH READ LOCK;

выполняем проверку :

> SHOW MASTER STATUS;

И видим, что мастер уже заработал:

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

Т.к я заблокировал все изменения, то не будет записываться ничего в эту БД и я могу создать дамп этой БД для переноса ее на слейв. Я выполню экспорт БД с помощью mysqldump:

# mysqldump -u root -p --opt magento_db > magento_db.sql

После того как создался дам, нужно вернуть все на свои места и разблокировать БД:

> USE magento_db;
> UNLOCK TABLES;
> QUIT;

Вуоля, master готов к использованию!

Настройка slave

Подключаемся на созданный slave сервер и создаем БД:

> CREATE DATABASE magento_db;
> EXIT;

Выполняем импорт базы которую мы создали с мастера:

# mysql -u root -p magento_db < /path_to_DB/magento_db.sql

Открываем конфигурационный файл и внесем некоторые изменения:

# vim /etc/mysql/my.cnf

Тоже прописываем ID сервера (значение должно быть не такое как у мастера):

[. ..]
server-id = 2
[...]

И прописываем следующие значения:

[...]
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = magento_db
[...]

Выполняем перезапуск MySQL со слейвом:

# service mysqld restart

Осталось активировать репликацию в MySQL. Открываем оболочку MySQL и вводим:

> CHANGE MASTER TO MASTER_HOST='192.168.13.128',MASTER_USER='slave_user', MASTER_PASSWORD='Slave_password666', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;

Пояснения данной команды:

Она определит текущий slave-сервер;
Предоставит серверу нужные данные для входа;
Назначит slave-серверу, выполнять репликацию;
Задаст лог для master-сервера и позицию, с которой нужно начинать репликацию.

Запускаем slave-сервер следующей командой:

> START SLAVE;

Проверяем что слейв запустился и работает должным образом:

> SHOW SLAVE STATUS\G

Иногда возникают проблемы со связью и их можно решить:

> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Вот некоторые полезные статьи:

Duplicate entry/Error_code: 1062 в MySQL репликации (Master-Slave)

Перезапуск MySQL репликации

А на этом, у меня все и тема «Репликация MySQL в виде Master/Slave» завершена.

Репликация Mysql (master-slave, master-master) | Блог любителя экспериментов

Всем привет. Давненько я не писал. Сегодня будет лонгрид. Некоторое время назад стояла задача развернуть несколько серверов 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_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)

Настройка репликации бинарного лога (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. Получение координат бинарного лога с Мастер сервера

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

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

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 по шагам:

Действие	Существующий 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 сервер может быть настроен несколькими способами:

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

Я опишу второй путь, т. к. первый не отличается от настройки обычной репликации. Итак, вспомним, что в нашей топологии есть 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 может завершится ошибкой, если ссылка на внешний ключ не успела реплицироваться.

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

Полезные команды для траблшутинга 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)

Проверить содержимое лога (/var/log/mysql/error.log)
Включен ли бинарный лог на источнике? Проверить с помощью команды SHOW MASTER STATUS. Обычно лог включен по умолчанию. Команда покажет позицию — ноль, если лог не включен. Проверить, что сервер запущен без опции --skip-log-bin.
Переменная server_id должна быть установлена и уникальна для каждого члена репликации.
Запущена ли реплика? Проверить командой START {REPLICA | SLAVE}. В выводе должно содержаться Replica_IO_Running и Replica_SQL_Running со значением Yes. Если нет, то проверить следующее: не запущен ли сервер с опцией --skip-slave-start или установлена переменная skip_slave_start, которые запрещают запуск потоков репликации.
Если потоки запущены, проверить установлено ли соединение с мастером командой SHOW PROCESSLIST, найти статус потоков I/O и SQL в выводе. Если статус Connecting to master, Проверить следующее:
- Пароли и привилегии пользователя репликации на сервере-источнике (можно попробовать временно настроить репликацию от root)
- Проверить порт и IP. (попробовать подключиться mysql клиентом по адресу и порту в выводе команды SHOW PROCESSLIST)
- Проверить системную переменную skip_networking на источнике и реплике. Если переменная установлена — выключить.
- проверить правила iptables и межсетевой экран.
Если реплика была запущена и работала, но потом упала, то скорей всего проблема в 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]

Другие материалы в категории MySQL/MariaDB

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

Теги: MySQL, настройка

Настройка репликации master-slave в MySQL

Репликация — механизм синхронизации содержимого нескольких копий объекта. Под этим процессом понимается копирование данных из одного источника на множество других и наоборот.

Обозначения:

master — главный сервер, данные которого необходимо дублировать;
replica — починенный сервер, хранящий копию данных главного

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

На главном сервере отредактируем файл файл my.cnf, в секцию mysqld добавить строки:


server-id = [master server id]
log-bin = mysql-bin
log-bin-index = mysql-bin.index
log-error = mysql-bin.err
relay-log = relay-bin
relay-log-info-file = relay-bin.info
relay-log-index = relay-bin.index
expire_logs_days=7
binlog-do-db = [dbname]

Где

[master server id] — уникальный идентификатор сервера MySQL, число в диапазоне 2^(0-31)
[dbname] — имя базы, информация о которой будет писаться в бинарный журнал, если баз несколько, то для каждой необходима отдельная строка с параметром binlog_do_db

На подчиненном отредактируем файл файл my. cnf, в секцию mysqld добавить строки:


server-id = [slave server id]
master-host = master
master-user = replication
master-password = password
master-port = 3306
relay-log = relay-bin
relay-log-info-file = relay-log.info
relay-log-index = relay-log.index
replicate-do-db = [dbname]

На главном сервере добавим пользователя replication с правами на репликацию данных:


GRAANT REPLICATION SLAVE ON *.* TO 'replication'@'replica' IDENTIFIED BY 'password'

Заблокируем реплицируемые базы на главном сервере от изменения данных, программно или с помощью функционала MySQL:


mysql@master> FLUSH TABLES WITH READ LOCK;
mysql@master> SET GLOBAL read_only = ON;

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


mysql@master> SET GLOBAL read_only = OFF;

Сделаем резервные копии всех баз данных на главном сервере (или тех которые нам необходимы):


root@master# tar -czf mysqldir. tar.gz /var/lib/mysql/[dbname]

или средствами утилиты mysqldump:


root@master# mysqldump -u root -p --lock-all-tables [dbname] > dbdump.sql

Остановим оба сервера (в отдельных случаях можно обойтись и без этого):


root@master# mysqlamdin -u root -p shutdown
root@replica# mysqlamdin -u root -p shutdown

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


root@replica# cd /var/lib/mysql
root@replica# tar -xzf mysqldir.tar.gz

или функционала mysql, тогда mysql на подчиненном сервере не было необходимости останавливать:


root@replica# mysql -u root -p [dbname] < dbdump.sql

Запустим mysql на главном сервере (а затем — на подчиненном, если это необходимо):


root@master# /etc/init.d/mysql start
root@replica# /etc/init.d/mysql start

Проверим работы главного и подчиненного серверов:


mysql@replica> start slave;
mysql@replica> SHOW SLAVE STATUS\G
mysql@master> SHOW MASTER STATUS\G

На подчиненном сервере проверить логи в файле master. info, там должны содержаться запросы на изменение данных в базе. Так этот файл бинарный необходимо сначала преобразовать его в текстовый формат:


root@replica# mysqlbinlog master.info > master_info.sql

При возникновении ошибок, можно использовать команды:


mysql@replica> stop slave;
mysql@replica> RESET SLAVE;
mysql@master> RESET MASTER;

и повторить все действия начиная с блокировки баз данных.

Для горячего добавления серверов репликации можно использовать синтаксис:


mysql@replica> SHOW SLAVE STATUS\G
mysql@master> SHOW MASTER STATUS\G
mysql@replica-2> CHANGE MASTER TO MASTER_HOST = "master", MASTER_USER ="replication ", 
    MASTER_PASSWORD = "password ", MASTER_LOG_FILE ="mysql-bin.000004 ", MASTER_LOG_POS = 155;
mysql@replica-2> START SLAVE;

Информация из статусов покажет позицию и имя текущего файла лога.

В случае асинхронной репликации обновление одной реплики распространяется на другие спустя некоторое время, а не в той же транзакции. Таким образом, при асинхронной репликации вводится задержка, или время ожидания, в течение которого отдельные реплики могут быть фактически неидентичными. Но у данного вида репликации есть и положительные моменты: главному серверу не надо беспокоится о синхронизации данных, можно блокировать базу (например, для создания резервной копии) на подчиненной машине, без проблем для пользователей.

Список использованных источников

Habrahabr.ru — Основы репликации в MySQL (http://habrahabr.ru/blogs/mysql/56702/)
Википедия (http://ru.wikipedia.org/wiki/Репликация_(вычислительная_техника))

При полном или частичном использовании любых материалов с сайта вы обязаны явным образом указывать ссылку на handyhost.ru в качестве источника.

Устранение задержки репликации в Базе данных Azure для MySQL

Статья
08/06/2022
Чтение занимает 11 мин

ОБЛАСТЬ ПРИМЕНЕНИЯ: отдельный сервер Базы данных Azure для MySQL Гибкий сервер Базы данных Azure для MySQL

Компонент Реплика чтения позволяет реплицировать данные с сервера службы «База данных Azure для MySQL» на сервер реплик только для чтения. Вы можете масштабировать рабочие нагрузки горизонтально, переправляя запросы на чтение и отчетность из приложения на серверы реплики. Эта конфигурация снижает нагрузку на исходный сервер. Она также повышает общую производительность и уменьшает задержку в приложении по мере его масштабирования.

Реплики чтения обновляются асинхронно с помощью технологии репликации на основе позиции файла собственного двоичного журнала (binlog) ядра MySQL. Дополнительные сведения см. в статье о конфигурации репликации на основе позиции файла binlog MySQL.

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

Задержки сети.
Объем транзакций на исходном сервере.
Уровень вычислений исходного сервера и сервера вторичной реплики чтения.
Запросы, выполняющиеся на исходном сервере и сервере-получателе.

В этой статье объясняются принципы устранения задержки репликации в Базе данных Azure для MySQL. Вы также узнаете о некоторых распространенных причинах увеличения задержки репликации на серверах реплик.

Примечание

Эта статья содержит упоминания термина slave (ведомый) . Корпорация Майкрософт больше не использует его. Когда этот термин будет удален из программного обеспечения, мы удалим его из статьи.

Основные понятия репликации

Когда включен двоичный журнал, сервер-источник записывает в него зафиксированные транзакции. Двоичный журнал используется для репликации. Он включен по умолчанию для всех новых подготовленных серверов, поддерживающих хранилище объемом до 16 ТБ. На каждом сервере реплики выполняются два потока. Один поток — это поток операций ввода-вывода, другой — поток SQL.

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

Мониторинг задержки репликации

Служба «База данных Azure для MySQL» также предоставляет в Azure Monitor метрику задержки репликации в секундах. Эта метрика доступна только на серверах реплики чтения. Она вычисляется по метрике seconds_behind_master, доступной в MySQL.

Чтобы понять причину увеличения задержки репликации, подключитесь к серверу реплики с помощью MySQL Workbench или Azure Cloud Shell. Затем выполните следующую команду.

Примечание

В коде замените значения из примеров на имя сервера реплики и имя пользователя администратора. Для имени администратора требуется указать значение @\<servername> службы «База данных Azure для MySQL».

mysql --host=myreplicademoserver.mysql.database.azure.com --user=myadmin@mydemoserver -p

Ниже показано, как выглядит этот процесс в терминале Cloud Shell.

Requesting a Cloud Shell.Succeeded.
Connecting terminal...
Welcome to Azure Cloud Shell
Type "az" to use Azure CLI
Type "help" to learn about Cloud Shell
user@Azure:~$mysql -h myreplicademoserver.mysql.database.azure.com -u myadmin@mydemoserver -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64796
Server version: 5.6.42.0 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

В том же окне терминала Cloud Shell выполните следующую команду:

mysql> SHOW SLAVE STATUS;

Вот типичный пример выходных данных:

Выходные данные содержат много информации. Как правило, необходимо обратить внимание только на строки, описанные в следующей таблице.

Метрика	Описание
Slave_IO_State	Представляет текущее состояние потока ввода-вывода. Как правило, при синхронизации исходного (главного) сервера указано состояние «Ожидание отправки события главным сервером». Такое состояние, как, например «Подключение к главному серверу», указывает на то, что реплика потеряла соединение с исходным сервером. Убедитесь, что исходный сервер запущен, или проверьте, не блокирует ли брандмауэр подключение.
Master_Log_File	Представляет двоичный файл журнала, в который записывает данные исходный сервер.
Read_Master_Log_Pos	Указывает, куда исходный сервер записывает данные в двоичный файл журнала.
Relay_Master_Log_File	Представляет двоичный файл журнала, который считывается сервером-репликой с исходного сервера.
Slave_IO_Running	Указывает, запущен ли поток ввода-вывода. Это значение должно быть равно `Yes`. Если значение равно `NO`, то репликация, скорее всего, нарушена.
Slave_SQL_Running	Указывает, запущен ли поток SQL. Это значение должно быть равно `Yes`. Если значение равно `NO`, то репликация, скорее всего, нарушена.
Exec_Master_Log_Pos	Указывает позицию в файле Relay_Master_Log_File, которую использует реплика. При наличии задержки эта позиция должна быть меньше значения Read_Master_Log_Pos.
Relay_Log_Space	Указывает общий суммарный размер всех существующих файлов журналов ретрансляции. Максимально допустимый размер можно проверить путем запроса `SHOW GLOBAL VARIABLES`, например `relay_log_space_limit`.
Seconds_Behind_Master	Отображает задержку репликации в секундах.
Last_IO_Errno	Отображает код ошибки потока ввода-вывода при его наличии. Дополнительные сведения об этих кодах см. в справочнике по сообщениям об ошибках сервера MySQL.
Last_IO_Error	Отображает сообщение об ошибке потока ввода-вывода при его наличии.
Last_SQL_Errno	Отображает код ошибки потока SQL при его наличии. Дополнительные сведения об этих кодах см. в справочнике по сообщениям об ошибках сервера MySQL.
Last_SQL_Error	Отображает сообщение об ошибке потока SQL при его наличии.
Slave_SQL_Running_State	Указывает текущее состояние потока SQL. В этом состоянии нормальным является вариант `System lock`. Также нормальным является состояние `Waiting for dependent transaction to commit`. Это состояние обозначает, что реплика ожидает, пока исходный сервер не обновит зафиксированные транзакции.

Если Slave_IO_Running имеет значение Yes, а Slave_SQL_Running — значение Yes, то репликация работает нормально.

Затем проверьте Last_IO_Errno, Last_IO_Error, Last_SQL_Errno и Last_SQL_Error. В этих полях отображается номер ошибки и сообщение об ошибке для самой последней ошибки, вызвавшей зависание потока SQL. Номер ошибки 0 и пустое сообщение означают отсутствие ошибки. Изучите любое ненулевое значение ошибки, проверив ее код в справочнике по сообщению об ошибке сервера MySQL.

Распространенные сценарии длительной задержки репликации

В следующих разделах рассматриваются ситуации, в которых часто встречается высокая задержка репликации.

Задержка в сети или высокая загрузка ЦП на исходном сервере

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

Slave_IO_State: Waiting for master to send event
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller than Master_Log_File, e.g. mysql-bin.00010

В этом случае поток ввода-вывода запущен и находится в состоянии ожидания на исходном сервере. Исходный сервер уже записал данные в двоичный файл журнала под номером 20. Реплика получила данные только до файла 10. Основными факторами высокой задержки репликации в этом сценарии являются скорость сетевого подключения или высокая загрузка ЦП на исходном сервере.

В Azure задержка сети в регионе обычно измеряется в миллисекундах. Между регионами задержка находится в диапазоне от миллисекунд до секунд.

В большинстве случаев задержка соединения между потоками ввода-вывода и исходным сервером вызвана высокой загрузкой ЦП на исходном сервере. Потоки ввода-вывода обрабатываются медленно. Эту проблему можно обнаружить с помощью Azure Monitor, проверив использование ЦП и количество одновременных подключений на исходном сервере.

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

Объемные пакеты транзакций на исходном сервере

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

Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller then Master_Log_File, e.g. mysql-bin.00010

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

Задержка не вызвана скоростью сетевого подключения. Реплика пытается нагнать процесс. Но размер обновленного двоичного журнала превышает верхний предел на размер журнала ретрансляции.

Чтобы устранить эту проблему, включите журнал медленных запросов (запросов с задержкой) на исходном сервере. Журналы медленных запросов помогают обнаружить долго выполняющиеся транзакции на исходном сервере. Затем скорректируйте обнаруженные запросы, чтобы уменьшить задержку на сервере.

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

Медленная работа на сервере реплики

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

Slave_IO_State: Waiting for master to send event
Master_Log_File: The binary log file sequence equals to Relay_Master_Log_File, e.g. mysql-bin.000191
Read_Master_Log_Pos: The position of master server written to the above file is larger than Relay_Log_Pos, e.g. 103978138
Relay_Master_Log_File: mysql-bin.000191
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: The position of slave reads from master binary log file is smaller than Read_Master_Log_Pos, e.g. 13468882
Seconds_Behind_Master: There is latency and the value here is greater than 0

В этом сценарии в выходных данных видно, что поток ввода-вывода и поток SQL работают нормально. Реплика считывает тот же двоичный файл журнала, который ведется исходным сервером. Однако определенная задержка на сервере реплики отражает те же операции, что и исходный сервер.

В следующих разделах описаны распространенные причины такой задержки.

В таблице нет первичного ключа или уникального ключа

База данных Azure для MySQL использует репликацию на основе строк. Исходный сервер записывает события в двоичный журнал, отражая изменения в отдельных строках таблиц. Затем поток SQL реплицирует эти изменения в соответствующие строки таблицы на сервере реплики. Если в таблице отсутствует первичный ключ или уникальный ключ, то поток SQL просматривает все строки в целевой таблице, чтобы применить изменения. Это сканирование может вызвать задержку репликации.

В MySQL первичный ключ — это связанный индекс, который обеспечивает высокую производительность запросов, поскольку не может содержать значения NULL. При использовании подсистемы хранилища InnoDB данные таблицы физически упорядочены для быстрого поиска и сортировки на основе первичного ключа.

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

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

select tab.table_schema as database_name, tab.table_name 
from information_schema.tables tab left join 
information_schema.table_constraints tco 
on tab.table_schema = tco.table_schema 
and tab.table_name = tco.table_name 
and tco.constraint_type = 'PRIMARY KEY' 
where tco.constraint_type is null 
and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys') 
and tab.table_type = 'BASE TABLE' 
order by tab.table_schema, tab.table_name;

Долго выполняющиеся запросы на сервере реплики

Рабочая нагрузка на сервере реплики может вести к отставанию потока SQL от потока ввода-вывода. Одной из распространенных причин высокой задержки репликации являются долго выполняющиеся запросы на сервере реплики. Чтобы устранить эту проблему, включите журнал запросов с задержкой (медленных запросов) на сервере реплики.

Медленные запросы могут увеличивать потребление ресурсов или замедлять работу сервера, и реплике не удается нагнать исходный сервер. В этом случае скорректируйте медленные запросы. Более быстрые запросы предотвращают блокирование потока SQL и значительно улучшают задержку репликации.

Запросы DDL на исходном сервере

На исходном сервере команда языка описания данных DDL, например ALTER TABLE, иногда занимает много времени. Во время выполнения команды DDL на исходном сервере могут параллельно выполняться тысячи других запросов.

При репликации DDL для обеспечения согласованности базы данных ядро MySQL выполняет DDL в одном потоке репликации. Во время выполнения этой задачи все остальные реплицируемые запросы блокируются и вынуждены дожидаться завершения операции DDL на сервере реплики. Эта задержка возникает даже в операциях DDL с подключением к сети. Операции DDL увеличивают задержку репликации.

Если на исходном сервере включен журнал запросов с задержкой (медленных запросов), вы можете обнаружить эту проблему, проверив, не выполнялась на исходном сервере команда DDL. Вы можете задействовать алгоритм INPLACE для изменения таблицы (ALTER TABLE) путем удаления, переименования и создания индекса. Возможно, вам потребуется скопировать данные таблицы и перестроить ее.

Как правило, алгоритм INPLACE поддерживает параллельное выполнение DML. Но при подготовке и запуске операции можно временно выполнить монопольную блокировку метаданных таблицы. Таким образом, для инструкции CREATE INDEX можно с помощью предложений ALGORITHM и LOCK повлиять на способ копирования таблицы и уровень параллелизма операций чтения и записи. Чтобы предотвратить выполнение операций DML, также можно добавить индекс FULLTEXT или SPATIAL.

В следующем примере создается индекс с помощью предложений ALGORITHM и LOCK.

ALTER TABLE table_name ADD INDEX index_name (column), ALGORITHM=INPLACE, LOCK=NONE;

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

Снижение характеристик сервера реплики

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

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

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

Улучшение задержки репликации путем настройки параметров исходного сервера

По умолчанию в Базе данных Azure для MySQL репликация оптимизирована для работы с параллельными потоками в репликах. Когда рабочие нагрузки с высокой степенью параллелизма на исходном сервере приводят к отставанию сервера реплики, улучшить задержку репликации можно путем настройки параметра binlog_group_commit_sync_delay на исходном сервере.

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

Попробуйте установить для параметра binlog_group_commit_sync_delay значение в районе 1000. Затем понаблюдайте за задержкой репликации. Будьте внимательны при настройке этого параметра и используйте его только для рабочих нагрузок с высоким уровнем параллелизма.

Важно!

На сервере реплики для параметра binlog_group_commit_sync_delay рекомендуется задавать значение 0. В отличие от исходного сервера, сервер реплики не отличается высоким параллелизмом, и увеличение значения binlog_group_commit_sync_delay на нем может опосредованно привести к увеличению задержки репликации.

Для рабочих нагрузок с низким уровнем параллелизма, включающих множество одноэлементных транзакций, параметр binlog_group_commit_sync_delay может увеличить задержку. Задержка может увеличиться, так как поток ввода-вывода ожидает обновления больших двоичных файлов журнала, даже если фиксируется лишь несколько транзакций.

Дальнейшие действия

Ознакомьтесь с обзором репликации файла binlog MySQL.

HighLoad Junior Блог

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

В конце будет один самый главный слайд со всякими ключевыми словами. Если вы забудете, то в доке искать вот эти-эти-эти ключевики нет никакой человеческой возможности. А потом прочитал документацию, вынес из доклада 3 ключевика и их прочитал.

Не будет обычного введения типа: «Давайте посмотрим, как настроить репликацию. У нас вообще ничего нет, давайте ее включим, для этого притушим сервер, настроим конфиг тут, перетащим MySQL дамп, не забудем записать позицию в binlog’e, или включим ключик у MySQL дампа, который это делает». Я не уверен, что все это умеют и знают, потому что как бы зачем? Но подобные туториалы под названием «Как взлететь за одну минуту?» их в интернетах много, хотя технически в интернетах вообще все есть, включая исходники.

Начнем с установления терминов. Как оно бывает? Как репликация устроена вообще в любой системе, которая существует на планете? Есть несколько осей измерения, несколько основных вариаций.

Можно сделать синхронную репликацию на физическом уровне, хотя это геморрой страшный, при этом по Push-модели и master-master это можно скомбинировать. Если вдуматься, то это ад и холокост из выбранных решений. Основное, что интересно здесь – это варианты синхронизации той или иной транзакции, т.е. что оно нам гарантирует. Остальное в обзорном виде – как устроена репликация.

Репликация бывает, во-первых. Это не миф. Во-вторых, она бывает синхронная, асинхронная, полусинхронная. На логическом уровне работает с конкретными записями на физической репликации конкретных файлов. Внутри логической – конкретно в применении к базе данных, любой базе данных, не обязательно SQL. На логическом уровне могут возникать еще какие-то интересные градации. И по модели распространения, кто кому изменения пихает push/pull, и по количеству точек записи, либо master-slave, либо master-master.

Репликация – это процесс копирования одной и той же базы данных… Подчеркиваю опять – не обязательно SQL. Когда MongoDB свою копию шарда кладет на соседнюю машину – это тоже репликация, которая делает из одной копии n копий.

Важно понимать, что когда у нас есть много копий, то мы доблестно отмасштабировали чтение из базы данных. Если у нас была база данных, в которую можно впихнуть на одном максимально толстом доступном сервере 1000 записей в секунду (записей в смысле не строк в таблице, а в смысле операций вставки, возможно более сложных, чем одна строка таблицы), если бы мы успевали впихнуть на этом сервере 1000 вставок в секунду и сделать 3000 чтений в секунду, 3000 селектиков, и внезапно сделали 4 реплики, то селектиков у нас будет 12000 в прыжке (на самом деле, скорее всего, чуть меньше), а вставок у нас будет меньше исходной 1 тысячи, потому что в случае с master-slave репликацией у нас одна точка входа, одна точка записи. 3 дополнительные реплики, которые мы подложили, они работают на чтение, они не работают на запись сами по себе.

Репликация отмасштабирует только чтение, записи она не отмасштабирует. Для того чтобы отмасштабировались еще и записи, надо плясать отдельные танцы – шардировать-шардировать-шардировать. Какой для этого механизм представляет MySQL из коробки, я не знаю, т.к. привык делать это вручную.

Важный момент про репликацию – помните, что writes’ы просто так не отмасштабировать, а чтение – можно.

Про синхронизацию. Самый важный момент всех этих возможных осей – как бывает? Бывает синхронная репликация, асинхронная и, типа, полусинхронная.

Синхронная означает, если у вас commit прошел, то база вам обещает, что не просто commit прошел, а он еще и прошел на удаленных нодах. Т.е. мы commit сделали, подключились к одной конкретной ноде, к одному конкретному серверу, эти данные в идеале, чтобы обеспечить и масштабируемость по чтениям и доступность данных, должны улететь на соседние машины, и транзакции там должны зафиксироваться. Это синхронная репликация.

Понятное дело, что на каждый commit дожидаться ответа от удаленных нод, о том, что они и прочитали данные, и успешно накатили транзакцию – это довольно долго, во-первых, и геморройно, во-вторых. Во-первых, ноды могут конкретно лагать, и транзакция, которая на master’е уже пробежала, она теоретически может на каком-нибудь slave’e проиграться сильно потом. Говорят, бывает, что репликация лагает неделями (я такого не видел). Причем не специально, т.е. это удобно для целей backup’а – ты DROP TABLE сделал на основном боевом сервере, и у тебя есть 2 недели, чтобы передумать. Но синхронная репликация – это медленно, счастья от нее особого нет.

Не очень интересен вариант под названием «асинхронная репликация», потому что гарантий нет, совсем никаких. Но ок, транзакция зафиксировалась локально, это тебе что-нибудь гарантирует? Правильный ответ – нет. Потому что возможно, что все остальные реплики лежат дохлые, и те данные, которые вы вкатили на master, они у вас никуда не резервируются. Если у вас master просто упадет, в смысле SQL-сервер упадет, то все хорошо условно, он эти изменения с Write-Ahead Log’a проиграет, но если у вас внезапно демоническая сила подожжет сервер, и он сгорит, прям весь, то данные, которые вы якобы зафиксировали, они на самом деле зафиксировались на одной машине.

Синхронность – хорошо с точки зрения надежности, но медленно. Асинхронность полная – зафиксировали локально транзакцию и не ждем вообще удаленные реплики – очевидно быстро (ничем же не отличается от локального commit’a), но никаких гарантий по надежности.

Возникает промежуточный вариант – полусинхронная репликация – это когда commit возвращает успех, в тот момент, когда локально транзакция уже зафиксирована, селекты к master’у уже начнут возвращать новое состояние, новый баланс, и удаленные сервера уже скачали эти данные, уже скачали эту транзакцию, но возможно еще не успели накатить, то ли накатят через 2 секунды, то ли через 2 недели – как повезет. Это самый важный момент, с которым на практике придется сталкиваться, и делать выбор, настраивать и т.д.

По-моему, все эти 3 варианта есть в MySQL’e, с тем единственным исключением, что синхронная репликация там есть не в традиционных, привычных всем движках и т.д. Слышал я одну легенду об InnoDB кластере, что некая адская машинка, пришедшая к нам из телекома, с чудовищными требованиями по доступности все держит в памяти, доступность 17 девяток, синхронная мультимастер-репликация, все работает… Одна проблема – живьем ее никто не видел. Людей с прямым опытом работы с InnoDB кластером найти не удается.

Какая еще теоретически бывает репликация?

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

Предположим, что у вас есть мощный ZFS, который на уровне файловой системы размазывает все данные, которые база успевает писать на диск, на много-много серверов и хорошо справляется с этой задачей. Чисто теоретически – вот вам репликация, с тем отличием от случая, который хочется, что она вам обеспечивает клевый горячий backup, распределенный на несколько серверов, но не обеспечивает горячую реплику, которую можно еще для чтения использовать. Технически, наверное, можно и так. Но я, правда, не слышал, чтобы это было сильно популярным. На логическом уровне проще с одной стороны, внутри базы реализовать на физическом вообще смысла нет, и становится возможным делать не просто backup, который куда-то отгружается, но и реплики, с которых еще и читать можно.

Модели рассылки изменений – кто кому звонит – push/pull. Pull – это если реплики тащат изменения с master’а. Push – это если master вдобавок к задаче обработки транзакций еще и вынужден slave’ы менеджить. Теоретически где-то бывает push, но я совершенно забыл, в каких эзотерических системах используется этот странный метод push – «Когда мне транзацию впихивают, я и так работаю за всех, я вам изменения всем рассылаю, еще и я ответственный. Да вы охренели, дорогие реплики, тяните изменения сами, кто из вас там сдохнет, кто выживет – вообще не моя проблема, я сервер, я занят, я хочу фыр-фыр-фыр, а мне надо 30 тыс. транзакций в секунду commit’ить».

Есть важная дифференциация – master-slave, master-master. Традиционно встроенная в MySQL репликация – master-slave. Т.е. есть одна ведущая нода, которая принимает все изменения, все остальные – ведомые реплики, в которые, технически, к несчастью, вы тоже можете сделать какое-то изменение. Т.е. у вас стоит master с основной таблицей и основным балансом пользователей, условно говоря, в вашем маленьком предбанкротном банке, у вас есть много реплик, потому что не клево хранить баланс. .. С точки зрения креативной бухгалтерии и уклонения от уголовного преследования, наоборот, клево хранить все на сервере, который стоит в «газели», законекчен по wi-fi, и как только приходит следственный комитет, «газель» уезжает. А что ты с ней сделаешь? Это частная собственность ее досматривать нельзя. С этой точки зрения клево иметь один master, а в других, более приличных случаях хочется иметь еще несколько реплик.

Master-slave модель – есть основные master-копии, есть несколько (теоретически read-only) реплик, которые только вытаскивают изменения с основной master-копии. К несчастью, в том числе в MySQL, можно на эти ведомые реплики, все равно, просунуть какое-то изменение прямо туда – на master’e у нас балансы меняются, все это разлетается на N реплик, а тут кто-то на одной реплике берет и что-то меняет, это может привести к бедам.

Еще бывает master-master модель, когда изменения можно пихать во много серверов разом, и они промеж себя этими изменениями обмениваются и пытаются удерживать консистентность данных. Понятно, когда у вас есть master-master репликация, то начинается поистине интересный креатив – вот, у вас стоит один сервер, условно говоря, в Бангкоке, второй – в Йоханнесбурге. Там свет долетает по прямой за вполне измеримое время, а ворона вообще никогда не долетит. И транзакция, которая прошла в Бангкоке, хорошо, если она не трогает никаких данных, по сравнению с транзакцией, которая в этот момент проходит в Йоханнесбурге. Но если они обе пытаются менять один и тот же баланс, то начинается масса интересных вещей: а в какое время была зафиксирована транзакция здесь, а в какое там, а на сколько расходятся часы, а какие данные они меняют, а в каком порядке их следовало применять?

Может возникнуть какой-то конфликт, и система может умудриться его автоматически разрешить, а может не смочь. Но в этом есть определенное счастье. Иногда оно помогает скейлить записи, а иногда – нет. Если у вас в конечном итоге все записи должны прилететь на один сервер, как вам поможет тот факт, что у вас 5 точек входа? Они же все равно сходятся в одной конкретной базе, и ее производительность будет bottleneck’ом. Читать в любой схеме можно с любой точки. Что у вас один master и 5 реплик, что у вас 6 master’ов, которые друг с другом успевают всяким меняться – читать можно со всех точек.

Важный момент – в каком формате передаем изменения между нодами? Вариантов не много.

Можно передавать сами запросы исходные. Пользователь заслал какой-то запрос, мы складываем его в протокол, в log, и его передаем на реплику. Либо можно вместо этого передавать измененные строчки. И то, и другое по-своему плохо. Можно смешивать, но это, по-моему, хуже всего.

Как сделано в MySQL? Правильный ответ: «А хрен его знает, товарищ прапорщик». Потому что в MySQL в зависимости от версии есть разные capabilities у сервера, т.е. версия 5.6. может полусинхронную репликацию делать, т.е. ты можешь заслать commit и молись… Еще получить гарантии, что этот commit не только прошел, а что реплики его хотя бы вычитали/скачали у тебя. Кроме того, дело осложняется тем, что фичу-то в каждой отдельной версии и сделали, но она не обязательно включена, а если включена, то может работать не так, как вы ожидаете.

Как устроено все на самом деле? Асинхронная репликация или синхронная, что это значит? А значит это следующее.

Master работает как обычно, вдобавок из-за репликации пишет специальный протокол под названием binary log (=binlog), который потом будет рассылать по сетке. А что мы пишем в этот binlog – то ли сами строчки, то ли стейтменты – это как раз про binary log. Т.е. дополнительная нагрузка на master из-за репликации, как вещи в себе, довольно не высокая. Введем дополнительный log, ну и хрен с ним, мы по сетке его рассылаем – это тоже довольно ненапряжно.

Единственный подлый момент, связанный с архитектурой MySQL – это то, что нет никакого MySQL на самом деле – это все миф и иллюзия, т. е. есть MySQL общий слой, который оптимайзер, репликации и т.д. и т.п. А есть физический уровень хранения данных. И вот архитектура MySQL’евская со втыкаемыми движками хранения pluggable storage engines, она приводит к тому, что у InnoDB свой Write-Ahead Log, т.е. ты суешь запись в MySQL – она прилетает в конкретный движок, в конкретный storage engine, если MyISAM, то все просто – записали в файл, потеряли, ништяк. Если в InnoDB, то все сложнее, пишем в памяти, пишем Write-Ahead Log, иногда сваливаем странички на диск. А за счет того, что Write-Ahead Log у каждого движка либо есть, либо нет, либо хрен его знает, то для репликации MySQL должен ввести свой собственный binlog. Получается overhead, который, если не аккуратно настроить и мало железа, может оказаться довольно нехилый.

В самом деле, то у нас storage engine, то у нас движок хранения делает работу – хранит данные, фиксирует транзакции и для этого ведет свой протокол. Было бы клево, если бы этот протокол Write-Ahead Log от InnoDB , либо его аналоги в других движках можно было использовать для репликации, но нет – нельзя. Это расплата за то, что в MySQL можно втыкать сторонние движки.

Получается, что слишком много записей на каждую запись и вот появляется дополнительная. Увы и ах. Но на самом деле дополнительная линейная запись в binlog – это не так страшно, особенно если вы ее избавите от всяких ненужных sync’ов, вынесете на отдельный диск и т.д.

На slave’e все интересней, потому что slave’у недостаточно файл прочитать, ему еще поработать надо для того, чтобы свое состояние догнать до того, которое наблюдается на master’е. Он может обрабатывать чтение, для этого и придуман. К несчастью, он может обрабатывать вставки тоже. Внутри для репликации здесь основные два концепта – это thread, который качает данные с master’а (или разных master’ов) бинарные логи, которые лежат на master’e и складывает их локально. Буквально: читает файл по сетке и локально пишет копию этого файла.

Там мог бы быть SCP, Rsync или что угодно еще, команда CP через NFS. По факту оно реализовано парой-тройкой SQL statement’ов, которые slave дает на master’e, после этого ему в socket начинают литься бинарные данные, которые он пишет на диск – тупо копирование файла по сетке. Поэтому проблемы под названием «у меня что-то не то с копированием – binlog с master’a не успевает в relay log локальный скопироваться» возникают редко. Задача прочитать файл по сетке не сложная, ее уже лет 60 решают и научились решать. Значительно смешнее задача проигрывания этих binlog’ов. Нам прилетают какие-то изменения с master’a, мы должны применять эти транзакции. Фарш начинается здесь. Нужно отслеживать позиции везде, т.е. где мы сейчас стоим на master’e, куда мы сейчас записали все это локально (локально называется relay log.

Путь записи. Как оно работает в случае приятном и простом, когда у вас один сервер и больше ничего?

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

В момент, когда появляется репликация, путь может быть более извилист. Запись не только попадает в измененное в таблицу на master’e, она еще попадает в binary log на master’e, что не очень напряжно, после этого оно попадает в relay log на slave’е, что опять не очень напряжно, тупо копирование файлов. После этого начинается самый интересный момент с проигрыванием. Основной bottleneck и тормоза – они здесь. Они в попадании из relay log’a в таблицу на slave’е. Отсюда оно уже из slave’а доступно читателям, и здесь начинается счастье, т.е. читатели могут обращаться не только к master’у, но и к slave’у, чтение уверенно растет.

Что за данные конкретно лежат в binary log? Как бывает, как хорошо, как плохо, и как посмотреть?

Зависит, к несчастью, и гибко настраивается.

Все любят системы, где есть одна кнопка «сделать хорошо», нажал ее – и тебе хорошо. Но такие системы научились строить на улице красных фонарей, но не в IT, поэтому в IT вечно какие-нибудь дуделки, вертелки и настройки.

Настройки – как раз те binlog_format, про которые я говорил, – SBR/RBR/mixed. Либо фиксируем сами запросы в том виде, в котором они прилетели от клиента, либо смотрим, например, что мы обновляем всю таблицу с пользователями, а пользователей у нас в таблице 10 млн, внезапно маленький запрос на 50 байт приводит к обновлению 10 млн строк. И что, нам обновленные 10 млн строк писать в этот log? Это плохо. В этом случае сильно эффективней писать само выражение. Наверное, из-за этой логики в основной массе версий MySQL оно дефолтиться в statement based репликацию, т. е. в тупую реплицирует сами SQL-запросы. Оно сохраняет и реплицирует как есть, без всякой дополнительной магии. Думаете, оно хотя бы стадию парсинга проходит, какое-то бинарное дерево пишет, хоть как-то облегчает работу slave’ам? Ни хрена, прям как есть. Запрос прилетел, в таком виде и будет записан. Как максимум, кавычки вокруг названий таблиц поставят на всякий случай и все.

Возникает один неприятный момент с этой statement based репликацией – в момент, когда у нас на master’e возникают некие недетерминированные функции, которые даже на первый взгляд могут казаться детерминированными. Что происходит, когда мы реплицируем этот запрос? Мы отключаем на master’e всех людей, которые не логинились в последние 100 дней по часам master’a. А реплика может проиграть этот запрос через 3 дня с одной стороны, плюс на ней сисадмин мог случайно забыть перенести time-зону с другой стороны, плюс на ней общий дрифт часов мог устроить расхождение еще на несколько дней со стороны третьей. Т.е. для краткости клево записать в протокол этот запрос и его проиграть на реплике. На практике у вас обязательно разойдутся данные внутри этого запроса. Обязательно, даже если вы не заметите. Понятное дело, если гранулярность достаточно высокая – вам повезло, значения now разошлись, но это не привело к расхождению в выбранных юзерах, то все хорошо.

Во всех остальных случаях – все плохо. В этих случаях нужно писать измененные строчки в запрос. Идея-то хорошая, но приходит пушной зверек «опа горностай».

Значит время несинхронно, расхождение и еще масса осложнений, которые делают запрос недетерминированным… Нам надо писать строчки. Строчки писать больно, по той причине, что много данных, по сравнению с запросом, но в некоторые моменты необходимо. И внезапно приходим к ситуации, как в том анекдоте:

– Чувак, как ты со своей бородой спишь?

– Сука, я из-за тебя вообще спать не могу, под одеяло ее суну – неудобно, над одеялом суну – тоже неудобно.

Statement based репликация, когда у нас SQL запросы, вроде бы хорошо, но и плохо одновременно. А RBR – row based репликация, когда сами копии строк складываются в протокол – тоже хорошо, но плохо.

Есть вариант «давайте получим лучшее из обоих миров» и будем смешивать, называется Mixed binlog format.

Там по умолчанию он дефолтится в тот же самый statement format, т.е. в протокол складируются все запросы, которые вы шарахали к базе данных, за исключением селектов, которые ничего не меняли, но иногда при определенных условиях доку откровенно страшно читать, чувствуется, что там вот такой вот список условий, что невозможно его запомнить, в принципе. Иногда оно само переключается на row based репликацию. Теоретически оно должно работать хорошо. Т.е. по умолчанию логировать стейтменты, минимизировать тем самым трафик между master’ом и репликой, но иногда переключаться на row based. Cмущает, что mixed format товарищи из MySQL включили дефолтным в небольшом окне версий, что-то типа с 5.1.12 по 5.1.28, а потом на всякий случай выключили и вернулись обратно к statement based репликации. А потом вернулись опять к row based репликации – не могут никак определиться.

Как посмотреть, что там за данные лежат? Вдруг когда-нибудь понадобиться.

Там довольно нехитрый бинарный формат, который, если хочется, руками довольно просто разбирать, но даже этого делать не надо – есть утилита mysqlbinlog.

Утилита mysqlbinlog в момент, когда оно туда пишет сами SQL-запросы, показывает прямо SQL-запросы, весь фарш бинарный, который кодирует все эти серверы ID, позиции в логе и прочие интересные штуки, она форматирует в текстовый вид и показывает запрос. Без ключика «–v», для road based репликации вам покажут вот такой вот клевый дамп, как на слайде. Спасибо, что based 64, спасибо, что не бинарь. В случае с ключиком «-v» оно его немного разбирает и имена колонок не восстанавливает, но показывает значения, которые в этом бинарном дампе закодированы – это для row based репликации.

Для statement based репликации все просто – утилита просто лог смотрит.

Дальше, что происходит внутри? Мы поняли, что происходит на master’e, когда мы включили репликацию. У нас завелся binary log, в него что-то началось писаться, писаться в разных форматах, с разными проблемами, и именно эти данные будут улетать на slave, на реплики.

Что происходит на slave’е? На slave’е, к несчастью, начинается все самое интересное.

Ок, есть спецтред, который качает binary log, локально его пишет в relay log, после чего другой спецтред из этого relay log свежепрочитанные данные проигрывает. Естественно на slave’е другое имя файла, другие позиции в relay log’ax, по сравнению с binlog’ами. И это до определенного момента важно, потому что в MySQL вплоть до версии 5.5 включительно чувствуется матерая ошибка молодости – нет никакого механизма идентифицировать транзакцию, которая где-то там случилась, кроме как по имени файла и позиции в этом файле. Транзакция значит, что ваш баланс в банке решительно поменялся и идентифицируется вплоть по версии 5.5 буквально парой. На master’e лежит файл binlog.013, в нем в позиции 571 лежит эта транзакция – это единственный метод опознать ее уникальность. Если кто-то слил вместе файлы на сервере, или переименовал, или они выпуржились, или т.д., то «все пропало, шеф», «атас, менты, смывай наколки».

Когда работает – все хорошо, проблема в том, что когда ломается – позиции слетают, файлы переименовываются, и прочие интересные вещи происходят, то этот механизм идентификации транзакции по имени файла на другом компьютере… Его нет локально уже, у нас локально на реплике relay log, binlog’a, возможно, вообще нет. Позиций на каждую транзакцию не нахранишься. Это одна большая беда.

Вторая – не то, чтобы ошибка молодости, а традиционно от лени, сначала сделали, чтобы хоть как-то работало, а потом много лет делаем, чтоб заработало нормально – это тот факт, что slave однопоточный в обоих местах. Понятное дело, что thread, который тащит данные по сети, он может очень много данных прокачать по сети, и nginx тому примером. Ты иди загрузи сервер с nginx’ом, который статику раздает на 100% CPU – удачи в бизнесе. А тот факт, что thread, который проигрывает свежескачанные транзакции однопоточные, вот это уже убийство, из-за этого возникает репликационный лаг и прочие неприятные эффекты. У вас master всей своей 32-х ядерной мощью, и умудряясь использовать хотя бы 12 из этих 32-х ядер на полезную работу, доблестно commit-ит транзакцию, а после этого репликой в один thread… Как в известном мультике: «Он пока на своих 4-х ногах на своей лошади 1-2-3-4, а ты на 2-х раз-два, раз-два». Вот вплоть до 5.5 включительно slave’ы «раз-два, раз-два», и производительности этого потока может хватить, может не хватить.

Вплоть до 5.5 включительно емкость slave’а невозможно нарастить добавлением ядер – не успевал и не будет успевать, только гнать по частоте и азотную установку для охлаждения. Это грустно, но, к несчастью, в MySQL тоже не дураки сидят (просто тормоза), и поэтому начинают насущные проблемы менять и фиксить.

С версии 5.6 наконец-то началась борьба с обоими дебильными проблемами – сделали мощную фичу под названием Global TID – глобальные идентификаторы транзакций, которые ликвидируют беду под названием «все, что происходит на master’e, мы опознаем по имени файла и смещению в этом файле». Не дай бог что-то с файлами произойдет.

Теперь к каждой транзакции добавляется некий уникальный номер, и у slave’а появляется целый новый ряд проблем, под названием «вдруг на нем кто-то херанул запись ошибочную, а его запромотили до master’a и тогда по GTID’ам мы же поймем, что запись немедленно должна разлететься на все остальные сервера» . Т.е. ты на slave’е убил какую-нибудь таблицу случайно, не нужна была, и на этом конкретном slave’е оно проканало, потом slave’ запромотили до master’a, в этот момент транзакция проиграется на всех остальных нодах кластера. Будет счастье.

Но, тем не менее, фича приятная и полезная, потому что, когда сервер может автоматически идентифицировать транзакции по некоему уникальному номеру, и тебе не надо смотреть с лупой на бинарный лог и вычислять, какая позиция в relay log’e локальном соответствует какой позиции в исходном binary log’e, который уже стерли – это приятно. Когда этим может заниматься автоматика, а с GTID’ами она может, это вот хорошо.

Кроме того, сделали «многопоточные» slave’ы в 5.6.5, т.е. если ваши изменения, которые льются в одну схему всех остальных баз данных, в MySQL известную как database, то ничего не поможет. Но если у вас несколько разных databases, и между ними несвязанные транзакции, то 5. 6 умеет их наливать в несколько потоков. Подчеркиваю, изменениям в одну таблицу оно не поможет никак. GTID’ы немного сажают производительность, но зато потом сильно экономят количество седых волос при восстановлениях при сбоях.

5.7, по-моему, в beta’e многолетней, вроде бы еще не generally available. Там борьба продолжается.

В 5.6 вернули функциональность под названием «group commit binlog», которая позволяет делать поистине многопоточную репликацию. В чем счастье? Раньше, до группового commit’a, до того, как его заставили снова работать, транзакции в binlog писались по одной: записал одну, делай fsynс, записал одну, делай fsynс – это с точки зрения общей производительности нехорошо.

Клево распознавать транзакции, которые независимо трогают разные данные и в лог их вкладывают небольшими группками – в несколько раз меньше записей в бинарный лог, по объему их столько же, но системных вызовов меньше write и fsync’ов меньше.

Binlog group commit приделали обратно еще в 5.6, но начиная с 5.7.2 вдобавок приделали поддержку этого дела, чтобы делать многопоточную репликацию на slave’ах. Как обычно, оно выключено по умолчанию и GDIT’ы выключены, и эти новые достижения тоже выключены и их нужно рукой подпихивать и включать и для того, чтобы счастье наступило, вам недостаточно проапгрейдиться на новую версию, вам надо еще всякое понавключать.

Еще в версии 5.7.7 сделали, с моей точки зрения, странные изменения – переключили дефолтный формат бинарного лога. 100 лет с версии 4.1 жили со statement based репликацией, но переключились на row based репликацию, при этом сменили дефолт binlog_format (есть настройка, что писать в binlog – то ли строчки, то ли statement’ы, то ли что), но при этом не сменили дефолт binlog_row_image. Это вызывает неподдельный интерес, потому что волшебная настройка binlog_row_image означает – в момент, когда мы пишем какое-то изменение в binlog, у нас есть несколько вариантов, что туда дописать.

Первая идея, что приходит мне в голову, – это у нас запись номер 123, ей ставят значение 456 в такую-то колонку, давайте это и запишем ID123 – новое значение 456. Если подумать еще немного, для полной паранойи, можно записать не только новое значение 456, но и предыдущее значение 455, чтобы в случае, если у нас что-то все-таки сломалось и транзакции пошли не в том порядке, мы могли понять, что транзакция, которую мы собираемся накатывать, внезапно пытается изменять другие данные.

Наверно, из соображений максимальной паранойи и хрен с ней с производительностью, MySQL дефолтится в режим, когда в log’e хранятся не просто измененные данные и идентификатор записи, а полная копия всей строчки до изменения, и полная копия всей строчки после изменения. Т.е. это прикольно, когда у вас таблица из 2-х колонок, но когда у вас 3-х килобайтная в среднем строчка, а вы в ней меняете одно маленькое поле, это означает, что в binlog пишутся все 6 Kбайт, из них поменялось 4 байта, которые вы поменяли в числовой колонке.

Это настраивается, слава богу, есть binlog_row_image, который дефолтится в full, но который можно перемкнуть в Noblob хотя бы, либо в Minimal. Но почему-то оно дефолтится именно в full. Как обычно, все плохо, но неплохо. Я уверен, что есть причины для того, чтобы делать именно так, но мне они неизвестны.

Надо понимать, что интересный момент в 5.7.7 произошел. Апгрейдитесь до 5.7.7 и, если у вас явно в конфиге не прописано, внезапно формат binlog’a меняется.

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

Итого итоги подведем про версии.

MySQL ширится и мастерски развивается – делают новые клевые фичи, которые закрывают старые головные боли.

5.7 с введением групповых commit’ов, и, как следствие, возможностью многопоточную закачку на slave’ы делать, тем самым закрывает старый головняк под названием «у нас slave не успевает и не успеет никогда».

GTID’ы ликвидировали старую операционную беду под названием «как бы прикольно идентифицировать транзакцию по имени файла где-то там далеко за горизонтом, но не прикольно потом разбирать сломанный инстанс».

5.7 с GTID’ами и групповыми commit’ами, и, как следствие, многопоточным проигрыванием изменений на slave’е – совсем хорошо, но не в тот момент, когда ты Facebook и полгода просто выкатываешь это изменение. Но даже в тот момент, когда ты Facebook и полгода выкатываешь это изменение, ты в конце концов, когда все выкатил, все равно пишешь в блоге, что было сложно, но в итоге – хорошо, все-таки оно того стоило.

Если 5. 7 очково ставить и всякие многопоточные штуки включать очково, то хотя бы 5.6 плюс глобальные TID хотя бы – так хорошо.

Традиционно в MySQL вызывают изумление принятые дефолты, дефолты странные, т.е. всякое новое полезное делают, но это дело отключено. Местами, когда эти дефолты меняются, они тоже меняются как-то странно. Переключились на row based репликацию, по умолчанию логируем полную строчку, что в моем понимании, в общем случае – ад и холокост. И не понятно, стоит ли профит в одном проценте случаев деградацией в 99-ти? Так что с дефолтами надо зорко озираться. Озираться с одной стороны, и просто так новый функционал не заработает – надо его включить, с другой стороны.

Также немаловажная деталь там, где я пишу 5.7, надо иметь в виду 5.7.2 или 5.7.7 или 5.7.12, где наконец-то починили Showstopper баг или что-то типа того. Т.е. версий 5.6, 5.7 недостаточно, еще минорные версии важны, вот в 5. 7.2 добавили вот это, в 5.7.7 сделали вот это, т.е. даже минорная версия может интересно подкузьмить.

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

И 2-ая половина доклада очень кратко в слайдах – вопросы с одной стороны и ряд стандартных проблем с другой стороны.

Есть ряд стандартных проблем и должно быть теперь понятно, как их чинить.

Возможен ряд всяких фокусов с MySQL’льной репликацией – несколько master’ов в цепочку завязать, сделать catch-all slave, сильно покреативить с репликацией, немножко потрансформировать данные в ходе репликации.

Некоторое количество ключевых слов, на счет которых можно дальше читать про репликацию.

Как настроить репликацию MySQL Master-Slave в Ubuntu 18.

Репликация MySQL — это процесс, который позволяет автоматически копировать данные с одного сервера базы данных на один или несколько серверов.

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

В этом руководстве рассматривается базовый пример репликации MySQL Master / Slave с одним главным и одним подчиненным серверами в Ubuntu 18.04. Те же шаги применяются для MariaDB.

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

Содержание

Подготовка

В этом примере предполагается, что у вас есть два сервера под управлением Ubuntu 18.04, которые могут связываться друг с другом по частной сети. Если ваш хостинг-провайдер не предлагает частные IP-адреса, вы можете использовать общедоступные IP-адреса и настроить брандмауэр, чтобы разрешить трафик на порт 3306 только из надежных источников.

Серверы в этом примере имеют следующие IP-адреса:

Master IP: 192.168.121.190
Slave IP:  192.168.121.236

Установить MySQL

По умолчанию репозитории Ubuntu 18.04 включают MySQL версии 5.7. Чтобы избежать каких-либо проблем, лучше всего установить одну и ту же версию MySQL на оба сервера.

Установите MySQL на главный сервер:

sudo apt-get updatesudo apt-get install mysql-server

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

sudo apt-get updatesudo apt-get install mysql-server

Настроить главный сервер

Первый шаг — настроить главный сервер MySQL. Внесем следующие изменения:

Настройте сервер MySQL на прослушивание частного IP-адреса .
Установите уникальный идентификатор сервера.
Включите двоичное ведение журнала

Для этого откройте файл конфигурации MySQL и раскомментируйте или установите следующее:

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

master:/etc/mysql/mysql.conf.d/mysqld.cnf

bind-address           = 192.168.121.190
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log

После этого перезапустите службу MySQL, чтобы изменения вступили в силу:

sudo systemctl restart mysql

Следующим шагом будет создание нового пользователя репликации. Войдите на сервер MySQL как пользователь root, набрав:

sudo mysql

Изнутри командной строки MySQL выполните следующие SQL-запросы, которые создадут пользователя replica и предоставят пользователю привилегию REPLICATION SLAVE :

CREATE USER 'replica'@'192. 168.121.236' IDENTIFIED BY 'replica_password';

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.121.236';

Убедитесь, что вы изменили IP-адрес на свой подчиненный IP-адрес. Вы можете назвать пользователя как хотите.

Находясь в командной строке MySQL, выполните следующую команду, которая выведет двоичное имя файла и позицию.

SHOW MASTER STATUSG

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 629
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Обратите внимание на имя файла mysql-bin.000001 и позицию 629 . Эти значения понадобятся вам при настройке подчиненного сервера. Эти значения, вероятно, будут другими на вашем сервере.

Настроить подчиненный сервер

Как и для главного сервера выше, мы внесем следующие изменения в подчиненный сервер:

Настройте сервер MySQL для прослушивания частного IP-адреса
Установите уникальный идентификатор сервера
Включите двоичное ведение журнала

Откройте файл конфигурации MySQL и отредактируйте следующие строки:

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

slave:/etc/mysql/mysql.conf.d/mysqld.cnf

bind-address           = 192.168.121.236
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log

Перезапустите службу MySQL:

sudo systemctl restart mysql

Следующим шагом является настройка параметров, которые подчиненный сервер будет использовать для подключения к главному серверу. Войдите в оболочку MySQL:

sudo mysql

Сначала остановите подчиненные потоки:

STOP SLAVE;

Выполните следующий запрос, который настроит подчиненное устройство для репликации главного устройства:

CHANGE MASTER TOMASTER_HOST='192.168.121.190',MASTER_USER='replica',MASTER_PASSWORD='replica_password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=629;

Убедитесь, что вы используете правильный IP-адрес, имя пользователя и пароль. Имя и позиция файла журнала должны совпадать со значениями, полученными от главного сервера.

После этого запустите подчиненные потоки.

START SLAVE;

Проверить конфигурацию

На этом этапе у вас должна быть работающая настройка репликации Master / Slave.

Чтобы убедиться, что все работает должным образом, мы создадим новую базу данных на главном сервере:

sudo mysql

CREATE DATABASE replicatest;

Войдите в подчиненную оболочку MySQL:

sudo mysql

Выполните следующую команду, чтобы вывести список всех баз данных :

SHOW DATABASES;

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

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replicatest        |
| sys                |
+--------------------+
5 rows in set (0. 00 sec)

Выводы

В этом руководстве мы показали, как создать репликацию MySQL Master / Slave.

Не стесняйтесь оставлять комментарии, если у вас есть вопросы.

MySQL :: Справочное руководство по MySQL 8.0 :: 17 Репликация

Содержание

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

Репликация на основе позиции
17.1.3 Репликация с глобальными идентификаторами транзакций
17.1.4 Изменение режима GTID на онлайн-серверах
17.1.5 Репликация нескольких источников MySQL
17.1.6 Опции и переменные для репликации и двоичная регистрация
17.1.7 Задачи общего введения репликации

17.2 Репликация реализация

17.2.1 Форматы репликации
17.2.2. Потоки
17.2.4 Журнал ретрансляции и хранилища метаданных репликации
17.2.5 Как серверы оценивают правила фильтрации репликации

17.3 Безопасность репликации

17. 3.1 Настройка репликации для использования зашифрованных соединений
17.3.2. Репликация для резервных копий
17.4.2 Обработка неожиданной остановки реплики
17.4.3 Мониторинг репликации на основе строк
17.4.4 Использование репликации с разными источниками и механизмами хранения реплик
17.4.5 Использование репликации для горизонтального масштабирования
17.4.6 Репликация разных баз данных на разные реплики
17.4.7 Повышение производительности репликации
17.4.8 Переключение источников при отказе
Асинхронное переключение источников и реплик Отказоустойчивое соединение
17.4.10 Полусинхронная репликация
17.4.11 Отложенная репликация

17.5 Примечания и советы по репликации

17.5.1 Функции и проблемы репликации
17.5.2 Совместимость репликации между версиями MySQL
17.5.3 Обновление топологии репликации
17.5. 4 Устранение неполадок репликации
17.5.5 Как сообщать об ошибках или проблемах репликации

Репликация MySQL в виде Master/Slave

Репликация MySQL в виде Master/Slave

Репликация MySQL в виде Master/Slave

Настройка Master

Настройка slave

Репликация Mysql (master-slave, master-master) | Блог любителя экспериментов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Другие материалы в категории MySQL/MariaDB

Настройка репликации master-slave в MySQL

Список использованных источников

Устранение задержки репликации в Базе данных Azure для MySQL

Основные понятия репликации

Мониторинг задержки репликации

Распространенные сценарии длительной задержки репликации

Задержка в сети или высокая загрузка ЦП на исходном сервере

Объемные пакеты транзакций на исходном сервере

Медленная работа на сервере реплики

В таблице нет первичного ключа или уникального ключа

Долго выполняющиеся запросы на сервере реплики

Запросы DDL на исходном сервере

Снижение характеристик сервера реплики

Улучшение задержки репликации путем настройки параметров исходного сервера

Дальнейшие действия

HighLoad Junior Блог

Какая еще теоретически бывает репликация?

Как настроить репликацию MySQL Master-Slave в Ubuntu 18.

Подготовка

Установить MySQL

Настроить главный сервер

Настроить подчиненный сервер

Проверить конфигурацию

Выводы

MySQL :: Справочное руководство по MySQL 8.0 :: 17 Репликация

MySQL :: Справочное руководство по MySQL 5.7 :: 16 Репликация

Учебное пособие по репликации MySQL Master-Slave | Toptal

Установка первого экземпляра MySQL

Настройка

Создание новых папок для второго экземпляра MySQL

Дополнительные параметры безопасности в AppArmor

Установка второго экземпляра MySQL

Дополнительная конфигурация

Автоматический запуск обоих экземпляров MySQL при загрузке

Настройка репликации ведущий-ведомый

Подведение итогов

Дополнительные материалы в блоге Toptal Engineering:

Репликация MySQL для обеспечения высокой доступности

Содержание в белой бумаге

1. Введение

2. Что такое репликация MySQL?

2.1. Схема репликации

2.1.1. Асинхронная репликация

2.1.2. Полусинхронная репликация

2.2. Глобальный идентификатор транзакции (GTID)