MySQL and MariaDB replication with Zabbix monitoring

https://habr.com/ru/companies/first/articles/690318/

Когда к отказоустойчивости интернет-магазина или другого сервиса с базами данных предъявляются повышенные требования, не обойтись без репликации серверов СУБД и файлов. Репликация совместно с другими технологиями отказоустойчивости помогает полностью защититься от сбоя оборудования, например, от выхода из строя отдельных серверов.

Из этой статьи вы узнаете, как настроить и проверить репликацию Master-Slave для MySQL и MariaDB, а также как контролировать ее работу с помощью Zabbix.

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

Мы будем настраивать репликацию базы данных myshop_db по схеме Master-Slave. При этом в роли мастера будет выступать сервер r01master, а в роли реплики — сервер r01slave.

Настройка конфигурации мастер-сервера

Отредактируйте файл конфигурации MariaDB /etc/mysql/mariadb.conf.d/50-server.cnf на мастер сервере, добавив в него следующие строки:

#bind-address = 127.0.0.1
server-id = 42442171
log_bin   = /var/log/mysql/mysql-bin.log
expire_logs_days = 5
max_binlog_size  = 50M
sync-binlog = 0
binlog_format = mixed
binlog-do-db = myshop_db
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT

Прежде всего, закройте символом комментария параметр bind-address со значением 127.0.0.1. Если этого не сделать, сервер реплики не получит доступ к мастер-серверу MariaDB, так как сервис СУБД будет принимать соединения только на локальном интерфейсе 127.0.0.1.

Удалите следующую настройку, если она есть:

skip-networking=1

Если этого не сделать, к серверу MariaDB нельзя будет подключиться по сети.

Открыв доступ к MariaDB по сети, не забудьте ограничить его по адресу IP файерволом.

Далее задайте уникальный идентификатор server-id (в примере показано произвольное число, задайте здесь другое значение). На сервере реплики необходимо будет задать другой уникальный идентификатор.

Для работы репликации необходим бинарный журнал, путь к файлу которого задается в параметре log_bin. При этом в параметрах expire_logs_days и max_binlog_size нужно задать количество дней, в течение которых будет храниться журнал, и максимальный размер журнала, соответственно. Выберите эти параметры исходя из наличия свободного пространства на дисках сервера, а также интенсивности обращений к СУБД.

Параметр sync-binlog управляет синхронизацией бинарного лога на диск. По умолчанию значение этого параметра равно 0, в результате чего синхронизацией управляет операционная система. С точки зрения потери данных безопаснее задать для этого параметра значение 1. При этом данные будут записываться на диск после каждой операции записи в журнал. Однако это может снизить производительность сервера СУБД.

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

Очень важно задать с помощью параметра binlog-do-db название реплицируемой базы данных. В нашем случае мы указали базу данных myshop_db.

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

Безопаснее будет указать значение, равное 1 (используется по умолчанию). При этом запись журнала на диск выполняется сразу после каждой транзакции. Но такой вариант будет работать медленнее. И, наконец, задав значение этого параметра, равное 2, данные будут сбрасываться не на диск, а в кэш операционной системы.

Подробнее о параметре innodb_flush_log_at_trx_commit можно прочитать здесь.

Параметр innodb_flush_method влияет на кэширование. В нашем случае база данных находится на локальном диске сервера, при этом значение этого параметра, равное O_DIRECT, отключает кэширование на уровне ОС.

Подробнее о параметре innodb_flush_method можно прочитать здесь.

После внесения всех изменений в конфигурацию MariaDB перезапустите сервис и проверьте его состояние:

# service mysql restart
# service mysql status

Если появились ошибки или предупреждения, внесите исправления в файл конфигурации и попробуйте запустить сервис еще раз.

Настройка конфигурации сервера реплики

После того как вы настроили MariaDB на мастер-сервере, отредактируйте конфигурацию MariaDB, расположенную в файле /etc/mysql/mariadb.conf.d/50-server.cnf на сервере реплики:

#bind-address = 127.0.0.1
server-id = 11213181
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 5
max_binlog_size = 50M
sync-binlog = 0
binlog_format = mixed
relay-log = /var/log/mysql/mysql-relay-bin.log
replicate-do-db = myshop_db
report-host=r01slave.domain.ru
slave_sql_verify_checksum = 0
#skip_slave_start = 1 # prevent restart slave after failure

Здесь обратите внимание на параметры replicate-do-db, report-host, slave_sql_verify_checksum и skip_slave_start.

Параметр replicate-do-db задает базу, которая будет реплицироваться с мастер-сервера. Если ее не указать, будут реплицированы все базы.

При помощи параметра report-host можно задать имя хоста реплики, как оно будет отображаться при просмотре на мастер-хосте списка хостов реплик.

