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

Оставьте комментарий 6,950

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

В докладе не будет:

Все это есть в Интернете, синтаксис разбирать смысла нет.

Мы немного пройдемся по теории, попытаемся объяснить, как это все работает внутри, а после этого вы с утроенными силами сможете сами нырнуть в документацию.

Что такое репликация, в принципе? Это копирование изменений. У нас есть одна копия БД, мы хотим с какой-то целью еще одну копию.

Репликация бывает разных видов. Разные оси сравнения:

степень синхронизации изменений (sync, async, semisync);
количество серверов записи (M/S, M/M);
формат изменений (statement-based (SBR), row-based (RBR), mixed);
теоретически, модель передачи изменений (push, pull).

Забавный факт – если немного задуматься, репликация нам теоретически помогает из принципиальных соображений скейлить только чтение. Вот такой несколько неочевидный вывод. Это потому что, если у нас на одну и ту же копию данных надо налить определенное количество изменений, и эта определенная копия данных обслуживается одним и тем же сервером, то этот сервер способен выдержать определенное количество апдейтов в секунду, и больше туда не залить. Способен сервер обновить 1000 записей в секунду, а 2000 – не способен. Что изменится от того, что ты поставишь к этому серверу реплику, неважно, в режиме мастер-слэйв или мастер-мастер? Сумеешь ты на эту реплику налить вторую тысячу апдейтов? Правильный ответ – нет.

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

Т.е. репликация – это больше про чтение.

Про синхронизацию.

Синхронизация – гарантия наличия и доступности. Доступности в том смысле, что у нас commit прошел, транзакция зафиксировалась, все хорошо, эти данные видно одной или нескольким нодам в кластере, они могут участвовать в следующих запросах. Наличие – это то, что данные, в принципе, есть более чем на одном сервере, но, возможно, транзакция не проигралась и не доступна.

Здесь нет рефрена "commit закончился успешно, что это значит?". Синхронный commit означает, что у нас локальный и удаленный (хотя бы на одной реплике) закончился, т.е. мы что-то закоммитили на машину, если у нас синхронный режим репликации, то эти изменения успешно закоммитились, они видны для последующих запросов на локальной машине, на удаленной машине (хотя бы на одной) тоже видны. Это означает, что если случилась стандартная внештатная ситуация, т.е. в один и серверов прилетел лом и пробил все насквозь – от процессора до самого винта, то, несмотря на это, данные не только скопированы на некий удаленный сервер, но еще, вдобавок, могут мгновенно, без каких-то дополнительных задержек, участвовать в последующих транзакциях.

Это все общая терминология, никак совершенно не связанная с MySQL. В любой распределенной системе оно будет устроено так.

Асинхронный commit – никаких дополнительных гарантий, как повезет.

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

Про сервера для записи. Какие бывают виды репликации.

Master-slave classic, изменения все льются на один сервер, после этого копируются на массу реплик.

Master-master true – когда изменения льются на кучу мастеров одновременно и каким-то образом с одного на другой, с другого на третий и между ними всеми, что порождает и ряд радостей, и ряд автоматических проблем. Понятно, что когда у тебя есть одна "золотая копия" и с нее несколько реплик, которые должны (в идеале – мгновенно) повторять эту "золотую копию", то все сравнительно просто с точки зрения того, как данные туда-сюда гонять и что делать на каждой конкретной копии. С master-master начинается интересная "головная боль", причем, подчеркиваю, не конкретно в случае MySQL, а сугубо теоретическая. Как же быть, если на двух нодах одновременно попытались прогнать одну и ту же транзакцию, которая меняет одни и те же данные, причем, меняет их, для простоты примера, по-разному. Понятно, что одновременно эти два изменения мы применить не можем. На момент, когда мы на одной ноде начинаем что-то изменять, на второй ноде еще пока ничего нет. Конфликт. Одну из транзакций придется откатывать. Вдобавок начинаются отдельные "пляски" со сверкой часов и т.п.

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

