Репликация mysql master slave

How to Configure MySQL Master-Slave Replication on CentOS 7

4 Января 2020
|

CentOS

Репликация MySQL — это процесс, который позволяет автоматически копировать данные с одного сервера базы данных на один или несколько серверов.

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

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

В этом примере мы предполагаем, что у вас есть два сервера под управлением CentOS 7, которые могут взаимодействовать друг с другом через частную сеть. Если ваш хостинг-провайдер не предоставляет частные IP-адреса, вы можете использовать публичные IP-адреса и настроить брандмауэр так, чтобы трафик на порт 3306 передавался только из надежных источников.

Серверы в этом примере имеют следующие IP-адреса:

Установите MySQL

По умолчанию репозитории CentOS 7 не содержат пакетов MySQL, поэтому мы установим MySQL из их официального репозитория Yum. Чтобы избежать каких-либо проблем, мы установим одну и ту же версию MySQL 5.7 на оба сервера.

Установите MySQL на главный и подчиненный серверы:

После завершения установки запустите службу MySQL и включите ее автоматический запуск при загрузке с помощью:

Когда сервер MySQL запускается в первый раз, для корневого пользователя MySQL генерируется временный пароль. Чтобы найти пароль, используйте следующую команду grep :

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

Введите временный пароль root и ответьте (да) на все вопросы.

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

Настройте главный сервер

Сначала мы настроим главный сервер MySQL и внесем следующие изменения:

Настройте сервер MySQL для прослушивания частного IP .
Установите уникальный идентификатор сервера.
Включите двоичное ведение журнала.

Для этого откройте файл конфигурации MySQL и добавьте в раздел следующие строки :

Master: /etc/my.cnf

После этого перезапустите службу MySQL, чтобы изменения вступили в силу.

Следующим шагом является создание нового пользователя репликации. Войдите на сервер MySQL от имени пользователя root:

В командной строке MySQL выполните следующие SQL-запросы, которые создадут пользователя и предоставят пользователю привилегию:

Убедитесь, что вы изменили IP-адрес с вашего ведомого IP-адреса. Вы можете назвать пользователя, как вы хотите.

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

Обратите внимание на имя файла «mysql-bin.000001» и «1427». Эти значения понадобятся вам при настройке подчиненного сервера

Эти значения, вероятно, будут отличаться на вашем сервере.

Настройте подчиненный сервер

Как и в случае с главным сервером, мы внесем следующие изменения в подчиненный сервер:

Настройте сервер MySQL для прослушивания частного IP
Установите уникальный идентификатор сервера
Включить бинарное ведение журнала

Откройте файл конфигурации MySQL и отредактируйте следующие строки:

Slave: /etc/my.cnf

Перезапустите службу MySQL:

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

Сначала остановите подчиненные устройства:

Выполните следующий запрос, который настроит подчиненное устройство для репликации мастера:

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

После этого запустите подчиненные устройства.

Проверьте конфигурацию

На этом этапе у вас должна быть работающая настройка репликации Master / Slave.

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

Войдите в подчиненную оболочку MySQL:

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

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

В этом руководстве мы показали, как создать репликацию MySQL Master / Slave в CentOS 7.

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

Master-Slave репликация

В этом подходе выделяется один основной сервер базы данных, который называется Мастером. На нем происходят все изменения в данных (любые запросы MySQL INSERT/UPDATE/DELETE). Слейв сервер постоянно копирует все изменения с Мастера. С приложения на Слейв сервер отправляются запросы чтения данных (запросы SELECT). Таким образом Мастер сервер отвечает за изменения данных, а Слейв за чтение.

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

В приложении нужно использовать два соединения – одно для Мастера, второе — для Слейва:

$master = mysql_connect('10.10.0.1', 'root', 'pwd');
$slave = mysql_connect('10.10.0.2', 'root', 'pwd');

# ...
mysql_query('INSERT INTO users ...', $master);

# ...
$q = mysql_query('SELECT * FROM photos ...', $slave);

Используем два соединения — для Мастера и Слейва — для записи и чтения соответственно

Несколько Слейвов

