Настройка репликации для БД MariaDB

Настройка репликации для БД MariaDB в режиме Master - Slave.

Измените настройки и создайте пользователя для репликации на хосте MariaDB Matser.

[root@www ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
# add follows in [mysqld] section : get binary logs
log-bin=mysql-bin
# уникальный ID сервера (uniq one)
server-id=101
max_binlog_size = 100M
[root@www ~]# systemctl restart mariadb
[root@www ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB-log MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
# create user (set any password for 'password' section)
MariaDB [(none)]> grant replication slave on *.* to repl_user@'%' identified by 'password'; 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> flush privileges; 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> exit
Bye

Есть несколько вариантов установки опций, которые могут повлиять на репликацию. Проверьте следующие настройки опций, чтобы в дальнейшем избежать проблем. Более подробно переменные для репликации описаны в Replication and Binary Log System Variables

• skip-networking. Если skip-networking=1, то сервер будет ограничивать подключения только к localhost, а удаленные slave не смогут соединиться. Удалите эту строку, если она существует.
• bind-address. Также, если адрес сервера прослушивает соединения TCP/IP 127.0.0.1 (localhost), удаленные slave не смогут соединиться. Закомментируйте эту строку или задайте bind-address = 0.0.0.0.
• Параметр replicate-do-db указываем имя базы данных, которая будет реплицироваться с мастер-сервера. Если ее не указать, будут реплицированы все базы.
• max_binlog_size (по умолчанию значение 1 ГБ) - если двоичный журнал превышает этот размер после записи, сервер ротирует его, закрывая его и открывая новый двоичный журнал. Отдельные транзакции всегда будут храниться в одном и том же двоичном журнале, поэтому сервер будет ждать завершения открытых транзакций перед ротацией. Выберите эти параметры исходя из наличия свободного пространства на дисках сервера, а также интенсивности обращений к СУБД.

Изменение настроек на подчиненных хостах MariaDB:

vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
## Replication
# add follows in [mysqld] section : get binary logs
log-bin=mysql-bin
# define server ID (uniq one)
server-id=102
# read only yes
read_only=1
# Имя хоста
report-host=node01.example.com
#slave-skip-errors = 1062

• При помощи параметра report-host можно задать имя хоста реплики, как оно будет отображаться при просмотре на мастер-хосте списка хостов реплик.
• При помощи параметра slave-skip-errors можно указать какие ошибки в репликации можно пропускать, например 1062 пропустит дублирование записей. Номер ошибок смотрите в статусе репликации, как описано ниже.

Перегружаем Mysql сервер

systemctl restart mariadb

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

• явное задание блокировки FLUSH TABLES WITH READ LOCK
• создает файл xtrabackup_binlog_info идентичный команде SHOW MASTER STATUS; В этом файле будет указана текущая позиция binary log и название файла

cat /home/mariadb_backup/xtrabackup_binlog_info
 
mysql-bin.000001	3367439	0-101-1789

После получения данных перенесите их на слейв сервер с помощью sftp, rsync, scp или другими способами.

rsync -avzP /home/mariadb_backup/ root@xxx.xxx.xxx.xxx:/home/mariadb_backup

Остановить работу MariaDB и удалите существующие данные

systemctl stop mariadb
rm -rf /var/lib/mysql/*

Прежде чем вы сможете восстановить резервную копию, вам сначала необходимо подготовить ее, чтобы обеспечить согласованность файлов данных. Вы можете сделать это с помощью опции prepare . Опция target-dir указывает, где размещены файлы резервной копии. Запускаем задачу подготовки перед задачей восстановления (OK, если [выполнено OK]):

mariabackup --prepare --target-dir /home/mariadb_backup
.....
.....
completed OK!

После завершения резервного копирования и подготовки резервной копии к восстановлению (предыдущий шаг) вы можете восстановить резервную копию, используя опции –copy-back или –move-back . Опция –copy-back позволяет сохранять исходные файлы резервных копий. Опция –move-back фактически перемещает файлы резервных копий на datadir , поэтому исходные файлы резервных копий теряются.

mariabackup --copy-back --target-dir /home/mariadb_backup
.....
.....
completed OK!

Устанавливаем права и запускаем MariaDB:

chown -R mysql. /var/lib/mysql
systemctl start mariadb

Забираем значение [File] и [Position] главного журнала из файла /home/mariadb_backup/xtrabackup_binlog_info, в моем случае имя файла mysql-bin.000001 и позиция 3367439.

cat /home/mariadb_backup/xtrabackup_binlog_info
mysql-bin.000001        3367439 0-101-1789

Настраиваем репликацию:

mysql -u root -p
 
MariaDB [(NONE)]> CHANGE master TO 
    -> master_host='10.0.0.31',             # Master Host IP address
    -> master_user='repl_user',             # replication USER
    -> master_password='password',          # replication USER password
    -> master_log_file='mysql-bin.000001',  # [File] VALUE confirmed above
    -> master_log_pos=3367439;              # [POSITION] VALUE confirmed above
Query OK, 0 ROWS affected (0.58 sec)

Запускаем репликацию:

MariaDB [(NONE)]> START slave; 
Query OK, 0 ROWS affected (0.00 sec)

Проверим статус репликации:

MariaDB [(none)]> show slave status\G

Если вы не видите ошибок в выводе, это означает, что репликация работает хорошо. Вы должны увидеть следующие два «Yes», указывающие, что все идет хорошо:

...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
...

Если Slave_IO_Running имеет значение Yes, а Slave_SQL_Running — значение Yes, то репликация работает нормально.

Затем проверьте Last_IO_Errno, Last_IO_Error, Last_SQL_Errno и Last_SQL_Error. В этих полях отображается номер ошибки и сообщение об ошибке для самой последней ошибки, вызвавшей зависание потока SQL. Номер ошибки 0 и пустое сообщение означают отсутствие ошибки. Изучите любое ненулевое значение ошибки, проверив ее код в справочнике по сообщению об ошибке сервера MySQL.

Seconds_Behind_Master отображает задержку репликации в секундах.

• STOP SLAVE; Чтобы остановить репликацию
• reset slave; чтобы репликация перезапускалась из чистого состояния, вы можете сбросить репликацию
• reset slave 'master01' all; тоже что и второе, только не пытается запустить репликации после перезапуска

На мастере смотрим узлы репликации:

MariaDB [(NONE)]> SHOW slave hosts;
+-----------+---------------+------+-----------+
| Server_id | Host          | Port | Master_id |
+-----------+---------------+------+-----------+
|       102 | 10.0.0.55 | 3306 |       101 |
+-----------+---------------+------+-----------+
1 ROW IN SET (0.000 sec)

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

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

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
INSERT INTO t VALUES ('5');

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

select * from t;
+------+
| c    |
+------+
| 5    |
+------+
1 row in set (0.001 sec)

Дополнительно: Устранение неполадок с задержкой репликации на База данных Azure для MySQL

Быстрая проверка статуса репликации Mysql из шелл: если Slave_IO_Running имеет значение Yes, а Slave_SQL_Running — значение Yes, то репликация работает нормально.

mysql  -e "SHOW SLAVE STATUS\G" |grep -E 'Slave_IO_Running|Slave_SQL_Running'
 
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

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

1. На мастер Mysql: reset master;
2. На слейве Mysql: stop slave;
3. На слейве Mysql: reset slave;
4. На слейве Mysql: start slave;

И мониторим командами, указанными выше.