Приятный вариант – под названием"Master-slave + routing запросов". Приятен он тем, что внутри программировать просто, у тебя есть одна основная копия, ты ее реплицируешь на кучу машин. Это намного проще, чем в мастер-мастер среде, когда все равноправны и т.д., но с точки зрения приложения все равно выглядит так, будто у тебя точек записи много. Ты приходишь на любую ноду, она знает, куда тебя зароутить, и успешно роутит. Ну, и чтения масштабируются – вот оно счастье репликации. Читать можно со всех точек все и всегда.

Теперь ближе к базам данных, "волшебным" форматам statement-based, row-based и т.д. Про формат изменений.

Что можно делать? Можно передавать сами запросы, а можно передавать только измененные строки. Подчеркиваю – пока мы еще не нырнули в дебри MySQL, этим может заниматься любая СУБД, в которой есть запросы, порождающие большое (или не очень) количество изменений, т.е. обновляющие много данных. Возникает вопрос – а что конкретно будем копировать? Можно сами запросы туда-сюда между нодами гонять, а можно гонять только измененные данные. Интересно, что и так и эдак очень плохо! Можно еще пытаться смешивать.

Еще один пункт про то, какие бывают репликации. Про модель распространения. Наверное, где-то до сих пор еще не полностью вымерла модель Push-based, когда та нода, которая внесла изменения, та и обязана их рассылать по всем остальным нодам. С точки зрения программирования и отслеживания state"ов это та еще морока. Поэтому рулит Pull-based. Забирать апдейты с той или иной ноды – это намного проще запрограммировать, чем на одной ноде следить за хаотичным кластером своих реплик.

Некие общие термины ввели. Переходим к тому, как сделали в MySQL.

MySQL, сам по себе, это некий обман. Есть логический слой под названием MySQL, который занимается всяким общими и изолированными от хранения данных делами – сеть, оптимизатор, кэши и т.д. Конкретный физический слой, который отвечает за хранение данных, лежит на этаж ниже. Есть несколько встроенных, есть ставящиеся плагинами. Но даже встроенные MyISAM, InnoDB и т.д. живут на физическом слое. Плагинная архитектура – это клево, можно подцепить новый движок, но мгновенно возникает некая неоптимальность. В принципе, транзакционные write-ahead log"и (WAL), которые физический слой хранения все равно пишет, было бы хорошо использовать для репликации, и если система знает о том, что есть некий физический уровень, или достаточно хорошо сопряжена с этим физическим уровнем, то можно было бы отдельный лог на логическом уровне не писать, а использовать тот же самый WAL. Но у MySQL это невозможно концептуально, либо, если поменять интерфейс в PSE так, чтобы стало возможно концептуально, то будет очень много работы.

Репликация реализована на уровне самого MySQL. В этом есть и хорошее – помимо одного лога в виде глубоко внутренних данных движка хранения, есть более-менее логический лог, возможно, на уровне statement"ов, который ведется отдельно от этого движка. А это "лишняя" безопасность и т.д. плюс, поскольку никаких ограничений внутри нет, можно делать всякий креатив типа подмены движка "на лету".

В веденных терминах в MySQL 4.1 было реализовано: master-slave, pull-based, строго async и строго SBR. Если вы застряли в древней эпохе 4.х, то, наверное, у вас все плохо. Версиям 5.х уже чуть ли не 10 лет – пора бы и обновиться.

Забавно прослеживать по версиям, как люди наступали на всяческие грабли и, когда сделать уже ничего было нельзя, прикручивали к этим граблям новые грабли, чтобы жизнь была не такая болезненная. Так, в версии 5.1 прикрутили RBR, чтобы компенсировать неизбежные проблемы с SBR, и прикрутили mixed режим. В версии 5.6 прикрутили еще приятных штук: semi-sync, delayed slave, GTID.

