Содержание

Репликация 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_modeON или ON_PERMISSIVE. Хотя , при включенном режиме gtid_mode, на мастер-сервере журналирование должно быть включено обязательно для возможности реплицировать завершенные транзакции, реплики могут работать без включения бинарного лога. Выключить бинарный лог можно, запустив сервер с опциями --skip-log-bin и --log-slave-updates=OFF

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

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

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

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

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

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

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

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

server_id = 1

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

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

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

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

log_bin = base_name_of_log

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

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

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

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

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

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

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

mysql> CREATE USER 'replication'@'%.k-max.name' IDENTIFIED IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.k-max.name';
Пример настройки сервера mysql master-slave

master1(Master/Source/10.0.2.2)

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

slave1(Slave/Replica/10.0.2.4)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поехали:

slave2(Second Slave/Replica/10.0.2.5)

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

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

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

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

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

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

master1(Master/Source/10.0.2.2)

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

master2(Master/Source/10.0.2.3)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

подробнее

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

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

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

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

Ошибка Last_IO_Error equal MySQL server UUIDs

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

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

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

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

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

а так же

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

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

Ошибка MY-010604

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

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

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

(c)

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

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

  • 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:


[email protected]> FLUSH TABLES WITH READ LOCK;
[email protected]> SET GLOBAL read_only = ON;

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


[email protected]> SET GLOBAL read_only = OFF;

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


[email protected]# tar -czf mysqldir. tar.gz /var/lib/mysql/[dbname]

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


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

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


[email protected]# mysqlamdin -u root -p shutdown
[email protected]# mysqlamdin -u root -p shutdown

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


[email protected]# cd /var/lib/mysql
[email protected]# tar -xzf mysqldir.tar.gz

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


[email protected]# mysql -u root -p [dbname] < dbdump.sql

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


[email protected]# /etc/init.d/mysql start
[email protected]# /etc/init.d/mysql start

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


[email protected]> start slave;
[email protected]> SHOW SLAVE STATUS\G
[email protected]> SHOW MASTER STATUS\G

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


[email protected]# mysqlbinlog master.info > master_info.sql

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


[email protected]> stop slave;
[email protected]> RESET SLAVE;
[email protected]> RESET MASTER;

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

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


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

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

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

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

 

 

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

 

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

  • Статья
  • Чтение занимает 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 [email protected] -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
[email protected]:~$mysql -h myreplicademoserver.mysql.database.azure.com -u [email protected] -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.

04

Репликация 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 Как сообщать об ошибках или проблемах репликации

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

Преимущества репликации в MySQL включают в себя:

  • Масштабируемые решения — распределение нагрузки между несколькими репликами для повышения производительности. В этой среде все пишет и обновления должны выполняться на исходном сервере. Читает, однако может иметь место на одной или нескольких репликах. Эта модель может улучшить производительность записей (поскольку источник посвящен обновления), при этом значительно увеличивая скорость чтения в увеличение количества реплик.

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

  • Аналитика — оперативные данные могут быть созданы на источнике, в то время как анализ информации может происходить на реплике без ущерба для производительности источника.

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

Сведения о том, как использовать репликацию в таких сценариях, см. Раздел 17.4, «Решения для репликации».

MySQL 8.0 поддерживает различные методы репликации. Традиционный метод основан на воспроизведении событий из двоичный журнал источника и требует файлы журнала и позиции в их для синхронизации между источником и репликой. Более новый метод на основе глобальной транзакции идентификаторы (GTID) являются транзакционными и, следовательно, не требуют работы с лог-файлами или позициями внутри этих файлов, что значительно упрощает многие распространенные задачи репликации. Репликация использование GTID гарантирует согласованность между источником и репликой, поскольку пока все транзакции, совершенные в источнике, также были применяется к реплике. Для получения дополнительной информации о GTID и Репликация на основе GTID в MySQL, см. Раздел 17.1.3, «Репликация с глобальными идентификаторами транзакций». Для получения информации об использовании двоичного репликация файла журнала на основе позиции, см. Раздел 17.1, «Настройка репликации».

Репликация в MySQL поддерживает различные типы синхронизации. Оригинальный тип синхронизации — односторонний, асинхронный. репликация, при которой один сервер выступает в качестве источника, а один или больше других серверов действуют как реплики. Это в отличие от синхронная репликация , которая является характеристика кластера NDB (см. главу 23, MySQL NDB Cluster 8.0 ). В MySQL 8.0 поддерживается полусинхронная репликация. в дополнение к встроенной асинхронной репликации. С полусинхронная репликация, фиксация, выполняемая на исходных блоках прежде чем вернуться к сеансу, который выполнил транзакцию, пока по крайней мере одна реплика подтверждает, что она получила и зарегистрировала события для транзакции; видеть Раздел 17.4.10, «Полусинхронная репликация». MySQL 8.0 также поддерживает отложенную репликацию, так что реплика намеренно отстает позади источника не менее чем на определенное время; видеть Раздел 17.4.11, «Отложенная репликация». Для сценариев, где требуется синхронная репликация , используйте NDB Кластер (см. главу 23, MySQL NDB Cluster 8.0 ).

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

Существует два основных типа формата репликации: на основе операторов. Репликация (SBR), которая реплицирует целые операторы SQL, и строки Репликация на основе (RBR), при которой реплицируются только измененные строки. Ты также может использовать третий вариант, смешанную репликацию (MBR). За дополнительную информацию о различных форматах репликации см. Раздел 17.2.1, «Форматы репликации».

Репликация контролируется с помощью ряда различных опций и переменные. Для получения дополнительной информации см. Раздел 17.1.6, «Параметры и переменные репликации и ведения двоичного журнала». Дополнительные меры безопасности может применяться к топологии репликации, как описано в Раздел 17.3, «Безопасность репликации».

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

Заметки и советы о том, как различаются типы данных и операторы обрабатывается во время репликации, включая детали репликации функции, совместимость версий, обновления и потенциальные проблемы и их разрешение см. в разделе 17.5, «Примечания и советы по репликации». За ответы на некоторые вопросы, которые часто задают новички в MySQL Репликация, см. раздел A.14, «Часто задаваемые вопросы по MySQL 8.0: репликация».

Подробную информацию о реализации репликации см. репликация работает, процесс и содержимое бинарного лога, фоновые потоки и правила, используемые для определения того, как записано и воспроизведено, см. Раздел 17.2, «Реализация репликации».

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

Содержание

16.1 Настройка репликации
Репликация на основе позиции файла
16. 1.3 Репликация с глобальными идентификаторами транзакций
16.1.4 Изменение режимов репликации на онлайн-серверах
16.1.5 Репликация нескольких источников MySQL
16.1.6 Варианты и переменные для репликации и двоичная регистрация
16.1.7 Задачи общего введения репликации
16.2 Репликация реализация
16.2.1 Форматы репликации
16.2.2. Потоки
16.2.4 Журнал ретрансляции и хранилища метаданных репликации
16.2.5 Как серверы оценивают правила фильтрации репликации
16.3 Решения для репликации
16.3.1 Использование репликации для резервного копирования
16.3.2 Обработка неожиданной остановки реплики
16.3.3 Использование репликации с различными источниками и механизмами хранения реплик
16.3.4 Использование репликации для Scale-Out
16.3.5 Репликация разных баз данных на разные реплики
16. 3.6 Повышение производительности репликации
16.3.7 Переключение источников во время аварийного переключения
16.3.8 Настройка репликации для использования зашифрованных соединений
16.3.9 Полусинхронная репликация
16.3.10 Задержка репликации
16.4 Примечания репликации и советы
16.4.1 Функции репликации
16.4.2 Совместимость репликации между MySQL Wersions
16.4.3. a Топология репликации
16.4.4 Устранение неполадок репликации
16.4.5 Как сообщить об ошибках или проблемах репликации

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

Преимущества репликации в MySQL включают в себя:

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

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

  • Аналитика — оперативные данные могут быть созданы на источнике, в то время как анализ информации может происходить на реплике без ущерба для производительности источника.

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

Сведения о том, как использовать репликацию в таких сценариях, см. Раздел 16.3, «Решения для репликации».

MySQL 5.7 поддерживает различные методы репликации. Традиционный метод основан на воспроизведении событий из двоичный журнал источника и требует файлы журнала и позиции в их для синхронизации между источником и репликой. Более новый метод на основе глобальной транзакции идентификаторы (GTID) являются транзакционными и, следовательно, не требуют работы с лог-файлами или позициями внутри этих файлов, что значительно упрощает многие распространенные задачи репликации. Репликация использование GTID гарантирует согласованность между источником и репликой, поскольку пока все транзакции, совершенные в источнике, также были применяется к реплике. Для получения дополнительной информации о GTID и Репликация на основе GTID в MySQL, см. Раздел 16.1.3, «Репликация с глобальными идентификаторами транзакций». Для получения информации об использовании двоичного репликация файла журнала на основе позиции, см. Раздел 16.1, «Настройка репликации».

Репликация в MySQL поддерживает различные типы синхронизации. Оригинальный тип синхронизации — односторонний, асинхронный. репликация, при которой один сервер выступает в качестве источника, а один или больше других серверов действуют как реплики. Это в отличие от синхронная репликация , которая является характеристика кластера NDB (см. главу 21, MySQL NDB Cluster 7.5 и NDB Cluster 7.6 ). В MySQL 5.7 поддерживается полусинхронная репликация. в дополнение к встроенной асинхронной репликации. С полусинхронная репликация, фиксация, выполняемая на исходных блоках прежде чем вернуться к сеансу, который выполнил транзакцию, пока по крайней мере одна реплика подтверждает, что она получила и зарегистрировала события для транзакции; видеть Раздел 16. 3.9, «Полусинхронная репликация». MySQL 5.7 также поддерживает отложенную репликацию, так что реплика намеренно отстает позади источника не менее чем на определенное время; видеть Раздел 16.3.10, «Отложенная репликация». Для сценариев, где требуется синхронная репликация , используйте NDB Кластер (см. главу 21, MySQL NDB Cluster 7.5 и NDB Cluster 7.6 ).

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

Существует два основных типа формата репликации: на основе операторов. Репликация (SBR), которая реплицирует целые операторы SQL, и строки Репликация на основе (RBR), при которой реплицируются только измененные строки. Ты также может использовать третий вариант, смешанную репликацию (MBR). За дополнительную информацию о различных форматах репликации см. Раздел 16.2.1, «Форматы репликации».

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

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

Заметки и советы о том, как различаются типы данных и операторы обрабатывается во время репликации, включая детали репликации функции, совместимость версий, обновления и потенциальные проблемы и их разрешение см. в разделе 16.4, «Примечания и советы по репликации». За ответы на некоторые вопросы, которые часто задают новички в MySQL Репликация, см. раздел A.14, «Часто задаваемые вопросы по MySQL 5.7: репликация».

Подробную информацию о реализации репликации см. репликация работает, процесс и содержимое бинарного лога, фоновые потоки и правила, используемые для определения того, как записано и воспроизведено, см. Раздел 16.2, «Реализация репликации».

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

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

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

Разработчики MySQL обычно работают только на одной машине и, как правило, имеют всю свою среду разработки на этой одной машине, исходя из логики, что они не зависят от сети или подключения к Интернету. Если требуется репликация master-slave, потому что, например, им нужно протестировать репликацию в среде разработки перед развертыванием изменений в другом месте, они должны создать ее на той же машине. Хотя настройка одного экземпляра MySQL довольно проста, нам нужно приложить дополнительные усилия для настройки второго, а затем репликации master-slave.

