Mysql репликация отдельных таблиц. Настройка репликации master-slave в MySQL

С репликацией серверов 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 MASTER SLAVE репликация используется чаще, но применяется и второй тип репликации — Master-Master.

Что такое MySQL MASTER SLAVE репликация и для чего она применяется

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

Репликация может осуществляться и с целью повышения производительности системы, однако производительность здесь практически всегда вторична.
При работе приложения с БД самыми частыми операциями являются операции SELECT — запросы на считывание данных, модификация данных — запросы DELETE , INSERT , UPDATE , ALTER статистически происходит гораздо реже.

Чтобы в случае выхода из строя одного из серверов не произошло потери данных операции на изменение информации в таблицах всегда обрабатываются Master-сервером. Затем изменения реплицируются на Slave. Считывание же можно производить с сервера играющего роль Slave.
За счет этого можно получить выигрыш в производительности вместе с надежностью.

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

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

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

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

MySQL MASTER SLAVE репликация — настройка на Debian

Будем использовать два сервера с адресами:

Master сервер 192.168.0.1
Slave сервер 192.168.0.2

Для демонстрации используются VDS объединенные в локальную сеть.
Чтобы всегда наверняка знать на каком сервере мы выполняем ту или иную команду отредактируем файлы /etc/hosts на обоих серверах

192.168.0.1 master

192.168.0.2 slave

Заменим существующие значения в /etc/hostname на master и slave соответственно, чтобы изменения вступили в силу сервера перезагрузим.

1. Производим настройки на мастер сервере.

root@master:/#

Редактируем основной конфигурационный файл сервера баз данных

mcedit /etc/mysql/my.cnf

Выбираем ID сервера — число можно указать любое, по умолчанию стоит 1 — строку достаточно раскомментировать

server-id = 1

Задаем путь к бинарному логу — также указано по умолчанию, раскомментируем

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

binlog_do_db = db1

Перезапускаем Mysql чтобы конфигурационный файл перечитался и изменения вступили в силу:

/etc/init.d/mysql restart

2. Задаем пользователю необходимые права

Заходим в консоль сервера баз данных:

Даем пользователю на подчиненном сервере необходимые права:

GRANT REPLICATION SLAVE ON *.* TO "slave_user"@"%" IDENTIFIED BY "123";

Блокируем все таблицы в БД

FLUSH TABLES WITH READ LOCK;

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

3. Создаем дамп базы данных на сервере

Создаем дамп базы данных:

mysqldump -u root -p db1 > db1.sql

Разблокируем таблицы в консоли mysql:

4. Переносим дамп базы на Slave-сервер

scp db1.sql [email protected]:/home

Дальнейшие действия производим на Slave-сервере

root@slave:/#

5. Созданием базу данных

Загружаем дамп:

mysql -u root -p db1 < db1.sql

6. Вносим изменения в my.cnf

mcedit /etc/mysql/my.cnf

Назначаем ID инкрементируя значение установленное на Мастер сервере

server-id = 2

Задаем путь к relay логу

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

и путь bin логу на Мастер сервере

log_bin = /var/log/mysql/mysql-bin.log

Указываем базу

binlog_do_db = db1

Перезапускаем сервис

/etc/init.d/mysql restart

7. Задаем подключение к Master серверу

CHANGE MASTER TO MASTER_HOST="192.168.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 327;

Запускаем репликацию на подчиненном сервере:

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

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 107
Relay_Log_Space: 555
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: 1
1 row in set (0.00 sec)

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

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

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

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

Master-Slave репликация MySQL

В терминологии Master-Slave мастером является первичный сервер с базой данных, на нем производится запись в базу, но чтение распределяется между мастером и слейвом в зависимости от нагрузки на систему, что повышает отказоустойчивость и производительность. Кроме того, благодаря такому подходу, копия базы данных всегда под рукой и ее можно восстановить в случае отказа одного из серверов.

В каких ситуациях может понадобится slave-сервер? Например, когда поступает большой массив данных для записи в базу и master-сервер просто не успевает выполнять чтение и клиенту приходится ждать окончания записи, чего можно избежать благодаря slave-серверу.

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

Репликацию настроить совсем не сложно, благо механизм заложен в MySQL с самого начала.

Настройка на Master сервере

Начнем с редактирования файла конфигурации my.cnf , который чаще всего расположен по адресу /etc/mysql/my.cnf . Необходимо найти и раскомментировать(убрать #), либо прописать такие строки.

Bind-address = 0.0.0.0 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log

Важно! Если bind-address уже был прописан, его нужно поменять, иначе не получится установить подключение между серверами.

Сразу после этого рестартуем базу данных на сервере.

/etc/init.d/mysql restart

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

GRANT REPLICATION SLAVE ON *.* TO "slave_user"@"%" IDENTIFIED BY "slave_password"; FLUSH PRIVILEGES;

Где вместо «slave_user» и «slave_password» нужно написать логин и пароль для слейва.

Теперь посмотрим данные о мастере

SHOW MASTER STATUS;

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

Настройка на Slave сервере

Первым делом необходимо создать базу данных с таким же именем, как у той, которую мы собираемся реплицировать. Это важный шаг, нельзя им пренебрегать. Далее переходим в уже знакомый нам файл конфигурации my.cnf и пишем настройки.

Server-id = 2 relay-log = /var/log/mysql/mysql-relay-bin.log bin-log = /var/log/mysql/mysql-bin.log

Важно! В bin-log прописывается путь к бин-логу на местер сервере . Идентификатор сервера должен отличаться от айди мастера, удобно ставить его на 1 больше.

CHANGE MASTER TO MASTER_HOST="1.1.1.1", MASTER_USER="slave_user", MASTER_PASSWORD="slave_password", MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 107; START SLAVE;

Где host — ip адрес мастер, логин и пароль соответствуют тем, что мы создали на мастере, master_log_file и master_log_pos заполняются информацией из последнего пункта настройки мастер-сервера .

С этого самого момента все изменения в базе данных будут передаваться с мастера на слейва.

Проверка статуса репликации

Кроме команды SHOW MASTER STATUS; есть аналогичная для слейва SHOW SLAVE STATUS\G , которая выведет таблицу с информацией. Главный признак того, что сервера соединились и корректно работают — наличие таких строк

whisk 8 апреля 2009 в 11:10

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

MySQL

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

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

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

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

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

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

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

Root@replica# service mysqld restart

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

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

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

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

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

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

Нам нужные будет значения 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-1> STOP SLAVE;
mysql@replica-1> SHOW SLAVE STATUS\G
Exec_Master_Log_Pos: 501

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

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

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

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

Заключение

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

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

Теги:

mysql
репликация

Добавить метки

Репликация данных 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