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

MySQL. Репликация базы данных Хостинг

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

Новости

Документация

Download

Webboard

Поиск

FAQ/ЧаВо

Обратная связь

Содержание
  1. 1: Установка и настройка MySQL на сервере А
  2. 2: Установка и настройка MySQL на сервере Б
  3. 3: Завершение репликации
  4. 4: Тестирование репликации Master-Master
  5. Что такое репликация MySQL?
  6. Требования
  7. 1: Настройка базы данных Master
  8. 2: Настройка slave базы данных
  9. Введение в репликацию Mysql
  10. Обзор репликации бинарного лога (Binary Log File Position Based Replication)
  11. Обзор репликации с Глобальными Идентификаторами транзакции (Global transaction identifier (GTID) Based Replication)
  12. Допущения в статье
  13. Топология репликации
  14. Настройка репликации бинарного лога (aka Master-slave)
  15. 1. Редактирование конфигов на мастер сервере и репликах
  16. 2. Создание пользователя для репликации
  17. Пример настройки сервера mysql master-slave
  18. 3. Получение координат бинарного лога с Мастер сервера
  19. 4. Перенос данных MySQL с Мастер-сервера на реплику
  20. Создание копии данных MySQL с помощью mysqldump
  21. Создание копии данных MySQL с помощью сырых файлов
  22. Передача файлов на реплику
  23. Настройка slave сервера MySQL, когда на источнике нет данных (чистый)
  24. Настройка slave сервера MySQL, при существующих данных в БД
  25. Добавление Slave сервера в существующее окружение репликации
  26. Multi-source репликация MySQL/MariaDB
  27. Репликация мастер-мастер (или круговая репликация)
  28. Переключение сервера MySQL из режима репликации в отдельный сервер (отключение репликации MySQL)
  29. Переключение мастер-сервера при отказах мастер-сервера (переключение на новый источник репликации)
  30. Рекомендации по резервному копированию в режиме MySQL репликации
  31. Диагностика репликации MySQL
  32. Полезные команды для траблшутинга MySQL
  33. Типичные ошибки репликации (и способы устранения)
  34. Основные шаги диагностики
