Меню

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

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

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

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

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

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

Создание сервера MySQL для использования в качестве реплики

Создайте новый сервер Базы данных Azure для MySQL.

Создайте новый сервер MySQL (например, replica.mysql.database.azure.com). Дополнительные сведения см. в статье о создании сервера Базы данных Azure для MySQL с помощью портала Azure. Этот сервер будет сервером-репликой в процессе репликации входных данных.

Сервер Базы данных Azure для MySQL необходимо создать в ценовой категории «Общего назначения» или «Оптимизировано для памяти».

Создайте одинаковые учетные записи пользователей и соответствующие привилегии.

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

Добавьте IP-адрес исходного сервера в правила брандмауэра для реплики.

Измените правила брандмауэра на портале Azure или с помощью Azure CLI.

Настройка исходного сервера

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

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

Убедитесь, что исходный сервер допускает входящий и исходящий трафик через порт 3306 и имеет общедоступный IP-адрес, DNS является общедоступным или имеет полное доменное имя (FQDN).

Проверьте подключение к исходному серверу, попытайтесь подключиться из такого средства, как Командная строка MySQL, размещенная на другом компьютере, или из Azure Cloud Shell , доступных в портал Azure.

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

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

Выполните приведенный ниже запрос.

Ниже приведен пример выходных данных:

Выйдите из командной строки MySQL.

Чтобы получить IP-адрес, выполните следующую команду в служебной программе Ping.

Настройте правила брандмауэра исходного сервера, чтобы включить выведенный на предыдущий шаг IP-адрес на порт 3306.

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

Включите ведение двоичного журнала.

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

Если переменная log_bin возвращается со значением «on», ведение двоичного журнала включено на сервере.

Если log_bin возвращается значение OFF и исходный сервер работает локально или на виртуальных машинах, где можно получить доступ к файлу конфигурации (My. cnf), можно выполнить следующие действия:

На исходном сервере откройте файл конфигурации MySQL (My. cnf). Например:/ЕТК/ми.КНФ

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

В разделе с MySQL добавьте следующую строку.

Перезапустите исходный сервер MySQL, чтобы изменения вступили в силу.

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

Параметры исходного сервера

Для Репликация входных данных требуется lower_case_table_names согласованность параметров между сервером источника и реплики. По умолчанию этот параметр в Базе данных Azure для MySQL равен 1.

Создайте новую роль репликации и настройте разрешения.

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

В следующих командах новая созданная роль репликации может обращаться к источнику с любого компьютера, а не только с компьютера, на котором размещен сам источник. Для этого следует указать «syncuser@’%'» в команде создания пользователя. Дополнительные сведения об указании имен учетных записей см. в документации по MySQL.

Команда SQL

Репликация с использованием SSL

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

Репликация без SSL

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

MySQL Workbench

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

Пользователи и привилегии

Введите имя пользователя в поле Login Name (Имя входа).

Синхронизация пользователя

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

Ведомая роль репликации

Установка исходного сервера в режиме только для чтения

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

Узнайте имя файла двоичного журнала и смещение.

Выполните show master status команду, чтобы определить текущее имя двоичного файла журнала и смещение.

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

Результаты состояния основного сервера

Дамп и восстановление исходного сервера

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

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

Задайте для параметра исходный сервер значение режим чтения/записи.

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

Восстановление файла дампа на новый сервер.

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

Связывание исходного и сервера реплики для запуска Репликация входных данных

Задайте исходный сервер.

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

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

master_host: имя узла исходного сервера

master_user: имя пользователя исходного сервера

master_password: пароль для исходного сервера

master_log_file: имя файла двоичного журнала из выполняемой команды show master status .

master_log_pos: позиция в двоичном журнале из выполняемой команды show master status .

master_ssl_ca: контекст сертификата ЦС. Если протокол SSL не используется, передайте пустую строку.

Читайте также:  О современном капитализме его кризисах и о том как на это должны отвечать современные марксисты Часть 1

Рекомендуется передавать этот параметр в виде переменной. Дополнительные сведения см. в следующих примерах.

Если исходный сервер размещен на виртуальной машине Azure, установите для параметра «разрешить доступ к службам Azure» значение «вкл.», чтобы разрешить серверу-источникам и репликам взаимодействовать друг с другом. Этот параметр можно изменить в параметрах Безопасность подключения. Дополнительные сведения см. в статье Управление правилами брандмауэра с помощью портала .

Примеры

Репликация с использованием SSL

Переменная @cert создается путем выполнения следующих команд MySQL:

