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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MariaDB: Настройка репликации базы данных в режиме Master-Master/Slave

Репликация в SQL базах данных это процесс копирования данных из одного источника в другой (или на несколько) и в обратнуюм сторону. Данные с одного сервера баз данных, постоянно копируются на один или несколько других серверов. С помощью репликации можно распределять нагрузку на сервера, обеспечивать отказоустойчивость и высокую доступность баз данных MariaDB. СУБД MariaDB/MySQL позволяет использовать два типа репликации БД Master-Master и Master-Slave. В данной статье мы рассмотрим, как настроить оба типа репликации MariaDB в CentOS 7. Начнем!

Установка MariaDB.

Ранее мы размещали статью с описанием процесса установки MariaDB на CentOS 7. Ознакомиться с ней вы можете по ссылке https://winitpro.ru/index.php/2019/08/28/ustanovka-i-optimizaciya-mariadb/. Поэтому, заострять внимание на самой установке MariaDB мы не будем, а сразу перейдем к настройке репликации.

Настройка репликации Master-Master в MariaDB

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

Репликация основана на специально файле binlog, в который Master сервер сохраняет все операции с БД. Slave сервер подключается к мастеру и применяет команды к своим базам.

1. MariaDB: Настройка первого мастер сервера (Master-1)

Добавляем в наш конфигурационный файл my.cnf на первом сервере MariaDB следующие строки:

#replication
server-id = 1
report_host = master
log_bin = /var/lib/mysql/mariadb-bin
log_bin_index = /var/lib/mysql/mariadb-bin.index
relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index

service mariadb restart

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

mysql
create user ‘test_master’@’%’ identified by ‘test_master’;
grant replication slave on *.* to ‘test_master’@’%’;

Для добавления Slave нам понадобятся данные bin_log с сервера Master1.

MariaDB [(none)]> show master status;

Это будет наш Master-1.

2. MariaDB: Настройка второго мастер сервера (Master-2)

Подключимся ко второму MariaDB серверу, открываем конфигурационный файл my.cnf и добавляем информацию:

#replication
server-id = 2
report_host = master2
log_bin = /var/lib/mysql/mariadb-bin
log_bin_index = /var/lib/mysql/mariadb-bin.index
relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index

И так же создаем пользователя на втором сервере:

create user ‘test_master2’@’%’ identified by ‘test_master2’;
grant replication slave on *.* to ‘test_master2’@’%’;
Bin_log на Master-2:

MariaDB [(none)]> show master status;

Приступим к настройке подключения между серверами MariaDB в нашем програмном кластере:

Добавляем Master-1 на второй сервер:

CHANGE MASTER TO MASTER_HOST=’IP_master1′, MASTER_USER=’test_master’, MASTER_PASSWORD=’test_master’, MASTER_LOG_FILE=’mariadb-bin.000002′, MASTER_LOG_POS=664;

Подключаемся на Master-1 и выполним ту же процедуру, только указав уже данные второго нашего сервера:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST=’183.219.19.36′, MASTER_USER=’test_master2′, MASTER_PASSWORD=’test_master2′, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=667;
START SLAVE;

Проверим статус второго сервера:

show slave status \G

Как видим на скриншотах, коннекты на двух серверах есть, ошибок не наблюдается.

3. Проверка репликации между серверами MariaDB.

Далее, чтобы проверить, что репликация между двумя серверами MariaDB работает в режиме master+master и что она вообще работает, мы создадим новую базу на Master-1 и создадим в ней таблицу.

MariaDB [(none)]> create database master1;

MariaDB [(none)]> use master1;