4.10.4 Возможности репликации и известные проблемы
  • 4 Администрирование баз данных
    • 4.10 Репликация в MySQL
      • 4.10.1 Введение
      • 4.10.2 Как реализована репликация: обзор
      • 4.10.3 Как настроить репликацию
      • 4.10.4 Возможности репликации и известные проблемы
      • 4.10.5 Опции репликации в файле `my.cnf'
      • 4.10.6 SQL-команды, относящиеся к репликации
      • 4.10.7 Часто задаваемые вопросы по репликации
      • 4.10.8 Поиск неисправностей репликации




23 ноября, 2015 12:33 пп

Cloud Server, Linux, VPS

Репликация MySQL – это процесс, во время которого единый набор данных, хранящийся в БД MySQL, используется несколькими серверами одновременно. Как правило, репликация настраивается по типу master-slave (где есть ведущий и ведомые серверы). Однако репликация по типу master-master (где, условно говоря, все серверы – ведущие) позволяет копировать данные с любого сервера, потому она более производительна. Это тонкое, но очень важное различие позволяет MySQL выполнять операции записи и чтения с любого сервера.

Примечание: В предыдущей статье рассказывается о балансировке нагрузки nginx.

В данном руководстве используется два сервера:

  • Сервер А: 3.3.3.3
  • Сервер Б: 4.4.4.4

1: Установка и настройка MySQL на сервере А

Для начала нужно установить пакеты mysql-server и mysql-client. Для этого введите:

sudo apt-get install mysql-server mysql-client

По умолчанию mysql принимает соединения на локальный хост (127.0.0.1). Чтобы изменить эту и некоторые другие стандартные настройки, необходимые для репликации, отредактируйте /etc/mysql/my.cnf на сервере А. В нём нужно изменить следующие 4 строки, которые на данный момент выглядят так:

#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1

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

Примечание: В данной статье будет тиражироваться одна БД по имени example.

Последняя строка настраивает сервер для поддержки соединений из Интернета (не прослушивая 127.0.0.1).

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = example
# bind-address            = 127.0.0.1

sudo service mysql restart

Затем нужно изменить некоторые настройки при помощи командной строки mysql. Вернитесь в оболочку и откройте сессию root-пользователя mysql:

mysql -u root -p

Примечание: При этом программа запросит root-пароль mysql (не root-пароль сервера).

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

Теперь нужно запустить несколько команд.

Создайте псевдопользователя, который будет использоваться для репликации данных между серверами. В данном руководстве этот пользователь будет называться replicator (замените это условное имя и слово password соответствующими данными).

Затем нужно дать этому пользователю права на репликацию данных:

К сожалению, права на репликацию выдаются глобально (а не индивидуально для каждой отдельной БД). Чтобы объяснить пользователю, какие БД нужно дублировать, используется отдельный конфигурационный файл.

В завершение нужно получить некоторые сведения о текущем экземпляре MySQL, которые позже нужно предоставить серверу Б. Для этого запустите команду:

show master status;

На экране появится подобный вывод:

Запишите данные File и Position, они понадобятся в дальнейшем.

2: Установка и настройка MySQL на сервере Б

Теперь нужно повторить те же действия на втором сервере. Установите все необходимые пакеты:

sudo apt-get install mysql-server mysql-client

После этого отредактируйте файл /etc/mysql/my.cnf.

sudo nano /etc/mysql/my.cnf

Измените те же четыре строки. По умолчанию они выглядят так:

#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1

Эти строки должны выглядеть следующим образом (пожалуйста, обратите внимание: сервер Б не может содержать в настройках 1, так как это значение уже использует сервер А).

server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = example
# bind-address            = 127.0.0.1

После этого нужно перезапустить mysql:

sudo service mysql restart

Откройте оболочку mysql:

mysql -u root -p

Сначала, как и на сервере А, нужно создать пользователя для выполнения репликации.

Примечание: Не забудьте заменить условные данные в команде своими данными.

Затем создайте БД, которая будет дублирована:

create database example;

Теперь передайте права на репликацию БД ранее созданному пользователю.

Чтобы запустить репликацию, необходимо предоставить серверу Б информацию о сервере А (которая была записана ранее). Введите:

Примечание: Укажите настоящий пароль, выбранный для репликации, вместо условного пароля password.

Значения MASTER_LOG_FILE и MASTER_LOG_POS могут отличаться; скопируйте значения, полученные при помощи команды show master status на сервере А.

Последнее, что нужно сделать в настройке репликации по типу master-master, – записать значения File и Position сервера Б.

Для этого используйте команду:

SHOW MASTER STATUS;

На экране появится результат:

Запишите эти значения, в дальнейшем их нужно будет передать серверу А.

3: Завершение репликации

Вернитесь на сервер А и завершите настройку при помощи командной строки. запустите:

Помните о том, что значения могут отличаться. Также не забудьте заменить значение MASTER_PASSWORD своим паролем, установленным для пользователя replicator.

Результат будет выглядеть примерно так:

Query OK, 0 rows affected (0.01 sec)

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

4: Тестирование репликации Master-Master

Теперь серверы настроены и готовы к репликации данных. Чтобы протестировать репликацию, создайте таблицу в тестовой БД. Эта таблица должна появиться на обоих серверах. Также таблица должна исчезнуть на обоих серверах после удаления.

На сервере А создайте БД example, которая будет дублирована между серверами. В оболочке mysql запустите:

create database example;

Затем создайте тестовую таблицу (условно назовём её test):

create table example.test (`id` varchar(10));

Проверьте, появилась ли БД и таблица на сервере Б:

show tables in example;

На экране должен появиться прмерно такой вывод:

Теперь попробуйте удалить тестовую таблицу на сервере Б. Она должна исчезнуть и на сервере А.

На сервере Б запустите:

DROP TABLE test;

Теперь команда show tables на сервере А должна вернуть следующее:

Empty set (0.00 sec)

Готово! Репликация данных mysql по типу master-master спешно настроена.

Tags: MySQL






23 июня, 2014 12:27 пп

Что такое репликация MySQL?

Репликация MySQL – это процесс, позволяющий легко поддерживать несколько копий данных MySQL путем их автоматического копирования из базы данных master (ведущей) в slave (ведомую). Это упрощает резервное копирование данных, помогает анализировать их без использования главной БД, а также используется в качестве средства масштабирования.

Данное руководство приводит очень простой пример репликации MySQL, в котором база данных master передает информацию БД slave. Для выполнения данного процесса нужны два IP: для master-сервера и для slave-сервера.

  • 12.34.56.789- Master
  • 12.23.34.456- Slave

Требования

В данной статье предполагается наличие пользователя с привилегиями sudo, а также уже установленной системы MySQL. Чтобы установить MySQL, наберите:

sudo apt-get install mysql-server mysql-client

1: Настройка базы данных Master

На master-сервере откройте конфигурационный файл mysql:

sudo nano /etc/mysql/my.cnf

В данный файл нужно внести несколько изменений.

Для начала найдите раздел, который выглядит так (он связывает сервер с локальным хостом):

bind-address            = 127.0.0.1

bind-address            = 12.34.56.789

Следующее изменение касается директивы server-id, расположенной в разделе mysqld. Здесь можно задать любую переменную (возможно, проще всего начать с 1), но число должно быть уникальным и не совпадать ни с одним другим server-id в группе репликации.

Убедитесь, что строка раскомментирована:

server-id               = 1

Затем найдите строку log_bin. Она содержит детали о репликации. Slave-сервер будет копировать все изменения, зарегистрированные в журнале. В данном случае нужно просто раскомментировать строку log_bin:

Читайте также:  Раскройте потенциал своего бизнеса с помощью расширенных административных программ

log_bin                 = /var/log/mysql/mysql-bin.log

В завершение укажите базу данных, которую нужно копировать на slave-сервер. Можно вносить более одной базы данных, повторяя эту линию в конфигурациях каждой нужной базы.

binlog_do_db            = newdatabase

Внеся все нужные изменения, сохраните их и закройте конфигурационный файл.

sudo service mysql restart

Остальные действия нужно выполнить в оболочке MySQL.

Откройте оболочку MySQL:

mysql -u root -p

Передайте привилегии slave-серверу. Эту строку можно также использовать для того, чтобы указать имя и пароль slave-сервера. Команда имеет такой формат:

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

В текущей вкладке откройте базу данных “newdatabase”.

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

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

Должна появиться подобная таблица:

С этой позиции slave БД начнет репликацию. Запишите эти числа, они пригодится позже.

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

База данных все еще должна оставаться заблокированной. Экспортируйте базу данных в новое окно с помощью mysqldump (следующую команду нужно выполнить в оболочке bash, а не MySQL).

mysqldump -u root -p --opt newdatabase > newdatabase.sql

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

Теперь master БД готова.

2: Настройка slave базы данных

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

CREATE DATABASE newdatabase;
EXIT;

Импортируйте ранее экспортированную из master базу данных.

mysql -u root -p newdatabase < /path/to/newdatabase.sql

Теперь нужно настроить slave таким же образом, как это было с master:

sudo nano /etc/mysql/my.cnf

Следуя советам предыдущего раздела, установите некоторые важные конфигурации. Начните с server-id; как упоминалось ранее, этот номер должен быть уникальным. Так как в предыдущем разделе было установлено значение 1, теперь нужно установить другое:

server-id               = 2

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

relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase

Кроме того, нужно внести строку relay-log, которой нет по умолчанию. По завершении не забудьте сохранить и закрыть конфигурационный файл slave.

Снова перезапустите MySQL:

sudo service mysql restart

Далее нужно активировать репликацию в оболочке MySQL.

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

Данная команда выполняет несколько действий:

  1. определяет текущий сервер как slave-сервер;
  2. предоставляет серверу правильные данные для входа;
  3. говорит slave-серверу, откуда начинать репликацию; журнал master-сервера и позиция, с которой нужно начинать репликацию, указываются с помощью чисел, которые были записаны ранее.

Готово! master- и slave-сервер настроены.

Просмотреть подробности репликации можно при помощи следующей команды. Параметр \G упорядочивает текст, что делает его более удобным для чтения.

SHOW SLAVE STATUS\G

При возникновении проблем со связью попробуйте запустить slave при помощи следующей команды:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Tags: Linux, Master/Slave, MySQL, VPS


Репликация MySQL

Всем привет. Давненько я не писал. Сегодня будет лонгрид. Некоторое время назад стояла задача развернуть несколько серверов Mysql в конфигурации с репликацией базы данных и описать весь процесс. Данная инсталляция легла в основу статьи. Статья написана на основе официальной документации Mysql. По большей части, является структурированным переводом. Любые дополнения приветствуются. Поехали.

Введение в репликацию Mysql

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

MySQL поддерживает различные методы репликации:

  • Традиционный метод, который основан на репликации событий (events) из бинарного лога источника (binary log replication). В основном именно этот метод и будет рассмотрен в статье. (индикатор — опция --log-bin)
  • Более новый метод репликации — основан на репликации глобальных идентификаторов транзакций (т.н. global transaction identifiers или GTIDs). Данный метод не требует указания координат бинарного лог-файла мастер-сервера и в некотором смысле — проще. Использование GTIDs гарантирует консистентность между источником и репликой. (индикатор — опция gtid_mode)

MySQL умеет различные типы/схемы синхронизации между источником и репликой:

  • однонаправленная, асинхронная репликация, при которой один сервер является источником, а остальные — репликами. При этом, реплика тоже может быть источником. Именно эту схему репликации и рассмотрим (CHANGE MASTER TO...)
  • синхронная репликация — такая конфигурация используется при работе NDB Cluster.
  • полусинхронная (semisynchronous) репликация — commit транзакции будет подтвержден, только тогда, когда хотябы одна из реплик подтвердит, что событие получено и зафиксировано/логировано.
  • репликация с задержкой (delayed) — между данными источника и репликой будет задержка, заданная администратором. Обычно используется для тестирования или для защиты от ошибок на мастере.

Существуют три основных типа формата реплицируемых событий (переменная binlog_format):

  • Statement Based Replication (SBR), при которой реплицируется SQL запрос/SQL выражение
  • Row Based Replication (RBR), реплицируются только измененные строки БД
  • Mixed Based Replication (MBR) это комбинация SBR и RBR

Каждый из данных форматов имеет свои особенности, недостатки и достоинства. Эта тема для отдельной статьи.

Обзор репликации бинарного лога (Binary Log File Position Based Replication)

Каждая реплика получает полную копию бинарного лога и именно реплика отвечает за то, чтобы выполнить все (или не все, а только не/отфильтрованные или только для заданных таблиц/БД) запросы из полученного лог-файла.

Slave-сервер понимает откуда начать читать лог, исходя из заданных координат при настройке репликации:

  • имя файла лога
  • позиция в этом файле

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

Резюмируя вышесказанное, а именно, наличие координат и возможность фильтровать/задавать таблицы и БД для чтения лога, позволяет подключить различные slave серверы к одному master и обрабатывать различные части исходной БД. Важно учитывать, что разработчики и приложения должны учитывать данные особенности, чтобы обеспечить консистентность данных/индексов/пр.

Обзор репликации с Глобальными Идентификаторами транзакции (Global transaction identifier (GTID) Based Replication)

GTID — это уникальный идентификатор, который создается и ассоциируется с каждой завершенной (commit) транзакцией на master сервере. Эта транзакция уникальна для всех серверов — участников репликации.
Таким образом, когда транзакция клиента выполнена (commit) и записана в бинарный лог на сервере-источнике, ей присваивается новый GTID. Каждый идентификатор GTID монотонно увеличивается, без промежутков в нумерации.

GTID представляет из себя пару координат, разделенных дветочием. Например:
GTID = source_id:transaction_id
или
GTID = 3E00FA47-22CA-01E1-9E33-C80UU9429452:23
, где

  • source_id обычно, это значение server_uuid мастер-сервера
  • transaction_id — порядковый номер транзакции

стоит знать, что в новых версиях MySQL/MariaDB формат GTID изменился и упростился

GTID сохраняется в телице БД mysql.gtid_executed только когда значение параметра gtid_modeON или ON_PERMISSIVE. Хотя , при включенном режиме gtid_mode, на мастер-сервере журналирование должно быть включено обязательно для возможности реплицировать завершенные транзакции, реплики могут работать без включения бинарного лога. Выключить бинарный лог можно, запустив сервер с опциями --skip-log-bin и --log-slave-updates=OFF

Допущения в статье

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

  • в статье не рассматриваются вопросы установки сервера (обычно, это просто apt install mysql-server mysql-client)
  • правила netfilter/iptables так же не рассмтариваются в данной статье (используются стандартные порты mysql — tcp/3306)
  • версия СУБД и дистрибутив Linux, на котором развернут стенд: Ubuntu 20.04 / Mysql 8.0.23-0ubuntu0.20.04.1
  • TLS/SSL настройки так же исключены и для авторизации используется стандартный плагин аутентификации mysql_native_password
  • GTID репликация рассмотрена поверхностно (тема достойна отдельной статьи)

Топология репликации

master1(Master/Source/10.0.2.2)  - async ->  slave1(Slave/Replica/10.0.2.4)
       /|\   |          \                            |
        |    |          |                       Multi-source
        |    |          |                          async
     master-master      |                            |   
     ( circular )       |                           \|/  
        |    |           \- async -> slave2(Second Slave/Replica/10.0.2.5)
        |   \|/
master2(Second Master/Source/10.0.2.3)		
MySQL. Репликация базы данных

Настройка репликации бинарного лога (aka Master-slave)

1. Редактирование конфигов на мастер сервере и репликах

server_id = 1

Этот параметр задает идентификатор сервера. Для всех серверов, которые включены в топологию репликации server_id должен быть установлен в диапазоне от 1 (по-умолчанию) до 4294967295 и он должен быть уникальным в рамках данной топологии. Значение параметра может быть изменено динамически командой mysql SET GLOBAL server_id = 2;. При этом, стоит знать, что если установить значение в ноль, то это отключит любые отношения репликации (отключит репликацию), изменение параметра в 0 требует перезагрузки/перезапуска сервера.

# на источнике
binlog_do_db    = db_name
# на получателях/репликах
replicate_do_db    = db_name

Необходимо задать базы данных, которые будут вовлечены (или исключены) в/из репликации. При старте репликации, реплика проверяет, есть ли база данных в параметрах --replicate-do-db или --replicate-ignore-db. Такой же параметр есть для мастера --binlog-do-db и --binlog-ignore-db. Поведение опций — как черные и белые списки. Опция -do- заставит сервер создавать бинарный лог только для баз данных из этой опции, опция -ignore- заставит сервер создавать бинарный лог для всех баз, кроме тех что указаны в опции.

Читайте также:  Как получить бесплатный хостинг для сайта

log_bin = base_name_of_log

Примечание: В разных источниках в интернете есть небольшая путаница с log-bin параметром. Где-то он указан без аргумента, где-то с аргументом. Это происходит из-за разной интерпретации данного аргумента разными версиями MySQL/MariaDB. Старые версии рассматривают его только как включение бинарного лога, в новых версиях — значение этого параметра воспринимается как базовое имя фала бинарного лога.

Эта опция обязательна на мастере. На самом деле, в старых версиях MySQL требовалось задавать/включать параметр log_bin, но с версии ~5.6 он включен по умолчанию. Для выключения этой опции, необходимо при запуске сервиса указать параметр --skip-log-bin или --disable-log-bin. Как только эта опция указана, все запросы к БД (которые вносят изменения) логируются в бинарный лог. Если задан параметр как в примере, все имена файлов будут иметь вид:

/var/lib/mysql/base_name_of_log.000001 
/var/lib/mysql/base_name_of_log.000002
 ...  

На самом деле, есть еще одна опция, которая влияет на работу источника:

  • для большей надежности и стабильности репликации БД, использующих движок InnoDB, рекомендуется включить опции innodb_flush_log_at_trx_commit=1 и sync_binlog=1 на мастер-сервере.
  • чтобы разрешить сетевые взаимодействия с сервером БД, необходимо убедиться, что выключена skip_networking и bind-address слушает правильный сетевой интерфейс.

2. Создание пользователя для репликации

Каждая реплика подключается к мастеру с помощью MySQL пользователя и пароля. Соответственно, на каждом мастере должен быть создан пользователь, который может использоваться репликой для подключения. Если учетная запись создается только для репликации, она должна иметь привелегии REPLICATION SLAVE. Например, для создания нового пользователя replication, который будет подключаться с любого хоста в домене k-max.name, необходимо выполнить следующее:

mysql> CREATE USER 'replication'@'%.k-max.name' IDENTIFIED IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.k-max.name';

Пример настройки сервера mysql master-slave

#/etc/mysql/my.cnf
server-id       = 2
log_bin
binlog_do_db    = db_name
#mysql CLI
mysql> CREATE USER 'replication'@'%.k-max.name' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.k-max.name';
#/etc/mysql/my.cnf
server-id           = 4
log_bin
replicate-do-db	    = db_name

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

3. Получение координат бинарного лога с Мастер сервера

Следущий шаг — это получить информацию о том, откуда (с какого места, с какой транзакции) слейв-серверу начинать репликацию бинарного лога.

  1. Если сервер-источник использует движок MyISAM и выполняет большое количество запросов на запись, желательно заблокировать эту активность (на самом деле, там немного все сложнее, да):
    mysql> FLUSH TABLES WITH READ LOCK;

  2. Получите текущих координат бинарного лога на мастер-сервере:

mysql> SHOW MASTER STATUS;                                                                        
+--------------------+----------+--------------+------------------+-------------------+   
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |   
+--------------------+----------+--------------+------------------+-------------------+   
| master1-bin.000001 |      156 | db_name      |                  |                   |   
+--------------------+----------+--------------+------------------+-------------------+   
1 row in set (0.00 sec)                                                                           

Пояснения к выводу команды: поле File показывает имя файла бинарного лога (обычно, в каталоге /var/lib/mysql/), Position показывает позицию в файле. Именно эти данные нужны для реплики.

Следующие шаги зависят от того, имеете ли Вы данные в БД на сервере-источнике:

  • Если сервер-источник имеет существующие базы данных с данными, необходимо эти данные перенести на реплику перед стартом процесса репликации.
  • Если исходный сервер чист, то можно приступить к запуску процесса репликации и разблокировать СУБД командой mysql> UNLOCK TABLES;.

4. Перенос данных MySQL с Мастер-сервера на реплику

Итак, если сервер-источник содержит данные, их можно перенести несколькими способами:

  • mysqldump (рекомендуемый метод для InnoDB)
  • перенос сырых данных (то есть просто скопировать каталог /var/lib/mysql). Даный способ требует дополнительных приседаний, они описаны в документации к технике «холодного» MySQL бэкапа. Способ несколько рискованней, но быстрее, чем mysqldump за счет отсутствия необходимости обновления индексов.
  • MySQL плагин для клонирования данных

Создание копии данных MySQL с помощью mysqldump

Следующая команда сделает дамп всех баз данных MySQL (--all-databases) в файл, который называется dbdump.db и включит в дамп текущие координаты (--master-data) бинарного лога. Если вы не используете опцию --master-data, необходимо заблокировать все таблицы БД вручную командой mysql> FLUSH TABLES WITH READ LOCK;.

master1> mysqldump --all-databases --master-data > /backup/path/dbdump.db

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

Создание копии данных MySQL с помощью сырых файлов

Итак, данный способ должен учитывать соблюдение дополнительных требований:

  • Этот способ может работать некорректно, если мастер и слэйв имеют различные значения для настроек ft_stopword_file, ft_min_word_len, или ft_max_word_len и копируются таблицы с полнотекстовыми индексами.
  • необходимо использовать технологию холодного резервного копирования MySQL для получения консистентных бинарных файлов при использовании InnoDB: перед копированием, выполните т.н. slow shutdown
  • на реплике, каталог назначения $datadir/(обычно /var/lib/mysql/) должен быть предварительно очищен.

Следующие файлы не нужны для репликации и могут бы исключены:

  • файлы данных БД mysql (обычно лежат в /var/lib/mysql/mysql/)
  • файл master.info с данными к подключению к мастер-серверу (обычно /var/lib/mysql/master.info )
  • файлы бинарных логов с мастер-сервера (указаны в параметре log-bin, relay_log и relay_log_index)

Передача файлов на реплику

[email protected]:~# scp /dev/shm/dbdump.db [email protected]:/dev/shm/ 
[email protected]'s password:
dbdump.db                             100%   13MB 128.3MB/s   00:00
[email protected]:~#

Настройка slave сервера MySQL, когда на источнике нет данных (чистый)

Повторюсь еще раз, этот метод приемлем только, если Вы устанавливаете репликацию с нового (чистого) MySQL сервера. И после запуска репликации Вы можете импортировать данные дампов в мастер. Таким образом, т.к. импорт будет осуществляться при установленной репликации, импортированные дампы будут скопированы (реплицированы) на реплику автоматически.

Список шагов следующий:

  • [x] мы уже настроили master
  • [x] мы уже настроили replica.
    И теперь нам нужно сделать следующее:
  • [-] запустить сервис mysql на реплике
  • [-] выполнить команду CHANGE REPLICATION SOURCE TO (mysql newer than 8.X) или CHANGE MASTER TO (older mysql) на реплике
  • [-] Если есть данные, которые можно импортировать на master сервере, то нужно их импортировать.
mysql> CHANGE MASTER TO		
  MASTER_HOST='10.0.2.2',
  MASTER_USER='replication',	
  MASTER_PASSWORD='password',	
  FOR CHANNEL 'master1_channel';	

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

master1# mysql < fulldb.dump

Проверить статус репликации можно в выводе команд SHOW REPLICA STATUS\G (mysql новее 8.X) или SHOW SLAVE STATUS\G (старые версии mysql) в значениях SQL thread and replication I/O thread.

Настройка slave сервера MySQL, при существующих данных в БД

Список шагов следующий:

  • [x] мы уже настроили master
  • [x] мы уже настроили replica.
  • [x] мы уже получили координаты бинарного лога с мастер-сервера
    И теперь нам нужно сделать следующее:
  • [-] запустить сервис mysql на реплике без репликации (указать опцию --skip-slave-start)
  • [-] импортировать данные на slave сервер
  • [-] выполнить команду CHANGE REPLICATION SOURCE TO (mysql newer than 8.X) или CHANGE MASTER TO (older mysql) на реплике
  • [-] Запустить поток репликации командой START {REPLICA | SLAVE}.

На что нужно обратить внимание:

Если сервер-источник имеет любые запланированные события в шедулере, необходимо убедиться, что эти события не буду запущены на реплике после импорта данных. Event Scheduler управляется переменной event_scheduler, которая по умолчанию — ON с версии MySQl 8.0. Тем самым, запланированные события запустятся при запуске реплики после импорта данных. Это вызовет ошибки. Чтобы отключить все события, необходимо перед импортом установить эту переменную в OFF \ DISABLED командой SET event_scheduler = 'OFF';.

# bash
slave1> mysql < fulldb.dump
# mysql CLI
mysql> CHANGE MASTER TO		/* from MySQL 8.0.23 - CHANGE REPLICATION SOURCE TO */
  MASTER_HOST='10.0.2.2',      /* адрес мастер сервера */
  MASTER_USER='replication',	/* имя пользователя, которого мы создали */
  MASTER_PASSWORD='password',	/* пароль для пользователя */
  MASTER_PORT=3306,	/* опционально-только если он отличается от дефолтного */
  MASTER_LOG_FILE='master1-bin.000001',	/* имя бинарного лога, который мы получили с мастера */
  MASTER_LOG_POS=156,		/* позиция в бинарном логе */
  MASTER_CONNECT_RETRY=10		/* опционально */
  FOR CHANNEL 'master1_channel';		/* опционально - если реплика настраивается на несколько источников */
mysql> START SLAVE FOR CHANNEL 'master1_channel'; /* from MySQL 8.0.23 - START REPLICA */

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

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

Добавление Slave сервера в существующее окружение репликации

Можно легко добавить новую реплику в существующую топологию репликации без остановки мастер-сервера. Для этого можно просто остановить существующую реплику, скопировать каталог данных MySQL на новый сервер и задать новый ID и UUID сервера.

Добавление нового Slave сервера в существующее окружение репликации Mysql по шагам:

Multi-source репликация MySQL/MariaDB

При настройке репликации MySQL из нескольких источников (т.н. FAN-IN репликация), Slave сервер может быть настроен несколькими способами:

  1. Запуск нескольких инстансов реплик на разных портах и настройка каждого инстанса на отдельный мастер сервер.
  2. Запуск одной реплики и настройка отдельного канала репликации на отдельный сервер.
Читайте также:  Улучшите свою ИТ-операцию с помощью нашего комплексного аудиторского отчета

Я опишу второй путь, т.к. первый не отличается от настройки обычной репликации. Итак, вспомним, что в нашей топологии есть 3 ключевых сервера:

master1(Master/Source/10.0.2.2)  - async ->  slave1(Slave/Replica/10.0.2.4)
                      \                            |
                      |                       Multi-source
                      |                          async
                      |                            |   
                      |                           \|/  
                       \- async -> slave2(Second Slave/Replica/10.0.2.5)

Два сервера-источника: master1(Master/Source/10.0.2.2) и slave1(Slave/Replica/10.0.2.4) (это slave, на котором включен бинарный лог, так что он может быть и мастером)
Один реплика-сервер:slave2(Second Slave/Replica/10.0.2.5).
Реплика-сервер будет реплицировать две базы данных: test с master1 и test_s1 с slave1.

Итак, шаги настройки такие:

  • [x] необходимо, чтобы у нас были настроены каждый источник и подготовлена реплика
    • бинарный лог включен, заданы ID серверов (server_id, etc)
    • имеется пользователь с правами репликации
    • известны координаты бинарного лога с двух серверов-источников
  • [-] Осталось запустить команду CHANGE REPLICATION SOURCE TO (mysql новее 8.X) или CHANGE MASTER TO (старые mysql) на slave сервере для настройки репликации с master1(Master/Source/10.0.2.2)
  • [-] запустить команду CHANGE REPLICATION SOURCE TO (mysql новее 8.X) или CHANGE MASTER TO (старые mysql) на slave сервере для настройки репликации с slave1(Slave/Replica/10.0.2.4)
  • [-] Запустить процессы/потоки репликации на slave2(Second Slave/Replica/10.0.2.5) с помощью вызова START {REPLICA | SLAVE}.
  • [-] (опционально) Установить фильтр для того, чтобы только заданные базы данных реплицировались со строго заданных серверов-источников test с master1 и test_s1 с slave1.
mysql> CHANGE MASTER TO		
  MASTER_HOST='10.0.2.2',
  MASTER_USER='replication',	
  MASTER_PASSWORD='password',	
  MASTER_PORT=3306,	
  MASTER_LOG_FILE='master1-bin.000014',	
  MASTER_LOG_POS=156,		
  MASTER_CONNECT_RETRY=10		
  FOR CHANNEL 'master1_channel';		/* Это ключевой параметр, который говорит серверу использовать отдельный поток */
mysql> CHANGE MASTER TO		
  MASTER_HOST='10.0.2.4',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',	
  MASTER_PORT=3306,
  MASTER_LOG_FILE='slave1-bin.000014',	
  MASTER_LOG_POS=156,		
  MASTER_CONNECT_RETRY=10		
  FOR CHANNEL 'slave2_channel';		/* Это ключевой параметр, который говорит серверу использовать отдельный поток */
mysql> START SLAVE FOR CHANNEL 'master1_channel';
mysql> START SLAVE FOR CHANNEL 'slave2_channel';
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('test.%') FOR CHANNEL 'master1_channel';
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('test_s1.%') FOR CHANNEL 'slave2_channel';

Репликация мастер-мастер (или круговая репликация)

Настройка репликации MySQL в режиме мастер-мастер подразумевает то, что в случае отказа одного из серверов — другие участники репликации прозрачно подхватят работу. То есть не нужно будет делать ручных шагов для перевода сервера роли Slave-сервера в Master (что вызовет перерыв сервиса). Круговая репликация (или circular replication) MySQL может быть использована для масштабирования MySQL нодов, доступных на запись (изменение базы данных). !Но есть нюансы. В данной конфигурации, MySQL не выполняет разрешение конфликтов, то есть нет реализованного протокола, который отслеживает блокировки таблиц\баз между нодами. То есть, например, если мы используем внешние ключи (FOREIGN KEY) в нашей базе данных INSERT может завершится ошибкой, если ссылка на внешний ключ не успела реплицироваться.

Мастер-мастер репликация Mysql
Master-master репликация между двумя нодами
MySQL. Репликация базы данных
Master-master репликация между четырьмя нодами

Настройка MySQL сервера для мастер-мастер репликации на самом деле — это просто настройка мастер-слэйв репликации много раз от одной ноды к другой по кругу. Самый простой пример такой репликации — репликация между двумя нодами. Репликация настраивается в две стороны: от первой ноде ко второй и от второй ноды — к первой. Рассмотрим пример настройки репликации между master1 и master2.

master1(Master/Source/10.0.2.2) 
       /|\   |         
        |    |        
        |    |          
     master-master     
     ( circular )       
        |    |          
        |   \|/
master2(Second Master/Source/10.0.2.3)		
#/etc/mysql/my.cnf
server-id       = 2
log_bin
binlog_do_db    = db_name
auto_increment_increment = 2
auto_increment_offset = 1
# systemctl restart mysql
#mysql CLI
mysql> stop slave;
mysql> SHOW MASTER STATUS;                                                                        
+--------------------+----------+--------------+------------------+-------------------+   
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |   
+--------------------+----------+--------------+------------------+-------------------+   
| master1-bin.000001 |      400 |              |                  |                   |   
+--------------------+----------+--------------+------------------+-------------------+   
1 row in set (0.00 sec)                                                                           
                                                                                                  
mysql>
mysql> CREATE USER 'replication'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
mysql> flush privileges;
mysql> CHANGE MASTER TO	
  MASTER_HOST='10.0.2.3',
  MASTER_USER='repication',	
  MASTER_PASSWORD='password',	
  MASTER_LOG_FILE='master2-bin.000001',	
  MASTER_LOG_POS=300,		
  FOR CHANNEL 'master2_channel';	
  
mysql> start slave;
#/etc/mysql/my.cnf
server-id       = 3
log_bin
binlog_do_db    = db_name
auto_increment_increment = 2
auto_increment_offset = 2
# systemctl restart mysql
#mysql CLI
mysql> SHOW MASTER STATUS;                                                                        
+--------------------+----------+--------------+------------------+-------------------+   
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |   
+--------------------+----------+--------------+------------------+-------------------+   
| master2-bin.000001 |      300 |              |                  |                   |   
+--------------------+----------+--------------+------------------+-------------------+   
1 row in set (0.00 sec)                                                                           
mysql> CREATE USER 'replication'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
mysql> flush privileges;
mysql> CHANGE MASTER TO	
  MASTER_HOST='10.0.2.2',
  MASTER_USER='replication',	
  MASTER_PASSWORD='password',	
  MASTER_LOG_FILE='master1-bin.000001',	
  MASTER_LOG_POS=400,		
  FOR CHANNEL 'master1_channel';
mysql> start slave;

Важный нюанс, на который стоит обратить внимание — это параметры auto_increment_increment и auto_increment_offset которые помогают защитить от перехлеста автоматически возрастающих индексов.

Переключение сервера MySQL из режима репликации в отдельный сервер (отключение репликации MySQL)

Если установить server ID в ноль, то бинарный лог продолжит работать, но в режиме server_id = 0 сервер MySQL отбрасывает любые подключения от реплик. так же, реплика с server_id = 0 не пытается установить соединения с мастером. Стоит помнить, что этот параметр может быть изменен на работающем сервере, но изменения будут приняты только после рестарта MySQL!

Переключение мастер-сервера при отказах мастер-сервера (переключение на новый источник репликации)

Ок. Давайте посмотрим на исходную топологию репликации:

MySQL. Репликация базы данных
Топология репликации MySQL до сбоя

Предположим, что мастер сервер умер и недоступен. Давайте сделаем нашу реплику1 новым мастером и получим следующую топологию репликации:

MySQL. Репликация базы данных
Сбой мастер-сервера

Для назначение нового мастер-сервера, необходимо выбрать реплику, которая станет новым мастером. В нашем случае — это Replica1. А оставшимся (2 и 3) необходимо просто запустить команды CHANGE REPLICATION SOURCE TO (from MySQL 8.0.23) или CHANGE MASTER TO с необходимыми параметрами. Все, реплика просто начнет читать бинарный лог с нового источника, выполнять запросы на своих базах данных и не будет проверять, совместима ли база данных на источнике.

Новый мастер сервер (который Replica1) желательно запустить с параметром --log-slave-updates=OFF или изменить его онлайн.

Так же, необходимо не забыть про клиентов — они должны быть перенаправлены на нового мастера, который теперь Replica1. Часто, для этого используют такое решения, как демон keepalived.

Итак, давайте пройдемся по шагам:

Более подробно — тут

Рекомендации по резервному копированию в режиме MySQL репликации

Резервное копирование и восстановление в режиме репликации использует те же принципы, что и отдельный (standalone) MySQL сервер. Резервное копирование может быть логическим (например, с помощью утилиты mysqldump) или физическим (копирование каталога данных /var/lib/mysql). mysqldump имеет опцию --single-transaction, которая создает образ и позволяет избежать блокирования работы других клиентов.

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

Есть так же рекомендация для бОльшей надежности и консистентности резервной копии — использовать переменную read_only и выполнять резервное копирование в следующей последовательности:

  1. Перевести сервер в режим только-чтение.
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;
  1. Выполнить логическое резервное копирование.
  2. Вернуть сервер в нормальный режим.
mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

Диагностика репликации MySQL

Полезные команды для траблшутинга MySQL

Типичные ошибки репликации (и способы устранения)

Ошибка Last_IO_Error equal MySQL server UUIDs

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Ошибка может появляться, если вы перенесли каталог данных MySQL на новый сервер и запустили сервер со старым файлом auto.cnf. Для устранения ошибки — необходимо удалить файл и при следующем запуске сервер сгенерирует новый UUID.

cat /var/lib/mysql/auto.cnf 
[auto]
 server-uuid=fea4e713-9552-11e6-a093-0341a44379d3

Ошибка MY-010584 — MY-002061

а так же

Ошибка возникает при подключении реплики к мастер-серверу более старой/новой версии. Для устранения — необходимо корректно настроить шифрование или использовать mysql_native_password в качестве плагина при создании пользователя репликации. подробнее тут

Ошибка возникает при переносе (импорте) базы данных с мастер-сервера на реплику. Для устранения — необходимо задать новое имя файла в параметрах relay-log и relay-log-index.

Основные шаги диагностики

  1. Проверить содержимое лога (/var/log/mysql/error.log)
  2. Включен ли бинарный лог на источнике? Проверить с помощью команды SHOW MASTER STATUS. Обычно лог включен по умолчанию. Команда покажет позицию — ноль, если лог не включен. Проверить, что сервер запущен без опции --skip-log-bin.
  3. Переменная server_id должна быть установлена и уникальна для каждого члена репликации.
  4. Запущена ли реплика? Проверить командой START {REPLICA | SLAVE}. В выводе должно содержаться Replica_IO_Running и Replica_SQL_Running со значением Yes. Если нет, то проверить следующее: не запущен ли сервер с опцией --skip-slave-start или установлена переменная skip_slave_start, которые запрещают запуск потоков репликации.
  5. Если потоки запущены, проверить установлено ли соединение с мастером командой SHOW PROCESSLIST, найти статус потоков I/O и SQL в выводе. Если статус Connecting to master, Проверить следующее:
    • Пароли и привилегии пользователя репликации на сервере-источнике (можно попробовать временно настроить репликацию от root)
    • Проверить порт и IP. (попробовать подключиться mysql клиентом по адресу и порту в выводе команды SHOW PROCESSLIST)
    • Проверить системную переменную skip_networking на источнике и реплике. Если переменная установлена — выключить.
    • проверить правила iptables и межсетевой экран.
  6. Если реплика была запущена и работала, но потом упала, то скорей всего проблема в SQL запросе. Запрос, который корректно может выполнится на источнике, но завершается ошибкой на реплике. Такое может произойти, например, если на реплику импортирован некорректный образ базы данных и он не консистентен с источником.
Оцените статью
Хостинги