Преимущество этого типа репликации в том, что Вы можете использовать более одного Слейва. Обычно следует использовать не более 20 Слейв серверов при работе с одним Мастером.

Тогда из приложения Вы выбираете случайным образом один из Слейвов для обработки запросов:

$master = mysql_connect('10.10.0.1', 'root', 'pwd');

$slaves = ;

$slave = mysql_connect($slaves, 'root', 'pwd');
# ...

mysql_query('INSERT INTO users ...', $master);
# ...

$q = mysql_query('SELECT * FROM photos ...', $slave);

Задержка репликации

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

$master = mysql_connect('10.10.0.1', 'root', 'pwd');
$slave = mysql_connect('10.10.0.2', 'root', 'pwd');
# ...

mysql_query('UPDATE users SET age = 25 WHERE id = 7', $master);
$q = mysql_query('SELECT * FROM users WHERE id = 7', $master);
# ...

$q = mysql_query('SELECT * FROM photos ...', $slave);

При обращении к изменяемым данным, необходимо использовать Мастер-соединение

Выход из строя

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

Если выходит из строя Мастер, нужно переключить все операции (и чтения и записи) на Слейв. Таким образом он станет новым Мастером. После восстановления старого Мастера, настроить на нем реплику, и он станет новым Слейвом.

Резервирование

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

Как это работает