Параметр slave_sql_verify_checksum управляет вычислением контрольной суммы при работе с журналом репликации на сервере реплики (релея): https://mariadb.com/kb/en/relay-log/. Если задать нулевое значение, проверка контрольной суммы будет отключена.

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

Также не забудьте удалить следующую настройку, если она есть:

skip-networking=1

Вы можете почитать описания параметров, имеющих отношение к репликации, в документации MariaDB.

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

# service mysql restart
# service mysql status

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

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

# mysql -u root
> CREATE USER repl_user;
> GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY '*******';
> FLUSH PRIVILEGES;

Копирование базы данных с мастер-сервера на сервер реплики

Для переноса базы данных нужно открыть два консольных окна на мастер-сервере.

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

mysql> USE myshop_db
mysql> FLUSH TABLES WITH READ LOCK;

В этом же окне проверяем статус мастера:

MariaDB [myshop_db]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |     342  | myshop_db    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Значения mysql-bin.000007 и 342 будут нужны для запуска реплики.

Внимание! Если выйти из окна консоли, где вы ввели команду FLUSH TABLES, то сервер разблокирует таблицы и они снова будут доступны на запись. Дамп базы нужно делать во втором, отдельном окне, не закрывая окно консоли, в котором была выдана команда FLUSH TABLES WITH READ LOCK.

Во втором консольном окне делаем дамп базы данных от имени пользователя myshop_db:

# mysqldump -umyshop_db -p -hlocalhost --opt --quote-names myshop_db > myshop_db.sql

Разблокируем таблицы на мастере в первом консольном окне, чтобы пользователи могли работать дальше:

mysql> UNLOCK TABLES;

На сервере реплики обычным пользователем, например, admdb создаем каталог /home/admdb/repl:

$ mkdir /home/admdb/repl

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

$ scp -v myshop_db.sql [e-mail]:/home/admdb/repl/

Здесь xxx.xxx.xxx.xxx — адрес IP сервера реплики.

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

$ mysql -umyshop_db -p -hlocalhost myshop_db < myshop_db.sql

После загрузки базы на сервере реплики надо почистить журналы в /var/log/mysql:

RESET MASTER;

Подключение к мастер-серверу

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

MariaDB [(none)]> SHOW SLAVE STATUS\G
Empty set (0.000 sec)

Если это не так, удалите данные репликации:

MariaDB [(none)]> RESET SLAVE ALL;
MariaDB [(none)]> SHOW SLAVE STATUS\G
Empty set (0.000 sec)

Укажите на сервере реплики параметры подключения к мастеру:

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='repl_user', MASTER_PASSWORD='*******', MASTER_LOG_FILE = 'mysql-bin.000007', MASTER_LOG_POS = 342;

Здесь xxx.xxx.xxx.xxx — адрес IP сервера мастера. Параметры MASTER_LOG_FILE и MASTER_LOG_POS нужно взять из состояния мастера (в первом консольном окне).

Запуск реплики

Запускаем реплику при помощи следующей команды:

MariaDB [(none)]> START SLAVE;

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

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: xxx.xxx.xxx.xxx
                   Master_User: repl_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000102
           Read_Master_Log_Pos: 2195
                Relay_Log_File: mysql-relay-bin.000007
                 Relay_Log_Pos: 1573
         Relay_Master_Log_File: mysql-bin.000007
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: myshop_db
           Replicate_Ignore_DB:
            Replicate_Do_DB:
        Replicate_Ignore_DB:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 2195
               Relay_Log_Pos: 1878
               Until Condition: None
                Until_Log_File:
                 Antilogous: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Cipher:
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 4244209171
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 3
1 row in set (0.000 sec)

Убедитесь, что в этой выдаче значения параметров Slave_IO_Running и Slave_SQL_Running равно Yes, а значение параметра Seconds_Behind_Master равно 0.

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

MariaDB [myshop_db]> show slave hosts;
+------------+----------------------+------+------------+
| Server_id  | Host                 | Port | Master_id  |
+------------+----------------------+------+------------+
| 11213181   | r01slave.domain.ru   | 3306 | 42442171   |
+------------+----------------------+------+------------+
1 row in set (0.000 sec)

Если все так, то значит, репликация работает.

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

Отключение репликации

Если вам нужно превратить сервер реплики в мастер-сервер, то следует отключить репликацию. Сначала выдайте команду STOP SLAVE IO_THREAD:

MariaDB [(none)]> STOP SLAVE IO_THREAD;

Далее выдавайте команду SHOW PROCESSLIST:

MariaDB [(none)]> SHOW PROCESSLIST;
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
…
| 41 | root | localhost | NULL | Query | 0 | Init | SHOW PROCESSLIST | 0.000 |
| 43 | system user | | NULL | Slave_SQL | 346 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
7 rows in set (0.000 sec)

Дождитесь появления сообщения:

Slave has read all relay log; waiting for the slave I/O thread to update it

Это сообщение говорит о том, что сервер реплики выполнил все команды из relay-лога в своей базе.

Теперь останавливаем реплику и очищаем bin-log:

MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> RESET MASTER;

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

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

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: xxx.xxx.xxx.xxx
                   Master_User: repl_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000100
           Read_Master_Log_Pos: 3342
                Relay_Log_File: mysql-relay-bin.00007
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000100
              Slave_IO_Running: No
             Slave_SQL_Running: No
               Replicate_Do_DB: myshop_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: 342
               Relay_Log_Space: 864
               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: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 4244209171
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State:
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

Убедитесь, что значение параметров Slave_IO_Running и Slave_SQL_Running указано как No.

Далее, чтобы при перезагрузке ОС на сервере репликации или при перезапуске сервиса MariaDB репликация не возобновилась, уберите символ комментария со строки параметра skip_slave_start в файле конфигурации сервера репликации /etc/mysql/mariadb.conf.d/50-server.cnf:

skip_slave_start = 1

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

Если вы настраиваете репликацию MariaDB версии 10.5 или новее, можете настроить репликацию на базе глобального идентификатора транзакции global transaction ID. Эта процедура описана здесь.

Мониторинг репликации с помощью Zabbix

Для мониторинга репликации MySQL или MariaDB используйте плагин MySQL by Zabbix agent. Про установку и настройку этого плагина мы уже рассказывали в статье «MariaDB: настройка и мониторинг с помощью Zabbix».

Здесь мы расскажем только об особенностях, имеющих отношение к мониторингу репликации.

Создание пользователя zbx_monitor

Вам необходимо создать пользователя zbx_monitor:

MariaDB [(none)]> CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';

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

MariaDB [(none)]> GRANT REPLICATION CLIENT, REPLICATION SLAVE,BINLOG MONITOR,SLAVE MONITOR,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'localhost';

Такие права нужны для того, чтобы плагин MySQL by Zabbix agent от имени этого пользователя мог выполнять в MariaDB версии 10.5 команду, на базе которой и построен мониторинг:

show slave status\G

Для мониторинга сервера MySQL или MariaDB версии до 10.5 (например, MariaDB 10.3) у пользователя zbx_monitor должны быть права REPLICATION CLIENT. Однако у новых версий MariaDB этих прав для мониторинга репликации недостаточно.

Подробнее от этом можно почитать здесь и здесь. Установите минимально необходимые права в соответствии с версией вашей СУБД.

Для проверки достаточности прав вы можете подключиться к консоли MariaDB как пользователь zbx_monitor, а затем выдать в консоли команду «show slave status\G». Если прав недостаточно, вы увидите соответствующее сообщение об ошибке.

Когда плагин MySQL by Zabbix пытается получить доступ к серверу с адреса 127.0.0.1, а не localhost, и этот доступ не настроен, то при выдаче команды «system mysql status» на консоли появится такое сообщение:

Sep 01 15:59:56 xxx1slave.domain.ru mariadbd[2543745]: 2022-09-01 15:59:56 1624 [Warning] Access denied for user 'zbx_monitor'@'127.0.0.1' (using password: YES)

Домен xxx1slave.domain.ru указан только для примера.

В этом случае нужно добавить пользователя:

MariaDB [(none)]> CREATE USER 'zbx_monitor'@'127.0.0.1' IDENTIFIED BY '<password>';
MariaDB [(none)]> GRANT REPLICATION CLIENT, REPLICATION SLAVE,BINLOG MONITOR,SLAVE MONITOR,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'127.0.0.1';

Метрики и триггеры мониторинга репликации

Если сервер MySQL или MariaDB участвует в репликации (как сервер реплики), средства Zabbix для обнаружения LLD автоматически создают необходимые метрики (рис. 1).

Рис. 1. Метрики мониторинга репликации

На рис. 1 мы закрасили адрес IP мастер-сервера.

Также автоматически создаются необходимые триггеры (рис. 2).

Рис. 2. Триггеры мониторинга репликации

На рис. 2 закрашены имена хоста реплики, а также адрес IP мастер-сервера.

Как видите, эти триггеры будут установлены, если репликация не выполняется или работает с большим отставанием.

Самый серьезный уровень Average по умолчанию назначен триггеру The slave I/O thread is not running. Если он установился, то информация из бинарного лога мастера не попадает в журнал релея (relay log) на сервере реплики. Такое бывает, например, в результате ошибки в сети.

Срабатывание триггера Replication lag is too high может означать, что сервер реплики не справляется со своей работой из-за недостаточной производительности или проблем с аппаратным обеспечением. Этот вопрос обсуждается здесь.

Уровень серьезности триггеров вы можете изменить в соответствии с требованиями вашего бизнеса.

Автор статьи: Александр Фролов.