Репликация MySQL

Для успешного использования репликации в 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, в секции [mysqld] добавляются такие параметры:

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

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

# Название БД MySQL, которая будет реплицироваться
replicate-do-db="dbreplica"

После модификации 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, который запишет в дамп информацию, необходимую для корректной работы slave-сервера:

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

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

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

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

# Идентификатор 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.

Далее создать БД, с таким же именем, как и на Master-сервере:

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, секции [mysqld]:

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.6), ROW (по умолчанию в 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

Как очистить бинарный лог вручную можно узнать на страницах официальной документации: 13.4.1.1 PURGE BINARY LOGS Syntax.

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

Пользователь для подключения 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 секция [mysqld]):

innodb_flush_log_at_trx_commit=1
sync_binlog=1

Опубликовано: 2016/05/27
HTML-код ссылки на эту страницу:
<a href="https://petrenco.com/mysql.php?txt=623" target="_blank">Репликация MySQL</a>
8045
Добавить комментарий
Ваш e-mail: (не виден посетителям сайта)
Ваше имя:
Комментарий:
Символы с картинки:
Только выделенные поля формы добавления комментариев обязательны к заполнению.