Как настроить репликацию mysql

Как настроить MySQL Master-Slave репликацию?

MySQL репликация типа Master-Slave часто используется для обеспечения отказоустойчивости приложений. Кроме этого, она позволяет распределить нагрузку на базу данных между несколькими серверами (репликами). Читайте подробнее о применении репликации.

Настройка репликации происходит в несколько шагов. Мы будем использовать два сервера с адресами:

Шаг 1. Настройка Мастера

На сервере, который будет выступать мастером, необходимо внести правки в my.cnf:

Шаг 2. Права на репликацию

Далее необходимо создать профиль пользователя, из под которого будет происходить репликация. Для этого запускаем консоль:

Далее создаем и назначаем права пользователю для реплики:

даем права пользователю slave_user с паролем password

Далее блокируем все таблицы в нашей базе данных:

Мы увидим что-то похожее на:

Выделенные значения мы будем использовать для запуска Слейва

Шаг 3. Дамп базы

Теперь необходимо сделать дамп базы данных:

mysqldump -u root -p newdatabase > newdatabase.sql

Разблокируем таблицы в консоли mysql:
UNLOCK TABLES;

Шаг 4. Создание базы на слейве

В консоли mysql на Слейве создаем базу с таким же именем, как и на Мастере:
CREATE DATABASE newdatabase;

После этого загружаем дамп (из bash):

Шаг 5. Настройка Слейва

В настройках my.cnf на Слейве необходимо указать такие параметры:

Шаг 6. Запуск Слейва

Нам осталось включить репликацию, для этого необходимо указать параметры подключения к мастеру. В консоли mysql на Слейве необходимо выполнить запрос:

Проверить работу репликации на Слейве можно запросом:

Читайте также как настроить Master-Master репликацию на MySQL.

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

Что такое индексы в Mysql и как их использовать для оптимизации запросов

Как исправить ошибку доступа к базе 1045 Access denied for user

Примеры ad-hoc запросов и технологии для их исполнения

Основные понятия о шардинге и репликации

Настройка Master-Master репликации на MySQL за 6 шагов

Check-unused-keys для определения неиспользуемых индексов в базе данных

Как создать и использовать составной индекс в Mysql

Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit

Синтаксис и оптимизация Mysql LIMIT

Правильная настройка Mysql под нагрузки и не только. Обновлено.

Типы и способы применения репликации на примере MySQL

Запрос для определения версии Mysql: SELECT version()

3 примера установки индексов в JOIN запросах

Быстрый подсчет уникальных значений за разные периоды времени

И как правильно работать с длительными соединениями в MySQL

Просмотр профиля запросов в Mysql

Анализ медленных запросов с помощью EXPLAIN

Правила выбора типов данных для максимальной производительности в Mysql

Описание, рекомендации и значение параметра query_cache_size

Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL

Использование партиций для ускорения сложных удалений

Источник

Репликация баз данных MySQL. Введение

Редкая современная продакшн система обходится без репликации баз данных. Это мощный инструмент на пути к повышению производительности и отказоустойчивости системы, и современному разработчику очень важно иметь хотя бы общее представление о репликации. В данной статье я поделюсь базовыми знаниями о репликации, и покажу простой пример настройки репликации в MySQL с помощью Docker.

Что такое репликация, и зачем она нужна

Само по себе, понятие репликации означает процесс синхронизации нескольких копий объекта. В нашем случае, таким объектом является сервер БД, а наибольшую ценность представляют собой сами данные. Если мы имеем два и более серверов, и любым возможным способом поддерживаем синхронизированный набор данных на них — мы реализовали репликацию системы. Даже ручной вариант с mysqldump -> mysql load — это также репликация.

Стоит понимать, что сама по себе репликация данных не имеет ценности, и является лишь инструментом решения следующих задач:

• повышение производительности чтения данных. С помощью репликации мы сможем поддерживать несколько копий сервера, и распределять между ними нагрузку.
• повышение отказоустойчивости. Репликация позволяет избавиться от единственной точки отказа, которой является одиночный сервер БД. В случае аварии на основном сервере, есть возможность быстро переключить нагрузку на резервный.
• распространение данных. В современную эпоху глобализации ваше приложение может обслуживать пользователей со всего мира, и мы хотим, чтобы жители и Сиднея, и Хельсинки имели минимальную задержку доступа к нему.
• распределение нагрузки. В случае, если БД обслуживает запросы разных типов (быстрые и легкие, медленные и тяжелые), может иметь смысл развести эти запросы по разным серверам, для увеличения эффективности работы каждого типа.
• тестирование новых конфигураций. С помощью репликации есть возможность проведения тестирования новых версий сервера БД, изменения параметров конфигурации, и даже изменения типов хранилища данных.
• резервное копирование. С помощью репликации есть возможность делать механизмы резервного копирования более гибкими и вносить меньше негативных эффектов в работающую систему.

