Настройка репликации mysql. Что такое репликация в MySQL? MASTER: действия, выполняемые на Master-сервере MySQL

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

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

ЧТО ТАКОЕ MARIADB GALERA?

MariaDB Galera - это кластерная система для MariaDB типа master-master. Начиная с MariaDB 10.1 программное обеспечение Galera Server и MariaDB Server поставляются в одном пакете, так что вы получаете все необходимое программное обеспечение сразу. На данный момент MariaDB Galera может работать только с движками баз данных InnoDB и XtraDB. Из преимуществ использования репликации можно отметить добавление избыточности для базы данных сайта. Если одна из баз данных, даст сбой, то вы сразу же сможете переключиться на другой. Все сервера поддерживают синхронизированное состояние между собой и гарантируют отсутствие потерянных транзакций.

Основные возможности MariaDB Galera:

• Репликация с постоянной синхронизацией;
• Автоматическое объединение узлов;
• Возможность подключения нескольких узлов master;
• Поддержка записи на любой из узлов;
• Прозрачная параллельная репликация;
• Масштабируемость чтения и записи, минимальные задержки;
• Давшие сбой ноды автоматически отключаются от кластера;
• Нельзя блокировать доступ к таблицам.

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

В этой инструкции мы будем использовать для примера Ubuntu 16.04 и MariaDB версии 10.1. Перед тем, как начать полностью обновите систему:

sudo apt-get update -y
sudo apt-get upgrade -y

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

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

sudo add-apt-repository "deb http://ftp.utexas.edu/mariadb/repo/10.1/ubuntu xenial main"

sudo apt-get update -y

Когда обновление списка пакетов завершено, установите MariaDB командой:

sudo apt install mariadb-server rsync -y

Пакет rsync нам понадобится для выполнения непосредственно синхронизации. Когда установка будет завершена, вам необходимо защитить базу данных с помощью скрипта mysql_secure_installation:

sudo mysql_secure_installation

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

Enter current password for root (enter for none):
Change the root password? n
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y

Когда все будет готово, можно переходить к настройке нод, между которыми будет выполняться репликация баз данных mysql. Сначала рассмотрим настройку первой ноды. Можно поместить все настройки в my.cnf, но лучше будет создать отдельный файл для этих целей в папке /etc/mysql/conf.d/.

Добавьте такие строки:

binlog_format=ROW

innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

wsrep_on=ON





wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.56.101"
wsrep_node_name="Node1"

Здесь адрес 192.168.56.101 - это адрес текущей ноды. Дальше перейдите на другой сервер и создайте там такой же файл:

sudo vi /etc/mysql/conf.d/galera.cnf

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.56.101,192.168.56.102"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.56.102"
wsrep_node_name="Node2"

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

• binlog_format - формат лога, в котором будут сохраняться запросы, значение row сообщает, что там будут храниться двоичные данные;
• default-storage-engine - движок SQL таблиц, который мы будем использовать;
• innodb_autoinc_lock_mode - режим работы генератора значений AUTO_INCREMENT;
• bind-address - ip адрес, на котором программа будет слушать соединения, в нашем случае все ip адреса;
• wsrep_on - включает репликацию;
• wsrep_provider - библиотека, с помощью которой будет выполняться репликация;
• wsrep_cluster_name - имя кластера, должно соответствовать на всех нодах;
• wsrep_cluster_address - список адресов серверов, между которыми будет выполняться репликация баз данных mysql, через запятую;
• wsrep_sst_method - транспорт, который будет использоваться для передачи данных;
• wsrep_node_address - ip адрес текущей ноды;
• wsrep_node_name - имя текущей ноды.

Настройка репликации MySQL почти завершена. Остался последний штрих перед запуском - это настройка брандмауэра. Сначала включите инструмент управления правилами iptables в Ubuntu - UFW:

Затем откройте такие порты:

sudo ufw allow 3306/tcp
sudo ufw allow 4444/tcp
sudo ufw allow 4567/tcp
sudo ufw allow 4568/tcp
sudo ufw allow 4567/udp

ЗАПУСК MARIADB GALERA

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

sudo galera_new_cluster

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

Сейчас там только одна машина, теперь перейдите на другой сервер и запустите ноду там:

sudo systemctl start mysql

Вы можете проверить прошел ли запуск успешно и были ли какие-либо ошибки командой:

sudo systemctl status mysql

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

mysql -u root -p -e "show status like "wsrep_cluster_size""

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

mysql -u root -p

MariaDB [(none)]> create database test_db;
MariaDB [(none)]> show databases;

mysql -u root -p

MariaDB [(none)]> show databases;

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

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

Это самый простой способ установки подчиненного сервера, однако он не единственный. Например, если уже имеется образ головного сервера, на головном сервере уже установлен ID сервера и производятся записи в журнал, подчиненный сервер можно установить, не останавливая головной сервер и даже не устанавливая блокировки обновлений (за дополнительной информацией обращайтесь к разделу See section 4.10.7 Часто задаваемые вопросы по репликации .

Чтобы стать настоящим гуру по репликации в MySQL, советуем сначала изучить, осмыслить и опробовать все команды, упомянутые в разделе See section 4.10.6 SQL-команды, относящиеся к репликации . Необходимо также ознакомиться с опциями запуска репликации из файла `my.cnf" в разделе See section 4.10.5 Опции репликации в файле `my.cnf" .

1. Удостоверьтесь, что на головном и подчиненном(ых) серверах установлена свежая версия MySQL. Используйте версию 3.23.29 или выше. В предыдущих релизах применялся другой формат двоичного журнала и содержались ошибки, которые были исправлены в более новых релизах. Большая просьба: пожалуйста, не посылайте сообщения об ошибках, не проверив, присутствует ли эта ошибка в последнем релизе.
2. Установите на головном сервере отдельного пользователя для репликации с привилегией FILE (в версиях MySQL ниже 4.0.2) или REPLICATION SLAVE в более новых версиях MySQL. У этого пользователя должно быть также разрешение подсоединяться со всех подчиненных серверов. Если пользователь будет выполнять только репликацию (рекомендуется), то ему не нужно предоставлять какие-либо дополнительные привилегии. Например, чтобы создать пользователя с именем repl , который может иметь доступ к головному серверу с любого хоста, можно использовать такую команду: mysql> GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY " ";
3. Завершите работу MySQL на головном сервере. mysqladmin -u root -p shutdown
4. Создайте образ всех данных на головном сервере. Легче всего сделать это (на Unix), создав при помощи tar архив всей своей директории данных. Точное местоположение директории данных зависит от вашей инсталляции. tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir Пользователи Windows для создания архива каталога данных могут использовать WinZIP или другую подобную программу.
5. В my.cnf на головном сервере добавьте записи к разделу записи log-bin и server-id=уникальный номер к разделу и перезапустите сервер. Очень важно, чтобы ID подчиненного сервера отличался от ID головного сервера. Можно считать, что server-id играет роль IP-адреса - он уникально идентифицирует сервер среди участников репликации. log-bin server-id=1
6. Перезапустите MySQL на головном сервере.
7. Добавьте в my.cnf на подчиненном сервере(ах) следующий фрагмент: master-host= master-user= master-password= master-port= server-id= заменяя значения в значениями, соответствующими вашей системе. Значения server-id должны быть различными на каждом сервере, участвующем в репликации. Если значение server-id не определено, оно будет установлено в 1, если также не определено значение master-host , оно будет установлено в 2. Обратите внимание, что если значение server-id опущено, то головной сервер будет отказывать в соединении всем подчиненным серверам, а подчиненный сервер - отказывать в соединении головному серверу. Таким образом, опускать установку значения server-id можно лишь в случае резервного копирования с использованием двоичного журнала.
8. Скопируйте данные снимка в директорию данных на подчиненном сервере (ах). Удостоверьтесь в правильности привилегий для файлов и каталогов. Пользователь, от имени которого запускается MySQL, должен иметь возможность читать и записывать данные в них так же, как и на головном сервере.
9. Перезапустите подчиненный(ые) сервер(ы).

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

Если не установлен идентификатор server -id для подчиненного сервера, в журнальный файл регистрации ошибок будет внесена следующая ошибка:

Warning: one should set server_id to a non-0 value if master_host is set. The server will not act as a slave. (Предупреждение: если задан master_host, следует установить server_id в ненулевое значение. Сервер не будет работать как подчиненный сервер.)

Если не установлен идентификатор головного сервера, подчиненные серверы не смогут подключиться к головному серверу.

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

После того как подчиненный сервер начнет выполнять репликацию, в той же директории, где находится журнал регистрации ошибок, появится файл `master.info" . Файл `master.info" используется подчиненным сервером для отслеживания того, какие записи двоичных журналов головного сервера обработаны. Не удаляйте и не редактируйте этот файл, если не уверены в том, что это необходимо. Даже если такая уверенность есть, все равно лучше использовать команду CHANGE MASTER TO .

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

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

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

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

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

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 =

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

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

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 =

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

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

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

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

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

Mysql@master> SET GLOBAL read_only = OFF;

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

Root@master# tar -czf mysqldir.tar.gz /var/lib/mysql/

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

Root@master# mysqldump -u root -p --lock-all-tables > dbdump.sql

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

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

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

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

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

Root@replica# mysql -u root -p < dbdump.sql

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 от личный от мастера и от других узлов

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
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 необходимо:

• Убедится, что на сервер, выступающий в роли Slave, установлена версия MySQL >= версии, установленной на Master. Репликация возможна и в обратном порядке, с Master с более новой версией на Slave с более старой, но работоспособность такого варианта не гарантируется.
• Проверить подключение со Slave-сервера MySQL на Master (# mysql -hMASTERHOST -uroot -p), так как оно может быть закрыто в firewall.

Master-slave репликация одной базы MySQL

Это простой пример master-slave репликации одной базы MySQL . Тем кто это делает впервые, следует начать с этого примера и в точности соблюдать инструкции.

Для начала, нужно прописать различные id для Master и Slave серверов. На Master-сервере нужно включить бинарный журнал (log-bin), указать БД для репликации и создать пользователя подчиненного сервера, через которого slave-сервер будет получать данные с master`а. На slave-сервере включается релейный лог (relay-log), указывается БД для репликации и запускается slave-репликация.

MASTER: действия, выполняемые на Master-сервере MySQL.

Отредактировать my.cnf - конфигурационный файл MySQL. Его месторасположение зависит от операционной системы и настроек самой MySQL. В my.cnf, в секции добавляются такие параметры:

server- id= 1

# Путь к бинарному логу.
# Записывается название файла, без расширения, так как расширение все равно будет установлено
# MySQL-сервером автоматически (.000001, .000002 и т.д.)
# Располагать mysql-bin желательно в корне директории, где хранятся все БД,
# во избежание проблем с правами доступа.
log- bin =/ var/ lib/ mysql/ mysql- bin

# Название БД MySQL, которая будет реплицироваться

После модификации my.cnf следует перезапустить MySQL. В директории для хранения журнала бинарных логов (log-bin) должен появиться один или несколько файлов mysql-bin.000001, mysql-bin.000002, ... .

Теперь нужно подключиться к MySQL как пользователь с максимальными правами и создать пользователя (rpluser_s500) с паролем (заменить PASSW), через которого Slave-сервер будет получать данные об обновлениях БД:

mysql> GRANT replication slave ON * .* TO "rpluser_ s500" @"% " IDENTIFIED BY "PASSW" ;
mysql> FLUSH PRIVILEGES ;

$ mysqldump -- master- data - hHOST - uUSER - p dbreplica > dbreplica.sql

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

SALVE: действия, выполняемые на Slave-сервере MySQL.

Первым делом нужно провести правки my.cnf в секции :

# Идентификатор Master сервера (число от 1 до 4294967295)
server- id= 500

# Путь к релей-логу, в котором хранятся данные, полученные от Master-сервера
# Требования такие же, как и к бинарному логу.
relay- log =/ var/ lib/ mysql/ mysql- relay- bin
relay- log- index =/ var/ lib/ mysql/ mysql- relay- bin .index

# Имя базы, в которую будут записываться все изменения,
# происходящие в БД с тем же именем на Master-сервере
replicate- do- db= "dbreplica"

После модификации my.cnf - перезапустить MySQL.

mysql> CREATE DATABASE dbreplica

Теперь в неё нужно залить дамп:

$ mysql - uROOT - p dbreplica < dbreplica.sql

Далее настраиваем подключение к Master-серверу, где MASTER_HOSTNAME_OR_IP заменяется на адрес или ip MySQL master сервера, а MASTER_USER и PASSWORD - учетные данные пользователя, созданного на Master-сервере для подключения со Slave:

mysql> CHANGE MASTER TO MASTER_HOST = "MASTER_ HOSTNAME_ OR_ IP" , MASTER_USER = "rpluser_ s500" , PASSWORD = "PASSW" ;

После запуска этого запроса, в директории, где хранятся БД, создается файл master.info, куда записываются данные о подключении к Master.

Теперь, для начала репликации осталось отправить запрос к MySQL:

mysql> START SLAVE;

После этого, если все прошло успешно, можно наблюдать, как все изменения в БД на Master-сервере, появляются в БД на Slave.

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

Настройки бинарного лог-файла (log-bin)

Бинарный лог MySQL используется для ведения журнала изменений, происходящих в базах данных сервера. Для репликации он должен быть обязательно включен на Master-сервере, на Slave-серверах его стоит использовать, только если Slave является одновременно и Master`ом для другой подчиненной MySQL. Log bin включается, путем добавления параметра в mysql.cnf, секции :

log- bin = mysql- bin

В примере настроек: "Master-slave репликация одной базы MySQL" был включен бинарный лог для всех баз данных MySQL. Если нужно вести лог только для определенных БД, например DB_NAME1 и DB_NAME2 в my.cnf мастера нужно добавить опции binlog-do-db :

binlog- do- db= "DB_ NAME1"
binlog- do- db= "DB_ NAME2"

То есть нужно перечислить все наименования БД, где для каждой БД своя строка с параметром binlog-do-db. Антонимом этого оператора является binlog-ignore-db ="DB_NAME", который указывает MySQL, что нужно заносить в лог все базы данных, кроме тех, что указанны в параметрах binlog-ignore-db.

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

Неправильное использование параметра binlog-ignore-db!

binlog- ignore- db= "DB_ NAME3, DB_ NAME4"

то на первый взгляд все будет работать как нужно - никаких ошибок нет, но на самом деле, базы DB_NAME3 и DB_NAME4 не будут исключены из бинарного журнала: MySQL будет считать, что "DB_NAME3, DB_NAME4" это одна база данных с именем "DB_NAME3, DB_NAME4" (т.е. в имени БД находится запятая и пробел)!

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

Параметр, отвечающий за формат хранения данных бинарным журналом - binlog_format , который начиная с версии MySQL 5.1 может принимать 3 значения: STATEMENT (используется по умолчанию в MySQL = 5.7.7) и MIXED.

STATEMENT - режим бинарного лога MySQL

STATEMENT - в этом режиме в бинарный лог записываются обычные sql-запросы на добавление, обновление и удаление информации с дополнительными служебными данными. Открыв такой лог в текстовом редакторе, можно найти в нем запросы на изменение данных в БД в текстовом формате. Преимущества использования binlog_format=STATEMENT: сравнительно небольшой размер файла, возможность просматривать лог в mysqlbinlog или PHPMyAdmin`е. Недостатки же таятся в использовании SQL-запросов, подробнее об этом ниже.

Предположим, что в бинарный лог добавляются данные только для одной БД c названием users (binlog- do- db= "users" ). Следующий запрос, который непосредственно затрагивает базу данных "users", не попадет в бинарный журнал:

Пример № 1

USE clients;
UPDATE users.accounts SET amount= amount+ 5 ;

Такое поведение вызвано тем, что по умолчанию используется БД "clients", которая не логируется в бинарном журнале в режиме Statement.

Другой пример, когда запрос к БД, которая не указана в binlog-do-db, попадает в бинарный журнал:

Пример № 2

USE users;
UPDATE clients.discounts SET percentage= percentage+ 5 ;

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

И первый и второй запрос может привести к неожиданным последствиям, при использовании репликации на Slave сервере. В случае запроса из первого примера, данные на Master и Slave серверах будут различаться: на мастере amount=amount+5 выполнено, на Slave - нет. При использовании второго запроса, на Slave будет отправлен запрос на изменение данных в БД, которая не прописана в списке подчиненных, и Master-Slave репликация: завершится с ошибкой, если БД clients не существует на слейве или... внесет изменения в таблицу базы данных, если таковая есть. Таким образом, при Master-Slave репликации в режиме бинарного лога Statement, можно внести изменения в базу данных подчиненного сервера, которая не предназначалась для репликации! К каким последствиям может привести такие изменения, можно только догадываться, так что нужно быть очень осторожным, используя режим бинарного лога Statement.

Еще одна проблема, при использовании бинарного журнала в режиме Statement, может проявится, если на Slave сервере настроить запись в базы данных с именами, отличными от оригинала. Например, производится репликация одной БД с мастера db_countries на слейв, где эта же БД называется db_countries_slave (новое имя БД на Slave-сервере определяется параметром replicate-rewrite-db="db_countries->db_countries_slave", а для репликации уже назначается новое имя БД: replicate-do-db="db_countries_slave"). Пока на мастере производится обновление данных в БД с использованием USE db_countries и UPDATE names SET ..., все хорошо, но как только пройдет запрос, в котором будет указываться имя БД, например: UPDATE db_countries.names SET ... репликация на Slave останавливается с ошибкой: Table "db_countries.names" doesn"t exist" on query. Default database: "db_countries_slave". Query: UPDATE db_countries.names SET ... . В режиме ROW такой проблемы нет.

ROW - режим бинарного лога

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

В бинарный лог записываются только изменённые данные для тех баз данных, которые определены с помощью параметров binlog-do-db или binlog-ignore-db. База данных по умолчанию не влияет на это поведение. Благодаря этому, после запросов из примера 1 данные об обновлении попадут в бинарный лог, а вот sql из второго примера уже не будет записан.

Более подробное описание достоинств и недостатков режимов Statement и Row можно почерпнуть из официальной документации на английском: 17.1.2.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication .

MIXED - режим бинарного лога

MIXED - режим, в котором бинарный лог одновременно использует 2 режима репликации: Statement и Row для хранения данных о различных запросах. Более подробно узнать, как работает режим бинарного лога Mixed можно из официальной документации на английском: 5.4.4.3 Mixed Binary Logging Format . Нельзя сказать, что это идеальный вариант, но если понимать, как работает Mixed, то его вполне можно применять на практике.

Автоматическая очистка бинарного лога - expire_logs_days

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

Пример автоматического удаления бинарного лога, с даты создания которого прошло более 10 дней

expire_logs_days= 10

Другие полезные настройки бинарного лога

Пользователь для подключения Slave к Master

При Master-Slave репликации, необходима минимум одна учетная запись пользователя на Master-сервере, которая будет использоваться Slave для подключения. Требования к правам доступа такого аккаунта: единственная привилегия REPLICATION SLAVE - открывать доступы к базам данным, таблицам или добавлять любые другие привилегии - не нужно. Один пользователь с REPLICATION SLAVE может использоваться разными подчиненными серверами для одновременного получения данных с главного сервера, или можно для каждого подчиненного создать отдельного пользователя.

Не стоит применять для репликации учетную запись наделенную любыми расширенными правами доступа. Логин и пароль для подключения к главному серверу хранится в открытом виде на подчиненном (файл master.info в каталоге с БД).

mysql> CREATE USER "replicat" @"10.0.0.1" IDENTIFIED BY "pass" ;
mysql> GRANT REPLICATION SLAVE ON * .* TO "replicat" @"10.0.0.1" ;

IP-адрес 10.0.0.1 - это ip Slave-сервера, нужно заменить на реальный. В sql-запросах можно заменить IP-адрес на специальный символ %, тогда подключиться к мастеру можно будет с любого хоста, но из соображений безопасности, лучше ограничится реальным адресом подчиненного сервера.

Дополнительные настройки

Для максимально корректной репликации баз данных, в которых используются таблицы типа InnoDB и транзакции, необходимо добавить такие строки в конфигурацию Master-сервера (my.cnf секция ):

innodb_flush_log_at_trx_commit= 1
sync_binlog= 1