Репликация с SSL настраивается между исходным сервером, размещенным в домене «companya.com», и сервером реплики, размещенным в базе данных Azure для MySQL. Эта хранимая процедура выполняется на реплике.

Репликация без SSL

Репликация без SSL настраивается между исходным сервером, размещенным в домене «companya.com», и сервером реплики, размещенным в базе данных Azure для MySQL. Эта хранимая процедура выполняется на реплике.

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

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

Чтобы обновить параметр, можно использовать портал Azure или Azure CLI.

Вызовите mysql.az_replication_start хранимую процедуру, чтобы запустить репликацию.

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

Вызовите show slave status команду на сервере реплики, чтобы просмотреть состояние репликации.

Если для параметра и задано Slave_IO_Running Slave_SQL_Running значение «Yes», а для параметра Seconds_Behind_Master равно «0», репликация работает правильно. Seconds_Behind_Master указывает величину задержки на реплике. Если значение не равно «0», это означает, что реплика обрабатывает обновления.

Другие хранимые процедуры

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

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

Удаление связи репликации

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

Пропуск ошибки репликации

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

Источник

Настройка репликации баз данных mysql

Настройка репликации баз данных mysql

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

Для чего можно использовать репликацию:
1. Распределение нагрузки между хостами для повышения производительности.
В такой схеме главный узел будет выполнять операции чтения и записи, узлы имеющие подписку на главном узле будут предоставлять базу для чтения, таким образом, мы разгрузим мастер сервер от операций чтения
2. Безопасность данных и удобство обслуживания, поскольку подчиненный узел содержит данные только для чтения, то изменение данных на подписчике будет ограничено, удобство обслуживания – возможность запускать процессы обслуживающие базу не прерывая работу приложений
3. Распределение данных на большие расстояния. Можно создать копию данных на любом хосте в независимости от его местоположения
Mysql поддерживает следующие методы репликации:
Традиционный — метод основан на тиражировании событий из бинарного файла лога мастера и требует файлы логов. Позиции между ведущим и ведомым серверами должны быть синхронизированы.
Метод с использованием глобальных идентификаторов транзакций GTIDs (транзакционный метод)
Mysql поддерживает следующие типы синхронизации:
асинхронную (односторонняя синхронизация)
полусинхронную (частичный контроль подписчиков)
синхронную (полный контроль подписчиков)

Настройка репликации баз данных Mysql традиционный метод

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

Настройка Мастера
my.ini должен содержать уникальный идентификатор – число от 1 до 2 в 32 степени – 1, server-id.
По умолчанию server-id=0, что означает не принимать подписки от подчиненных серверов
[mysqld]
log-bin=mysql-bin
server-id=1

Этих двух строк достаточно для запуска
Примечание: однако если используется InnoDB, то дополнительно рекомендуется внести
innodb_flush_log_at_trx_commit=1
sync_binlog=1

И нужно проверить, что не отключена возможность работать с сетью не выставлен параметр skip-networking
Ведомый сервер подключается к главному, используя имя пользователя и пароль, поэтому на мастер сервере предварительно создаем пользователя
CREATE USER repl@%.mydomain.com IDENTIFIED BY slavepass;
GRANT REPLICATION SLAVE ON *.* TO repl@%.mydomain.com;

Смотрим состояние
SHOW MASTER STATUS
Если ранее уже была запущена процедура создания бинарных журналов, то для таблиц InnoDB, предварительно в одном из сеансов нужно залочить таблицы
FLUSH TABLES WITH READ LOCK;
Если выйти из сеанса, то блокировка таблиц автоматически снимается
В другом сеансе получаем значения имени bin лога и позицию
Оба значения представляют собой координаты репликации при которых ведомый сервер должен начать чтение из файла в нужном месте, чтобы начать репликацию.
Следующий шаг зависит от того есть ли данные на ведомом сервере, данные от мастера
Если они есть, то оставляем таблицы залоченными, создаем dump (это рекомендуемый способ при использовании InnoDB)
Узнать тип базы можно командой
mysqlshow -u mysql_user -p -i database-name
Если база хранится в бинарных файлах, то допускается их копирование с ведущего на ведомый сервер
Делаем dump
mysqldump —all-databases —master-data dbdump.db
для выбора баз mysqldump —databases —master-data dbdump.db
Параметр master-data, автоматически добавляет CHANGE MASTER TO на подчиненном узле, если параметр не добавлять, то необходимо блокировать все таблицы в сессии в ручную
Снять блокировку
UNLOCK TABLES;

Настройка ведомого узла
Добавляем в my.ini server-id от личный от мастера и от других узлов
[mysqld]
server-id=2