Как MySQL реплицирует данные

Процесс репликации подразумевает собой распространение изменений данных с главного сервера (обычно он называется как мастер, master), на один или более подчиненных серверов (слейв, slave). Существуют и более сложные конфигурации, в частности с несколькими мастер-серверами, но для каждого изменения на конкретном мастер-сервере остальные мастера условно становятся слейвами, и потребляют эти изменения.

В общем виде, репликация в MySQL состоит из трех шагов:

1. Мастер-сервер записывает изменения данных в журнал. Этот журнал называется двоичным журналом (binary log), а изменения — событиями двоичного журнала.
2. Слейв копирует изменения двоичного журнала в свой, который называется журналом ретрансляции (relay log).
3. Слейв воспроизводит изменения из журнала ретрансляции, применяя их к собственным данным.

Виды репликации

Существует два принципиально разных подхода к репликации: покомандная и построчная. В случае покомандной репликации, в журнал мастера протоколируются запросы изменения данных (INSERT, UPDATE, DELETE), а слейвы в точности воспроизводят те же команды у себя. При построчной же репликации в журнале окажутся непосредственно изменения строк в таблицах, и эти же фактические изменения применятся затем на слейве.

Как нет серебряной пули, так и каждый из этих методов имеет свои преимущества и недостатки. Покомандная репликация проще в реализации и понимании, снижает нагрузку на мастер и на сеть. Но тем не менее, покомандная репликация может приводить к непредсказуемым эффектам, при использовании недетерминированных функций, таких как NOW(), RAND(), и т.д. Могут быть также проблемы, вызванные рассинхронизацией данных между мастером и слейвом. Построчная же репликация приводит к более прогнозируемым результатам, так как фиксируются и воспроизводятся фактические изменения данных. Тем не менее этот метод может значительно увеличивать нагрузку на мастер-сервер, которому приходится фиксировать каждое изменение в журнале, и на сеть, через которую эти изменения распространяются.

В MySQL поддерживаются оба способа репликации, а дефолтный (можно сказать, что и рекомендуемый) изменялся в зависимости от версии. В современных версиях, например MySQL 8, по умолчанию используется построчная репликация.

Второй принцип разделения подходов к репликации — количество мастер-серверов. Наличие одного мастер сервера подразумевает, что только он принимает изменения данных, и является неким эталоном, с которого уже распространяются изменения на множество слейвов. В случае же с мастер-мастер репликацией мы получаем как и некоторый профит, так и проблемы. Один из плюсов, например, то, что мы можем давать удаленным клиентам из тех же Сиднея и Хельсинки одинаково быструю возможность записывать свои изменения в базу. Из этого исходит и главный недостаток, если оба клиента одновременно изменили одни и те же данные, чьи изменения считать окончательными, чью транзакцию коммитить, а чью откатывать.

Также, стоит отметить, что наличие мастер-мастер репликации в общем случае не может увеличить производительность записи данных в системе. Представим, что наш единственный мастер может обрабатывать до 1000 запросов в единицу времени. Добавив к нему реплицируемый второй мастер, мы не сможем обрабатывать по 1000 запросов на каждом из них, так как кроме обработки “своих” запросов, им придется применять изменения, сделанные на втором мастере. Что в случае покомандной репликации сделает суммарно возможную нагрузку на оба не больше, чем на самый слабый из них, а с построчной репликацией эффект не совсем предсказуемый, может быть как положительный, так и отрицательный, в зависимости от конкретных условий.

Пример построения простой репликации в MySQL

А сейчас настало время создать простую конфигурацию репликации в MySQL. Для этого мы будем использовать Docker и MySQL образы из dockerhub, а также базу данных world.

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

Для мастер контейнера указано подключение volume c дампом world.sql, для того, чтобы имитировать наличие некоторой начальной базы на нем. При создании контейнера, mysql загрузит и выполнит sql скрипты, размещенные в директории docker-entrypoint-initdb.d.