Slave-сервер с определённой периодичностью будет опрашивать master-сервер на предмет изменений в базе. Таким образом все изменения в master-сервере будут повторяться на slave-сервере. Таким образом создаётся избыточность данных на двух серверах и тем самым достигается высокая доступность и надёжность данных. Важным преимуществом между “холодным копированием” заключается в том, что мы переносим по сети только изменения, а не все данные каждый раз. Тем более не стоит забывать что во время создания backup`а мы нагружаем наш master-сервер. Здесь же всё иначе, master-сервер все изменения в базе пишет в “бинарный журнальный лог”, присваивая каждой операции номер. Когда slave-сервер обращается к нашему главному серверу, то он сообщает номер последней операции, которую он уже произвёл у себя и получает все новые изменения отсчитывая от этого номера.

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

При малых объемах БД и необходимости настройки реплики, легко и просто использовать mysqldump. Но когда объем БД за 10 Гб и более, то распаковка дампа может занять уже продолжительное время. Поэтому логичнее использовать инструмент XtraBackup от Percona. Он позволяет снимать копию с базы данных в момент её работы, используя возможности движка InnoDB.

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

Этот же инструмент можно использовать и при настройке репликации Master-Slave с GTID. XtraBackup поддерживает GTID с версии 2.1.0. Для работы с MySQL 5.7 необходима версия 2.4.

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

При работе с XtraBackup, все значения для подключения к MySQL берутся из конфигов.

Для снятия полной копии всей БД и размещения её по пути /mnt/share необходимо выполнить следующую команду:

Подготовленную копию теперь необходимо восстановить на слейве. Для этого демон mysql слейва должен быть остановлен, а директория data_dir быть пуста:

До текущего момента процесс подготовки копии был стандартный. Копия снята, слейв запущен. Но теперь необходимо выполнить подготовку GTID для последующей настройки репликации. При снятии копии, на мастере создался файл xtrabackup_binlog_info, в котором указано значение GTID последней транзакции в данной копии. Это значение необходимо импортировать на слейв. Вывод будет следующий:

На слейве установить значение GTID из файла xtrabackup_binlog_info для переменной GTID_PURGED:

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

В данном случае по сути было выполнено тоже самое, что и через mysqldump, но с помощью XtraBackup. А значение GTID_PURGED, которое хранится обычно в дампе, было создано вручную – это единственный нюанс, который нужно учесть.

Настройка 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

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

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

server-id — идентификатор сервера, должен быть уникален. Лучше не использовать 1. Это единственный обязательный параметр;
log_bin — путь к бинарному логу. Оптимально указывать по аналогии с мастером;
log_slave_updates — включает запись реляционных событий в собственный журнал на подчинённом сервере
binlog_do_db — позволяет перечислить отдельные базы, для которых будет использоваться реплика.Если не инициализирована, то реплицируются все.

server-id = 22
log_bin = varlogmysqlmysql-bin.log
relay_log = mysql-relay-bin
 
# База данных для репликации
binlog_do_db = newdatabase
 
# если необходимо сделать базу доступной только для чтения
# read_only = 1

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

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

После этого запускаем репликацию на Слейве:

Статус репликации

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

mysql> SHOW SLAVE STATUS\G

             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: root
                Master_Port: 3306
              Connect_Retry: 3
            Master_Log_File: gbichot-bin.005
        Read_Master_Log_Pos: 79
             Relay_Log_File: gbichot-relay-bin.005
              Relay_Log_Pos: 548
      Relay_Master_Log_File: gbichot-bin.005
           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: 79
            Relay_Log_Space: 552
            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: 8

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

В этой инструкции мы будем использовать для примера Ubuntu 16.04 и MariaDB версии 10.1. Перед тем, как начать полностью обновите систему:

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

Когда обновление списка пакетов завершено, установите MariaDB командой:

Пакет rsync нам понадобится для выполнения непосредственно синхронизации. Когда установка будет завершена, вам необходимо защитить базу данных с помощью скрипта mysql_secure_installation:

По умолчанию разрешен гостевой вход, есть тестовая база данных, а для пользователя root не задан пароль. Все это надо исправить. Читайте подробнее в статье установка MariaDB в Ubuntu. Если кратко, то вам нужно будет ответить на несколько вопросов:

Когда все будет готово, можно переходить к настройке нод, между которыми будет выполняться репликация баз данных mysql. Сначала рассмотрим настройку первой ноды. Можно поместить все настройки в my.cnf, но лучше будет создать отдельный файл для этих целей в папке /etc/mysql/conf.d/.

Добавьте такие строки:

Здесь адрес 192.168.56.101 — это адрес текущей ноды. Дальше перейдите на другой сервер и создайте там такой же файл:

Аналогично тут адрес ноды — 192.168.0.103. Остановимся на примере с двумя серверами, так как этого достаточно чтобы продемонстрировать работу системы, а добавить еще один сервер вы можете, прописав дополнительный IP адрес в поле wsrep_cluster_address. Теперь рассмотрим что означают значения основных параметров и перейдем к запуску:

binlog_format — формат лога, в котором будут сохраняться запросы, значение row сообщает, что там будут храниться двоичные данные;
default-storage-engine — движок SQL таблиц, который мы будем использовать;
innodb_autoinc_lock_mode — режим работы генератора значений AUTO_INCREMENT;
bind-address — ip адрес, на котором программа будет слушать соединения, в нашем случае все ip адреса;
wsrep_on — включает репликацию;
wsrep_provider — библиотека, с помощью которой будет выполняться репликация;
wsrep_cluster_name — имя кластера, должно соответствовать на всех нодах;
wsrep_cluster_address — список адресов серверов, между которыми будет выполняться репликация баз данных mysql, через запятую;
wsrep_sst_method — транспорт, который будет использоваться для передачи данных;
wsrep_node_address — ip адрес текущей ноды;
wsrep_node_name — имя текущей ноды.

Настройка репликации MySQL почти завершена. Остался последний штрих перед запуском — это настройка брандмауэра. Сначала включите инструмент управления правилами iptables в Ubuntu — UFW:

Затем откройте такие порты:

Шаг 2. Настройка кластера MariaDB в режиме Master — Master

Если одностороннего режима копирования данных нам недостаточно, продолжаем настройку.

Настройка на сервере Slave

На втором сервере откроем конфигурационный файл MariaDB:

vi /etc/my.cnf.d/server.cnf

и допишем в него следующее:

log_bin=mysql-bin
log_error=mysql-bin.err
binlog-ignore-db=information_schema,mysql,test

Перезагрузим демон для применения настроек:

systemctl restart mariadb

Теперь подключимся к MariaDB:

mysql -uroot -p

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

MariaDB > GRANT replication slave ON *.* TO «replmy»@»192.168.166.155» IDENTIFIED BY «password»;

* replmy: имя учетной записи (можно использовать любое). 192.168.166.155: IP-адрес первого сервера, с которым будем реплицировать данные. password: пароль для учетной записи (желательно, сложный).

Выведем состояние работы мастера:

MariaDB > show master status\G

Как и при настройке первого сервера, запомните или запишите значения для File и Position.

Настройка на сервере Master

Теперь подключитесь к первому серверу.

И зайдем в командную оболочку MariaDB:

mysql -uroot -p

введем такую команду:

MariaDB > change master to master_host = «192.168.166.156», master_user = «replmy», master_password = «password», master_log_file = «mysql-bin.000003», master_log_pos = 245;

* 192.168.166.156: IP-адрес моего второго сервера. replmy: учетная запись для репликации, которая была создана на втором сервере. password: пароль для учетной записи, также был сделан на втором сервере. mysql-bin.000003: имя файла, которое мы должны были записать или запомнить (у вас может быть другим). 245: номер позиции, с которой необходимо начать репликацию (также должны были записать или запомнить ранее).

Теперь запустим вторичный сервер для репликации:

MariaDB > start slave;

И проверим состояние репликации:

MariaDB > SHOW SLAVE STATUS\G

Отключитесь от СУРБД:

MariaDB > \q

Настройка кластера в режиме Master — Master закончена.

Теперь остается окончательно убедиться, что репликация работает. Внесите изменения на первом сервере — они должны попасть на второй. И наоборот, при внесении изменений на втором сервере, они должны попадать на первый.

Задержка репликации

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

<?$master = mysql_connect('10.10.0.1', 'root', 'pwd');$slave = mysql_connect('10.10.0.2', 'root', 'pwd');mysql_query('UPDATE users SET age = 25 WHERE id = 7', $master);$q = mysql_query('SELECT * FROM users WHERE id = 7', $master);# ...$q = mysql_query('SELECT * FROM photos ...', $slave);

Главный-подчиненный резервное копирование базы данных MySQL8

В основном используется базовая версия. Самые известные — это версия 5.5 и версия 5.7 серии 5.MySQL репликация master-slave Функция репликации главный-подчиненный, предоставляемая самой базой данных MySQL, позволяет легко реализовать несколько автоматических резервных копий данных и реализовать расширение базы данных. Множественные резервные копии данных могут не только усилить безопасность данных, но также могут еще больше повысить производительность загрузки базы данных за счет реализации разделения чтения и записи. На следующем рисунке описана модель репликации главный-подчиненный и разделения чтения-записи между несколькими базами данных: В системе баз данных с одним главным и несколькими подчиненными несколько подчиненных серверов асинхронно обновляют изменения в главной базе данных, а бизнес-сервер выполняет операции записи или связанные операции модификации базы данных на главном сервере. Операция чтения выполняется на каждом подчиненном сервере. Если настроено несколько подчиненных серверов или несколько главных серверов задействованы в соответствующей проблеме балансировки нагрузки, конкретные технические детали балансировки нагрузки не изучались. Сегодня мы просто реализуем функцию репликации главный-подчиненный для одного главного и одного подчиненного. Схема реализации репликации Mysql ведущий-ведомый примерно выглядит следующим образом (исходная сеть): Основой репликации данных между MySQL является двоичный файл журнала. После включения двоичного журнала для базы данных MySQL в качестве ведущего, все операции в его базе данных будут записываться в двоичный журнал в форме «событий». Другие базы данных действуют как ведомые устройства для связи с ведущим сервером через поток ввода-вывода и Отслеживайте изменения в двоичном файле журнала ведущего устройства. Если он обнаруживает, что двоичный файл журнала ведущего устройства изменился, он скопирует изменения в свой собственный журнал реле, а затем поток SQL ведомого устройства выполнит соответствующие «события» в своей собственной базе данных. Для достижения согласованности между подчиненной базой данных и главной базой данных также реализована репликация главный-подчиненный.

Практика господина-раба:

С сервера:

Конкретный процесс реализации выглядит следующим образом:

Заключение

В данном курсовом проекте был рассмотрен практический пример как реализовать репликации БД exampledb с сервера server1 на сервер server2 с использованием SSL соединения, используя базу данных MySQL. При репликации содержимое БД дублируется на нескольких серверах. Зачем необходимо прибегать к дублированию? Есть несколько причин:

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

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