Настройка репликации Master-Slave в MySQL. Настройка репликации MySQL без остановки мастера Репликация баз данных 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 .

Настройка репликации MySQL без остановки мастера.

1. Настройка Master сервера:

Смотрим где должен лежать конфиг.

# ps aux | grep my.cnf

mysql 51189 0.0 0.0 17064 1912 — Is 6:35PM 0:00.05 /bin/sh /usr/local/bin/mysqld_safe —defaults-extra-file=/var/db/mysql/my.cnf —user=mysql —datadir=/var/db/mysql

Если файл отсутствует его можно скопировать из примера.

# cp /usr/local/share/mysql/my-small.cnf /var/db/mysql/my.cnf

Или создать пустой.

# touch /var/db/mysql/my.cnf

В созданный конфиг в секции пишем.

#Уникальный ID сервера. У мастера должен быть ниже реплики и не дублироваться

server — id = 1

#Формат лога

binlog — format = mixed

#Путь где будет лежать бинлог (По умолчанию размер одного лога 1г)

#Время хранения бинлогов

expire_logs_days = 30

replicate-do-db = database_1

replicate-do-db = database_2

replicate-do-db = database_3

replicate-do-db = database_4

#Лог ошибок

На этом закругляемся с редактированием и рестартим MySQL с новым конфигом.

# /usr/local/etc/rc.d/mysql-server restart

Теперь надо добавить пользователя на Master для Slave сервера.

Для репликации достаточно будет прав REPLICATION SLAVE. Заходим под root на cервер MySQL.

# mysql -uroot -p

Создаем пользователя:

mysql> use mysql;

mysql>CREATE USER ‘replica’@’ip_address_slave_server’ ;

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’ip_address_slave_server’ IDENTIFIED BY ‘password_for_user_replica’ ;

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

mysql>FLUSH PRIVILEGES;

2. Создаем дамп нужных баз:

Все базы.

# mysqldump -uroot -p —skip-lock-tables —single-transaction —flush-logs —hex-blob —master-data=2 -A > /usr/home/Timur/dump.sql

Определенные базы.

# mysqldump -uroot -p —skip-lock-tables —single-transaction —flush-logs —hex-blob —master-data=2 -B DATABASE DATABASE1 DATABASE2 DATABASE3 > /usr/home/Timur/dump.sql

3. Смотрим какой бинлог использовать и его позицию:

# head -n80 /usr/home/Timur/dump.sql | grep «MASTER_LOG_POS»

— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000049 ‘, MASTER_LOG_POS=107 ;

Желательно записать!!!

4. Жмем дамп и переносим на Slave сервер:

# gzip /usr/home/Timur/dump.sql

Переносим.

# scp /usr/home/Timur/dump.sql.gz _address_slave_server:/usr/home/Timur

5. Настройка Slave сервера (my.cnf).

server — id =2

binlog — format = mixed

log-bin=/var/log/mysql/mysql-bin

expire_logs_days = 30

#Бинлоги Slave

relay-log = /var/log/mysql/mysql-relay.log
relay-log-index = /var/log/mysql/mysql-relay-bin.index

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

log-slave-updates = 1

#Ставим базы только на чтение. На суперпользователей данная опция не распространяется!!!

read-only = 1

#Пропустить дублирующие записи. После того как Seconds_Behind_Master станет 0, закоментировать и ребутнуть SLAVE

slave-skip-errors=all

#Указываем какие базы нам нужно реплицировать

replicate-do-db = database_1

replicate-do-db = database_2

replicate-do-db = database_3

replicate-do-db = database_4

#Лог ошибок

log-error=/var/log/mysql/mysqld-error.log

#Для того чтобы при запуске сервера не стартовал Slave. Запустить можно в ручную START SLAVE;

skip-slave-start = On

Перезагружаем сервер (MySQL).

6. Заливаем дамп на Slave и стартуем репликацию:

Разахивируем.

# gunzip /usr/local/Timur/dump.sql.gz

Заливаем дамп.

# mysql -uroot -p < /usr/local/Timur/dump.sql

Говорим Slave откуда тащить данные и стартуем. MASTER_LOG_FILE и MASTER_LOG_POS берем то, что записали при дампе баз на Master 😉

mysql>CHANGE MASTER TO MASTER_HOST = ‘<>’ , MASTER_USER = ‘replica’ , MASTER_PASSWORD = ‘password_for_user_replica’ , MASTER_LOG_FILE = ‘mysql-bin.000049 ‘ , MASTER_LOG_POS =107 ; START SLAVE ;

Смотрим командой SHOW SLAVE STATUS\G все ли у нас стартануло.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: Тут адрес Master сервера
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000049
Read_Master_Log_Pos: 1919771
Relay_Log_File: mysql-relay.000050
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000049
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: database_1,database_2,database_3,database_4,database_1,database_2,database_3,database_4
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1919771
Relay_Log_Space: 3125
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 5
1 row in set (0.00 sec)

Все завелось.

Должен рости Exec_Master_Log_Pos: 1919771

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

mysql> STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE;

Всем доброго дня! Сегодня в нашей статье мы рассмотрим примеры настройки репликации типа “master-slave”.

Немного теории

Зачем нужна репликация? В первую очередь это подстраховка на случай, если основной mysql-сервер выйдет из строя, тогда можно переключиться на slave-сервер и продолжить работу. Во вторых, это возможность уменьшить нагрузку на основной сервер Mysql, используя master-сервер только для записи, а операции на чтение выполнять на slave-сервере. Как происходит репликация? Master-сервер пишет binlog-и, в которых указывает операции, которые выполняются над базой данных (базами данных) и запоминает смещение в журнале от его начала до текущей записи (позицию). Slave-сервер подключается к master-у, сравнивает значения позиций и считывает изменения в журнале начиная со значения собственной позиции и заканчивая значением позициии master-a. Изменения (команды) он применяет к базам данных на slave-сервере.

Установка и настройка Master

Изменяем my.cnf на головном сервере:

Server-id = 1 - указываем id сервера log_bin = /var/log/mysql/mysql-bin.log - наименое лога и его путь

Небольшое уточнение: по умолчанию, мастер пишет binlog-и для всех баз данных, это можно изменить с помощью "binlog-do-db". В логи будет записываться значения, когда будет использоваться определенная БД, изменения в остальных БД не будут записываться. Здесь же можно указать, сколько дней хранить логи, какой их максимальный размер (параметры expire_logs_days и max_binlog_size). Добавляем в MySQL пользователя, под правами которого будет производиться репликация:

GRANT replication slave ON *.* TO имя_пользователя@ip_slave_сервера IDENTIFIED BY "пароль";

replication slave - привилегия, позволяющая пользователю читать binlog-и. ip_slave_сервера - ip сервера, с которого будет подключаться пользователь. Перезагружаем mysql-сервер:

/etc/init.d/mysql restart

Проверяем работу мастера:

Show master status;

Должны увидеть название binlog-a и позицию в нем. При выполнении команд над БД, позиция будет меняться.

Настройка Slave

В файл my.cnf вносим измнения:

Server-id = 2 - идентификатор slave-сервера должен обязательно отличаться от идентификатора master. relay-log = /var/lib/mysql/mysql-relay-bin - как и двоичный журнал, состоит из набора пронумерованных файлов, содержащих события, которые описывают изменения в базе данных. relay-log-index = /var/lib/mysql/mysql-relay-bin.index - индексный файл, который содержит имена всех используемых файлов журналов relay. replicate-do-db = БД, которая будет реплицироваться.