Для работы с конфигурационными файлами, нам потребуется текстовый редактор. Можно использовать любой удобный, я предпочитаю vim.

Первым делом, создадим учетную запись на мастере, которая будет использоваться для репликации:

Далее, изменим конфигурационные файлы для мастер-сервера:

В файл my.cnf в секции [mysqld] необходимо добавить следующие параметры:

При включении/выключении двоичного журнала необходима перезагрузка сервера. В случае с Docker перезагружается контейнер.

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

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

Далее, с помощью mysqldump сделаем экспорт данных из базы. Конечно, в данном примере можно использовать тот же world.sql, но приблизимся к более реалистичному сценарию.

После этого, необходимо еще раз выполнить команду SHOW MASTER STATUS, и запомнить или записать значения File и Position. Это, так называемые координаты двоичного журнала. Именно от них мы далее укажем стартовать слейву. Начиная с MySQL 5.6 стало возможным использование глобальных идентификаторов транзакций GTID вместо координат в виде файл-позиция. Это упростило настройку репликации, а также повысило стабильность ее работы. Но рассмотрение этой темы выходит за рамки данной статьи, и с ней можно ознакомиться в документации.

Теперь можем снова разблокировать мастер:

Мастер настроен, и готов реплицироваться на другие сервера. Перейдем теперь к слейву. Первым делом, загрузим в него дамп, полученный с мастера.

А затем изменим конфиг слейва, добавив параметры:

После этого перезагрузим слейв:

И теперь нам нужно указать слейву, какой сервер будет являться для него мастером, и откуда начинать реплицировать данные. Вместо MASTER_LOG_FILE и MASTER_LOG_POS необходимо подставить значения, полученные из SHOW MASTER STATUS на мастере. Эти параметры вместе называются координатами двоичного журнала.

Запустим воспроизведение журнала ретрансляции, и проверим статус репликации:

Если все прошло успешно, ваш статус должен иметь аналогичный вид. Ключевые параметры здесь:

• Slave_IO_State, Slave_SQL_State — состояние IO потока, принимающего двоичный журнал с мастера, и состояние потока, применяющего журнал ретрансляции соотвественно. Только наличие обоих потоков свидетельствует об успешном процессе репликации.
• Read_Master_Log_Pos — последняя позиция, прочитанная из журнала мастера.
• Relay_Master_Log_File — текущий файл журнала мастера.
• Seconds_Behind_Master — отставание слейва от мастера, в секундах.
• Last_IO_Error, Last_SQL_Error — ошибки репликации, если они есть.

Попробуем изменить данные на мастере:

И проверить, появились ли они на слейве.

Отлично! Внесенная запись видна и на слейве. Поздравляю, теперь вы создали свою первую репликацию MySQL!

Заключение

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

• Доклад «Как устроена MySQL-репликация» Андрея Аксенова (Sphinx)
• Книга “MySQL по максимуму. Оптимизация, репликация, резервное копирование” — Бэрон Шварц, Петр Зайцев, Вадим Ткаченко
• «Хайлоад» — здесь можно найти конкретные рецепты по репликации данных

Надеюсь, что данная статья была полезна, и буду рад отзывам и комментариям!

Источник

Основы репликации в MySQL

Небольшое введение

Репликация (от лат. replico -повторяю) — это тиражирование изменений данных с главного сервера БД на одном или нескольких зависимых серверах. Главный сервер будем называть мастером, а зависимые — репликами.
Изменения данных, происходящие на мастере, повторяются на репликах (но не наоборот). Поэтому запросы на изменение данных (INSERT, UPDATE, DELETE и т. д.) выполняются только на мастере, а запросы на чтение данных (проще говоря, SELECT) могут выполняться как на репликах, так и на мастере. Процесс репликации на одной из реплик не влияет на работу других реплик, и практически не влияет на работу мастера.
Репликация производится при помощи бинарных логов, ведущихся на мастере. В них сохраняются все запросы, приводящие (или потенциально приводящие) к изменениям в БД (запросы сохраняются не в явном виде, поэтому если захочется их посмотреть, придется воспользоваться утилитой mysqlbinlog). Бинлоги передаются на реплики (бинлог, скачанный с мастера, называется «relay binlog «) и сохраненные запросы выполняются, начиная с определенной позиции. Важно понимать, что при репликации передаются не сами измененные данные, а только запросы, вызывающие изменения.
При репликации содержимое БД дублируется на нескольких серверах. Зачем необходимо прибегать к дублированию? Есть несколько причин:

• производительность и масштабируемость. Один сервер может не справляться с нагрузкой, вызываемой одновременными операциями чтения и записи в БД. Выгода от создания реплик будет тем больше, чем больше операций чтения приходится на одну операцию записи в вашей системе.
• отказоустойчивость. В случае отказа реплики, все запросы чтения можно безопасно перевести на мастера. Если откажет мастер, запросы записи можно перевести на реплику (после того, как мастер будет восстановлен, он может принять на себя роль реплики).
• резервирование данных. Реплику можно «тормознуть » на время, чтобы выполнить mysqldump, а мастер — нет.
• отложенные вычисления. Тяжелые и медленные SQL-запросы можно выполнять на отдельной реплике, не боясь помешать нормальной работе всей системы.

Кроме того, есть некоторые другие интересные возможности. Поскольку на реплики передаются не сами данные, а запросы, вызывающие их изменения, мы можем использовать различную структуру таблиц на мастере и репликах. В частности, может отличаться тип таблицы (engine) или набор индексов. Например, для осуществления полнотекстового поиска мы можем на реплике использовать тип таблицы MyISAM, несмотря на то, что мастер будет использовать InnoDB.

Настройка репликации

Настройки мастера

Обязательно укажем уникальный ID сервера, путь для бинарных логов и имя БД для репликации в секции [mysqld]:
= 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-бинлогам в секции [mysqld] конфига, затем перезагрузим MySQL:
= 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 на второй реплике и убедимся, что мы внесли нужные параметры в конфиг:
= 3
replicate-do-db = testdb

Теперь остановим репликацию на первой реплике:
stop slave;

Реплика продолжит работать нормально, однако данные на ней уже не будут актуальными. Посмотрим статус и запомним позицию мастера, до которой реплика дошла перед остановкой репликации:
SHOW SLAVE STATUS\G

Нам нужные будет значения Master_Log_File и Exec_Master_Log_Pos:
Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 155

Создадим дамп БД и продолжим репликацию на первой реплике:
START SLAVE;

Восстановим данные из дампа на второй реплике. Затем включим репликацию:
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;
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 командой и запомним позицию мастера:
STOP SLAVE;
SHOW SLAVE STATUS\G
Master_Log_File: mysql-bin.000015
Exec_Master_Log_Pos: 231

Создадим дамп БД testdb2 и возобновим репликацию (на этом манипуляции с replica-2 закончились). Дамп восстановим

Ситуация на replica-1 такая: БД testdb1 находится на одной позиции мастера и продолжает реплицироваться, БД testdb2 восстановлена из дампа с другой позиции. Синхронизируем их.

Остановим репликацию и запомним позицию мастера:
STOP SLAVE;
SHOW SLAVE STATUS\G
Master_Log_File: mysql-bin.000016
Exec_Master_Log_Pos: 501

Убедимся, что в конфиге на секции [mysqld] указано имя второй БД:
replicate-do-db = testdb2

Перезагрузим MySQL, чтобы изменения в конфиге вступили в силу. Кстати, можно было просто перезагрузить MySQL, не останавливая репликацию — из лога мы бы узнали, на какой позиции мастера репликация остановилась.

Теперь проведем репликацию с позиции, на которой была приостановлена позиции, на которой мы только что приостановили репликацию:
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;
start slave until MASTER_LOG_FILE = «mysql-bin.000016 «, MASTER_LOG_POS = 501;

Репликация закончится, как только реплика дойдет до указанной позиции в секции until, после чего обе наши БД будут соответствовать одной и той же позиции мастера (на которой мы остановили репликацию Убедимся в этом:
SHOW SLAVE STATUS\G
START SLAVE;
Master_Log_File: mysql-bin.000016
Exec_Master_Log_Pos: 501

Добавим в конфиг на секции [mysqld] имена обеих БД:
replicate-do-db = testdb1
replicate-do-db = testdb2

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

Рокировка мастера и реплики

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

Включим ведение бинарных логов (дополнительно к relay-бинлогам) в конфиге в секции [mysqld]:
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.
• изменение логики работы приложения. В идеальной ситуации, запросы на чтение данных надо направлять на реплики, а на изменение — на мастер. Однако, отставания реплик, такая схема часто неработоспособна и необходимо выявлять такие запросы на чтение, которые все же должны выполнятся на мастере.

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

Источник