Вообщем, мне удалось успешно решить задачу по настройке Master-Master MySQL репликации между двумя удаленными серверами. Репликацию настраивал поверх OpenVPN канала, который предварительно настроил по инструкции взятой здесь ("Настройка OpenVPN - перевод официальной документации").
Для чистоты пошаговки сообщаю, что все делалось на Ubuntu 18.04
lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.2 LTS
Release: 18.04
Codename: bionic
Первый сервер
Итак, после настройки OpenVPN, имея уже установленный MySQL, редактируем /etc/my.cnf. Для начала найдем где он:
egrep -v '^$|^#' /etc/mysql/my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
Как видно из вывода выше, по стандартному файла нет, а лежит он здесь:
root@ubuntu-8gb-hel1-1:/etc/mysql/mysql.conf.d# ls
mysqld.cnf mysqld_safe_syslog.cnf
Имя файла - mysqld.cnf. Приведем файл к такому виду:
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 0.0.0.0
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
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
slave_exec_mode = IDEMPOTENT
Ключевых моментов здесь несколько.
- MySQL был перевешен на все имеющиеся адреса (bind-address= 0.0.0.0)
- добавлен log_bin и остальные параметры для репликации
Поскольку я буду гонять репликацию через тунель OpenVPN, мне нужно разрешить трафик только по этому внутреннему туннельному интерфейсу и запретить все ненужное (держать порт 3306 открытым на весь мир не тру).
tun0: flags=4305<UP,POINTOPOINT,RUNNING,NOARP,MULTICAST> mtu 1500
inet 10.8.0.1 netmask 255.255.255.255 destination 10.8.0.2
inet6 fe80::782e:bdb2:60ef:cb76 prefixlen 64 scopeid 0x20<link>
unspec 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00 txqueuelen 100 (UNSPEC)
RX packets 32386 bytes 8441773 (8.4 MB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 32429 bytes 8292722 (8.2 MB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
Итак, как видно из вывода выше, мой внутренний адрес 10.8.0.1.
Далее задействуем iptables что бы разрешить трафик MyQL только по OpenVPN.
iptables -A INPUT -s 127.0.0.1/32 -d 127.0.0.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.6/32 -d 10.8.0.6/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.6/32 -d 10.8.0.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.1/32 -d 10.8.0.6/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 3306 -j DROP
Что бы правила сохранились и после рестарта системы, смотрите инструкцию тут.
Далее создадим пользователя для репликации:
mysql -u root -p [root_password]
CREATE USER 'replicator'@'10.8.0.6' IDENTIFIED BY '[myC001p@ssw0rd]';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.8.0.6' IDENTIFIED BY '[myC001p@ssw0rd]';
Обратите внимание. Адрес 10.8.0.6 - это адрес моего второго сервера, который подключен по OpenVPN к текущей машине. Важный момент - для репликации пароль пользователя не должен превышать 32 символа. Если зададите более длинный пароль, прийдется тратить время и менять его. Я на этот момент таки напоролся, потому что официальная документация об этом не предупреждает (либо я невниимательно читал).
Теперь переходим к настройке второго сервера.
Второй сервер
На второй машине у меня точно такая же убунту, поэтому расположение конфига тоже. как и для первого сервака. Редактируем файл /etc/mysql/mysql.conf.d/mysqld.cnf:
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 0.0.0.0
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
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
slave_exec_mode = IDEMPOTENT
Далее аналогичным образом создадим пользователя для репликации. Только теперь у этого пользователя будет адрес первого сервера (откуда он будет подключаться):
mysql -u root -p [root_password]
CREATE USER 'replicator'@'10.8.0.1' IDENTIFIED BY '[myC001p@ssw0rd]';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.8.0.1' IDENTIFIED BY '[myC001p@ssw0rd]';
Теоретически, пароли можно (и наверное нужно) иметь разные. Для безопасности. Но что бы не запутаться, я оставил один пароль на обоих машинах.
Далее внесем правила в iptables (они точно такие же как и на первой машине):
iptables -A INPUT -s 127.0.0.1/32 -d 127.0.0.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.6/32 -d 10.8.0.6/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.6/32 -d 10.8.0.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.1/32 -d 10.8.0.6/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 3306 -j DROP
Теперь, если вам нужно реплицировать данные между уже настроенными серерами (как было в моем случае - база уже существовала), эту базу нужно перекинуть чепез mysqldump и восстановить на той машине, где ее нет. Иначе репликация будет ругаться что какая то таблица не найдена и т.п. Это важный момент, потому что я этого изначально не знал и пришлось потратить лишние 10 минут что бы это выяснить.
Далее нужно активировать саму репликацию.