Важное замечание! При организации cross db (когда используется одна БД, а данные обновляются в другой БД) в настройках мастер-сервера не нужно указывать binlog-do-db, binlog-и должны писаться для всех баз данных, а в настройках slave нужно вместо replicate-do-db указать replicate-wild-do-table=db_name.%, где db_name - имя реплицируемой БД. Перезагружаем mysql-сервер:

/etc/init.d/mysql restart

Включение репликации

SET GLOBAL read_only = ON;

Смотрим состояние master-a:

Show master status;

Запоминаем значения File и Position (а лучше их записать). Сейчас значение Position не должно изменяется. Делаем дамп мастера командой mysqldump:

Mysqldump -uname -ppassword db_master_name > dump_db,

где name - имя пользователя, password - пароль, db_master_name - имя БД, dump_db - название дампа. После завершения дампа разрешаем запись в БД:

SET GLOBAL read_only = OFF;

Переносим дамп на slave и разворачиваем

Mysql -uname -ppassword db_slave_name < dump_db

Настраиваем репликацию

CHANGE MASTER TO MASTER_HOST = “ip-мастера”, MASTER_USER = "имя_пользователя ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "название лога", MASTER_LOG_POS = позицию;

ip-мастера - ip сервера, на котором расположен master, имя_пользователя - имя пользователя, которого мы создали на master-е, название лога - значение File на мастере, когда делал дамп БД, позицию - значение Position на мастере, когда делал дамп БД. Запускаем slave:

Start slave;

Смотрим как идет репликация: На мастере: SHOW MASTER STATUS\G На slave: SHOW SLAVE STATUS\G

Настройки безопасности на master-сервере

Параметр bind-address в /etc/mysql/my.cnf задает, какой ip-адрес mysql-сервер будет слушать в ожидании соединения. Обычно он имеет значение bind-address = 127.0.0.1. Но после настройки slave-сервера, нам нужно разрешить подключение со slave-сервера и при этом должны работать локальные подключения. Bind-address может разрешить подключение только с одного ip либо со всех. Т.к. нам нужно указать больше одного ip для соединения, мы комментируем строку с bind-address = 127.0.0.1. Теперь mysql-сервер будет принимать соединения со всех ip-адресов, что очень опасно. Решить эту проблему нам поможет iptables:

Iptables -I INPUT -p tcp -s ip_slave_server-a --dport 3306 -j ACCEPT -в начале разрешаем подключение с ip-адреса slave-сервера iptables -I INPUT -p tcp --dport 3306 -j DROP - потом запрещаем подключение со всех остальных ip-адресов.

Теперь у нас будет работать 2 MySQL сервера в режиме master-slave, что существенно повышает надежность сайта и для некоторых Drupal сайтов помогает увеличить скорость работы. В следующей статье рассмотрим переключение между режимами master и slave в случае падения master сервера.

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

Небольшое введение

Репликация (от лат. replico -повторяю) — это тиражирование изменений данных с главного сервера БД на одном или нескольких зависимых серверах. Главный сервер будем называть мастером , а зависимые — репликами .
Изменения данных, происходящие на мастере, повторяются на репликах (но не наоборот). Поэтому запросы на изменение данных (INSERT, UPDATE, DELETE и т. д.) выполняются только на мастере, а запросы на чтение данных (проще говоря, SELECT) могут выполняться как на репликах, так и на мастере. Процесс репликации на одной из реплик не влияет на работу других реплик, и практически не влияет на работу мастера.
Репликация производится при помощи бинарных логов , ведущихся на мастере. В них сохраняются все запросы, приводящие (или потенциально приводящие) к изменениям в БД (запросы сохраняются не в явном виде, поэтому если захочется их посмотреть, придется воспользоваться утилитой mysqlbinlog). Бинлоги передаются на реплики (бинлог, скачанный с мастера, называется "relay binlog ") и сохраненные запросы выполняются, начиная с определенной позиции. Важно понимать, что при репликации передаются не сами измененные данные, а только запросы, вызывающие изменения.
При репликации содержимое БД дублируется на нескольких серверах. Зачем необходимо прибегать к дублированию? Есть несколько причин:

• производительность и масштабируемость . Один сервер может не справляться с нагрузкой, вызываемой одновременными операциями чтения и записи в БД. Выгода от создания реплик будет тем больше, чем больше операций чтения приходится на одну операцию записи в вашей системе.
• отказоустойчивость . В случае отказа реплики, все запросы чтения можно безопасно перевести на мастера. Если откажет мастер, запросы записи можно перевести на реплику (после того, как мастер будет восстановлен, он может принять на себя роль реплики).
• резервирование данных . Реплику можно «тормознуть » на время, чтобы выполнить mysqldump, а мастер - нет.
• отложенные вычисления . Тяжелые и медленные SQL-запросы можно выполнять на отдельной реплике, не боясь помешать нормальной работе всей системы.

Кроме того, есть некоторые другие интересные возможности. Поскольку на реплики передаются не сами данные, а запросы, вызывающие их изменения, мы можем использовать различную структуру таблиц на мастере и репликах. В частности, может отличаться тип таблицы (engine) или набор индексов. Например, для осуществления полнотекстового поиска мы можем на реплике использовать тип таблицы MyISAM, несмотря на то, что мастер будет использовать InnoDB.

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

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

• IP-адрес мастера 192.168.1.101, реплики — 192.168.1.102.
• MySQL установлен и настроен
• требуется настроить репликацию БД testdb
• мы можем приостановить работу мастера на некоторое время
• у нас, разумеется, есть root на обеих машинах

Настройки мастера

Обязательно укажем уникальный ID сервера, путь для бинарных логов и имя БД для репликации в секции :
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
replicate-do-db = testdb
Убедитесь, что у вас достаточно места на диске для бинарных логов.

Добавим пользователя replication, под правами которого будет производится репликация. Будет достаточно привилегии "replication slave ":
mysql@master> GRANT replication slave ON "testdb".* TO "replication"@"192.168.1.102" IDENTIFIED BY "password";

Перезагрузим MySQL, чтобы изменения в конфиге вступили в силу:
root@master# service mysqld restart

Если все прошло успешно, команда "show master status " должна показать примерно следующее:
mysql@master> SHOW MASTER STATUS\G
File: mysql-bin.000003
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
Значение position должно увеличиваться по мере того, как вносятся изменения в БД на мастере.

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

Укажем ID сервера, имя БД для репликации и путь к relay-бинлогам в секции конфига, затем перезагрузим MySQL:
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = testdb

Root@replica# service mysqld restart

Переносим данные

Здесь нам придется заблокировать БД для записи. Для этого можно либо остановить работу приложений, либо воспользоваться установкой флажка read_only на мастере (внимание: на пользователей с привилегией SUPER этот флаг не действует). Если у нас есть таблицы MyISAM, сделаем также "flush tables ":
mysql@master> FLUSH TABLES WITH READ LOCK;
mysql@master> SET GLOBAL read_only = ON;

Посмотрим состояние мастера командой «show master status» и запомним значения File и Position (после успешной блокировки мастера они не должны изменятся):
File: mysql-bin.000003
Position: 98

Делаем дамп БД, и после завершения операции снимаем блокировку мастера:
mysql@master> SET GLOBAL read_only = OFF;

Переносим дамп на реплику и восстанавливаем из него данные.
Наконец, запускаем репликацию командами "change master to " и "start slave " и посмотрим, все ли прошло хорошо:
mysql@replica> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000003 ", MASTER_LOG_POS = 98;
mysql@replica> start slave;
Значения MASTER_LOG_FILE и MASTER_LOG_POS мы берем с мастера.

Посмотрим, как идет репликация командой "show slave status ":
mysql@replica> SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 98
Relay_Log_File: mysql-relay-bin.001152
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb,testdb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 5

