Настройка репликации баз данных 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

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

Как работает репликация MySQL

Для создания репликации необходимо, как минимум, 2 сервера. Один из них будет являться master-сервером, а второй (и другие) — slave. При выполнении операций в базе данных на master сервере, она записывает всю информацию в специальный файл — binlog. А slave сервер, в свою очередь, подключается к master серверу, сравнивает значения и если появляется новая информация — выполняет ее у себя в базе данных.

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

Для примера, мы возьмем 2 сервера. Устанавливать будем на примере Debian GNU/Linux. Настройка везде примерно одинакова за исключением того, что файл my.cnf в Debian (Ubuntu) находится в директории /etc/mysql/, а в CentOS — просто в /etc/.

• Master-сервер — 192.168.0.1
• Slave-сервер — 192.168.0.2

Настраиваем Master-сервер.

Для начала, нужно открыть файл my.cnf и указываем следующее:

# выбираем ID сервера, произвольное число, лучше начинать с 1
server-id = 1

# путь к бинарному логу
log_bin = /var/log/mysql/mysql-bin.log

# название базы данных, реплика которой необходима
binlog_do_db = websitedb

Затем, перезапускаем сервер MySQL командой

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

Добавляем необходимые права для пользователя slave_user (владельца нашей базы websitedb) и задаем ему пароль password:

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

Затем, нам нужно заблокировать все таблицы от всех изменений:

USE websitedb;
FLUSH TABLES WITH READ LOCK;

Затем, нам нужно проверить статус работы Master-сервера с помощью команды:

SHOW MASTER STATUS;

Если все сделано верно, Вы увидите сообщение с именем файла, с названием вида:

А так же запомните значение из столбца Position. В нашем случае, это 10.
Это имя нам потребуется при создании и запуска слейв-сервера.

Делаем свежий дамп базы данных:

mysqldump -u root -p websitedb > websitedb.sql

Копируем файл на слейв-сервер и на мастере подключаемся к MySQL еще раз под root’ом и выполняем команду, чтобы разблокировать таблицы:

Создаем базу на Slave-сервере

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

CREATE DATABASE websitedb;

И заливаем наш дамп, который мы уже скопировали командой:

Настройка репликации типа Master-Master на MySQL (MariaDB)

В MySQL существуют два типа репликации данных:

В данной статье мы рассмотрим репликацию Master-Master. При данной репликации данные, попавшие на тот или иной сервер в кластере будут реплицированы между собой.

Тестовый стенд:

• Master_1 — сервер на Centos 7 c адресом 192.168.15.2
• Master_2 — сервер на Centos 7 с адресом 192.168.15.3

Разделим процесс настройки репликации Master-Master на 2 этапа:

1. Организация Master-Slave репликации. Главным сервером будет выступать Master_1, а вспомогательным будет Master_2.
2. Организация обратной Master-Slave репликации — серверы будут синхронизировать информацию из логов соседа.

Установку MySQL мы рассматривали в данной статье, поэтому этот этап мы не будем рассматривать.

Настройка репликации типа Master-Master на MySQL

На двух серверах поднят MySQL. Первым делом нам необходимо провести редактирование файла my.cnf на каждом сервере.

Сервер Master_1 (192.168.15.2)

В открывшимся файле приводим всё к данному виду:

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

Указываем IP адрес для пользователя репликации:

Производим перезапуск MySQL сервера:

Первый Master-сервер развернут.

Проверяем статус созданного нами мастер сервера:

Далее переходим ко второму серверу Master_2 (192.168.15.3):

Редактируем файл аналогичным образом, как в конфиге ниже:

На данном этапе создаем папку и файлы, куда будут записываться логи):

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

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

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

На данном этапе мы настраиваем непосредственно репликацию. Запускаем репликацию на втором сервере (Master_2). Для того, чтобы это сделать нам необходимо узнать MASTER_LOG_FILE и MASTER_LOG_POS на первом сервере (Master_1).

Статус мастера на сервере Master_1

Обратим внимание, что MASTER_LOG_FILE это mysql-bin.000002, а MASTER_LOG_POS равен 107.

На втором сервере (Master_2) выполняем:

Далее производим репликацию на 1-ом сервере. Для этого нужно посмотреть статус мастера на Master_2 сервере:

Обратим внимание, что MASTER_LOG_FILE это mysql-bin.000002, а MASTER_LOG_POS равен 106.

На сервере Master_1 выполняем команду:

После всех действий просматриваем статус слейва на всех мастер-серверах:

Настройка репликации 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, там должны содержаться запросы на изменение данных в базе. Так этот файл бинарный необходимо сначала преобразовать его в текстовый формат:

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

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

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

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

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