MariaDB [master1]> CREATE TABLE hello (

-> AuthorID INT NOT NULL AUTO_INCREMENT,

Query OK, 0 rows affected (0.005 sec)

Проверяем, что база автоматически появилась и на втором мастере, и в ней также присутствует наша таблица:

MariaDB [(none)]> show databases;

MariaDB [(none)]> use master1;

MariaDB [master1]> show tables;

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

MariaDB [master1]> CREATE TABLE hello_master1 (

-> AuthorID INT NOT NULL AUTO_INCREMENT,

Таблица hello_master1 передалась на первый сервер:

MariaDB [master1]> show tables;

Как вы видите, новая таблица появилась на Master-1. Репликация работает так, как мы и хотели.

Настройка Master-Slave репликации в MariaDB

В данном варианте репликации один сервер выступает в роли Slave-сервера, на который постоянно передаются данные с Master. Все изменения, которые будут проводится на сервере Slave, передаваться на Master не будут. Это более отказоустойчивый тип репликации баз данных. Чаще всего используется именно такой вариант. В такой конфигурации у вас всегда будет backup-сервер с актуальными данными, а при сбое на Slave-серверах, информация на Master-сервере не будет потеряна. Так же можно распределить нагрузку на БД для вашего проекта, чтобы приложения осуществляли чтение со Slave серверов, а данные записывались только через Master сервер. Таким образом вы сводите к минимуму отклик БД.

При настройке реплики базы данных MariaDB по типу master + slave, мастер сервера (master1) настраивается как описано выше.

Переходим к slave серверу. Добавляем в my.cnf строки:

#replication
server-id = 2
report_host = slave2
log_bin = /var/lib/mysql/mariadb-bin
log_bin_index = /var/lib/mysql/mariadb-bin.index
relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index

Перезапускаем mariadb. На первом сервере берем данные bin_log.

MariaDB [(none)]> show master status;

На slave сервер в консоли консоли mysql выполняем следующее:

MariaDB [(none)]> STOP SLAVE;

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=’IP_master’, MASTER_USER=’test_master’, MASTER_PASSWORD=’test_master’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=664;

MariaDB [(none)]> START SLAVE;

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

Проверка статуса Slave: SHOW SLAVE STATUS\G;

Создаем БД на Master:

MariaDB [(none)]> create database master_hello;

Проверяем, что база данных создалась и на Slave сервер:

MariaDB [(none)]> show databases;

Создадим БД на Slave и проверим, передались ли данные на наш Master.

Как видите, базу мы создали, и она есть на Slave. Проверяем, появилась ли она на Master. Ее нет. Репликация со slave на master не идет.

То есть репликация MariaDB работает только в одну сторону. Сделаем еще одну проверку, удалив БД master_hello с Slave-сервера:

И проверим, не удалилась ли она на Master-сервере:

Как мы видим, все в порядке и база на месте.

P.S. При настройке реплики, вы можете столкнуться с некоторыми подводными камнями, самый частый из них — это firewall. По умолчанию на Centos 7 установлен брандмауэр firewalld, в котором закрыт порт 3306, который и использует MariaDB. Вы можете либо открыть данный порт через iptables, либо отключить ваш сетевой экран (плохой вариант).

По-умолчанию в конфигурации my.cnf в параметре bind-address указан IP адрес, на котором ожидаются подключения к базе ( bind-address = 127.0.0.1 ). Чтобы разрешить и локальные и внешние подключения, нужно раскомментировать эту строку и добавить правило iptables, разрешающее подключения с IP адреса мастер/слейв сервера порne 3306.

iptables -I INPUT -p tcp -s ip_address_slave_server —dport 3306 -j ACCEPT
iptables -I INPUT -p tcp —dport 3306 -j DROP

При первичной настройке я столкнулся с такой проблемой и она легко выявляется. Если запустить проверку статуса Slave «SHOW SLAVE STATUS\G;», вы увидите ошибку:

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

server-id = 1 — указываем ID сервера, обычно начинаем с 1, но можно использовать любую цифру, главное чтобы она не совпадала с другими серверами, которые будут задействованы в репликации.

report_host = master — обычно прописывается хостнейм сервера, можно указать IP-адрес

log_bin = /var/lib/mysql/mariadb-bin — путь до журнала обновлений

log_bin_index = /var/lib/mysql/mariadb-bin.index — позволяет узнать, какой журнал на данный момент активен и какие журналы ранее были использованы.

relay_log_index = /var/lib/mysql/relay-bin.index — сами логи репликации

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

replicate-do-db = имябд — если нужно несколько БД, перечисляем через запятую.

Исключение каких-либо БД из репликации:

Обычно исключаются служебные базы, такие как:

information_schema ,mysql и performance_schema

Время хранения bin_log:

expire_logs_days = 10 — где 10 это количество дней которые будут храниться логи.

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

На этом все наши настройки закончены. Думаю, с помощью данной статьи вы без проблем сможете настроить репликацию БД MariaDB как в режиме Master + Master, так и Master + Slave.