Еще один момент. Поскольку MySQL – это некий общий слой, с одной стороны, и куча pluggable движков, с другой стороны, в том числе, встроенных, там есть с определенного момента божественный NDB cluster, про который рассказывают крутое. Там полностью синхронная мастер-мастер репликация, очень доступная in-memory БД... Но есть один нюанс – как только начинаешь искать людей, которые в продакшене используют NDB cluster, то таких людей находится крайне мало.

Чем занимается мастер в тот момент, когда вы решили включить репликацию? На мастере происходит довольно мало дополнительных движений. Как обычно, мы по сети принимаем запросы, парсим их, гоняем транзакции, фиксируем их и т.д. Вдобавок к этому, на логическом уровне MySQL мастер начинает вести binary log – файл, не совсем текстовый, в который сыплются все подряд изменения. Также мастер умеет рассылать эти логи по сети. Все это очень просто и, вроде как, работает.

Чем занимается слэйв? Изменения на слэйв лучше не слать, потому что можно попасть в непонятное. У слэйва чуть больше работы. Помимо того, чтобы вести один дополнительный лог и по запросу его рассылать, еще есть тред, который ходит к удаленному мастеру, возможно, даже не к одному, и качает оттуда binary log"и. Решение "давайте ходить к нескольким удаленным мастерам и с них качать разные логи" неоднозначно. С одной стороны неплохо, а с другой получается мгновенное расхождение. Просто физически копировать файлы по SCP нельзя, уже получается на сервере один лог, в нем свои позиции, локально мы их по сетке тянем, складываем в отдельный лог, еще отдельный тред бегает и пытается проигрывать эти локальные логи. Самое адское, на мой взгляд, заключается в том, что вплоть до версии 5.6 идентификация той или иной транзакции в логе происходила по имени файла и позиции на мастере. Интересное решение.

Вот путь записи, который простенький insert проходит без репликации:

Приложение сконнектилось к серверу, положило в таблицу и отбой.

С репликацией получается несколько дополнительных шагов:

Приложение-писатель точно так же идет к мастеру, но вдобавок эти данные попадают в том или ином виде в binary log, потом качаются по сети в relay log, потом из relay log"а постепенно реплеются (если нам повезло, и слэйв не лагает, реплеются сразу) в таблицу на слэйве, после этого все доступно в читателе.

Что конкретно попадает в binary log, зависит от настроек SBR/RBR/mixed. Откуда это все растет? Представим себя базой данных. Нам прилетел простой запрос "обнови одну конкретную запись" – UPDATE users SET x=123 WHERE id=456

Что записать в binary log? В принципе, все равно, на самом деле. Можем коротенький запрос записать, либо (а он обновил одну запись) можем записать изменение каким-то образом в том или ином формате.

Другая ситуация. Представим, что нам прилетел тот самый запрос, который сам по себе маленький, а данных меняет много – UPDATE users SET bonus=bonus+100

Тут эффективный вариант один – писать сам запрос, потому что запрос – ровно 32 байта, а записей он может обновить произвольное количество – 1000, 100 000, 1 000 000, сколько угодно... Неэффективно писать измененные записи в лог.

А что произойдет, если мы в лог поместим такой нехитрый запрос "давайте отключим всех юзеров, которые не логинились давно" – UPDATE users SET disabled=1 WHERE last_login

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

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

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