Создаем подписку
CHANGE MASTER TO
MASTER_HOST=master_host_name,
MASTER_USER=replication_user_name,
MASTER_PASSWORD=replication_password,
MASTER_LOG_FILE=recorded_log_file_name,
MASTER_LOG_POS=recorded_log_position;

При настройке репликации с существующими данными нужно передать снимок от ведущего к ведомому перед началом репликации
Используем mysqldump
1.Запускаем подчиненный узел используя —skip-slave-start параметр, чтобы репликация не запускалась
2.Импортируем файл дампа
mysql fulldb.dump
3. Запускаем процесс подписки
START SLAVE;
Проверка состояния репликации
SHOW SLAVE STATUS\G
Slave_IO_State: — текущее состояние ведомого устройства
Slave_IO_Running: — читается ли поток данных с мастера
Slave_SQL_Running: — работают ли sql запросы , должно быть yes

Пример Настроим Мастер (ведущий) сервер – ip 11.11.11.10 В my.ini
[
mysqld] log-bin=mysql-bin server-id=1
Создаем пользователя mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO replica@% IDENTIFIED BY password; FLUSH PRIVILEGES;
Далее блокируем все таблицы в базе данных FLUSH TABLES WITH READ LOCK;
Смотрим статус SHOW MASTER STATUS; Запоминаем имя файла и позицию, их будем мы будем использовать на Ведомом сервере для подписки

На Слейве В my.ini
[mysqld] log-bin=mysql-bin server-id=2

Создаем подписку CHANGE MASTER TO MASTER_HOST=11.11.11.10, MASTER_PORT=3306,
MASTER_USER=replica, MASTER_PASSWORD=password,
MASTER_LOG_FILE=server-mysql-bin.000002,
MASTER_LOG_POS=1151664, MASTER_CONNECT_RETRY=10;
START SLAVE;
Статус репликации SHOW SLAVE STATUS\G

Репликация Mysql в режиме Master-Master настраиваем аналогично только в обе стороны, каждый сервер будет Slave и Master

CHANGE MASTER TO MASTER_LOG_FILE=’ ‘, MASTER_LOG_POS=

Источник



Mysql репликация только одной таблицы