Для этого пошагового руководства я выбрал Ubuntu Linux в качестве основной операционной системы, и предоставленные команды предназначены для этой операционной системы. Если вы хотите настроить репликацию master-slave MySQL в какой-либо другой операционной системе, вам нужно будет внести изменения для ее конкретных команд. Однако общие принципы настройки репликации master-slave MySQL на одной и той же машине одинаковы для всех операционных систем.

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

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

Самый простой способ установить MySQL на Ubuntu — запустить следующую команду из командной строки терминала:

 sudo apt-get install mysql-server
 

В процессе установки вам будет предложено установить пароль для пользователя MySQL root .

Настройка

mysqld_multi

Чтобы эффективно управлять двумя экземплярами MySQL на одном компьютере, нам нужно использовать mysqld_multi .

Первым шагом в настройке mysqld_multi является создание двух отдельных групп [mysqld] в существующем файле my.cnf . Расположение файла my.cnf по умолчанию в Ubuntu — /etc/mysql/ . Итак, откройте файл my.cnf в вашем любимом текстовом редакторе и переименуйте существующую группу [mysqld] в [mysqld1] . Эта переименованная группа будет использоваться для настройки первого экземпляра MySQL, а также будет настроена как главный экземпляр. Как и в репликации master-slave MySQL, каждый экземпляр должен иметь свой собственный уникальный server-id добавьте следующую строку в группу [mysqld1] :

 server-id = 1
 

Поскольку нам нужна отдельная группа [mysqld] для второго экземпляра MySQL, скопируйте группу [mysqld1] со всеми текущими конфигурациями и вставьте ее ниже в тот же файл my.cnf . Теперь переименуйте скопированную группу в [mysqld2] и внесите следующие изменения в конфигурацию подчиненного устройства:

 server-id = 2
порт = 3307
сокет = /var/run/mysqld/mysqld_slave.sock
pid-файл = /var/run/mysqld/mysqld_slave. pid
каталог данных = /var/lib/mysql_slave
log_error = /var/log/mysql_slave/error_slave.log
реле-журнал = /var/log/mysql_slave/relay-bin
реле-лог-индекс = /var/log/mysql_slave/relay-bin.index
главный-информационный-файл = /var/log/mysql_slave/master.info
реле-лог-информация-файл = /var/log/mysql_slave/relay-log.info
только для чтения = 1
 

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

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

Чтобы разрешить этому второму экземпляру использовать одни и те же двоичные файлы MySQL, нам нужно установить разные значения для socket , pid-file , datadir и log_error .

Нам также необходимо включить relay-log , чтобы использовать второй экземпляр в качестве ведомого (параметры relay-log , relay-log-index и relay-log-info-file ), как а также установить master-info-file .

Наконец, чтобы сделать подчиненный экземпляр доступным только для чтения, для параметра read_only установлено значение 1 . Вы должны быть осторожны с этой опцией, так как она не полностью предотвращает изменения на ведомом устройстве. Даже когда read_only установлен на 1 , обновления будут разрешены только пользователям с привилегией SUPER . MySQL недавно представил новый параметр super_read_only , чтобы предотвратить внесение изменений пользователями SUPER . Эта опция доступна с версии 5.7.8.

