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

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

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

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

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

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

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

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

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

Если не установлен идентификатор 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 . Я решил, что эта тема может быть многим полезна, поэтому этой статье я расскажу о том, что такое репликация в 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_данного_подчинённого_сервера
Перенесите все базы с головного сервера на подчинённые.
Запустите головной сервер, потом все подчинённые.

Для успешного использования репликации в 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

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

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