мастер многопоточен, а слэйв – нет. Понятно, что если мастер наливает нагрузку в четыре ядра, слэйв эту нагрузку в одно ядро наливать не успевает. Все довольно плохо;
состояние слэйва определяется именем позиции в файле мастера. Вдумайтесь – состояние одной ноды в кластере определяется именем файла и позицией в этом файле на другой ноде кластера, с которой может по любым причинам произойти что угодно!
"спасительный" RBR. Оказывается, по умолчанию туда пишутся полные before/after row image, т.е. мы изменили одну колонку в пяти-килобайтной строке, оп! – 10 Кб трафика и байтов 20-40 оверхедов на эту строку, потом оп! – едет такая жирная строка предыдущей версии, оп! – едет после этого версия с новыми значениями. Администраторы воют хором! Тем не менее, это просто офигенно с точки зрения некоторых извращенных приложений, например, внешних читалок, которые пытаются подцепиться к серверу MySQL, с него вытягивать данные и делать с ними что-нибудь, например, совать их в полнотекстовый индекс. Насколько это плохо с точки зрения администрирования базы, в которой одно изменение на три байта порождает 10 Кб трафика на винте, а потом 10 Кб трафика по сети на каждого слэйва, настолько же это хорошо для всяких систем типа полнотекстового поиска, как Sphinx, у которых нет локальной копии данных, а MySQL с нуля имплементировать нет никакого желания. В MySQL 5.6 спохватились и сделали binlog_row_image (но по дефолту full, а не minimal или noblob).

Короче говоря, устроено все не хитро – палка, веревка, один лог, второй лог. И даже в этом логе "детские" болезни довольно забавные:

Для человека, который использует репликацию два дня, все это страшно и тяжело. Но, зная, насколько она нехитро устроена, в принципе, понятно, как с ней жить:

прежде всего, не верим дефолтам;
внимательно смотрим на настройки, думаем, чего хотим – SBR, RBR и т.д.

И лучше сразу настроить, чтобы потом не разбирать странный фарш.

В ситуации "протух лог, разошлась позиция, неизвестно, что происходит" есть определенный инструментарий – смотрим event"ы, пытаемся понять, какая транзакция уже проскочила, какая – нет, можно ли все это дело спасти или восстановить и т.д. Если GTID"ы заранее сумели включить, то жизнь становится проще.

Другой момент наблюдения за репликацией. Интересно посмотреть, как внутреннее кривое устройство провоцирует не то, что конкуренцию, а создание дополнительных продуктов. "Волшебный" Tungsten Replicator, говорят, хорошо решает задачу под названием "однопоточный слэйв – это плохо", а если бы не врожденные сложности, не было бы дополнительного продукта, который позволяет пользоваться этим механизмом, переливать данные в другие системы, с одной стороны, и заодно решать ряд проблем, встроенных в существующую систему, с другой стороны.

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

Что делать, если вы зачем-то использовали реплики как бэкап? Я считаю, надо биться головой об стену, потому что реплика и бэкап – это две разные штуки. Тем не менее, если вы креативные пацаны и используете достаточно новую версию, delayed replication вас спасает, с одной стороны, но с другой стороны, если вы не делаете полноценных бэкапов, вас все равно ничего не спасет.

Далее еще один элемент креатива. Нетрудно представить ситуацию, когда мастер забил логами весь 10 PB облачный диск или забил рассылкой этих логов всю сеть, при этом 90% этих обновлений нам не нужны, потому что нам интересно реплицировать, например, одну таблицу прицельно или одну базу прицельно, а по умолчанию все валится валом в бинарный лог – все изменения по всем базам, по всем таблицам, по всему. Решение опять поражает своей креативностью. С одной стороны, есть четыре настройки – {binlog|replicate}_{do|ignore}_db, которые позволяют фильтровать на мастере – что запишется в лог, а что проигнорируется. На слэйве, соответственно, позволяет делать то же самое. Т.е. на мастере мы можем отфильтровать то, что попадает в binary log – в эту воронку, которая потом сливается в сеть, а на слэйве, соответственно, мы можем поставить входящий фильтр на то, что прилетает из сети. Или писать на диск только часть данных, а потом на слэйве реплеить, опять же, только часть данных. Внезапно даже в этой нехитрой истории наступает ужас, потому что комбинация – используем одну БД, а апдейтим таблицу в другой БД через интересный синтаксис – она ведет себя как-то... А как конкретно она себя поведет – неизвестно, т.к. разные фильтры срабатывают в разные моменты.

