Для успешного использования репликации в MySQL необходимо:
Это простой пример master-slave репликации одной базы MySQL . Тем кто это делает впервые, следует начать с этого примера и в точности соблюдать инструкции.
Для начала, нужно прописать различные id для Master и Slave серверов. На Master-сервере нужно включить бинарный журнал (log-bin), указать БД для репликации и создать пользователя подчиненного сервера, через которого slave-сервер будет получать данные с master`а. На slave-сервере включается релейный лог (relay-log), указывается БД для репликации и запускается slave-репликация.
Отредактировать 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
Дамп можно снимать с БД под нагрузкой, но следует учесть, что если БД большая, то на время записи дампа БД будет не доступна на запись.
Первым делом нужно провести правки 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 используется для ведения журнала изменений, происходящих в базах данных сервера. Для репликации он должен быть обязательно включен на 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 - в этом режиме в бинарный лог записываются обычные 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 - при выборе этого способа хранения бинарного лога, в файл записывается исключительно изменения, для выбранных баз данных в двоичном формате. Данные могут занимать намного больше места, чем при режиме 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 - режим, в котором бинарный лог одновременно использует 2 режима репликации: Statement и Row для хранения данных о различных запросах. Более подробно узнать, как работает режим бинарного лога Mixed можно из официальной документации на английском: 5.4.4.3 Mixed Binary Logging Format . Нельзя сказать, что это идеальный вариант, но если понимать, как работает Mixed, то его вполне можно применять на практике.
По умолчанию, бинарные логи никогда не очищаются автоматически. Для автоочистки log-bin служит параметр expire_logs_days, в котором задается кол-во дней, которое MySQL будет хранить бинарный журнал.
Пример автоматического удаления бинарного лога, с даты создания которого прошло более 10 дней
expire_logs_days= 10
При 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
Термин репликация используется для обозначения механизма синхронизации нескольких копий данных, что повышает сохранность информации, отказоустойчивость и производительность системы. Ярким примером служит репликация базы данных между двумя серверами.
В терминологии Master-Slave мастером является первичный сервер с базой данных, на нем производится запись в базу, но чтение распределяется между мастером и слейвом в зависимости от нагрузки на систему, что повышает отказоустойчивость и производительность. Кроме того, благодаря такому подходу, копия базы данных всегда под рукой и ее можно восстановить в случае отказа одного из серверов.
В каких ситуациях может понадобится slave-сервер? Например, когда поступает большой массив данных для записи в базу и master-сервер просто не успевает выполнять чтение и клиенту приходится ждать окончания записи, чего можно избежать благодаря slave-серверу.
Возможны ситуации, когда мастер-сервер выходит из строя, в этом случае слейв-сервер подхватывает все функции мастера и работает в одиночку до его восстановления. Клиент скорее всего ничего не заметит, и уж точно не будет час-два-три ждать пока починят мастера.
Репликацию настроить совсем не сложно, благо механизм заложен в MySQL с самого начала.
Начнем с редактирования файла конфигурации 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 нужно запомнить, они будут использованы в настройке слейва, к чему мы сейчас и переходим.
Первым делом необходимо создать базу данных с таким же именем, как у той, которую мы собираемся реплицировать. Это важный шаг, нельзя им пренебрегать. Далее переходим в уже знакомый нам файл конфигурации 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 необходимо выполнить ниже описанную последовательность действий, но это не догма и параметры могут изменяться в зависимости от обстоятельств.
На главном сервере отредактируем файл файл my.cnf, в секцию mysqld добавить строки:
Server-id = log-bin = mysql-bin log-bin-index = mysql-bin.index log-error = mysql-bin.err relay-log = relay-bin relay-log-info-file = relay-bin.info relay-log-index = relay-bin.index expire_logs_days=7 binlog-do-db =
На подчиненном отредактируем файл файл my.cnf, в секцию mysqld добавить строки:
Server-id = master-host = master master-user = replication master-password = password master-port = 3306 relay-log = relay-bin relay-log-info-file = relay-log.info relay-log-index = relay-log.index replicate-do-db =
На главном сервере добавим пользователя replication с правами на репликацию данных:
GRAANT REPLICATION SLAVE ON *.* TO "replication"@"replica" IDENTIFIED BY "password"
Заблокируем реплицируемые базы на главном сервере от изменения данных, программно или с помощью функционала MySQL:
Mysql@master> FLUSH TABLES WITH READ LOCK; mysql@master> SET GLOBAL read_only = ON;
Для разблокировки используется команда:
Mysql@master> SET GLOBAL read_only = OFF;
Сделаем резервные копии всех баз данных на главном сервере (или тех которые нам необходимы):
Root@master# tar -czf mysqldir.tar.gz /var/lib/mysql/
или средствами утилиты mysqldump:
Root@master# mysqldump -u root -p --lock-all-tables > dbdump.sql
Остановим оба сервера (в отдельных случаях можно обойтись и без этого):
Root@master# mysqlamdin -u root -p shutdown root@replica# mysqlamdin -u root -p shutdown
Восстановим реплицируемые базы данных на подчиненном сервере с помощью копирования директории. Перед началом репликации базы данных должны быть одинаковы:
Root@replica# cd /var/lib/mysql root@replica# tar -xzf mysqldir.tar.gz
или функционала mysql, тогда mysql на подчиненном сервере не было необходимости останавливать:
Root@replica# mysql -u root -p < dbdump.sql
Запустим mysql на главном сервере (а затем - на подчиненном, если это необходимо):
Root@master# /etc/init.d/mysql start root@replica# /etc/init.d/mysql start
Проверим работы главного и подчиненного серверов:
Mysql@replica> start slave; mysql@replica> SHOW SLAVE STATUS\G mysql@master> SHOW MASTER STATUS\G
На подчиненном сервере проверить логи в файле master.info, там должны содержаться запросы на изменение данных в базе. Так этот файл бинарный необходимо сначала преобразовать его в текстовый формат:
Root@replica# mysqlbinlog master.info > master_info.sql
При возникновении ошибок, можно использовать команды:
Mysql@replica> stop slave; mysql@replica> RESET SLAVE; mysql@master> RESET MASTER;
и повторить все действия начиная с блокировки баз данных.
Для горячего добавления серверов репликации можно исользовать синтаксис:
Mysql@replica> SHOW SLAVE STATUS\G mysql@master> SHOW MASTER STATUS\G mysql@replica-2> CHANGE MASTER TO MASTER_HOST = "master", MASTER_USER ="replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE ="mysql-bin.000004 ", MASTER_LOG_POS = 155; mysql@replica-2> START SLAVE;
Информация из статусов покажет позицию и имя текущего файла лога.
В случае асинхронной репликации обновление одной реплики распространяется на другие спустя некоторое время, а не в той же транзакции. Таким образом, при асинхронной репликации вводится задержка, или время ожидания, в течение которого отдельные реплики могут быть фактически неидентичными. Но у данного вида репликации есть и положительные моменты: главному серверу не надо беспокоится о синхронизации данных, можно блокировать базу (например, для создания резервной копии) на подчиненной машине, без проблем для пользователей.
При полном или частичном использовании любых материалов с сайта вы обязаны явным образом указывать ссылку на в качестве источника.
1. Настройка Master сервера:
Смотрим где должен лежать конфиг.
# ps aux | grep my.cnf
mysql 51189 0.0 0.0 17064 1912 — Is 6:35PM 0:00.05 /bin/sh /usr/local/bin/mysqld_safe —defaults-extra-file=/var/db/mysql/my.cnf —user=mysql —datadir=/var/db/mysql
Если файл отсутствует его можно скопировать из примера.
# cp /usr/local/share/mysql/my-small.cnf /var/db/mysql/my.cnf
Или создать пустой.
# touch /var/db/mysql/my.cnf
В созданный конфиг в секции пишем.
#Уникальный ID сервера. У мастера должен быть ниже реплики и не дублироваться
server — id = 1
#Формат лога
binlog — format = mixed
#Путь где будет лежать бинлог (По умолчанию размер одного лога 1г)
#Время хранения бинлогов
expire_logs_days = 30
replicate-do-db = database_1
replicate-do-db = database_2
replicate-do-db = database_3
replicate-do-db = database_4
#Лог ошибок
На этом закругляемся с редактированием и рестартим MySQL с новым конфигом.
# /usr/local/etc/rc.d/mysql-server restart
Теперь надо добавить пользователя на Master для Slave сервера.
Для репликации достаточно будет прав REPLICATION SLAVE. Заходим под root на cервер MySQL.
# mysql -uroot -p
Создаем пользователя:
mysql> use mysql;
mysql>CREATE USER ‘replica’@’ip_address_slave_server’ ;
mysql>GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’ip_address_slave_server’ IDENTIFIED BY ‘password_for_user_replica’ ;
Теперь можно или перегрузить сервер или сказать
mysql>FLUSH PRIVILEGES;
2. Создаем дамп нужных баз:
Все базы.
# mysqldump -uroot -p —skip-lock-tables —single-transaction —flush-logs —hex-blob —master-data=2 -A > /usr/home/Timur/dump.sql
Определенные базы.
# mysqldump -uroot -p —skip-lock-tables —single-transaction —flush-logs —hex-blob —master-data=2 -B DATABASE DATABASE1 DATABASE2 DATABASE3 > /usr/home/Timur/dump.sql
3. Смотрим какой бинлог использовать и его позицию:
# head -n80 /usr/home/Timur/dump.sql | grep «MASTER_LOG_POS»
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000049 ‘, MASTER_LOG_POS=107 ;
Желательно записать!!!
4. Жмем дамп и переносим на Slave сервер:
# gzip /usr/home/Timur/dump.sql
Переносим.
# scp /usr/home/Timur/dump.sql.gz _address_slave_server:/usr/home/Timur
5. Настройка Slave сервера (my.cnf).
server — id =2
binlog — format = mixed
log-bin=/var/log/mysql/mysql-bin
expire_logs_days = 30
#Бинлоги Slave
relay-log = /var/log/mysql/mysql-relay.log
relay-log-index = /var/log/mysql/mysql-relay-bin.index
#Указывает подчиненному серверу, чтобы тот вел записи об обновлениях, происходящих на подчиненном сервере, в двоичном журнале. По умолчанию эта опция выключена. Ее следует включить, если требуется организовать подчиненные серверы в гирляндную цепь.
log-slave-updates = 1
#Ставим базы только на чтение. На суперпользователей данная опция не распространяется!!!
read-only = 1
#Пропустить дублирующие записи. После того как Seconds_Behind_Master станет 0, закоментировать и ребутнуть SLAVE
slave-skip-errors=all
#Указываем какие базы нам нужно реплицировать
replicate-do-db = database_1
replicate-do-db = database_2
replicate-do-db = database_3
replicate-do-db = database_4
#Лог ошибок
log-error=/var/log/mysql/mysqld-error.log
#Для того чтобы при запуске сервера не стартовал Slave. Запустить можно в ручную START SLAVE;
skip-slave-start = On
Перезагружаем сервер (MySQL).
6. Заливаем дамп на Slave и стартуем репликацию:
Разахивируем.
# gunzip /usr/local/Timur/dump.sql.gz
Заливаем дамп.
# mysql -uroot -p < /usr/local/Timur/dump.sql
Говорим Slave откуда тащить данные и стартуем. MASTER_LOG_FILE и MASTER_LOG_POS берем то, что записали при дампе баз на Master 😉
mysql>CHANGE MASTER TO MASTER_HOST
=
‘<
Смотрим командой SHOW SLAVE STATUS\G все ли у нас стартануло.
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: Тут адрес Master сервера
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000049
Read_Master_Log_Pos: 1919771
Relay_Log_File: mysql-relay.000050
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000049
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: database_1,database_2,database_3,database_4,database_1,database_2,database_3,database_4
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1919771
Relay_Log_Space: 3125
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 5
1 row in set (0.00 sec)
Все завелось.
Должен рости Exec_Master_Log_Pos: 1919771
Если появилась ошибка, то можно ее пропустить выполнив:
mysql> STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE;
Всем доброго дня! Сегодня в нашей статье мы рассмотрим примеры настройки репликации типа “master-slave”.
Зачем нужна репликация? В первую очередь это подстраховка на случай, если основной mysql-сервер выйдет из строя, тогда можно переключиться на slave-сервер и продолжить работу. Во вторых, это возможность уменьшить нагрузку на основной сервер Mysql, используя master-сервер только для записи, а операции на чтение выполнять на slave-сервере. Как происходит репликация? Master-сервер пишет binlog-и, в которых указывает операции, которые выполняются над базой данных (базами данных) и запоминает смещение в журнале от его начала до текущей записи (позицию). Slave-сервер подключается к master-у, сравнивает значения позиций и считывает изменения в журнале начиная со значения собственной позиции и заканчивая значением позициии master-a. Изменения (команды) он применяет к базам данных на slave-сервере.
Изменяем my.cnf на головном сервере:
Server-id = 1 - указываем id сервера log_bin = /var/log/mysql/mysql-bin.log - наименое лога и его путь
Небольшое уточнение: по умолчанию, мастер пишет binlog-и для всех баз данных, это можно изменить с помощью "binlog-do-db". В логи будет записываться значения, когда будет использоваться определенная БД, изменения в остальных БД не будут записываться. Здесь же можно указать, сколько дней хранить логи, какой их максимальный размер (параметры expire_logs_days и max_binlog_size). Добавляем в MySQL пользователя, под правами которого будет производиться репликация:
GRANT replication slave ON *.* TO имя_пользователя@ip_slave_сервера IDENTIFIED BY "пароль";
replication slave - привилегия, позволяющая пользователю читать binlog-и. ip_slave_сервера - ip сервера, с которого будет подключаться пользователь. Перезагружаем mysql-сервер:
/etc/init.d/mysql restart
Проверяем работу мастера:
Show master status;
Должны увидеть название binlog-a и позицию в нем. При выполнении команд над БД, позиция будет меняться.
В файл my.cnf вносим измнения:
Server-id = 2 - идентификатор slave-сервера должен обязательно отличаться от идентификатора master. relay-log = /var/lib/mysql/mysql-relay-bin - как и двоичный журнал, состоит из набора пронумерованных файлов, содержащих события, которые описывают изменения в базе данных. relay-log-index = /var/lib/mysql/mysql-relay-bin.index - индексный файл, который содержит имена всех используемых файлов журналов relay. replicate-do-db = БД, которая будет реплицироваться.
Важное замечание! При организации cross db (когда используется одна БД, а данные обновляются в другой БД) в настройках мастер-сервера не нужно указывать binlog-do-db, binlog-и должны писаться для всех баз данных, а в настройках slave нужно вместо replicate-do-db указать replicate-wild-do-table=db_name.%, где db_name - имя реплицируемой БД. Перезагружаем mysql-сервер:
/etc/init.d/mysql restart
SET GLOBAL read_only = ON;
Смотрим состояние master-a:
Show master status;
Запоминаем значения File и Position (а лучше их записать). Сейчас значение Position не должно изменяется. Делаем дамп мастера командой mysqldump:
Mysqldump -uname -ppassword db_master_name > dump_db,
где name - имя пользователя, password - пароль, db_master_name - имя БД, dump_db - название дампа. После завершения дампа разрешаем запись в БД:
SET GLOBAL read_only = OFF;
Переносим дамп на slave и разворачиваем
Mysql -uname -ppassword db_slave_name < dump_db
Настраиваем репликацию
CHANGE MASTER TO MASTER_HOST = “ip-мастера”, MASTER_USER = "имя_пользователя ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "название лога", MASTER_LOG_POS = позицию;
ip-мастера - ip сервера, на котором расположен master, имя_пользователя - имя пользователя, которого мы создали на master-е, название лога - значение File на мастере, когда делал дамп БД, позицию - значение Position на мастере, когда делал дамп БД. Запускаем slave:
Start slave;
Смотрим как идет репликация: На мастере: SHOW MASTER STATUS\G На slave: SHOW SLAVE STATUS\G
Параметр bind-address в /etc/mysql/my.cnf задает, какой ip-адрес mysql-сервер будет слушать в ожидании соединения. Обычно он имеет значение bind-address = 127.0.0.1. Но после настройки slave-сервера, нам нужно разрешить подключение со slave-сервера и при этом должны работать локальные подключения. Bind-address может разрешить подключение только с одного ip либо со всех. Т.к. нам нужно указать больше одного ip для соединения, мы комментируем строку с bind-address = 127.0.0.1. Теперь mysql-сервер будет принимать соединения со всех ip-адресов, что очень опасно. Решить эту проблему нам поможет iptables:
Iptables -I INPUT -p tcp -s ip_slave_server-a --dport 3306 -j ACCEPT -в начале разрешаем подключение с ip-адреса slave-сервера iptables -I INPUT -p tcp --dport 3306 -j DROP - потом запрещаем подключение со всех остальных ip-адресов.
Теперь у нас будет работать 2 MySQL сервера в режиме master-slave, что существенно повышает надежность сайта и для некоторых Drupal сайтов помогает увеличить скорость работы. В следующей статье рассмотрим переключение между режимами master и slave в случае падения master сервера.
