Описание

Резервирование данных, хранящихся в таблице СУБД MariaDB, осуществляется путём встречной репликации по принципу master-master (ведущий-ведущий). При такой схеме работы все изменения в БД на одном хосте в реальном режиме времени транслируются на второй хост. При этом каждый экземпляр сервиса Eltex.EMS работает по общему виртуальному адресу с одним из экземпляров СУБД ( https://mariadb.com/kb/ru/setting-up-replication/). Это позволяет получить актуальную копию БД на двух хостах одновременно. При разрыве связи изменения накапливаются, после восстановления происходит синхронизация.

Настройка конфигурации

В секции [mysqld] файла конфигурации /etc/mysql/mariadb.conf.d/50-server.cnf произвести следующие изменения:

Закомментировать или удалить строку:

bind-address = 127.0.0.1

Указать server-id. Для серверов необходимо задать разные идентификаторы:

для первого:

server-id = 1

для второго:

server-id = 2

Включить бинарные логи:

log_bin = /var/log/mysql/mysql-bin.log;

Указать параметр максимального количества подключений (коннектов) к базе данных

max_connections=500

Указать настройку репликации с использованием GTID и отключить использование SSL

gtid_strict_mode=1
ssl=0

Указать параметры auto_increment_increment (шаг приращения) и auto_increment_offset (стартовую точку).

Для первого сервера:

auto_increment_increment= 2
auto_increment_offset = 1

Для второго сервера:

auto_increment_increment= 2
auto_increment_offset = 2

Указать базы, для которых будут вестись логи:

binlog-do-db = eltex_alert
binlog-do-db = eltex_ems
binlog-do-db = wireless
binlog-do-db = radius
binlog-do-db = eltex_auth_service
binlog-do-db = ELTEX_PORTAL
binlog-do-db = eltex_doors
binlog-do-db = eltex_ngw
binlog-do-db = eltex_pcrf
binlog-do-db = eltex_ott
binlog-do-db = eltex_jobs
binlog-do-db = eltex_jerry
binlog-do-db = eltex_bruce
binlog-do-db = eltex_wifi_customer_cab
binlog-do-db = eltex_sorm2
binlog-do-db = eltex_ont
binlog-do-db = eltex_wids

Указать базы, для которых не будут вестись логи:

binlog-ignore-db = mysql
binlog-ignore-db = Syslog
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

Перезапустить сервис mysql на каждом сервер и создать БД для репликации:

sudo service mariadb restart

Создание учетных записей для репликации

Создать учетную запись для репликации на первом сервере:

GRANT SELECT, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'<ip_server2>' IDENTIFIED BY 'password';
GRANT SELECT, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'<ip_server1>' IDENTIFIED BY 'password'; #необходимо для проверки состояния репликации из EMS
FLUSH PRIVILEGES;

Создать учетную запись для репликации на втором сервере:

GRANT SELECT, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'<ip_server2>' IDENTIFIED BY 'password';
GRANT SELECT, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'<ip_server1>' IDENTIFIED BY 'password'; #необходимо для проверки состояния репликации из EMS
FLUSH PRIVILEGES;

Запуск slave-репликации на втором сервере

Первый сервер

В консоли MySQL выполнить команду:

show master status \G

Ожидать ответ вида:

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000016
        Position: 3764739
    Binlog_Do_DB: eltex_alert,eltex_ems,wireless,radius,eltex_auth_service,ELTEX_PORTAL,eltex_doors,eltex_ngw,eltex_pcrf,eltex_ott,eltex_jobs,eltex_jerry,eltex_bruce,eltex_wifi_customer_cab,eltex_sorm2,eltex_ont,eltex_wids
Binlog_Ignore_DB: mysql,Syslog,performance_schema,information_schema
1 row in set (0.000 sec)

Скопировать значения параметров File и Position.

Второй сервер

Настроить и запустить репликацию второго сервера командой:

mysql>  CHANGE MASTER TO MASTER_HOST='<ip_server1>', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_USE_GTID=slave_pos;
START SLAVE;

Проверка состояния репликации

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.152.15
                   Master_User: replication
                   Master_Port: 5890
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000007
           Read_Master_Log_Pos: 20315691
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 5341
         Relay_Master_Log_File: mysql-bin.000007
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_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: 20315691
               Relay_Log_Space: 5651
               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: 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: 2
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-2-82063
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 46799
1 row in set (0.001 sec)

Если параметры Slave _ IO _ Running иSlave _ SQL _ Running имеют значение «Yes», репликация успешно запустилась.

Тетстирование

На первом сервере необходимо создать таблицу в БД и наполнить ее данными:

use eltex_ems;
create table test1 (mid int(11) auto_increment, PRIMARY KEY (mid)) Engine=InnoDB;

Проверить на втором сервере, что новая таблица скопирована.

show tables from eltex_ems;

Запуск репликации на первом сервере

Второй сервер

В консоли MySQL выполнить команду:

show master status \G

Ожидать ответ вида:

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000007
        Position: 20315691
    Binlog_Do_DB: eltex_alert,eltex_ems,wireless,radius,eltex_auth_service,ELTEX_PORTAL,eltex_doors,eltex_ngw,eltex_pcrf,eltex_ott,eltex_jobs,eltex_jerry,eltex_bruce,eltex_wifi_customer_cab,eltex_sorm2,eltex_ont,eltex_wids
Binlog_Ignore_DB: mysql,Syslog,performance_schema,information_schema
1 row in set (0.000 sec)

Первый сервер

Настроить и запустить репликацию первого сервера командой:

CHANGE MASTER TO MASTER_HOST='<ip_server2>', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_USE_GTID=slave_pos;
START SLAVE;

Проверка состояния репликации

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.152.12
                   Master_User: replication
                   Master_Port: 5890
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000016
           Read_Master_Log_Pos: 3805471
                Relay_Log_File: mysqld-relay-bin.000003
                 Relay_Log_Pos: 3805770
         Relay_Master_Log_File: mysql-bin.000016
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_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: 3805471
               Relay_Log_Space: 3807056
               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: 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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-1-95152
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 4
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 13118
1 row in set (0.000 sec)

(пример вывода приведен не полностью ввиду того, что остальные данные не так важны)

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

Скрипт для запуска репликации на серверах

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

#!/bin/bash
REMOTE_HOST='<IP встречного сервера>'
LOGIN='<логин для рутового доступа на локальный и удаленный сервер>'
PASS='<пароль для рутового доступа на локальный и удаленный сервер>'
 
REMOTE_FILE=`mysql --host=$REMOTE_HOST --user=$LOGIN --password=$PASS -e "show master status \G" | grep "File" | awk '{print $2}'`
REMOTE_POS=`mysql --host=$REMOTE_HOST --user=$LOGIN --password=$PASS -e "show master status \G" | grep "Position" | awk '{print $2}'`
 
echo REMOTE_FILE=$REMOTE_FILE
echo REMOTE_POS=$REMOTE_POS
mysql --user=$LOGIN --password=$PASS -e "stop slave"
mysql --user=$LOGIN --password=$PASS -e "CHANGE MASTER TO MASTER_HOST='<ip_server2>', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_USE_GTID=slave_pos;"
mysql --user=$LOGIN --password=$PASS -e "start slave"

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

Также скрипту нужно дать права на исполнение:

chmod +x replication.sh

Автоматическое восстановление репликации при потере связи между серверами

Необходимо создать файл /etc/keepalived/revive_mysql_replication.sh на обоих серверах с содержимым:

#!/bin/bash
LOCAL_FILE=`mysql --user=root --password=root -e "show slave status \G" | grep" Master_Log_File" | awk ' {print $2}'`
REMOTE_FILE=`mysql --host=<IP адрес встречного сервера> --user=root --password=root -e "show master status \G" | grep "File" | awk '{print $2}'`
 
if [ $LOCAL_FILE != $REMOTE_FILE ]
 then
 mysql --user=root --password=root -e "stop slave"
 mysql --user=root --password=root -e "start slave"
fi

host – адрес встречного сервера.

Добавить в cron выполнение скрипта каждую минуту:

crontab -l | { cat; echo "*/1 * * * * /etc/keepalived/revive_mysql_replication.sh"; } | crontab

Добавить пользователей на первом сервере (указать IP-адрес встречного сервера):

GRANT ALL PRIVILEGES ON *.* TO 'root'@'<ip_server2>' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

Добавить пользователей на втором сервере (указать IP-адрес встречного сервера):

GRANT ALL PRIVILEGES ON *.* TO 'root'@'<ip_server1>' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;