Настройку репликации начинаем с сервера-мастера, первым делом необходимо указать в /var/db/mysql/my.cnf(для версий MySQL

#Запись всех запросов в бинлог log-bin #Идентификатор сервера #Идентификатор сервера я формирую из IP сервера #убрав точки server-id = 8925234107

Теперь переходим к клиенту, у него в my.cnf необходимо указать server-id:

Теперь мы делаем дамп данных на сервере — что бы поместить их на слейве, при этом, если вам нужна репликация только одной базы, то необходимо сдампить только ее, а в my.cnf на слейве добавить(в нашем примере база hilik):

Если вдобавок нужно исключить ряд таблиц из репликации впишем в my.cnf такие записи(исключаем из репликации таблицу node_stat), каждую таблицу и запись надо описать отдельной записью:

После тога как внесены все изменения, рестарутем mysql сервер на будущем мастере и слейве.
Теперь приступаем к дампу данных. В первую очередь, в случае, если у нас происходит активная работа с mysql, необходимо засечь приблизительно время, когда мы начали дампить данные, это нужно, что бы потом можно было отметить по бинлогу позицию с которой начинать реплицировать данные, а приблизительно, потому что, все равно надо будет взять позицию минут на 5-10 раньше начала дампа. Приступаем к дампу:
1. Дампим все данные(будет полная репликация):

Читайте также:  Таблица размеров детские converse

mysqldump -A -uroot -p >dump.sql

2. Дампим базу hilik:

mysqldump -uroot -p hilik >dump.sql

После того как данные сдамплены копируем дамп на слейв и вставляем их:

mysql -uroot -p
Теперь можно приступать к точке с которой нужно начинать репликацию. На сервере мастере, заходим в /var/db/mysql/, в этом каталоге у нас появился как минимум один файл такого вида:

так файл называется потому, что hostname у сервера на котором стоит mysql, sh.hilik.org.ua, соответсвенно имена бинлогов в вашем случае будут именоваться в соотвествии с хостнеймом вашего сервера. Определившись с именем бинлога приступаем к определению позиции репликации. Делается это с помощью утилиты mysqlbinlog. В том случае если, бинлог мы только включили и он единственный, вопросов никаких не возникает:

при выполнении этой команды появятся подобные строки:

Из преведенного на экране нас интересует только дата, время и запись сходная с «at 562». В нашем случае 562 определяет позицию в бин-лог файле, запроса который выполнялся 30-го октября 2008 в 13:19:22, так мы можем определиться с какой позиции стартовать репликацию.
На мастере выполняем:

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

GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’89.252.34.110’ IDENTIFIED BY ‘slave-333’;

При выполнении приведенной строки, следует заменить на свои параметры:
1. ‘slave’@’89.252.34.110’ — slave здесь имя пользователя для авторизации на репликацию, 89.252.34.110 — ip адрес сервера слейва
2. slave-333 — пароль для авторизации на репликацию
Теперь переходим непосредственно к слейву.
Запускаем mysql:

И в появившейся командной строке делаем:

CHANGE MASTER TO MASTER_HOST=’89.252.34.107′, MASTER_USER=’slave’, MASTER_PASSWORD=’slave-333′, MASTER_LOG_FILE=’sh-bin.000001′, MASTER_LOG_POS=562;

Здесь необходимо указать вместо:
1. MASTER_HOST=’89.252.34.107′ — указать ip своего мастера
2. MASTER_USER=’slave’ — указать пользователя для репликации
3. MASTER_PASSWORD=’slave-333′ — указать пароль для репликации
4. MASTER_LOG_FILE=’sh-bin.000001′ — указать имя бинлога в котором находится начальная точка, с которой должна стартовать репликация
5. MASTER_LOG_POS=562 — позиция в мастер бинлог файле.
После этого можно в консоли mysql сделать:

и проверить состояние слейва:

SHOW SLAVE STATUS;

если мы увидели подобное:

| Waiting for master to send event | 89.252.34.107 | slave | 3306 | 60 | sh-bin.000001 | 10710 | sh1-relay-bin.000001 | 10724 | sh-bin.000001 | Yes | Yes

Источник

Репликация баз данных MySQL. Введение

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

image

Что такое репликация, и зачем она нужна

Само по себе, понятие репликации означает процесс синхронизации нескольких копий объекта. В нашем случае, таким объектом является сервер БД, а наибольшую ценность представляют собой сами данные. Если мы имеем два и более серверов, и любым возможным способом поддерживаем синхронизированный набор данных на них — мы реализовали репликацию системы. Даже ручной вариант с mysqldump -> mysql load — это также репликация.

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

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

Как MySQL реплицирует данные

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

В общем виде, репликация в MySQL состоит из трех шагов:

  1. Мастер-сервер записывает изменения данных в журнал. Этот журнал называется двоичным журналом (binary log), а изменения — событиями двоичного журнала.
  2. Слейв копирует изменения двоичного журнала в свой, который называется журналом ретрансляции (relay log).
  3. Слейв воспроизводит изменения из журнала ретрансляции, применяя их к собственным данным.

Виды репликации

Существует два принципиально разных подхода к репликации: покомандная и построчная. В случае покомандной репликации, в журнал мастера протоколируются запросы изменения данных (INSERT, UPDATE, DELETE), а слейвы в точности воспроизводят те же команды у себя. При построчной же репликации в журнале окажутся непосредственно изменения строк в таблицах, и эти же фактические изменения применятся затем на слейве.

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

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

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

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

Пример построения простой репликации в MySQL

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

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

Читайте также:  Суммированный учет рабочего времени

Для мастер контейнера указано подключение volume c дампом world.sql, для того, чтобы имитировать наличие некоторой начальной базы на нем. При создании контейнера, mysql загрузит и выполнит sql скрипты, размещенные в директории docker-entrypoint-initdb.d.

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

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

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

В файл my.cnf в секции [mysqld] необходимо добавить следующие параметры:

При включении/выключении двоичного журнала необходима перезагрузка сервера. В случае с Docker перезагружается контейнер.

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

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

Далее, с помощью mysqldump сделаем экспорт данных из базы. Конечно, в данном примере можно использовать тот же world.sql, но приблизимся к более реалистичному сценарию.

После этого, необходимо еще раз выполнить команду SHOW MASTER STATUS, и запомнить или записать значения File и Position. Это, так называемые координаты двоичного журнала. Именно от них мы далее укажем стартовать слейву. Начиная с MySQL 5.6 стало возможным использование глобальных идентификаторов транзакций GTID вместо координат в виде файл-позиция. Это упростило настройку репликации, а также повысило стабильность ее работы. Но рассмотрение этой темы выходит за рамки данной статьи, и с ней можно ознакомиться в документации.

Теперь можем снова разблокировать мастер:

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

А затем изменим конфиг слейва, добавив параметры:

После этого перезагрузим слейв:

И теперь нам нужно указать слейву, какой сервер будет являться для него мастером, и откуда начинать реплицировать данные. Вместо MASTER_LOG_FILE и MASTER_LOG_POS необходимо подставить значения, полученные из SHOW MASTER STATUS на мастере. Эти параметры вместе называются координатами двоичного журнала.

Запустим воспроизведение журнала ретрансляции, и проверим статус репликации:

Если все прошло успешно, ваш статус должен иметь аналогичный вид. Ключевые параметры здесь:

  • Slave_IO_State, Slave_SQL_State — состояние IO потока, принимающего двоичный журнал с мастера, и состояние потока, применяющего журнал ретрансляции соотвественно. Только наличие обоих потоков свидетельствует об успешном процессе репликации.
  • Read_Master_Log_Pos — последняя позиция, прочитанная из журнала мастера.
  • Relay_Master_Log_File — текущий файл журнала мастера.
  • Seconds_Behind_Master — отставание слейва от мастера, в секундах.
  • Last_IO_Error, Last_SQL_Error — ошибки репликации, если они есть.

Попробуем изменить данные на мастере:

И проверить, появились ли они на слейве.

Отлично! Внесенная запись видна и на слейве. Поздравляю, теперь вы создали свою первую репликацию MySQL!

Заключение

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

  • Доклад «Как устроена MySQL-репликация» Андрея Аксенова (Sphinx)
  • Книга “MySQL по максимуму. Оптимизация, репликация, резервное копирование” — Бэрон Шварц, Петр Зайцев, Вадим Ткаченко
  • «Хайлоад» — здесь можно найти конкретные рецепты по репликации данных

Надеюсь, что данная статья была полезна, и буду рад отзывам и комментариям!

Источник

Репликация баз данных MySQL по типу Master/Slave

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

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

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

  • 12.34.56.789- Master
  • 12.23.34.456- Slave

Требования

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

sudo apt-get install mysql-server mysql-client

1: Настройка базы данных Master

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

sudo nano /etc/mysql/my.cnf

В данный файл нужно внести несколько изменений.

Для начала найдите раздел, который выглядит так (он связывает сервер с локальным хостом):

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

Следующее изменение касается директивы server-id, расположенной в разделе mysqld. Здесь можно задать любую переменную (возможно, проще всего начать с 1), но число должно быть уникальным и не совпадать ни с одним другим server-id в группе репликации.

Убедитесь, что строка раскомментирована:

Затем найдите строку log_bin. Она содержит детали о репликации. Slave-сервер будет копировать все изменения, зарегистрированные в журнале. В данном случае нужно просто раскомментировать строку log_bin:

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

Внеся все нужные изменения, сохраните их и закройте конфигурационный файл.

sudo service mysql restart

Остальные действия нужно выполнить в оболочке MySQL.

Откройте оболочку MySQL:

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

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;

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

В текущей вкладке откройте базу данных “newdatabase”.

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

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

Должна появиться подобная таблица:

С этой позиции slave БД начнет репликацию. Запишите эти числа, они пригодится позже.

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

База данных все еще должна оставаться заблокированной. Экспортируйте базу данных в новое окно с помощью mysqldump (следующую команду нужно выполнить в оболочке bash, а не MySQL).

mysqldump -u root -p —opt newdatabase > newdatabase.sql

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

UNLOCK TABLES;
QUIT;

Теперь master БД готова.

2: Настройка slave базы данных

Подготовив master БД, можно перейти к настройке slave БД.

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

CREATE DATABASE newdatabase;
EXIT;

Импортируйте ранее экспортированную из master базу данных.

mysql -u root -p newdatabase

Теперь нужно настроить slave таким же образом, как это было с master:

sudo nano /etc/mysql/my.cnf

Следуя советам предыдущего раздела, установите некоторые важные конфигурации. Начните с server-id; как упоминалось ранее, этот номер должен быть уникальным. Так как в предыдущем разделе было установлено значение 1, теперь нужно установить другое:

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

relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = newdatabase

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

Снова перезапустите MySQL:

sudo service mysql restart

Далее нужно активировать репликацию в оболочке MySQL.

Откройте оболочку MySQL и внесите следующие детали, заменяя значения по умолчанию.

CHANGE MASTER TO MASTER_HOST=’12.34.56.789′,MASTER_USER=’slave_user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS= 107;

Данная команда выполняет несколько действий:

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

Готово! master- и slave-сервер настроены.

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

SHOW SLAVE STATUS\G

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

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Источник