MySQL (MariaDB): репликация базы Master - Slave

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

В моём случае между серверами настроено VPN подключение, через которое будет происходить обмен данными. Репликацию базы будем делать по схеме Master (10.1.254.1) - Slave (10.1.254.3).

На мастере

В конфиг MySql на мастере добавляем:

#уникальный ИД сервера
server-id = 1
# путь к бинарному логу
log_bin = /var/log/mysql/mysql-bin.log
# имя базы, которая будет реплицироваться, можно повторить для нескольких баз
binlog_do_db = my_base
# количество дней сколько хранятся бинарные логи
expire_logs_days = 5
#синхронизация закешированного бинароного лога с его копией на диске,
#увеличивает надежность сохранения лога, но уменьшает быстродействие базы
#sync_binlog=1

В консоли сервера проверим значение server-id:

mysql> show variables like 'server_id';

Cоздадим пользователя с правами для репликации:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

Блокируем таблицы (необязательно при использовании gtid_slave_pos):

USE my_base;
FLUSH TABLES WITH READ LOCK;

Смотрим статус мастера:

mysql> SHOW MASTER STATUS;

Запоминаем значение File и Position, в моём случае это mysql-bin.000001 и 123.

Делаем дамп базы:

mysqldump -v -u root -p --events --routines --triggers --master-data=2 --single-transaction --skip-lock-tables --hex-blob my_base | gzip > my_base.sql.gz

Делая бекап таким образом (ключ master-data) таблицы в базе предварительно можно не блокировать. В большинстве случаев это срабатывает, но если у ваc возникнут ошибки - блокируйте таблицы.
Немного про ключи:
-v выводим статистику выполнения
--events бекапим таблицу событий
--routines бекапим функции и процедуры
--triggers бекапим триггеры
--master-data добавляем в выгрузку данные о текущей позиции binlog-a
--single-transaction для таблиц InnoDB гарантирует целостность данных бекапа и создание моментального снапшота
--skip-lock-tables отключаем блокировку таблиц во время бекапа (для myisam)
--hex-blob передает бинарные данные в hex формате, дамп будет чуть больше но избежим проблем c ошибками формата данных

Не забываем разблокировать таблицы:

USE my_base;
UNLOCK TABLES;

На слейве

Создаем базу и загружаем дамп:

mysql> CREATE DATABASE my_base;
gunzip my_base.sql.gz
mysql -u root -p -D my_base < my_base.sql

Смотрим и запоминаем значение gtid_slave_pos из дампа:

head my_base.sql -n100 | grep "gtid_slave_pos"

В моём случае это '0-1-117297'.

В конфиге MySql на слейве добавляем:

# ID Слейва, число отличное от мастера
server-id = 2
# Путь к relay логу
relay-log = /var/log/mysql/mysql-relay-bin.log
# Путь к bin логу на Мастере
log_bin = /var/log/mysql/mysql-bin.log
# Индексный файл со списком используемых файлов журналов
log_bin_index = mysql-bin.index
# Файлы бинлогов автоматически удаляются после 5 дней
expire_logs_days = 5
# База данных для репликации
binlog_do_db = my_base
# если необходимо сделать базы доступными только для чтения
#read_only = 1

Настраиваем, активируем слейв и проверяем подключение:

mysql> SET GLOBAL gtid_slave_pos='0-1-117297';
mysql> CHANGE MASTER TO MASTER_HOST='10.1.254.1', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 123, master_use_gtid=slave_pos;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

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

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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

mysql> RESET MASER;

И начинаем заново с блокировки таблиц на мастере и создании бекапа.
В консоли слейва:

mysql> STOP SLAVE;
mysql> RESET SLAVE;

Загружаем бекап и настраиваем заново подключение.

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

Для более подробного ознакомления и выбора способа репликации можно изучить, например, эту статью. В моём случае (mariadb 10.3) формат изменений (binlog_format) по умолчанию mixed (все запросы выполняются в режиме STATEMENT, но если встречаются данные которые не возможно передать при помощи этого типа, то журналирование выполняется в формате ROW), данные передаются асинхронно (мастер не контролирует успешность транзакций на slave). В каждом конкретном случае нужно выбирать отдельный способ синхронизации баз: пожертвовать скоростью или надежностью.