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

Репликация данных 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 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 , которая выведет таблицу с информацией. Главный признак того, что сервера соединились и корректно работают — наличие таких строк

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

Главная задача репликации - объединять мощности нескольких серверов . Допустим, у Вашего сайта выделенный сервер, но со временем он становится очень посещаемым и уже не выдерживает нагрузку. В результате, начинаются тормоза и регулярные падения сервера. Самый простой способ - это купить более мощный сервер, и так большинство и поступает. Но рано или поздно настаёт момент, когда уже стоимость роста цены на сервер не соответствует росту его производительности, поэтому выгоднее купить 2 разных сервера за меньшие деньги.

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

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

Теперь разберём, как настраивается репликация в MySQL :

Установите самые свежие версии MySQL на все сервера.
Создайте на головном сервере пользователя с привилегией REPLACATION SLAVE . В качестве адреса, с которого он может подключаться, укажите "все ".
Остановите все сервера.
В настройках MySQL (в файле my.cnf ) в разделе добавьте следующие строки: log-bin
server-id=1 Обратите внимание, что server-id на всех серверах должен быть разный. Фактически это то, что отличает один сервер от другого.
На подчинённых серверах добавьте в настройки MySQL следующие строки: master-host=имя_головного_хоста
master-user=логин_созданного_пользователя
master-password=пароль_созданного_пользователя
master-port=порт_для_подключения_к_головному_серверу
server-id=id_данного_подчинённого_сервера
Перенесите все базы с головного сервера на подчинённые.
Запустите головной сервер, потом все подчинённые.