Ubuntu 18.04. MySQL 5.7. Настройка Master-Master репликации базы на двух серверах

Опубликовано / Обновленно

Задача: сделать синхронизацию одной базы данных на двух разных серверах. Для этого подойдёт мастер-мастер репликация Mysql.

Ubuntu 18.04.
Mysql 5.7
2 сервера: srv1.server.ru и srv2.server.ru

Установка Mysql

sudo apt update
sudo apt install mysql-server mysql-client

Изменение конфигурации Mysql

Отредактировать файл /etc/mysql/mysql.conf.d/mysqld.cnf на обоих серверах. Добавить, изменить.

На сервере 1:

server-id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1
bind-address = 0.0.0.0
replicate-do-db = mydb

На сервере 2:

server-id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2
bind-address = 0.0.0.0
replicate-do-db = mydb

server-id — Уникальный идентифицир сервера среди участников репликации
log_bin — Путь к бинарному логу. Журнал изменений
log_bin_index — Индексный файл журнала, позволяет узнать, какой журнал на данный момент активен и какие журналы ранее были использованы
relay_log — Журнал изменений полученых с реплики (промежуточный репликационный лог)
relay_log_index — Сами логи репликации. Таже суть что и для log_bin_index
expire_logs_days = 10 — Удалять бинарные логи старше 10 дней
max_binlog_size = 100M — Определяет размер файла бинлога, который используется при репликации. Mysql создаст новый файл, когда размер текущего файла достигнет лимита. По умолчанию, размер этого файла 1Гб
log_slave_updates — Обязывает подчинённый сервер записывать реплицированные события в собственный двоичный журнал (включает обмен данными между промежуточным и основным логами)
auto-increment-offset=1 — Чтобы исключить конфликты автоинкремента задаем ID начиная с 1 (1,3,5,…) или 2 (2,4,6,…)
auto-increment-increment=2 — Прибавление на 2
auto_increment_increment=2 и auto_increment_offset=1 устанавливают для данного сервера назначение автоматически только нечетных id. Этим самым мы избежим добавление с одинаковым ID на разные сервера записей, и при синхронизации данные не перетрутся;
bind-address — на каком ip запускать mysql сервер
replicate-do-db — Сообщает подчиненному серверу, что реплицироваться должна только указанная база данных. Если нужно реплицировать все базы, то этот параметр не указываем.

Перезапустить MySQL на обоих серверах:

sudo service mysql restart

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

Создание пользователя replicator с паролем «пароль»

create user 'replicator'@'%' identified by 'пароль';
grant replication slave on *.* to 'replicator'@'%';

Настройка репликации базы данных

На 1 мастере

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Запоминаем файл mysql-bin.000001 и Position = 154
На 2 мастере

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'srv1.server.ru', MASTER_USER = 'replicator', MASTER_PASSWORD = 'пароль', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154; 
START SLAVE;

На 2 мастере

show master status; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      149 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Запоминаем файл mysql-bin.000001 и Position = 149
На 1 мастере

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'srv2.server.ru', MASTER_USER = 'replicator', MASTER_PASSWORD = 'пароль', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 149; 
START SLAVE;

Вместо хостов srv1.server.ru и srv2.server.ru можно использовать ip адреса, но в моём случае удобней было через хост.

Проверка репликации

Создание базы на сервере 1:

CREATE DATABASE mydb;

На сервере 2 смотрим список баз:

SHOW DATABASES;
 +-------------------------+
 | Database                |
 +-------------------------+
 | information_schema      |
 | mydb                    |
 | mysql                   |
 | performance_schema      |
 | sys                     |
 +-------------------------+
 5 rows in set (0.01 sec)

Работает.

https://ruhighload.com/failover+и+доступность
https://ruhighload.com/Репликация+данных
https://ruhighload.com/Как+настроить+mysql+master-master+репликацию%3f
https://linoxide.com/linux-how-to/5-steps-setup-mysql-master-master-replication-ubuntu-16-04/
http://blog.sedicomm.com/2018/04/02/nastrojka-replikatsii-master-master-dlya-baz-dannyh-mysql/
http://ru.ispdoc.com/index.php/Репликация_master-master_в_MySQL
https://skeletor.org.ua/?p=2011