Встроенных приятных штук под названием "перевыборы мастера, если он внезапно сдох" нет, надо поднимать руками. Отсутствие инструментов для менеджмента кластера – это, по моему мнению, хорошо – порождает конкуренцию, порождает создание дополнительных продуктов. В самом деле, если бы в обычном MySQL идеально работала очень клевая мастер-мастер репликация, или хотя бы автоматическое поднятие после сбоев, то зачем бы была нужна всякая Galera, Рercona/MariaDB Cluster и т.д.?

Еще немного фокусов. Интересна реализация репликации, которая простая как палка и веревка, без всяких проверок, с одной стороны, и без всяких инструментов, чтобы приятнее менеджить кластер реплицирующегося слэйва, с другой стороны. Это плохо. Но зато можно вручную лепить из этого такие интересные конфигурации, что содрогнутся все, кто потом придет и за вами будет это разбирать.

Конфигурация №1. Мастер-мастер «на коленке» в стиле MySQL делается вот так:

Что пугает – сколько в мире идиотов! Погуглите "Мастер-мастер MySQL репликация" – каждая вторая ссылка вот такая. Ад и холокост.

Фокус №2 – catch-all slave – поприятнее. Никаких ненужных проверок нет – что с кого прилетает, кому попадает, и что с этим делать. За счет этого можно сделать забавные штуки типа слэйва, на который либо прицельно сливается часть данных с кучи серверов, либо прицельно сливаются все данные со всех серверов – сервер со всеми-всеми бэкапами. Но, повторюсь, репликация есть, т.е. есть некий базовый инструмент, который копирует таблицу А вместо В и все.

Ну и, наконец, фокус №3 – подменяем всякое. Вспоминаем, что репликация живет на логическом уровне, никак не связанном с физическим уровнем хранения. За счет этого можно крайне интересно чудить. Можно менять движок «на лету» с непонятными целями – вот true story, что, дескать, репликация из InnoDB баз в MyISAM таблицы просто ради того, чтобы полнотекстовый поиск работал хоть как-то. Есть креативный финт под названием "изменение схемы через репликацию". В чем жир, понимать отказываюсь, но бывают и такие фокусы. Ну и, есть понятный и интересный режим работы под названием "параноидальный апгрейд версии через репликацию".

В ходе доклада мы узнали:

Тем не менее, с этим адом можно жить, если хотя бы примерно понимать, как он устроен.

Основной посыл в том, что:

В 2015 году на конференции HighLoad++ Junior Андрей Аксёнов прочитал новую версию своего доклада об устройстве репликации в MySQL. Её мы тоже расшифровали и в своём блоге.

Всем доброго дня! Сегодня в нашей статье мы рассмотрим примеры настройки репликации типа “master-slave”.

Немного теории

Зачем нужна репликация? В первую очередь это подстраховка на случай, если основной mysql-сервер выйдет из строя, тогда можно переключиться на slave-сервер и продолжить работу. Во вторых, это возможность уменьшить нагрузку на основной сервер Mysql, используя master-сервер только для записи, а операции на чтение выполнять на slave-сервере. Как происходит репликация? Master-сервер пишет binlog-и, в которых указывает операции, которые выполняются над базой данных (базами данных) и запоминает смещение в журнале от его начала до текущей записи (позицию). Slave-сервер подключается к master-у, сравнивает значения позиций и считывает изменения в журнале начиная со значения собственной позиции и заканчивая значением позициии master-a. Изменения (команды) он применяет к базам данных на slave-сервере.

Установка и настройка Master

Изменяем 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 и позицию в нем. При выполнении команд над БД, позиция будет меняться.

Настройка Slave

В файл 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

Настройки безопасности на master-сервере

Параметр 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 сервера.

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

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