Помимо групп [mysqld1] и [mysqld2] , нам также необходимо добавить новую группу [mysqld_multi] в файл my. cnf :

 [mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
пользователь = multi_admin
пароль = многопроходный
 

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

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

На предыдущем шаге мы подготовили файл конфигурации для второго экземпляра MySQL. В этом файле конфигурации используются две новые папки. Следующие команды Linux должны использоваться для создания этих папок с соответствующими привилегиями:

 mkdir -p /var/lib/mysql_slave
chmod --ссылка /var/lib/mysql /var/lib/mysql_slave
chown --ссылка /var/lib/mysql /var/lib/mysql_slave
 
mkdir -p /var/log/mysql_slave
chmod --ссылка /var/log/mysql /var/log/mysql_slave
chown --ссылка /var/log/mysql /var/log/mysql_slave
 

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

В некоторых средах Linux параметры безопасности AppArmor необходимы для запуска второго экземпляра MySQL. По крайней мере, они требуются в Ubuntu.

Чтобы правильно настроить AppArmor, отредактируйте файл /etc/apparmor.d/usr.sbin.mysqld в своем любимом текстовом редакторе, добавив следующие строки:

 /var/lib/mysql_slave/ r,
/var/lib/mysql_slave/** RWK,
/var/журнал/mysql_slave/ г,
/var/журнал/mysql_slave/* RW,
/var/run/mysqld/mysqld_slave.pid RW,
/var/run/mysqld/mysqld_slave.sock w,
/run/mysqld/mysqld_slave.pid RW,
/run/mysqld/mysqld_slave.sock w,
 

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

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

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

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

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

 mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave
 

После инициализации каталога данных MySQL вы можете запустить оба экземпляра MySQL с помощью службы mysqld_multi :

 mysqld_multi start
 

Установите пароль root для второго экземпляра MySQL, используя mysqladmin с соответствующим хостом и портом. Имейте в виду, если хост и порт не указаны, mysqladmin будет подключаться к первому экземпляру MySQL по умолчанию:

 mysqladmin --host=127.0.0.1 --port=3307 -u пароль root rootpwd
 

В приведенном выше примере я установил пароль «rootpwd», но рекомендуется использовать более надежный пароль.

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

mysqld_multi

В конце раздела «Настройка mysqld_multi» я написал, что позже мы предоставим соответствующие привилегии пользователю multi_admin , так что сейчас самое время для этого. Нам нужно дать этому пользователю соответствующие привилегии в обоих экземплярах, поэтому давайте сначала подключимся к первому экземпляру:

 mysql --host=127.0.0.1 --port=3306 -uroot -p
 

После входа в систему выполните следующие две команды:

 mysql> ПРЕДОСТАВЬТЕ ОТКЛЮЧЕНИЕ НА *.* КОМУ 'multi_admin'@'localhost', ОПРЕДЕЛЕННОМУ 'multipass';
mysql> УДАЛИТЬ ПРИВИЛЕГИИ;
 

Выйдите из клиента MySQL и подключитесь ко второму экземпляру:

 mysql --host=127.0.0.1 --port=3307 -uroot -p
 

После входа в систему выполните те же две команды, что и выше:

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost', ОПРЕДЕЛЕННЫЙ 'multipass';
mysql> УДАЛИТЬ ПРИВИЛЕГИИ;
 

Выход из клиента MySQL.

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

Последним шагом настройки mysqld_multi является установка сценария автоматической загрузки в init.d .

Для этого создайте новый файл с именем mysqld_multi в /etc/init. d и предоставьте ему соответствующие привилегии:

 cd /etc/init.d
коснитесь mysqld_multi
chmod +x /etc/init.d/mysqld_multi
 

Откройте этот новый файл в своем любимом текстовом редакторе и скопируйте следующий скрипт:

 #!/бин/ш
### НАЧАЛО ИНФОРМАЦИИ
# Предоставляет: имя сценария
# Обязательное начало: $remote_fs $syslog
# Обязательная остановка: $remote_fs $syslog
# Старт по умолчанию: 2 3 4 5
# Стоп по умолчанию: 0 1 6
# Краткое описание: запуск демона во время загрузки
# Описание: Включить службу, предоставляемую демоном.
### КОНЕЦ ИНФОРМАЦИИ
 
bindir=/usr/bin
 
если тест -x $bindir/mysqld_multi
тогда
    mysqld_multi="$bindir/mysqld_multi";
еще
    echo "Не удается выполнить $bindir/mysqld_multi";
    выход;
фи
 
случай "$1" в
    'Начало' )
     "$mysqld_multi" старт $2
     ;;
    'остановка' )
     "$mysqld_multi" остановить $2
     ;;
    'отчет' )
     Отчет "$mysqld_multi" $2
     ;;
    'запустить снова' )
     "$mysqld_multi" остановить $2
     "$mysqld_multi" старт $2
     ;;
    *)
     echo "Использование: $0 {старт|стоп|отчет|перезапуск}" >&2
     ;;
эсак
 

Добавьте службу mysqld_multi к уровням выполнения по умолчанию с помощью следующей команды:

 update-rc. d mysqld_multi defaults
 

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

 mysqld_multi report
 

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

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

Часть настройки уже была выполнена в главе «Настройка mysqld_multi». Единственным оставшимся изменением в файле my.cnf является настройка ведения двоичного журнала на ведущем устройстве. Для этого отредактируйте файл my.cnf со следующими изменениями и дополнениями в группе [mysqld1] :

 log_bin = /var/log/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog-формат = ROW
 

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

 mysqld_multi stop 1
mysqld_multi старт 1
 

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

 mysql -uroot -p --host=127.0.0.1 --port=3306
 

Создайте нового пользователя для репликации:

 mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
 

Выход из клиента MySQL.

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

 mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump .sql
 

Здесь мы используем опцию --master-data=2 , чтобы иметь комментарий, содержащий оператор CHANGE MASTER внутри файла резервной копии. Этот комментарий указывает координаты репликации во время резервного копирования, и нам понадобятся эти координаты позже для обновления основной информации в подчиненном экземпляре. Вот пример этого комментария:

 --
-- Позиция для начала репликации или восстановления на определенный момент времени из
--
-- ЗАМЕНИТЬ МАСТЕР НА MASTER_LOG_FILE='mysql-bin. 000001', MASTER_LOG_POS=349;
 

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

 mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql
 

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

Подключиться к ведомому экземпляру с помощью клиента MySQL с соответствующим хостом и портом:

 mysql -uroot -p --host=127.0.0.1 --port=3307
 

Выполните следующую команду, чтобы обновить информацию о мастере (возьмите координаты репликации из файла дампа replicationdump.sql , как описано выше):

 mysql> CHANGE MASTER TO
  -> MASTER_HOST='127.0.0.1',
  -> MASTER_USER='репликация',
  -> MASTER_PASSWORD='репликация',
  -> MASTER_LOG_FILE='mysql-bin.000001',
  -> MASTER_LOG_POS=349;
 

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

 mysql> START SLAVE;
 

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

 mysql> SHOW SLAVE STATUS \G
 

Поздравляю. Теперь ваша репликация master-slave MySQL на том же компьютере успешно настроена.

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

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

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

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

  • Полное руководство по базам данных NoSQL
  • Подробное изучение производительности Entity Framework при использовании «содержит»
  • Практический пример: почему я использую облачную инфраструктуру AWS для своих продуктов
  • Объяснение оптимизации производительности Magento
  • Прохождение оценки PageSpeed ​​Insights от Google

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

В этом техническом документе содержится информация о репликации MySQL, а также информация о последних функциях, представленных в версиях 5. 6, 5.7 и 8.0. Существует также более практический раздел о том, как быстро развернуть и управлять настройкой репликации с помощью ClusterControl.

Содержание в белой бумаге
  • Введение
  • Что такое репликация MySQL
  • Топология для MySQL Replication
  • .
  • Проблемы и устранение неполадок

Загрузите технический документ

1. Введение

Репликация MySQL, вероятно, является самым популярным решением высокой доступности для MySQL и широко используется ведущими веб-ресурсами, такими как Twitter и Facebook. Несмотря на простоту настройки, текущее обслуживание, такое как обновление программного обеспечения, изменение схемы, изменение топологии, аварийное переключение и восстановление, всегда было сложным. По крайней мере, до MySQL 5.6.

К счастью, MySQL 5.6 привнес ряд существенных улучшений в репликацию, включая глобальные идентификаторы транзакций, контрольные суммы событий, многопоточные подчиненные устройства и отказоустойчивые подчиненные/ведущие устройства. Репликация стала еще лучше в MySQL 5.7 и MySQL 8.0.

Связанные ресурсы:

  • Технический документ по схеме репликации MySQL

В этом руководстве содержится основная информация о репликации MySQL, а также информация о функциях, представленных в версиях 5.6, 5.7 и 8.0. В конце вы должны быть в состоянии ответить на такие вопросы, как:

  • Как использовать GTID с репликацией?
  • Как восстановить настройки в случае сбоя мастера?
  • Как обновить главный и подчиненный серверы без простоев?
  • Как изменить схему базы данных на всех серверах?
  • Как справиться с задержкой подчиненного устройства?
  • и т. д.

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

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

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

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

В настоящее время MySQL поддерживает две схемы репликации:

  • Асинхронная репликация
  • Полусинхронная репликация

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

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

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

  • Мастер не ждет ведомого.
  • Ведомый определяет, сколько и с какой точки считывать в двоичном журнале.
  • Подчиненное устройство может произвольно отставать от главного при чтении или применении изменений.

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

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

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

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

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

Хорошим примером использования полусинхронной репликации является мастер резервного копирования, чтобы уменьшить влияние отказа мастера за счет сведения к минимуму риска потери данных. Мы объясним это подробно в разделе «Глава 3 — Топология для репликации MySQL».

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

Глобальные идентификаторы транзакций (GTID) были введены в MySQL 5. 6. GTID — это уникальный идентификатор, созданный и связанный с каждой транзакцией, совершенной на исходном сервере (главном). Этот идентификатор уникален не только для сервера, на котором он был создан, но и для всех серверов в данной настройке репликации. Существует однозначное соответствие между всеми транзакциями и всеми GTID. Обратите внимание, что MySQL и MariaDB имеют разную реализацию GTID, как мы объясним ниже.

2.2.1. Репликация в MySQL 5.5 и более ранних версиях

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

2.2.2. Как GTID решает проблему

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

Каждая транзакция имеет уникальный идентификатор, который идентифицирует ее одинаково на каждом сервере. Больше не важно, в какой позиции бинарного журнала была записана транзакция, все, что вам нужно знать, это GTID: ‘966073f3-b6a4-11e4-af2c-080027880ca6:4’. GTID состоит из двух частей: уникального идентификатора сервера, на котором впервые была выполнена транзакция, и порядкового номера. В приведенном выше примере мы видим, что транзакция была выполнена сервером с server_uuid 9.0128  из ‘966073f3-b6a4-11e4-af2c-080027880ca6’, и там выполняется четвертая транзакция. Этой информации достаточно для выполнения сложных изменений топологии — MySQL знает, какие транзакции были выполнены, и, следовательно, знает, какие транзакции необходимо выполнить следующими. Забудьте о бинарных логах, теперь все в GTID.

Всю необходимую информацию для синхронизации с мастером можно получить непосредственно из потока репликации. Когда вы используете GTID для репликации, вам не нужно включать параметры MASTER_LOG_FILE или MASTER_LOG_POS в оператор CHANGE MASTER TO; вместо этого необходимо только включить опцию MASTER_AUTO_POSITION.

2.2.3. MariaDB GTID против MySQL GTID

MariaDB имеет другую реализацию идентификатора глобальной транзакции (GTID) и включен по умолчанию, начиная с MariaDB 10. 0.2. GTID MariaDB состоит из трех отдельных значений:

  • Идентификатор домена — домен репликации. Домен репликации — это сервер или группа серверов, которые генерируют один строго упорядоченный поток репликации.
  • Идентификатор сервера — Идентификационный номер сервера, позволяющий главному и подчиненному серверам идентифицировать себя уникальным образом.
  • Идентификатор группы событий — порядковый номер набора событий, которые всегда применяются как единое целое. Каждая группа событий binlog (например, транзакция, DDL, нетранзакционный оператор) аннотируется своим GTID.

На рисунке ниже показаны различия между двумя GTID:

В MariaDB не требуется специальной настройки сервера для начала использования GTID. Некоторые из преимуществ GTID MariaDB:

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

Несмотря на различия между этими двумя версиями, по-прежнему возможна репликация с MySQL 5.6 на MariaDB 10.0 или наоборот. Однако вы не сможете использовать функции GTID для автоматического выбора правильной позиции binlog при переключении на новый мастер. Репликация MySQL в старом стиле будет работать.

Дополнительную информацию можно найти на странице документации по MariaDB GTID и MySQL GTID.

2.3. Многопоточный ведомый сервер

MySQL 5.6 позволяет выполнять реплицированные события параллельно, если данные разделены по нескольким базам данных. Эта функция называется «Multi-Threaded Slave» (MTS), и ее легко включить, установив для slave_parallel_workers значение > 1. В MySQL 5.7 его теперь можно использовать для любой рабочей нагрузки, включая внутреннюю схему, в отличие от версии 5. 6, где его можно было применять только с одним потоком на схему. В MySQL 8.0 представлены наборы записей, которые позволяют еще лучше распараллелить применение событий двоичного журнала.

2.4. Crash-Safe Slave

Защита от сбоев означает, что даже в случае сбоя подчиненного устройства mysqld/OS вы можете восстановить подчиненное устройство и продолжить репликацию без восстановления баз данных MySQL на подчиненном устройстве. Чтобы сделать подчиненную работу безопасной при сбоях, вы должны использовать только механизм хранения InnoDB, а в 5.6 вам нужно установить relay_log_info_repository = TABLE и relay_log_recovery = 1.

Устойчивость (sync_binlog = 1 и innodb_flush_log_at_trx_commit = 1) НЕ требуется.

2.5. Групповая фиксация

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

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

3.1. Master with Slave (Single Replication)

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

3.2. Мастер с ведомыми реле (цепная репликация)

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

Использование подчиненного ретранслятора имеет свои проблемы:

  • log_slave_updates имеет некоторые потери производительности.
  • Задержка репликации на подчиненном сервере ретрансляции приведет к задержке на всех его подчиненных устройствах.
  • Мошеннические транзакции на подчиненном сервере ретрансляции заразят все его подчиненные устройства.
  • Если подчиненный сервер ретрансляции выходит из строя и вы не используете GTID, репликация всех подчиненных серверов прекращается, и их необходимо повторно инициализировать.

3.3. Мастер с активным мастером (циклическая репликация)

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

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

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

3.4. Мастер с резервным мастером (множественная репликация)

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

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

3.5. Несколько мастеров в одно ведомое устройство (репликация из нескольких источников)

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

MySQL и MariaDB имеют разные реализации репликации с несколькими источниками, где MariaDB должен иметь GTID с gtid-domain-id, настроенным для различения исходных транзакций, в то время как MySQL использует отдельный канал репликации для каждого мастера, с которого реплицируется подчиненное устройство. В MySQL мастера в топологии репликации с несколькими источниками могут быть настроены на использование либо репликации на основе глобального идентификатора транзакции (GTID), либо репликации на основе позиции двоичного журнала.

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

3.6. Galera с Replication Slave (гибридная репликация)

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

Производительность кластера Galera такая же высокая, как у самого медленного узла. Наличие подчиненного устройства с асинхронной репликацией может свести к минимуму влияние на кластер, если вы отправляете на подчиненное устройство длительные отчеты/запросы типа OLAP или если вы выполняете тяжелые задания, требующие блокировки, такие как mysqldump. Ведомое устройство также может служить в качестве оперативного резервного копирования для локального и удаленного аварийного восстановления.

Гибридная репликация поддерживается ClusterControl, и вы можете развернуть ее непосредственно из пользовательского интерфейса ClusterControl. Дополнительные сведения о том, как это сделать, см. в сообщениях блога — Гибридная репликация с MySQL 5.6 и Гибридная репликация с MariaDB 10.x.

4. Развертывание настройки репликации MySQL

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

Установите ClusterControl, следуя инструкциям на странице "Начало работы". Не забудьте настроить беспарольный SSH от ClusterControl ко всем узлам (включая сам узел ClusterControl). Мы собираемся использовать пользователя root для развертывания. На узле ClusterControl выполните:

 $ ssh-keygen -t rsa
$ ssh-copy-id 10.0.0.200 # управление кластером
$ ssh-copy-id 10.0.0.201 # мастер
$ ssh-copy-id 10.0.0.202 # резервный мастер
$ ssh-копия-идентификатор 10. 0.0.203 # раб1
$ ssh-copy-id 10.0.0.204 # раб2 

Откройте пользовательский интерфейс ClusterControl, перейдите к «Создать узел базы данных» и откройте вкладку «Репликация MySQL». В диалоговом окне нужно заполнить 3 вкладки, как показано на следующих снимках экрана.

4.1. Общие и параметры SSH

В разделе «Общие параметры и параметры SSH» укажите необходимую информацию:

  • Пользователь SSH — укажите пользователя SSH, который ClusterControl будет использовать для подключения к целевому хосту.
  • Путь к ключу SSH — для SSH без пароля требуется ключ SSH. Укажите здесь физический путь к ключевому файлу.
  • Пароль Sudo — пароль Sudo, если пользователь sudo использует пароль для повышения привилегий. В противном случае оставьте поле пустым.
  • Номер порта SSH — не требует пояснений. Значение по умолчанию — 22.
  • Имя кластера — имя кластера после развертывания с помощью ClusterControl.

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

4.2. Определите серверы MySQL

Перейдите на следующую вкладку, определите параметры установки и настройки серверов MySQL:

  • Поставщик. В настоящее время поддерживаются поставщики Percona Server, MariaDB и Oracle.
  • Версия — основная версия MySQL. Рекомендуется версия 5.7 (Oracle/Percona) или 10.3 (MariaDB).
  • Каталог данных сервера — физическое расположение каталога данных MySQL. По умолчанию – /var/lib/mysql.
  • Порт сервера — порт сервера MySQL. По умолчанию 3306.
  • Шаблон My.cnf — шаблон конфигурации MySQL. Оставьте его пустым, чтобы использовать шаблон по умолчанию, расположенный в папке /usr/share/cmon/templates. Для MySQL 5.7 ClusterControl будет использовать my.cnf.repl57 для MySQL 5.7, my.cnf.gtid_replication для MySQL 5. 6 и my.cnf.replication для MySQL 5.5.
  • Корневой пароль — корневой пароль MySQL. ClusterControl настроит это за вас.
  • Репозиторий — рекомендуется выбрать значение по умолчанию, если только вы не хотите использовать существующие репозитории на узлах базы данных. Вы также можете выбрать «Создать новый репозиторий», чтобы создать и отразить репозиторий поставщика текущей базы данных, а затем выполнить развертывание с использованием локального зеркального репозитория.

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

4.3. Define Topology

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

  • IP/имя хоста — укажите IP-адрес или имя хоста целевых хостов. В этом диалоговом окне вы можете определить топологию стандартной репликации master-slave или multi-master. Для репликации с несколькими мастерами мастер A будет записывающим, а мастер B будет запущен только для чтения. ClusterControl должен иметь доступ к указанному серверу через SSH без пароля. Если ClusterControl не сможет подключиться к хосту, здесь будет показана ошибка, указывающая на основную причину.

После того, как мы заполнили необходимую информацию, нажмите «Развернуть», чтобы начать развертывание. ClusterControl развернет кластер репликации в соответствии с топологией. Начиная с ClusterControl v1.4, новое развертывание репликации MySQL будет настроено с полусинхронной схемой репликации, чтобы уменьшить вероятность потери данных. Подробности этой схемы объясняются в этой главе «Что такое репликация MySQL».

Ход развертывания можно отслеживать в разделе «Действия» (верхнее меню) на вкладке «Задания». Выберите «Создать кластер» и щелкните диалоговое окно «Полная информация о задании», когда вы нажмете значок вращающейся стрелки в верхнем меню:

ClusterControl выполняет следующие задачи:

  1. Проверяет подключение SSH.
  2. Устанавливает указанный сервер MySQL.
  3. Создает каталог данных и устанавливает системные таблицы.
  4. Создает/предоставляет пользователя mysql для сервера MySQL.
  5. Предоставляет пользователю CMON сервер ClusterControl.
  6. Настраивает роль репликации для MySQL (ведущий/подчиненный).
  7. Проверяет развертывание.
  8. Регистрирует узел на сервере ClusterControl.

Теперь кластер репликации MySQL развернут.

4.4. Горизонтальное масштабирование

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

Чтобы увеличить масштаб путем добавления дополнительных копий для чтения, перейдите к ClusterControl > выберите кластер базы данных > Действия > Добавить узел > Создайте и добавьте новый узел БД и введите соответствующую информацию для подчиненного устройства:

ClusterControl поддерживает добавление цепочки репликации под любым из работающих подчиненных устройств благодаря реализации GTID. Большим преимуществом цепной репликации является то, что она не увеличивает нагрузку на главный сервер. Вы можете решить, имеет ли ведомое устройство более низкий приоритет, чтобы оно могло быть подчинено определенному ведомому устройству. В этом примере мы собираемся добавить новый сервер в качестве ведомого (10.0.0.205), реплицирующегося с одного из доступных ведомых (10.0.0.202). Также можно включить узел как часть набора для балансировки нагрузки или настроить его как подчиненное устройство с задержкой. Вы также можете использовать одну из резервных копий для подготовки этого нового ведомого устройства вместо того, чтобы копировать данные непосредственно с его ведущего устройства.

Нажмите «Добавить узел». Отслеживайте ход выполнения в ClusterControl > Activity > Jobs > выберите задание «Добавление подчиненного устройства репликации» > Полные сведения о задании , и вы должны увидеть примерно следующее:

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

  1. Проверяет SSH-подключение.
  2. Устанавливает ту же основную версию сервера MySQL, что и мастер из репозитория.
  3. Создает каталог данных и устанавливает системные таблицы.
  4. Создает/предоставляет пользователя mysql для сервера MySQL.
  5. Предоставляет пользователю CMON сервер ClusterControl.
  6. Передает данные на ведомое устройство от выбранного ведущего устройства.
  7. Настраивает роль репликации для подчиненного устройства MySQL с GTID.
  8. Запуск репликации.
  9. Проверяет развертывание.
  10. Регистрирует узел под соответствующим «идентификатором кластера» на сервере ClusterControl.
  11. Обновление набора балансировки на балансировщике нагрузки.

Из статуса видно, что репликация между 10.0.0.202 и slave2 запущена, а статус развертывания возвращается «Завершено ОК». На данный момент slave3 реплицируется с slave2, и наша архитектура теперь выглядит так:

В конце вы увидите сводку репликации со страницы Обзор.

Вы также можете подтвердить топологию в представлении топологии:

Мы увеличили масштаб нашей установки репликации MySQL.

5. Подключение приложения к настройке репликации

К настоящему времени у нас должна быть готова установка репликации MySQL. Следующим шагом является импорт существующей базы данных или создание новой базы данных для нового приложения. При разработке или развертывании вашего приложения просто помните, что все операции записи (операторы/запросы, которые изменяют состояние базы данных) должны выполняться ТОЛЬКО на главном сервере. Примером операций записи являются операторы, содержащие следующее:

  • DDL – CREATE, ALTER, DROP, TRUNCATE, RENAME
  • DML – INSERT, DELETE, UPDATE, REPLACE
  • DCL – GRANT, REVOKE

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

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

5.1. Application Connector

Если ваше приложение работает на PHP, вы можете использовать собственный драйвер MySQL (mysqlnd) для выполнения разделения чтения/записи без больших изменений на стороне приложения. Пользователи Java могут использовать ConnectorJ для разделения чтения/записи с некоторыми незначительными изменениями на стороне кодирования. Поскольку маршрутизацию выполняет сам коннектор, можно избежать дополнительных сетевых задержек, связанных с решениями на основе прокси.

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

Мы рассмотрели несколько примеров разделения чтения-записи в следующих сообщениях блога:

  • Разделение чтения-записи с PHP mysqlnd, MySQL Replication и HAProxy
  • Разделение чтения-записи с ConnectorJ, MySQL Replication и HAproxy

5.

2 . Fabric-Aware Connector

Oracle выпустила MySQL Fabric, расширяемую платформу для управления фермами серверов MySQL. На момент написания статьи он поддерживает две основные категории функций — высокую доступность и масштабирование с использованием сегментирования данных. Для High Availability MySQL Fabric управляет отношениями репликации, обнаруживает отказ главного устройства и автоматически продвигает одно из подчиненных устройств в качестве нового главного устройства. Что касается сегментирования, администратор может определить, как данные распределяются между сегментами — например, какие столбцы таблицы должны использоваться в качестве ключей сегмента и как сопоставлять ключи с правильным сегментом (HASH или RANGE). Все это прозрачно для приложения.

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

5.3. Обратный прокси/балансировщик нагрузки

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

Совместное размещение балансировщиков нагрузки с приложением или использование отдельных хостов зависит от того, как вы хотите использовать балансировщик нагрузки. Некоторые из них, например ProxySQL или MaxScale, поддерживают кэширование запросов. Если вы хотите воспользоваться этой функцией, может быть лучше совместить их с хостами приложений. Имейте в виду, что локальное соединение через сокет Unix всегда будет иметь меньшую задержку, чем соединение с прокси-сервером через TCP. Вы получите больше пользы от кэширования, если задержка будет ниже. С другой стороны, использование отдельных хостов устраняет потенциальное соперничество за ресурсы на хостах приложений, когда и веб-серверы, и прокси-серверы будут конкурировать за ЦП. Также проще управлять ограниченным количеством высокопроизводительных прокси-узлов, чем десятками из них, расположенных рядом с серверами приложений.

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

При добавлении обратного прокси-сервера наша архитектура должна выглядеть так:

На момент написания статьи существовало несколько обратных прокси-серверов, поддерживающих разделение чтения и записи, например, MaxScale, ProxySQL и MySQL Router. ClusterControl версии 1.7.1 поддерживает развертывание MaxScale, ProxySQL и HAproxy для репликации master-slave прямо из пользовательского интерфейса.

5.3.1. MariaDB MaxScale

MariaDB MaxScale — это прокси базы данных, который позволяет пересылать операторы базы данных на один или несколько серверов баз данных MySQL/MariaDB. Последняя версия MaxScale 2.3 распространяется под лицензией MariaDB BSL, которую можно бесплатно использовать на двух серверах баз данных.

MaxScale поддерживает модульную архитектуру. Базовая концепция модулей позволяет расширять прокси-сервисы MaxScale. Текущая версия реализует разделение чтения и записи и балансировку нагрузки. MaxAdmin — это интерфейс командной строки, доступный с MaxScale, который позволяет выполнять расширенные проверки работоспособности, управлять пользователями, статусом и контролем операций MaxScale. ClusterControl дает вам прямой доступ к командам MaxAdmin. Вы можете добраться до него, перейдя на вкладку «Узлы», а затем щелкнув свой узел MaxScale:

Чтобы развернуть экземпляр MaxScale, просто выберите Управление > Балансировщик нагрузки > Установить MaxScale и укажите необходимую информацию. Выберите экземпляр сервера, который будет включен в набор балансировки нагрузки, и все готово. После развертывания вы можете просто отправить соединение MySQL на хост балансировщика нагрузки через порт 4008 для слушателя разделения чтения/записи или порт 4006 для прослушивателя циклического перебора.

Мы подробно рассказали об этом в этой записи блога. Примеры развертывания и дополнительные сведения см. в нашем блоге Как развернуть MaxScale и управлять им с помощью ClusterControl.

5.3.2. ProxySQL

ProxySQL — это новый высокопроизводительный прокси-сервер MySQL с лицензией GPL с открытым исходным кодом. Он был выпущен как общедоступный (GA) для производственного использования в конце 2015 года. Он принимает входящий трафик от клиентов MySQL и перенаправляет его на внутренние серверы MySQL. Он поддерживает различные топологии MySQL с такими возможностями, как маршрутизация запросов (например, разделение чтения/записи), сегментирование, перезапись запросов, зеркальное отображение запросов, объединение пулов соединений и многое другое.

ProxySQL для MySQL Репликация разработана на основе концепции групп хостов — различных наборов узлов, которые так или иначе связаны. Идея заключается в том, что при некоторых условиях (только две группы хостов, одна для ведущего и одна для всех ведомых, read_only используется для различения ведущего и ведомых) ProxySQL может начать отслеживать флаг только для чтения на этих хостах. Благодаря этому он может следить за изменениями топологии и автоматически вносить изменения в определения серверов для отражения топологии. ClusterControl использует флаг read_only для пометки главного (read_only=0) и подчиненного (read_only=1). Если вы продвинете ведомое устройство как новое ведущее вручную и соответствующим образом измените флаги read_only, ProxySQL сможет обнаружить такое изменение и переместить старый «главный» хост в группу «читателей», в то время как новый мастер будет перемещен в группу «писателей».

Чтобы развернуть экземпляр ProxySQL, просто выберите Управление > Балансировщик нагрузки > Установить ProxySQL и укажите необходимую информацию. Выберите экземпляр сервера, который будет включен в набор балансировки нагрузки, и укажите максимальную задержку репликации для каждого из них. По умолчанию ClusterControl настроит разделитель чтения/записи по умолчанию для кластера репликации MySQL. Любой базовый запрос на выборку будет направляться в группу хостов 20 (пул чтения), а все остальные запросы будут направляться в группу хостов 10 (главная).

После развертывания вы можете просто отправить соединение MySQL с хостом балансировщика нагрузки через порт 6033. На следующем снимке экрана показана группа хостов для чтения (группа хостов 20) с некоторой статистикой, полученной ProxySQL:

Мы рекомендуем вам прочитать дальше следующее. ресурсы для лучшего понимания ProxySQL:

  • Балансировка нагрузки MySQL с ProxySQL — обзор
  • Использование ClusterControl для развертывания и настройки ProxySQL поверх репликации MySQL
  • Советы и рекомендации — Как разделить MySQL с помощью ProxySQL в ClusterControl
5.
3.3. HAProxy (репликация Master-Slave)

HAProxy в качестве балансировщика нагрузки MySQL работает аналогично TCP-форвардеру, который работает на транспортном уровне модели TCP/IP. Он не понимает запросы MySQL (которые работают на более высоком уровне), которые он распределяет на внутренние серверы MySQL. Для настройки HAProxy для репликации MySQL требуются два разных прослушивателя HAProxy, например, порт 3307 для записи на главный сервер и порт 3308 для чтения на все доступные подчиненные устройства (включая главный).

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

  • Создайте/модифицируйте свое приложение, чтобы иметь возможность отправлять операции чтения и записи соответствующим прослушивателям.
  • Используйте соединитель приложений, который поддерживает встроенное разделение чтения/записи. Если вы используете Java, вы можете использовать Connecter/J. Для PHP вы можете использовать php-mysqlnd для master-slave. Это сведет к минимуму изменения на стороне приложения.

Чтобы создать экземпляр HAProxy для репликации master-slave, перейдите к Управление > Балансировщик нагрузки > Установить HAproxy и убедитесь, что установлен флажок «Установить для разделения чтения/записи» — это должно происходить по умолчанию для настроек репликации. На следующем снимке экрана показана статистика экземпляра HAproxy, развернутого ClusterControl для репликации MySQL:

Мы подробно рассмотрели HAproxy на странице нашего руководства «Балансировка нагрузки MySQL с HAproxy».

6. Аварийное переключение с помощью ClusterControl

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

Как определить, какое ведомое устройство является самым современным? Процесс отличается в зависимости от того, используете ли вы GTID или нет. GTID упрощает эту задачу, хотя вы можете столкнуться с такими проблемами, как ошибочные транзакции. Отказ репликации подробно описан в разделе «Стать администратором баз данных MySQL» — Общие операции — Изменения топологии репликации.

6.1. Automatic Failover of Master

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

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

  1. Если ClusterControl не может подключиться к главному серверу, ClusterControl помечает неисправный главный сервер как отключенный.
  2. Будет поднят аварийный сигнал, указывающий на сбой репликации, и все доступные узлы будут доступны только для чтения.
  3. ClusterControl выберет главного кандидата на основе replication_failover_whitelist, replication_failover_blacklist или самого последнего подчиненного устройства.
  4. ClusterControl проверит наличие ошибочных транзакций на главном кандидате. При наличии ошибочных транзакций процесс аварийного переключения будет остановлен, и будет поднято предупреждение, указывающее на сбой в процедурах аварийного переключения.
  5. Затем ClusterControl выполнит отработку отказа главного устройства, остановив все подчиненные устройства и выполнив оператор CHANGE MASTER для нового главного устройства.
  6. Если отработка отказа прошла успешно (все ведомые устройства запущены), ClusterControl помечает новый ведущий как доступный для записи (устанавливает read_only = 0), и сигнал тревоги сбрасывается.
  7. Затем обратный прокси-сервер должен соответствующим образом обновить набор балансировки нагрузки.

Если явно не отключено (replication_check_external_bf_failover=0), ClusterControl попытается подключиться к ведомым устройствам и экземплярам ProxySQL (если они доступны в настройках), чтобы проверить, могут ли эти узлы получить доступ к вышедшему из строя ведущему. Если некоторые из узлов могут это сделать, отработки отказа не произойдет. Скорее всего, есть сетевой раздел и ClusterControl почему-то не может напрямую подключиться к мастеру. Но поскольку мастер виден подчиненным устройствам и/или балансировщику нагрузки, значит, мастер все еще работает.

6.1.1. Белые и черные списки

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

  • replication_failover_whitelist — список IP-адресов или имен хостов подчиненных устройств ( через запятую), которые следует использовать в качестве потенциальных главных кандидатов. Если эта переменная установлена, будут учитываться только эти хосты.
  • replication_failover_blacklist – список хостов (через запятую), которые никогда не будут считаться главными кандидатами. Вы можете использовать его для списка ведомых устройств, которые используются для резервного копирования или аналитических запросов. Если аппаратное обеспечение ведомых устройств различается, вы можете указать здесь ведомые устройства, использующие более медленное оборудование.

В нашем случае мы хотели бы иметь мастер резервного копирования (10.0.0.202) в качестве следующего мастера всякий раз, когда происходит аварийное переключение. Таким образом, внутри файла конфигурации CMON (при условии, что cluster_id = 1), /etc/cmon.d/cmon_1.cnf, мы добавили следующую строку:

 replication_failover_whitelist=10.0.0.201,10.0.0.202 

Обратите внимание, что предпринята попытка аварийного переключения. Только однажды. Если попытка аварийного переключения не удалась, дальнейшие попытки не будут предприниматься до тех пор, пока контроллер не будет перезапущен. Вы можете заставить ClusterControl повторить попытку с более агрессивным подходом, указав replication_stop_on_error=0 в файле конфигурации CMON (однако есть вероятность, что подчиненные устройства могут нарушить репликацию). Или выполните аварийное переключение главного сервера вручную, как описано в следующем разделе.

6.2. Ручное переключение главного сервера при сбое

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

Если ведущий не работает, нам нужно сделать одного из подчиненных (резервный ведущий) ведущим. Для этого перейдите в ClusterControl > Узлы > выберите резервный главный узел > Продвиньте подчиненный узел 9.0128 :

Вам будет предложено следующее:

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

Когда старый ведущий снова появится, он будет запущен как только для чтения, а затем повторно синхронизироваться с новым мастером (резервным мастером) в качестве ведомого. Это действие автоматически организуется ClusterControl. На следующем снимке экрана показано, что старый мастер (10. 0.0.201) стал ведомым для резервного мастера (10.0.0.202) в репликации:

Старому мастеру (10.0.0.201), возможно, потребуется немного наверстать упущенное после запуска подчиненной службы, и после того, как он будет обновлен с новым мастером (10.0.0.202), он останется подчиненным.

6.3. Отказ ведомого устройства

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

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

На данный момент настройка репликации восстановлена ​​до исходной топологии.

6.4. Сценарии до и после отказа

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

  1. Replication_onfail_failover_script — этот сценарий выполняется, как только обнаруживается необходимость аварийного переключения. Если сценарий возвращает ненулевое значение, аварийное переключение будет прервано. Если сценарий определен, но не найден, отработка отказа будет прервана. Сценарию передаются четыре аргумента: arg1='все серверы' arg2='старый мастер' arg3='кандидат', arg4='ведомые устройства старого мастера' и передаются следующим образом: 'scripname arg1 arg2 arg3 arg4'. Сценарий должен быть доступен на контроллере и быть исполняемым.
  2. Replication_pre_failover_script — этот сценарий выполняется до того, как произойдет аварийное переключение, но после того, как кандидат был выбран, и можно продолжить процесс аварийного переключения. Если сценарий возвращает ненулевое значение, он принудительно прервет аварийное переключение. Если сценарий определен, но не найден, отработка отказа будет прервана. Сценарий должен быть доступен на контроллере и быть исполняемым.
  3. Replication_post_failover_script — этот сценарий выполняется после аварийного переключения. Если сценарий возвращает ненулевое значение, в журнал задания будет записано предупреждение. Сценарий должен быть доступен на контроллере и быть исполняемым.
  4. Replication_post_unsuccessful_failover_script — этот сценарий выполняется после неудачной попытки переключения. Если сценарий возвращает ненулевое значение, в журнал задания будет записано предупреждение. Сценарий должен быть доступен на контроллере и быть исполняемым.
  5. Replication_failed_reslave_failover_script — этот скрипт выполняется после того, как новый мастер был повышен, и если переподчинить слейвы новому мастеру не удалось. Если сценарий возвращает ненулевое значение, в журнал задания будет записано предупреждение. Сценарий должен быть доступен на контроллере и быть исполняемым.
  6. Replication_pre_switchover_script — этот сценарий выполняется до того, как произойдет переключение. Если сценарий возвращает ненулевое значение, это приведет к сбою переключения. Если сценарий определен, но не найден, переключение будет прервано. Сценарий должен быть доступен на контроллере и быть исполняемым.
  7. Replication_post_switchover_script — этот скрипт выполняется после того, как произошло переключение. Если сценарий возвращает ненулевое значение, в журнал задания будет записано предупреждение. Сценарий должен быть доступен на контроллере и быть исполняемым.

Как видите, хуки охватывают большинство случаев, когда вам может потребоваться выполнить некоторые действия — до и после переключения, до и после отработки отказа, при сбое повторного подчинения или при сбое отработки отказа. Все скрипты вызываются с четырьмя аргументами (которые могут обрабатываться или не обрабатываться в скрипте, скрипту не обязательно использовать их все): все серверы, имя хоста (или IP — как это определено в ClusterControl) старого мастера, имя хоста (или IP — как это определено в ClusterControl) кандидата в мастера и четвертого — все реплики старого мастера. Эти параметры должны позволять обрабатывать большинство случаев.

Все эти перехватчики должны быть определены в файле конфигурации для данного кластера (/etc/cmon.d/cmon_X.cnf, где X — идентификатор кластера). Пример может выглядеть так:

 replication_pre_failover_script=/usr/bin/stonith.py
replication_post_failover_script=/usr/bin/vipmove.sh 

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

6.4.1. Когда хуки могут быть полезны?

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

Shoot The Other Node In The Head (STONITH) — это процесс проверки того, что старый мастер, который мертв, останется мертвым (и да… мы не любим зомби, бродящих по нашей инфраструктуре). Последнее, что вам, вероятно, нужно, это иметь не отвечающий старый мастер, который затем снова подключается к сети, и в результате вы получаете два доступных для записи мастера. Существуют меры предосторожности, которые вы можете предпринять, чтобы убедиться, что старый мастер не будет использоваться, даже если он снова появится, и для него безопаснее оставаться в автономном режиме. Способы обеспечения этого будут отличаться от среды к среде. Поэтому, скорее всего, встроенной поддержки STONITH в инструменте отработки отказа не будет. В зависимости от среды вы можете выполнить команду CLI, которая остановит (и даже удалит) виртуальную машину, на которой работает старый мастер. Если у вас есть локальная установка, у вас может быть больше контроля над оборудованием. Можно было бы использовать какое-то удаленное управление (встроенный Lights-out или какой-либо другой удаленный доступ к серверу). У вас также может быть доступ к управляемым розеткам питания, и вы можете отключить питание в одной из них, чтобы убедиться, что сервер никогда не запустится снова без вмешательства человека.

6.4.1.1. Обнаружение служб

Мы уже немного упоминали об обнаружении служб. Существует множество способов хранения информации о топологии репликации и определения того, какой хост является ведущим. Определенно, одним из наиболее популярных вариантов является использование etc.d или Consul для хранения данных о текущей топологии. С его помощью приложение или прокси-сервер могут полагаться на эти данные для отправки трафика на правильный узел. ClusterControl (как и большинство инструментов, поддерживающих обработку отказоустойчивости) не имеет прямой интеграции ни с etc.d, ни с Consul. Задача обновления данных топологии лежит на пользователе. Она может использовать хуки, такие как replication_post_failover_script или replication_post_switchover_script, чтобы вызвать некоторые сценарии и внести необходимые изменения. Другим довольно распространенным решением является использование DNS для направления трафика на правильные экземпляры. Если вы будете поддерживать низкое время жизни записи DNS, вы сможете определить домен, который будет указывать на ваш мастер (например, writes.cluster1.example.com). Для этого требуется изменить записи DNS, и, опять же, хуки, такие как replication_post_failover_script или replication_post_switchover_script, могут быть очень полезны для внесения необходимых изменений после аварийного переключения.

6.4.1.2. Реконфигурация прокси-сервера

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

6.4.1.3. Дополнительное ведение журнала

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

7. Операции — Управление вашей настройкой репликации MySQL

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

7.1. Показать состояние репликации

Вы можете найти сводку состояния репликации MySQL непосредственно из панели сводки в списке кластера базы данных. Статус кластера репликации может быть ACTIVE, FAILED или DEGRADED.

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

7.2. Запуск/остановка репликации

ClusterControl поддерживает запуск и остановку ведомого устройства из пользовательского интерфейса. Это похоже на выполнение «STOP SLAVE» и «START SLAVE» через командную строку.

Если остановлены потоки SQL или IO, ClusterControl выведет дополнительную опцию для запуска/остановки потока.

7.3. Повышение уровня ведомого устройства

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

7.4. Rebuild Replication Slave

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

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

Процесс подготовки будет выполняться Percona Xtrabackup из-за возможности горячего резервного копирования в механизме хранения InnoDB. Если у вас есть таблицы MyISAM, FLUSH TABLES произойдет в конце процесса резервного копирования, и в это время выбранный мастер будет на мгновение доступен только для чтения.

7.5. Резервное копирование

Ранее мы писали в блоге о стратегиях резервного копирования для MySQL. ClusterControl поддерживает mysqldump и xtrabackup (полное и инкрементное) для резервного копирования. Резервное копирование может выполняться или планироваться на любом узле базы данных (главном или подчиненном) и храниться локально или централизованно на узле ClusterControl. При хранении резервных копий на узле ClusterControl резервная копия сначала создается на целевом узле базы данных, а затем передается с помощью netcat на узел контроллера. Вы также можете выбрать резервное копирование отдельных баз данных или всех баз данных. Ход резервного копирования доступен под ним, и вы будете получать уведомление о состоянии резервного копирования каждый раз, когда оно создается.

Чтобы создать резервную копию, просто перейдите к Резервное копирование > Создать резервную копию и укажите необходимые данные:

Чтобы запланировать резервное копирование, нажмите «Расписание резервного копирования» и настройте расписание соответствующим образом:

Резервные копии, созданные ClusterControl, могут быть восстановлены на один из узлов базы данных.

7.6. Restore

ClusterControl имеет возможность восстанавливать резервные копии (mysqldump и xtrabackup), созданные ClusterControl или извне с помощью какого-либо другого инструмента. Для внешнего резервного копирования файлы резервных копий должны существовать на узле ClusterControl, и поддерживаются только расширения xbstream, xbstream.gz и tar.gz.

Все добавочные резервные копии автоматически группируются под последней полной резервной копией и расширяются с помощью раскрывающегося списка. Каждая созданная резервная копия будет иметь кнопки «Восстановить» и «Журнал»:

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

Если резервная копия была сделана с помощью Percona Xtrabackup, репликацию необходимо остановить. Будут выполнены следующие шаги:

  1. Остановить все узлы в настройке репликации.
  2. Скопируйте файлы резервной копии на выбранный сервер.
  3. Восстановить резервную копию.
  4. После завершения задания восстановления запустите восстановленный узел в разделе ClusterControl > Nodes > выберите восстановленный узел > Start Node .
  5. После запуска повысьте узел как новый главный (если он не был ведущим) в ClusterControl > Nodes > выберите восстановленный узел > Повысьте уровень ведомого .
  6. На каждом подчиненном устройстве перестройте подчиненное устройство репликации, выбрав ClusterControl > Nodes > slave node > Stage Replication Slave .

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

Важнейшей частью резервного копирования является восстановление. Основная проблема заключается в том, что вы не можете сказать, будет ли резервная копия работать, пока не попытаетесь ее восстановить. Каждая резервная копия — это резервная копия Шредингера — она может работать или нет, и вы не можете определить ее состояние, пока не будет предпринята попытка восстановления. Вот почему тестирование резервных копий является обязательным. ClusterControl предоставляет вам простой способ сделать это. При планировании резервного копирования вы можете решить, запускать тест восстановления или нет.

Когда вы решите это сделать, вам будет предложен другой набор опций.

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

Аналогичный случай, когда вы пытаетесь восстановить одну из резервных копий:

Вы можете либо восстановить ее в кластере, либо запустить восстановление резервной копии на автономном хосте. Здесь, помимо тестирования и проверки резервных копий, одним из вариантов использования является снижение потери данных при восстановлении частично удаленных данных. Предположим, у вас есть большой набор данных, и вы не делаете логических резервных копий с помощью mysqldump из-за времени, необходимого для создания такой резервной копии. Предположим, что небольшая таблица или подмножество строк были удалены или обновлены по ошибке. Если вы восстановите весь набор данных, вы потеряете все изменения, которые произошли впоследствии. Вместо этого вы можете использовать эту опцию для восстановления набора резервных копий на отдельном узле, поддерживать его работоспособность, а затем извлекать (используя SELECT INTO OUTFILE или любым другим способом) только нужные данные, а затем загружать их на главный сервер.

7.7. Обновление программного обеспечения

Вы можете выполнить обновление программного обеспечения базы данных через ClusterControl > Manage > Upgrades > Upgrade . Обновления находятся в режиме онлайн и выполняются на одном узле за раз. Один узел будет остановлен, затем программное обеспечение будет обновлено с помощью диспетчера пакетов, и, наконец, узел снова запустится. Если узел не может обновиться, процесс обновления прерывается. Обновления следует выполнять только тогда, когда на хостах базы данных минимально возможный трафик.

Вы можете отслеживать ход обновления MySQL из  ClusterControl > Activity > Jobs , как показано на следующем снимке экрана:

ClusterControl выполняет обновление настройки репликации MySQL, обновляя все подчиненные устройства по одному. После того, как все подчиненные устройства будут обновлены, убедитесь, что новая версия верна на странице обзора кластера. Затем продвиньте обновленного ведомого (используя «Promote Slave»), чтобы он стал новым ведущим. Наконец, обновите старый мастер, повторив тот же шаг обновления.

7.8. Изменения конфигурации

Системные переменные находятся в файле my.cnf. Некоторые переменные являются динамическими и могут быть установлены во время выполнения, другие нет. ClusterControl предоставляет интерфейс для одновременного обновления параметров конфигурации MySQL во всех экземплярах БД. Выберите экземпляр(ы) БД, группу конфигурации и параметр, и ClusterControl выполнит необходимые изменения с помощью SET GLOBAL (если возможно), а также сделает их постоянными в my.cnf.

Если требуется перезагрузка, ClusterControl подтвердит это в диалоговом окне журнала изменений конфигурации:

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

7.

9. Изменения схемы

Традиционно изменение схемы в MySQL было блокирующей операцией — таблица должна была быть заблокирована на время выполнения инструкции ALTER. В репликации MySQL некоторые команды ALTER могут блокировать запись на мастере и создавать задержку репликации. Причина в том, что репликация MySQL является однопоточной, и если поток SQL выполняет оператор ALTER, он не будет выполнять ничего другого. Также важно понимать, что ведомое устройство может начать репликацию изменения схемы только после того, как оно завершится на ведущем устройстве. Это приводит к значительному количеству времени, необходимому для завершения изменений на ведомом устройстве: время, необходимое для изменения на главном устройстве, плюс время, необходимое для изменения на ведомом устройстве.

К счастью, есть способы выполнить эту операцию в режиме онлайн:

  • Непрерывное обновление схемы — выведите один из ведомых из ротации, выполните ALTER, верните его, промойте и повторяйте, пока все ведомые не будут обновлены. Как только это будет сделано, повысьте уровень одного из ведомых до главного, запустите ALTER на старом ведущем, верните его в качестве ведомого.
  • Инструменты для изменения онлайн-схемы:
    • pt-online-schema-change by Percona
    • Online Schema Change от Facebook
    • gh-ost от GitHub

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

7.10. Изменения топологии

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

В зависимости от того, используете ли вы репликацию на основе GTID или стандартную репликацию с binlog, шаги аварийного переключения различаются и требуют особого внимания. Мы подробно обсуждали это на этом вебинаре, посвященном изменениям топологии репликации для MySQL и MariaDB, а также в этой записи блога — Операции DBA — изменения топологии репликации.

8. Проблемы и устранение неполадок

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

  • Аварийное переключение не происходит автоматически и должно выполняться квалифицированным специалистом.
  • Подчиненные устройства могут легко получить данные, отличные от данных ведущего устройства, из-за аппаратных проблем, программных ошибок или использования недетерминированных функций. Расходящиеся наборы данных на главном и подчиненном серверах приводят к остановке репликации.
  • Сбой мастера может привести к повреждению двоичного журнала. При перезапуске подчиненные серверы не смогут продолжить работу с последней позиции двоичного журнала.
  • Отработка отказа на основе GTID подвержена ошибочной транзакции. Мы опишем это далее в этом руководстве, а также в этом блоге.
  • Задержка подчиненного устройства может быть кошмаром, когда ваше приложение считывает устаревшие данные с подчиненного устройства.
  • Можно настроить двустороннюю репликацию между двумя серверами mysql. Однако кольцевые топологии не рекомендуются. Репликация MySQL в настоящее время не поддерживает какой-либо протокол блокировки между ведущим и подчиненным, чтобы гарантировать атомарность распределенного обновления между двумя разными серверами.

8.1. Состояние репликации

Состояние репликации можно проверить только с реплицирующего ведомого устройства с помощью следующего оператора:

 mysql> SHOW SLAVE STATUS\G
*************************** 1-й ряд ********************** *******
               Slave_IO_State: ожидание отправки события мастером
                  Мастер_Хост: 192.168.55.111
                  Мастер_Пользователь: подчиненный
                  Мастер_Порт: 3306
                Connect_Retry: 60
              Master_Log_File: binlog. 000005
          Read_Master_Log_Pos: 2980
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 3144
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: Да
            Slave_SQL_Running: Да
              Репликация_Do_DB:
          Репликация_игнорировать_БД:
           Репликация_Do_Table:
       Репликация_Игнорировать_Таблицу:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Последняя_Ошибка:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2980
              Relay_Log_Space: 3311
              Пока_Условие: Нет
               До_файла_журнала:
                До_Log_Pos: 0
           Master_SSL_Allowed: Нет
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Секунды_За_Мастером: 0
Master_SSL_Verify_Server_Cert: Нет
                Last_IO_Errno: 0
                Последняя_IO_Error:
               Last_SQL_Errno: 0
               Последняя_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: a2bac331-a899-11e5-98f0-000c29901dfb
             Master_Info_File: /var/lib/mysql/master. info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave прочитал весь журнал ретрансляции; ожидание подчиненного потока ввода-вывода, чтобы обновить его
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: a2bac331-a899-11e5-98f0-000c29901dfb:10-1937
            Executed_Gtid_Set: a2bac331-a899-11e5-98f0-000c29901dfb:1-1937 

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

 Slave_IO_Running: Да
    Slave_SQL_Running: Да
Секунды_За_Мастером: 0
     Master_Server_Id: 1 

Приведенное выше указывает на то, что потоки ввода-вывода и SQL подчиненного устройства выполняются, реплицируясь с главного сервера (идентификатор сервера = 1) без задержки репликации (где Seconds_Behind_Master равен 0). Помимо вышеупомянутого подчиненного статуса, вы также можете использовать следующие операторы:

  • SELECT @@global.gtid_executed — показывает примененные транзакции.
  • SELECT @@gtid_purged — показывает примененные, но уже очищенные двоичные журналы.

8.2. Задержка репликации

Задержка репликации — это количество секунд, в течение которых ведомое устройство отстает от ведущего. Если это произойдет, ваше приложение может считать старые данные с подчиненного устройства. Это несколько вносит недостаток на стороне приложения при извлечении данных из отстающего подчиненного устройства. Например, вы можете настроить приложение для извлечения данных, когда значение Seconds_Behing_Master равно только 0 на этом ведомом устройстве. В противном случае приложение возвращается к мастеру для получения данных. ProxySQL также можно настроить для отслеживания задержки подчиненного устройства.

Вы можете решить, что данный сервер не будет получать трафик, когда задержка репликации превышает «Максимальную задержку репликации», определенную для него. Как только задержка снова станет ниже порогового значения, ProxySQL снова начнет отправлять трафик на этот сервер.

Репликация MySQL работает с двумя потоками, IO_THREAD и SQL_THREAD. Для IO_THREAD ведомое устройство:

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

Пока SQL_THREAD, ведомое устройство:

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

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

Если ведомое устройство SQL_THREAD является источником задержек репликации, возможно, это связано с тем, что запросы, поступающие из потока репликации, выполняются на ведомом устройстве слишком долго. Иногда это происходит из-за разного оборудования между ведущим и подчиненным, разных индексов схемы или рабочей нагрузки. Более того, рабочая нагрузка подчиненной OLTP иногда вызывает задержки репликации из-за блокировки. Обратите внимание, что до MySQL 5.6 репликация была однопоточной, что могло быть еще одной причиной задержек в SQL_THREAD подчиненного устройства.

8.3. Дрейф данных

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

Вы можете использовать pt-table-checksum Percona Toolkit для проверки согласованности репликации в режиме онлайн, выполняя запросы контрольной суммы на мастере, что приводит к другим результатам на репликах, несовместимых с мастером. Затем вы можете применить отсутствующие транзакции вручную или использовать pt-table-sync для повторной синхронизации ведомого устройства.

Использование репликации на основе строк (путем установки binlog_format=ROW) также является надежным способом снижения риска дрейфа данных. При построчной репликации мастер записывает в двоичный журнал события, указывающие, как изменяются отдельные строки таблицы. Репликация мастера на ведомый работает путем копирования событий, представляющих изменения строки, на ведомый.

8.4. Ошибочная транзакция

Ошибочные транзакции — это транзакции, которые выполняются непосредственно на ведомом устройстве при репликации на основе GTID. Таким образом, они существуют только на конкретном подчиненном устройстве. Это может быть результатом ошибки, например, приложение записывает в ведомое устройство, а не в ведущее, или это может быть преднамеренно, например, вам нужны дополнительные таблицы для отчетов. Это может привести к повреждению данных или ошибке репликации, если ведомое устройство с ошибочной транзакцией будет повышено до нового ведущего устройства. Основная проблема с ошибочными транзакциями заключается в том, что при сбое ведомое устройство может выполнять транзакции, «приходящие из ниоткуда», которые могут незаметно повредить ваши данные или нарушить репликацию.

Если вы обнаружите ошибочную транзакцию на одном сервере, есть два способа преодолеть ошибочную транзакцию:

  • Либо зафиксировать пустую транзакцию с GTID ошибочной транзакции на всех остальных серверах;
  • Или удалите соответствующий GTID на неисправном ведомом устройстве.

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

8.5. Corrupted Slave

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

Во-первых, проверьте, происходит ли повреждение на главном или подчиненном устройстве. Хорошим индикатором является то, что если другие подчиненные устройства реплицируются без ошибок, наиболее вероятно, что поврежден только журнал ретрансляции на этом конкретном подчиненном устройстве. Чтобы это исправить, просто перенаправьте репликацию на подчиненном устройстве на Relay_Master_Log_FIle:Exec_Master_Log_Pos:9.0005

 (повреждено ведомое устройство)> СТОП ВЕДУЩЕГО;
(поврежденное ведомое устройство)> ЗАМЕНИТЬ ГЛАВНЫЙ НА master_log_file=Relay_Master_Log_File,master_log_pos=Exec_Master_Log_Pos;
(поврежденный ведомый)> SLAVE START; 

8.6. Рекомендации

  • Используйте репликацию на основе глобального идентификатора транзакции (GTID) для упрощения развертывания и аварийного переключения.
  • Используйте механизм хранения InnoDB, так как он обеспечивает полную возможность транзакций с соответствием требованиям ACID и улучшенным восстановлением после сбоев.
  • Репликация выполняется только в одном направлении, приложения записывают только на мастер.
  • Разверните мастер резервного копирования, мастер отправляет изменения на мастер резервного копирования и на одно или несколько подчиненных устройств.
    • Использовать полусинхронную репликацию между главным и резервным главным.
    • Мастер отправляет обновление мастеру резервного копирования и ожидает фиксации транзакции.
    • Мастер резервного копирования получает обновление, записывает в свой журнал ретрансляции и сбрасывает на диск. Затем мастер резервного копирования подтверждает получение транзакции мастеру.
    • Мастер продолжает фиксацию транзакции.
    • Полусинхронная репликация влияет на производительность, но риск потери данных минимален.
  • Используйте только процесс репликации для внесения изменений на ведомых устройствах, чтобы свести к минимуму риск конфликтов данных на ведомых устройствах. Поэтому ведомые устройства должны запускаться в режиме только для чтения. Приложения не смогут изменять данные непосредственно на ведомых устройствах, но процесс репликации по-прежнему будет работать на сервере, доступном только для чтения.
  • Репликация отправляет большие пакеты между серверами, max_allowed_packet имеет высокое значение, чтобы избежать ошибок репликации.
  • Двоичные журналы должны быть доступны для обновления нового подчиненного устройства. Для подготовки нового ведомого устройства требуется комбинация последнего резервного копирования и всех транзакций, которые произошли после резервного копирования.
  • Параметры соединения репликации не должны помещаться в файл my.cnf. Например, файл конфигурации ведомого устройства может быть перезаписан, и он не знает, с какой точки продолжить репликацию.

Репликация базы данных MySQL: 2 простых метода

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

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

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

Содержание

  • Что такое MySQL?
  • Что такое репликация в MySQL?
  • Предварительные условия
  • Методы репликации базы данных MySQL
    • Метод 1. Использование подхода «главный-подчиненный» для репликации базы данных MySQL
      • Шаг 1. Настройка брандмауэра исходного сервера
      • Шаг 2. Настройка главного сервера
      • Шаг 93:105 a Пользователь репликации
      • Шаг 4. Настройка подчиненного сервера
    • Способ 2. Использование данных Hevo для репликации базы данных MySQL
  • Заключение

Что такое MySQL?

Источник изображения: logo.wine

MySQL — одна из самых популярных, широко используемых и надежных СУБД с открытым исходным кодом (система управления реляционными базами данных), доступная бесплатно по общедоступной лицензии GNU, также известной как проприетарная версия премиум-класса. . Это позволяет пользователям использовать запросы на основе SQL (язык структурированных запросов) для доступа, добавления и удаления данных из нужной базы данных.

Майкл Видениус первоначально разработал MySQL в MySQL AB, шведской компании, позже приобретенной Sun Microsystems, а затем Oracle. Многие известные организации, такие как Facebook, YouTube, Flickr и т. д., используют MySQL для управления своими потребностями в данных.

Основные характеристики MySQL

  • Безопасность: MySQL предоставляет пользователям функции безопасности корпоративного уровня, позволяя только авторизованному персоналу получать доступ к данным.
  • Совместимость : MySQL обеспечивает поддержку всех основных современных платформ и операционных систем, таких как Windows, Linux, Unix и т. д. 
  • Масштабируемость : Он имеет масштабируемую и надежную архитектуру, которая обеспечивает высокую производительность даже при работе с сложные запросы и большие объемы данных.
  • Smooth Experience:  MySQL легко загрузить, установить, легко использовать, а также она доступна бесплатно.

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

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

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

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

В механизме репликации master-slave репликация данных происходит как односторонний процесс и, следовательно, позволяет пользователям копировать данные только с главного сервера и сохранять их на подчиненных серверах. Здесь главный сервер отвечает за выполнение операций записи, а подчиненные — за операции чтения.

Способ 1: использование подхода «главный-подчиненный» для репликации базы данных MySQL

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

Метод 2: использование Hevo Data для репликации базы данных MySQL

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

Отказоустойчивый конвейер данных Hevo предлагает более быстрый способ перемещения ваших данных из MySQL и 100+ других источников данных (включая 40+ бесплатных источников данных)  в хранилища данных, базы данных, инструменты BI или любое другое место назначения по вашему выбору. . Hevo возьмет на себя полную ответственность за процесс репликации данных, что позволит вам сосредоточиться на ключевых бизнес-операциях.

Предпосылки

  • Знание MySQL.
  • Знание MySQL Workbench.
  • База данных MySQL.
  • Общее представление о репликации данных.
  • Общее представление о командах MySQL.

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

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

  • Метод 1: Использование подхода Master-Slave для репликации базы данных MySQL
  • Метод 2: Использование данных Hevo для репликации базы данных MySQL

Способ 1: использование подхода «ведущий-ведомый» для репликации базы данных MySQL

Вы можете использовать механизм master-slave для репликации базы данных MySQL, выполнив следующие действия:

  • Шаг 1. Настройка брандмауэра исходного сервера
  • Шаг 2. Настройка главного сервера
  • Шаг 3. Создание пользователя репликации
  • Шаг 4. Настройка подчиненного сервера

Шаг 1. Настройка брандмауэра исходного сервера

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

Выполнение этой конкретной команды разрешает любые соединения, исходящие с IP-адреса сервера-реплики, представленного replica_server_ip , к номеру порта MySQL по умолчанию, 3306 :

 sudo ufw allow from replica_server_ip на любой порт 3306 
90 replica_server_ip  с фактическим IP-адресом вашего сервера-реплики. После успешного добавления вы увидите следующий вывод:

 Правило добавлено.
 

Шаг 2. Настройка главного сервера

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

 /etc/mysql/my. cnf 

Открыв файл, измените адрес привязки мастер-сервер, изменив значение на фактический IP-адрес мастер-сервера:

 bind-address = 127.0.0.1
к
bind-address = 198.1.12.123 

Обновите раздел «mysqld», чтобы уведомить MySQL о главном сервере и указать основанные на журнале и другие параметры. Для этого откройте файл конфигурации и обновите его, добавив следующий код строк:

Здесь new_database — это база данных, которую мы хотим реплицировать.

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

 sudo service mysql restart 

Шаг 3: Создание пользователя репликации

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

 mysql -u корень -p
Введите пароль: 

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

 mysql> CREATE USER ‘slaveuser’@’%’ IDENTIFIED BY ‘PASSWORD’; 

Теперь, когда ваш ведомый пользователь настроен, предоставьте ему необходимые привилегии, используя следующую строку кода:

 mysql> ПРЕДОСТАВЬТЕ РЕПЛИКАЦИЮ SLAVE ON *.* TO 'slaveuser'@'%'; 

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

 mysql> FLUSH PRIVILEGES; 

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

 mysql> USE new_database;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> ПОКАЗАТЬ ГЛАВНЫЙ СТАТУС; 

После выполнения команд вы сможете увидеть на экране следующий вывод:

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

 mysqldump -u root -p новая_база_данных> новая_база_данных.sql
Введите пароль: 

После выполнения команды mysqldump появится новый файл резервной копии, известный как «new_database.sql». Переведите этот файл на подчиненный сервер, чтобы завершить процесс резервного копирования.

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

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

Шаг 4: Настройка подчиненного сервера

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

 mysql -u root -p
Введите пароль:
mysql> СОЗДАТЬ БАЗУ ДАННЫХ new_database;
mysql> выйти; 

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

 mysql -u root -p new_database < /PATH_TO_new_database.sql
Введите пароль: 

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

 идентификатор сервера = 2
log_bin = /данные/mysql/mysql-bin.log
binlog_do_db = новая_база данных 

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

 sudo service mysql restart 

Чтобы завершить процесс репликации MySQL, откройте новый терминал и обновите конфигурации master-slave следующим образом:

Вот как вы можете настроить подчиненный сервер и успешно реплицируйте базу данных MySQL.

Способ 2: использование данных Hevo для репликации базы данных MySQL

Источник изображения

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

Чтобы узнать больше, ознакомьтесь с документацией Hevo по репликации MySQL.

Узнайте, что делает Hevo удивительным:

  • Безопасный : Hevo имеет отказоустойчивую архитектуру, которая гарантирует, что данные обрабатываются безопасным, последовательным образом без потери данных.
  • Автоматическое сопоставление схем : Hevo устраняет утомительную задачу управления схемой и автоматически определяет схему входящих данных из MySQL и реплицирует ее в целевую схему.
  • Быстрая настройка : Hevo с его автоматизированными функциями можно настроить за минимальное время. Кроме того, благодаря простому и интерактивному пользовательскому интерфейсу новым клиентам очень легко работать и выполнять операции.
  • Преобразования : Hevo обеспечивает преобразования предварительной загрузки с помощью кода Python. Это также позволяет запускать код преобразования для каждого события в настроенных вами конвейерах данных. Вам необходимо отредактировать свойства объекта события, полученные в методе преобразования в качестве параметра, чтобы выполнить преобразование. Hevo также предлагает преобразования перетаскивания, такие как функции даты и управления, JSON и управление событиями, и это лишь некоторые из них. Их можно настроить и протестировать, прежде чем использовать для агрегации.
  • Hevo создана для масштабирования : По мере роста количества источников и объема ваших данных Hevo масштабируется горизонтально, обрабатывая миллионы записей в минуту с очень небольшой задержкой.
  • Добавочная загрузка данных : Hevo позволяет передавать измененные данные в режиме реального времени. Это обеспечивает эффективное использование полосы пропускания на обоих концах.
  • Онлайн-поддержка : команда Hevo доступна круглосуточно, чтобы предоставить своим клиентам исключительную поддержку через чат, электронную почту и звонки в службу поддержки.

Благодаря непрерывному перемещению данных в режиме реального времени Hevo позволяет вам реплицировать ваши данные MySQL вместе с другими источниками данных и беспрепятственно загружать их в место назначения по вашему выбору с помощью простого в настройке интерфейса без кода. Попробуйте нашу 14-дневную бесплатную пробную версию с полнофункциональным доступом!

Начните работу с Hevo бесплатно

Заключение

В этой статье вы узнаете, как легко реплицировать базу данных MySQL. Он предоставляет глубокие знания о концепциях, лежащих в основе каждого шага, чтобы помочь вам понять и эффективно реализовать их. Хотя вы можете использовать метод Master-Slave для настройки репликации MariaDB, как описано в этом посте, он довольно трудоемкий и требует глубоких технических знаний, и именно здесь Hevo спасает положение.

Hevo Data предоставляет автоматизированный конвейер данных без кода, который позволяет вам преодолеть вышеупомянутые ограничения. Hevo обслуживает более 100 источников данных (включая более 40 бесплатных источников) и может беспрепятственно выполнять репликацию MySQL в режиме реального времени. Отказоустойчивая архитектура Hevo обеспечивает согласованную и безопасную репликацию ваших данных MariaDB. Это упростит вашу жизнь и сделает репликацию данных беспроблемной.

Узнайте больше о Hevo

Хотите попробовать Hevo? Подпишитесь на 14-дневную бесплатную пробную версию и испытайте многофункциональный пакет Hevo на собственном опыте.

Расскажите нам о своем опыте обучения репликации базы данных MySQL! Поделитесь своими мыслями в разделе комментариев ниже!

Репликация MySQL Master Slave: 7 простых шагов

Репликация MySQL — это процесс, позволяющий одновременно копировать/реплицировать данные с одного сервера на другой. В основном это делается для повышения доступности данных. Одной из основных причин, по которой люди используют репликацию master-slave в MySQL, является восстановление данных. В случае какой-либо катастрофы или аппаратного сбоя репликация MySQL гарантирует, что точная резервная копия существует все время.

В этой статье вы подробно рассмотрите процесс репликации MySQL master-slave вместе с пошаговым руководством о том, как добиться репликации.

Содержание

  • Что такое MySQL?
  • Что такое репликация ведущий-ведомый?
  • Цель репликации Master-Slave
  • Предварительные условия
  • Шаги для достижения репликации MySQL Master-Slave
  • Использование конвейера данных Hevo без кода для бесперебойной репликации MySQL
  • Заключение

Что такое MySQL?

MySQL — одна из самых популярных и широко используемых СУБД с открытым исходным кодом (система управления реляционными базами данных). MySQL доступен бесплатно под общедоступной лицензией GNU, а также доступен как проприетарная версия премиум-класса. Первоначально MySQL был разработан Майклом Видениусом из шведской компании MySQL AB. В 2012 году Sun Microsystems приобрела MySQL AB, а позже Oracle приобрела Sun Microsystems.

MySQL используется для множества приложений и в основном построен на основе SQL (язык структурированных запросов). MySQL широко используется в приложениях, созданных с использованием PHP. Он также используется некоторыми популярными веб-сайтами, включая Twitter, Facebook, Mediawiki, YouTube и Flickr и т. д.

Что такое репликация ведущий-ведомый?

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

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

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

Назначение репликации Master-Slave

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

  • Масштабируемость : Все запросы к базе данных могут быть перенаправлены на несколько серверов базы данных, чтобы уменьшить нагрузку на сервер и обеспечить более быстрый доступ. Большинство веб-приложений и сайтов, с которыми вы сталкиваетесь в настоящее время, загружены большим количеством операций чтения, чем операций записи в базу данных. Следовательно, администраторы веб-сайтов должны обеспечить идеальную настройку для быстрой загрузки деталей на веб-сайт.
  • Производительность. Все операции записи базы данных выполняются в базе данных master. Как только эти изменения вносятся в основную базу данных, они передаются от главного к подчиненному. Но запросы на чтение с веб-сайтов могут быть разделены между несколькими ведомыми устройствами для повышения производительности веб-сайта.
  • Резервное копирование: можно просто реплицировать последний моментальный снимок базы данных в другую базу данных и создать резервную копию всего за пару минут. Повреждение данных значительно снижается, так как главный сервер работает без проблем и обеспечивает 99,9% Время безотказной работы. Это позволяет приложениям без проблем обрабатывать большое количество операций чтения или записи.
  • Аналитика и сравнительный анализ : Этот процесс позволяет аналитикам баз данных выполнять все виды тестов и экспериментов по анализу данных на ведомых устройствах, не мешая главному.

Предварительные условия

Для настройки репликации MySQL master-slave вам необходимо иметь следующее:

  • 2 VM (виртуальная машина) или VPS (виртуальный выделенный сервер) с корневым доступом.
  • Рабочий интернет.

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

Узнайте о 3 способах репликации баз данных и о том, какой из них вы предпочитаете.

Шаги для достижения репликации MySQL Master-Slave

Для этой демонстрационной цели вы будете вызывать master как [email protected] и slave как [email protected]

Для этой демонстрации предположим, что IP-адреса для главного и подчиненного устройства следующие:

Главный сервер: 12.34.56.111
Подчиненный сервер: 12.23.34.222

Чтобы начать настройку репликации MySQL master-slave, следуйте пошаговому руководству, представленному ниже: От главного к подчиненному

  • Настройка подчиненного сервера
  • Импорт дампа данных
  • Запуск подчиненного сервера
  • Проверка репликации MySQL Master-Slave
  • для установки и настройки главного сервера.

    Если вы не установили MySQL, вы можете установить MySQL с помощью следующей команды:

     [email protected]:~# sudo apt-get update
     [email protected]:~# sudo apt-get install mysql-server mysql-client -y
     [email protected]:~# sudo mysql_secure_installation
     

    После завершения процесса установки MySQL используйте следующую команду для редактирования файла конфигурации MySQL:

     [email protected]:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf 

    Далее , в том же файле найдите строку, содержащую bind-address = 127.0.0.1, и замените этот IP-адрес на IP-адрес вашего главного сервера репликации. Итак, строка будет иметь вид:
    bind-address = 12.34.56.111

    Далее найдите в файле следующие строки:

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

    Вы увидите, что приведенные выше строки были закомментированы, просто раскомментируйте эти строки и выйдите из интерфейса редактирования, нажав CTRL + X. Сохраните изменения и перезапустите службу MySQL. чтобы изменения вступили в силу.

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

     [email protected]:~# sudo service mysql restart 

    2. Создайте нового пользователя для подчиненного сервера

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

     [email protected]:~# mysql -uroot -p;
    mysql> СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ «slave» @ «12.34.56.789», ИДЕНТИФИЦИРОВАННОГО «SLAVE_PASSWORD»;
    mysql> ПРЕДОСТАВЬТЕ ПОДЧИНЕННУЮ РЕПЛИКАЦИЮ НА . ТО ‘ведомый’@’12.34.56.222’;
    mysql> УДАЛИТЬ ПРИВИЛЕГИИ;
    mysql> FLUSH TABLES WITH READ LOCK; 

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

     mysql> ПОКАЗАТЬ ГЛАВНЫЙ СТАТУС; 

    Эта команда также указывает ведомому устройству следовать за ведущим с этой позиции.

    3. Переместить данные с ведущего устройства на ведомое

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

     [email protected]:~# mysqldump -u root -p –all-databases –master-data > data.sql 

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

     scp data.sql [email protected] 

    Разблокируйте таблицы с помощью следующей команды:

     mysql> UNLOCK TABLES; 

    4. Настройка подчиненного сервера

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

     [email protected]:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf 

    Так же, как и для главного сервера, вам нужно привязать IP-адрес и раскомментировать эти две строки для подчиненного сервера.
    Теперь перезапустите сервер MySQL с помощью следующей команды:

     [email protected]:~# sudo service mysql restart 

    5.

    Импорт дампа данных

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

     [email protected]:~# mysql -uroot -p < data.sql 

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

     [email protected]:~# mysql -uroot -p;
    mysql> ОСТАНОВИТЬ ВЕДОМОЕ; 

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

    6. Запустить подчиненный сервер

    Затем используйте команду «Запустить подчиненный», чтобы запустить подчиненный сервер.

     ЗАПУСК ПОДЧИНЕННЫЙ; 

    7. Проверка репликации главного подчиненного сервера MySQL

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

    Создайте тестовую базу данных на главном сервере с именем «sampledb».

     СОЗДАТЬ БАЗУ ДАННЫХ sampledb; 

    Теперь войдите на свой подчиненный сервер и перечислите базы данных, и если вы увидите там «sampledb», значит, процесс репликации главного подчиненного устройства работает нормально.

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

     show databases; 

    Использование конвейера данных Hevo без кода для бесперебойной репликации MySQL

    Источник изображения

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

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

    Больше причин любить Hevo :

    • Безопасный : Hevo имеет отказоустойчивую архитектуру, которая обеспечивает безопасную и согласованную обработку данных без потери данных.
    • Автоматическое сопоставление схемы : Hevo устраняет утомительную задачу управления схемой и автоматически определяет схему входящих данных из файлов MySQL и сопоставляет ее с целевой схемой.
    • Быстрая настройка : Hevo с его автоматизированными функциями можно настроить за минимальное время. Кроме того, благодаря простому и интерактивному пользовательскому интерфейсу новым клиентам очень легко работать и выполнять операции.
    • Преобразования до и после загрузки : Используя преобразования в Hevo, вы можете подготовить данные несколькими способами перед их загрузкой в ​​место назначения. Hevo предлагает множество преобразований данных, включая очистку, повторное выражение, фильтрацию данных, нормализацию и т. д.
    • Hevo создан для масштабирования : По мере роста количества источников и объема ваших данных Hevo масштабируется горизонтально, обрабатывая миллионы записей в минуту с очень небольшой задержкой.
    • Поддержка в режиме реального времени : команда Hevo доступна круглосуточно, чтобы предоставить исключительную поддержку своим клиентам через чат, электронную почту и звонки в службу поддержки.

    Благодаря непрерывному перемещению данных в режиме реального времени загрузите свои данные из MySQL на целевой склад с помощью простого в настройке интерфейса Hevo без кода. Попробуйте нашу 14-дневную бесплатную пробную версию с полным доступом к .

    Начните работу с Hevo бесплатно

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

    Узнайте о 3 способах репликации баз данных и о том, какой из них вы предпочитаете.

    Заключение

    Вышеприведенная статья предоставила вам достаточно информации о том, как настроить репликацию MySQL master-slave. Пошаговое руководство специально предназначено для того, чтобы помочь новичкам понять процесс и приступить к тестированию процесса репликации в своей среде. Но этот процесс требует большой тяжелой работы, так как вам придется вручную настраивать многие детали. Итак, если вы хотите забыть об этих трудностях и испытать беспроблемный процесс, попробуйте Hevo.

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