Наиболее интересные сейчас значения я выделил. При успешном начале репликации их значения должны быть примерно такими, как в листинге (см. описание команды "show slave status " в документации). Значение Seconds_Behind_Master может быть любым целым числом.
Если репликация идет нормально, реплика будет следовать за мастером (номер лога в Master_Log_File и позиция Exec_Master_Log_Pos будут расти). Время отставания реплики от мастера (Seconds_Behind_Master), в идеале, должно быть равно нулю. Если оно не сокращается или растет, возможно, что нагрузка на реплику слишком высока — она просто не успевает повторять изменения, происходящие на мастере.
Если же значение Slave_IO_State пусто, а Seconds_Behind_Master равно NULL, репликация не началась. Смотрите лог MySQL для выяснения причины, устраняйте её и заново запускайте репликацию:
mysql@replica> start slave;

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

• заблокировать запись в мастер флагом read_only, запомнить позицию и остановить MySQL.
• после этого скопировать файлы БД на реплику и включить мастер.
• начать репликацию обычным способом.

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

Добавляем реплики

Пусть у нас уже есть работающие мастер и реплика, и нам нужно добавить к ним еще одну. Сделать это даже проще, чем добавить первую реплику к мастеру. И гораздо приятнее то, что нет необходимости останавливать для этого мастер.
Для начала настроим MySQL на второй реплике и убедимся, что мы внесли нужные параметры в конфиг:
server-id = 3
replicate-do-db = testdb

Теперь остановим репликацию на первой реплике:
mysql@replica-1> stop slave;

Реплика продолжит работать нормально, однако данные на ней уже не будут актуальными. Посмотрим статус и запомним позицию мастера, до которой реплика дошла перед остановкой репликации:
mysql@replica-1> SHOW SLAVE STATUS\G

Нам нужные будет значения Master_Log_File и Exec_Master_Log_Pos:
Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 155

Создадим дамп БД и продолжим репликацию на первой реплике:
mysql@replica-1> START SLAVE;

Восстановим данные из дампа на второй реплике. Затем включим репликацию:
mysql@replica-2> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000004 ", MASTER_LOG_POS = 155;
mysql@replica-2> START SLAVE;

Значения MASTER_LOG_FILE и MASTER_LOG_POS — это соответственно значения Master_Log_File и Exec_Master_Log_Pos из результата команды «show slave status » на первой реплике.
Репликация должна начаться с той позиции, на которой была остановлена первая реплика (и соответственно, создан дамп). Таким образом, у нас будет две реплики с идентичными данными.

Объединяем реплики

Иногда возникает такая ситуация: на мастере существует две БД, одна из которых реплицируется на одной реплике, а вторая — на другой. Как настроить репликацию двух БД на обеих репликах, не делая их дампы на мастере и не выключая его из работы? Достаточно просто, с использованием команды "start slave until ".
Итак, у нас имеется master с базами данных testdb1 и testdb2, которые реплицируются соответственно на репликах replica-1 и replica-2. Настроим репликацию обеих БД на replica-1 без остановки мастера.
Остановим репликацию на replica-2 командой и запомним позицию мастера:
mysql@replica-2> STOP SLAVE;
mysql@replica-2> SHOW SLAVE STATUS\G
Master_Log_File: mysql-bin.000015
Exec_Master_Log_Pos: 231

Создадим дамп БД testdb2 и возобновим репликацию (на этом манипуляции с replica-2 закончились). Дамп восстановим на replica-1.

Ситуация на replica-1 такая: БД testdb1 находится на одной позиции мастера и продолжает реплицироваться, БД testdb2 восстановлена из дампа с другой позиции. Синхронизируем их.

Остановим репликацию и запомним позицию мастера:
mysql@replica-1> STOP SLAVE;
mysql@replica-1> SHOW SLAVE STATUS\G
Exec_Master_Log_Pos: 501

