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

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

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

В этом руководстве мы настроим два сервера с MySQL: один в качестве исходной базы данных, «источника», а второй — в виде его «реплики». Также мы рассмотрим, как MySQL производит репликацию. 

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

Тем не менее этот процесс завершён не до конца. Хотя документация и большая часть команд в 8 версии MySQL были обновлены так, чтобы ссылаться на серверы как «источник» и его «реплики», всё ещё остаются места со старой терминологией. По умолчанию в этом руководстве будут использоваться более современные термины и команды, но в некоторых случаях без понятий master и slave не обойтись. 

dbaas

Требования 

• Два сервера под управлением Ubuntu 20.04. На обоих должен быть административный непривилегированный пользователь с sudo-правами и брандмауэр, настроенный с помощью утилиты UFW.

• MySQL, уже установленная на каждом сервере. В этом руководстве по умолчанию предполагается последняя версия MySQL, доступная из репозиториев Ubuntu. На момент написания руководства это 8.0.25.

Описанный здесь процесс включает назначение MySQL на одном сервере в качестве исходной базы данных, «источника», а затем настройку MySQL на другом сервере в качестве её копии, «реплики».

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

Как работает репликация в MySQL

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

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

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

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

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

Механизм такой репликации схож с репликацией на основе файла двоичного журнала: каждый раз, когда транзакция происходит в источнике, MySQL назначает и записывает для неё GTID в двоичный файл журнала вместе с самой транзакцией. Затем GTID и транзакция передаются репликам для обработки. 

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

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

Шаг 1 — Настройка брандмауэра исходного сервера для репликации MySQL

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

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

Эта команда разрешает любые соединения, исходящие от IP-адреса реплики — в примере это replica_server_ip — к дефолтному номеру порта MySQL, 3306: 

sudo ufw allow from replica_server_ip to any port 3306  

Не забудьте заменить replica_server_ipна фактический IP-адрес вашего сервера-реплики.

После этого вам не нужно что-либо менять в брандмауэре реплики, так как этот сервер не будет получать никаких входящих подключений, а исходящие подключения к исходному серверу MySQL не блокируются UFW. Теперь давайте перейдём к настройке источника. 

Шаг 2 — Настройка исходной базы данных для MySQL-репликации

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

В Ubuntu 20.04 файл конфигурации сервера MySQL по умолчанию называется mysqld.cnf и находится в директории/etc/mysql/mysql.conf.d/. Откройте этот файл на исходном сервере в любом текстовом редакторе. Здесь мы будем использовать nano: 

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf 

Найдите в файле директиву bind-address. По умолчанию она выглядит так: 

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
bind-address        = 127.0.0.1 
...

127.0.0.1 — это адрес IPv4, представляющий localhost. Если указать это значение для директивы bind-address, то MySQL будет слушать соединения только по адресу localhost. Другими словами, этот экземпляр MySQL сможет принимать только соединения, исходящие от сервера, на котором он установлен. 

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

Замените 127.0.0.1 на IP-адрес исходного сервера. После этого директива bind-address будет выглядеть вот так, но с IP-адресом вашего сервера вместо source_server_ip:

...
bind-address        = source_server_ip 
...

Затем найдите директиву server-id, определяющую идентификатор, с помощью которого MySQL различает серверы внутри системы репликации. Каждый сервер в системе, включая источник и все его реплики (мастера и все слейвы), должен иметь собственное уникальное значение server-id. По умолчанию эта директива закомментирована и выглядит так: 

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
# server-id         = 1 
...

Раскомментируйте эту строку, удалив знак решетки (#). Вы можете выбрать любое число в качестве значения этой директивы, но помните, что номер должен быть уникальным и не может совпадать с другими server-id в вашей группе репликации. Для простоты в следующем примере это значение остаётся по умолчанию (1): 

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
server-id         = 1 
...

Найдите директиву log_bin под строкой server-id. Она определяет имя и расположение двоичного журнала MySQL. 

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

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
log_bin                   = /var/log/mysql/mysql-bin.log 
...

Наконец, прокрутите вниз до конца файла, чтобы найти закомментированную директиву binlog_do_db: 

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
# binlog_do_db      = include_database_name

Удалите знак решетки, чтобы раскомментировать эту строку, и замените include_database_name именем базы данных, которую вы хотите дублировать. В этом примере показана директива binlog_do_db, указывающая на базу данных с именем db. Если у вас в источнике уже есть существующая база данных, которую вы хотите реплицировать, используйте ее имя вместо db: 

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
binlog_do_db      = db  

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

/etc/mysql/mysql.conf.d/mysqld.cnf 
... 
binlog_do_db      = db 
binlog_do_db      = db_1 
binlog_do_db      = db_2

Либо же вы можете указать, какие базы данных MySQL не следует дублировать, добавив директиву binlog_ignore_db для каждой из них: 

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
binlog_ignore_db      = db_to_ignore  
...

Внеся изменения, сохраните и закройте файл. Если вы использовали nano, сделайте это, нажав CTRL + X, Y, а затем ENTER.  

Перезапустите MySQL, выполнив следующую команду: 

sudo systemctl restart mysql  

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

Шаг 3 — Создание учётной записи пользователя для репликации MySQL

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

Начните с открытия оболочки MySQL: 

sudo mysql

Примечание: если вы настроили учётную запись с аутентификацией по паролю, вы можете подключиться к MySQL с помощью следующей команды: 

mysql -u username -p

Замените username именем своего пользователя и в нужный момент введите его пароль. 

Имейте в виду, что некоторые операции в этом руководстве, в том числе те, которые должны выполняться на сервере-реплике, требуют расширенных прав. Из-за этого может быть удобнее подключиться с правами администратора, как это можно сделать с помощью предыдущей команды sudo mysql. Если же вы хотите использовать пользователя MySQL с более ограниченными правами, ему как минимум должны быть предоставлены права CREATE USER, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE и REPLICATION_SLAVE_ADMIN. 

Создайте нового пользователя MySQL. В этом примере создается пользователь с именем replica_user, вы же можете вписать любое имя. Обязательно измените replica_server_ip на публичный IP-адрес сервера реплики и не забудьте задать более надёжный пароль вместо стоящего по умолчанию password: 

CREATE USER 'replica_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

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

Создав нового пользователя, предоставьте ему соответствующие права. Как минимум, такой пользователь должен иметь разрешения REPLICATION SLAVE: 

GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'replica_server_ip';

После этого рекомендуется запустить команду FLUSH PRIVILEGES. Это освободит всю память, кэшированную сервером для выполнения предыдущих операторов CREATE USER и GRANT: 

FLUSH PRIVILEGES;

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

Шаг 4 — Получение координат двоичного журнала из базы-источника 

Как вы помните из раздела «Как работает репликация в MySQL», MySQL выполняет репликацию, построчно копируя события базы данных из двоичного журнала источника и исполняя каждое событие в реплике. При использовании репликации двоичного файла журнала на основе позиции вы должны сообщить реплике набор координат, в которых подробно указывается имя исходного двоичного журнала и конкретная позиция в этом файле. Затем реплика использует эти координаты для определения точки в файле журнала, с которой она должна начать копирование событий базы данных, и отслеживать, какие события она уже обработала. 

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

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

FLUSH TABLES WITH READ LOCK;

Затем выполните следующую операцию, которая выведет информацию о текущем состоянии двоичного журнала источника (мастера): 

SHOW MASTER STATUS;

С этой позиции реплика начнет копирование событий базы данных. Зафиксируйте (запомните или запишите) имя File и значение Position; они понадобятся вам позже, когда вы запустите репликацию. 

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

Если в вашем источнике нет данных для переноса 

Если вы только установили данную MySQL или в ней нет существующих данных, которые вы хотите перенести в слейв, на этом этапе вы можете разблокировать таблицы: 

UNLOCK TABLES;

Вы можете создать базу данных для репликации master-slave, пока у вас еще открыта оболочка MySQL. Как в примере из шага 2, следующая команда создаст базу данных под именем db: 

CREATE DATABASE db;

После этого закройте оболочку MySQL: 

exit

Теперь вы можете перейти к следующему шагу. 

Если в вашем источнике есть данные для переноса

Уже существующие данные из исходной базы данных в слейв можно перенести, создав снапшот (копию) базы данных с помощью утилиты mysqldump. Однако ваша база данных всё ещё заблокирована. Если вы сделаете какие-либо новые изменения в том же окне либо закроете его, она автоматически разблокируется, что может привести к проблемам. Разблокировка таблиц означает, что данные в базе могут быть изменены. Это потенциально может привести к несоответствию между вашей копией-снапшотом и координатами двоичного журнала, которые вы только что получили. 

Поэтому вы должны открыть новое окно или вкладку терминала на локальном компьютере. Так вы сможете создать снапшот базы данных без разблокировки MySQL. 

В новом окне или вкладке откройте еще один сеанс SSH на том сервере, где размещен исходный экземпляр MySQL: 

ssh username@source_server_ip  

Затем из новой вкладки или окна экспортируйте свою базу данных с помощью mysqldump. В данном примере создается файл дампа с именем db.sql из базы с именем db. Убедитесь, что у вас стоит имя вашей собственной базы данных. Кроме того, не забудьте запустить эту команду в оболочке bash, а не в оболочке MySQL: 

sudo mysqldump -u root db > db.sql

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

UNLOCK TABLES;

Выходите из оболочки MySQL: 

exit

Теперь вы можете отправить снапшот на свой сервер-реплику. При условии, что вы уже настроили ключи SSH на исходном сервере и добавили открытый ключ источника в файл authorized_keys вашей реплики, вы можете защищённо отправить снапшот с помощью команды scp: 

scp db.sql username@replica_server_ip:/tmp/

Не забудьте, что вам нужно заменить sammy именем административного профиля пользователя Ubuntu, созданного на сервере-реплике, а также сменить replica_server_ip на IP-адрес вашего сервера-реплики. Также обратите внимание, что эта команда помещает снапшот в директорию /tmp сервера-реплики. 

Отправив снапшот на сервер-реплику, подключитесь к нему по SSH: 

ssh username@replica_server_ip

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

sudo mysql

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

CREATE DATABASE db;

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

 exit

Импортируйте снапшот: 

sudo mysql db < /tmp/db.sql

Теперь ваша реплика master-slave содержит все существующие данные из исходной базы данных. Давайте перейдём к последнему шагу, чтобы настроить сервер-реплику на начало репликации. 

Шаг 5 — Настройка реплики 

Нам осталось только изменить конфигурацию реплики так же, как вы меняли исходную базу данных. Откройте файл конфигурации MySQL, mysqld.cnf, теперь уже на сервере-реплике: 

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Как упоминалось ранее, каждый экземпляр MySQL в среде должен иметь уникальное значение server-id. Найдите в реплике директиву server-id, раскомментируйте её и измените её значение на любое положительное целое число, отличающееся от значения исходной базы данных: 

/etc/mysql/mysql.conf.d/mysqld.cnf 
server-id           = 2  

После этого обновите значения log_bin и binlog_do_db, чтобы они соответствовали значениям, которые вы установили в файле конфигурации исходного сервера: 

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
log_bin             = /var/log/mysql/mysql-bin.log 
...
binlog_do_db        = db 
...

Наконец, добавьте директиву relay-log, определяющую расположение файла журнала ретрансляции реплики. Включите данную строку в конец файла конфигурации: 

/etc/mysql/mysql.conf.d/mysqld.cnf 
...
relay-log           = /var/log/mysql/mysql-relay-bin.log

Внеся изменения, сохраните и закройте файл. Затем перезапустите MySQL на реплике, чтобы запустить новые настройки: 

sudo systemctl restart mysql

После перезапуска службы mysql репликация будет готова к запуску. 

Шаг 6 — Запуск и тестирование репликации 

К данному моменту оба ваших экземпляра MySQL полностью настроены на начало репликации master-slave. Для её запуска откройте оболочку MySQL на сервере реплики: 

sudo mysql

В командной строке выполните данную операцию; она настраивает несколько параметров репликации MySQL одновременно. Выполнив эту команду и запустив репликацию, сервер попытается подключиться к IP-адресу в SOURCE_HOST, используя логин и пароль (SOURCE_USER и SOURCE_PASSWORD, соответственно). Он также будет искать двоичный журнал с именем SOURCE_LOG_FILE, и начинать его чтение с позиции SOURCE_LOG_POS. 

Обязательно замените source_server_ip на IP-адрес исходного сервера. Аналогичным образом, replica_user и password должны соответствовать имени пользователя репликации, созданного вами на шаге 2; mysql-bin.000001 и 899 должны отражать координаты двоичного журнала, полученные на шаге 3. 

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

CHANGE REPLICATION SOURCE TO 
SOURCE_HOST='source_server_ip', 
SOURCE_USER='replica_user', 
SOURCE_PASSWORD='password', 
SOURCE_LOG_FILE='mysql-bin.000001', 
SOURCE_LOG_POS=899; 

Теперь активируйте сервер-реплику: 

START REPLICA;

Если вы ввели все данные правильно, этот экземпляр начнёт репликацию всех изменений, внесенных в базу данных db в источнике. 

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

SHOW REPLICA STATUS\G; 

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

Примечание: если у реплики проблемы с подключением или репликация неожиданно останавливается, возможно, какое-то событие в двоичном журнале источника препятствует репликации. В таких случаях попробуйте запустить команду SET GLOBAL SQL_SLAVE_SKIP_COUNTER, чтобы пропустить некоторое количество событий, следующих за положением в двоичном журнале, определённым в предыдущей команде. Например, здесь пропускается только первое событие: 

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

После этого вам нужно будет перезапустить реплику: 

START REPLICA;

Если же вам когда-нибудь будет нужно остановить репликацию, запустите данную команду на сервере-реплике: 

 STOP REPLICA;

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

 Откройте оболочку MySQL на сервере-источнике: 

sudo mysql

Выберите базу данных для репликации: 

USE db;

Создайте в ней таблицу. В этом примере у нас таблица под названием example_table с одним столбцом example_column: 

CREATE TABLE example_table ( 
example_column varchar(30) 
); 

При желании можете добавить туда данных: 

INSERT INTO example_table VALUES 
('Первая строка'), 
('Вторая строка'), 
('Третья строка'); 

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

USE db;

Затем запустите оператор SHOW TABLES, чтобы вывести список всех таблиц в выбранной базе данных: 

SHOW TABLES;

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

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

SELECT * FROM example_table;

В SQL звёздочка (*) означает «все столбцы». По сути, этот запрос сообщает MySQL, что нужно вывести каждый столбец из example_table. Если репликация MySQL работает как надо, эта операция покажет эти данные.

Если ни одна из этих операций не вывела таблицу или данные из источника, возможно, у вас есть ошибка где-то в конфигурации. Чтобы найти проблему, вы можете попытаться запустить операцию SHOW REPLICA STATUS\G. Кроме того, вы можете обратиться к документации MySQL по устранению неполадок репликации. 

Тарифы MySQL

Заключение 

С помощью этого руководства мы настроили среду репликации MySQL на основе позиции в двоичном журнале с одним источником и одной репликой. Но помните, что это только один из способов настройки репликации в MySQL. В MySQL есть несколько различных вариантов синхронизации мастера и слейва, которые вы можете выбрать и настроить в соответствии с  вашими нуждами. Существует также ряд сторонних инструментов, к примеру Galera Cluster, которые можно использовать для расширения встроенных функций репликации MySQL на серверах https://timeweb.cloud.