Убедимся, что в конфиге на replica-1 в секции указано имя второй БД:
replicate-do-db = testdb2

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

Теперь проведем репликацию с позиции, на которой была приостановлена replica-2 до позиции, на которой мы только что приостановили репликацию:
mysql@replica-1> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000015 ", MASTER_LOG_POS = 231;
mysql@replica-1> start slave until MASTER_LOG_FILE = "mysql-bin.000016 ", MASTER_LOG_POS = 501;

Репликация закончится, как только реплика дойдет до указанной позиции в секции until, после чего обе наши БД будут соответствовать одной и той же позиции мастера (на которой мы остановили репликацию на replica-1). Убедимся в этом:
mysql@replica-1> SHOW SLAVE STATUS\G
mysql@replica-1> START SLAVE;
Master_Log_File: mysql-bin.000016
Exec_Master_Log_Pos: 501

Добавим в конфиг на replica-1 в секции имена обеих БД:
replicate-do-db = testdb1
replicate-do-db = testdb2

Важно: каждая БД должна быть указана на отдельной строке.
Перезагрузим MySQL и продолжим репликацию:
mysql@replica-1> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000016 ", MASTER_LOG_POS = 501;
После того, как replica-1 догонит мастер, содержание их БД будет идентично. Объединить БД на replica-2 можно или подобным образом, или сделав полный дамп replica-1.

Рокировка мастера и реплики

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

Включим ведение бинарных логов (дополнительно к relay-бинлогам) в конфиге в секции :
log-bin = /var/lib/mysql/mysql-bin

И добавим пользователя для ведения репликации:
mysql@master> GRANT replication slave ON ’testdb’.* TO ’replication’@’192.168.1.101′ IDENTIFIED BY "password ";

Пассивный мастер ведет репликацию как и обычная реплика, но кроме этого создает бинарные логии — то есть, мы можем начать репликацию с него. Убедимся в этом командой "show master status ":
mysql@replica> SHOW MASTER STATUS\G
File: mysql-bin.000001
Position: 61
Binlog_Do_DB:
Binlog_Ignore_DB:

Теперь, чтобы перевести пассивный мастер в активный режим, необходимо остановить репликацию на нем и включить репликацию на бывшем активном мастере. Чтобы в момент переключения данные не были утеряны, активный мастер необходимо заблокировать на запись.
mysql@master> FLUSH TABLES WITH READ LOCK
mysql@master> SET GLOBAL read_only = ON;
mysql@replica> STOP SLAVE;
mysql@replica> SHOW MASTER STATUS;
File: mysql-bin.000001
Position: 61
mysql@master> CHANGE MASTER TO MASTER_HOST = "192.168.1.102 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000001 ", MASTER_LOG_POS = 61;
mysql@master> start slave;
Все, так мы поменяли активный мастер. Можно снять с бывшего мастера блокировку.

Заключение

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

• устранение единичных точек отказа (SPF, Single Points of Failure). При использовании единственного сервера MySQL, его отказ приводил к отказу всей системы. При использовании нескольких серверов, отказ любого из них приведет к отказу системы, если только мы специально не позаботимся об этом. Нам нужно предусмотреть обработку ситуации с отказом мастера и реплики. Одно из существующих средств — MMM , однако, требует доработки напильником.
• балансировка нагрузки. При использовании нескольких реплик нам было бы удобно использовать прозрачный механизм балансировки, особенно если производительность реплик неодинакова. Под Linux возможно использовать стандартное решение — LVS .
• изменение логики работы приложения. В идеальной ситуации, запросы на чтение данных надо направлять на реплики, а на изменение — на мастер. Однако, из-за возможного отставания реплик, такая схема часто неработоспособна и необходимо выявлять такие запросы на чтение, которые все же должны выполнятся на мастере.

Надеюсь осветить эти вопросы в дальнейших статьях.
Спасибо за внимание!

В наши дни база данных 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 работает.