Multi-source репликация в MySQL5.7

Сегодня мой рассказ будет о такой захватывающей штуке, как репликация баз данных в MySQL из нескольких источников. Отмечу, что данная статья не претендует на звание «истины в последней инстанции» и призвана осветить особенности данной технологии в разрезе возникшей у меня проблемы. Итак, приступим. Однажды в далёкой-далёкой галактике…

Любое приключение начинается внезапно, так получилось и в этот раз. Специфика разрабатываемого мною приложения такова, что различные пользователи в различных офисах заполняют базу данными, но также должны видеть данные своих коллег из других отделений. Казалось бы классическая клиент-серверная архитектура в помощь, но… Однажды вечером шеф пришёл в офис и объявил новость по проекту: «Приложение должно работать в нескольких офисах, в случае разрыва соединения с центральным офисом потеря сохраняемых пользователями данных недопустима. Подумайте на этот счёт.» Ну что ж, слово сказано — давайте искать решение. Спустя некоторое время обсуждения всех аспектов проблемы между разработчиками и админами офиса был вынесен вердикт: «Нам поможет репликация!» Загуглив реализацию возможности репликации выяснилось, что multi-source репликации официально поддерживается только в версии MySQL 5.7, а на момента написания статьи она был ещё не очень стабильной. Ну да мы никуда не торопимся, авось к моменту релиза устаканится.

Если вы не знакомы с принципами репликации в MySQL, советую ознакомится с этой темой тут: Репликация данных, Как настроить MySQL Master-Slave репликацию.

С моей точки зрения, самый простой способ чему-то научится это практика. Приступим. Для начала определимся что нам нужно реплицировать и как мы это будем делать. Я выбрал следующую архитектуру. В офисе каждой компании работает свой промежуточный MySQL Master-сервер, который хранит данные только этого офиса. Он собирает запросы INSERT, UPDATE, DELETE от конечных пользователей. Все запросы типа SELECT пользователи отправляют на центральный, физически удалённый Slave-сервер. В случае если соединения с центральным сервером нет, то запросы SELECT направляются на офисный промежуточный Master-сервер. Связано это с особенностями репликации самого MySQL. Репликация из нескольких источников, по сути, происходит по схеме Master-Slave, в которой существует несколько Master-серверов, данные с которых синхронизируются на Slave-сервере. Как следствие, изменение данных на Slave-сервере, приведёт к ошибке репликации. К тому же, в моём случае сохранение данных поступающих от пользователей в офисах приоритетней нежели актуальность данных в выборках. В итоге общая схема работы становится такой, пользователи сохраняют свои данные на промежуточный сервер, откуда те поступают на центральный сервер, при отправлении пользователем запроса на выборку, он направляется на центральный сервер, если тот доступен или на промежуточный, если нет.
Вуаля, и овцы целы, и волки сыты. Пользователи имеют возможность сохранять свои данные не думая о доступности центрального сервера, а центральный сервер получит все данные из промежуточных серверов как только соединение с ними восстановятся.

В моём случае структура базы данных как на промежуточных серверах, так и на центральном сервере абсолютно идентична, а значит у меня есть проблема — уникальность ключевых полей. За уникальностью приходится следить самостоятельно на уровне приложения, для этого я решил в каждую таблицу базы данных добавить специальное поле, куда будет записываться номер промежуточного сервера, к которому относится эта запись.

    Id | company_id |   field1   |   field2       1 |     1      | somevalue1 | somevalue1      2 |     1      | somevalue2 | somevalue2

Далее предстоит немного изменить структуру таблиц в базе данных на центральном сервере, таким образом чтобы поле Id не было уникальным, в итоге данные таблицы на центральном сервере будут иметь следующую структуру.

    Id | company_id |   field 1  |   field2      1 |     1      | somevalue1 | somevalue1      1 |     2      | somevalue1 | somevalue1      2 |     1      | somevalue2 | somevalue2      2 |     2      | somevalue2 | somevalue2

В моём случае, данный подход был признан лучшим, так как данные в любом случае должны различаться по признаку принадлежности к конкретному офису, а усложнение SQL запросов типа SELECT на одно условие — незначительно.

И так, казалось бы всё просто, у нас есть схема репликации Master-Slave, в которой имеется несколько серверов типа Master и один сервер типа Slave, куда мастера по средствам бинарных логов, отправляют запросы произведённые в их базе данных, таким образом приводя в полное соответствие обе базы данных. Но давайте зададимся вопросом каким же образом Slave-сервер различает какие из запросов откуда поступили, какие из них были выполнены, а какие нет.

Дело в том что каждый бинарный лог содержит в себе определённый набор транзакций, соответствующим способом проименованный, данную задачу решает GTID идентификаторы (global transaction identifier). Каждый из MySQL-серверов участвующий в цепочке репликации должен иметь уникальный server-uuid, который находится в файле /var/lib/mysql/auto.cnf

server-uuid=51164157-b6c9-11e5-bb7c-4e745964e860

В дальнейшем, когда Master-сервер формирует транзакции запросов, выполненных над реплицируемой базой данных, он нумерует каждую из них следующим образом:

GTID = source_id:transaction_id 51164157-b6c9-11e5-bb7c-4e745964e860:23

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

Конфигурация master-сервера:

# Replication master settings  # ID сервера server-id = 2  # определяем где будут лежать бинарные логи для транзакций log-bin = /var/lib/mysql/mysql-bin.log  # Включаем GTID транзакции, не забываем что нам нужно перегенерить server-uuid в auto.cnf gtid-mode = ON enforce-gtid-consistency = ON  # Список реплицируемых баз данных binlog-do-db  = crm  # Список не реплицируемых баз данных binlog-ignore-db = mysql binlog-ignore-db = sys binlog-ignore-db = performance_schema binlog-ignore-db = information_schema

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

CREATE USER 'slave_user'@'%' IDENTIFIED BY 'slavepass'; GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';

Проверяем состояние мастера:

mysql> show master status; +------------------+----------+--------------+----------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB     | Executed_Gtid_Set | +------------------+----------+--------------+----------------------+-------------------+ | mysql-bin.000001 |      154 | crm          | mysql,sys,           |                   | |                  |          |              | performance_schema,  |                   | |                  |          |              | information_schema   |                   | +------------------+----------+--------------+----------------------+-------------------+ 1 row in set (0,00 sec)

Конфигурация slave-сервера:

# Replication Slave settings  # Номер сервера server-id = 1  # Включаем GTID транзакции, не забываем следить чтобы в auto.cnf был перегенерен server-uuid gtid-mode = on enforce_gtid_consistency = on  # Включаем хранение информации о логах через служебные таблицы, а не через файлы master-info-repository = TABLE relay-log-info-repository = TABLE  # Определение пути хранения логов relay-log = /var/lib/mysql/mysql-relay-bin.log  # Путь к bin логу на Мастере log-bin = /var/lib/mysql/mysql-bin.log  # Базы данных для репликации binlog-do-db = crm  # Список баз не реплицируемых баз данных binlog-ignore-db = mysql binlog-ignore-db = sys binlog-ignore-db = information_schema binlog-ignore-db = performance_schema

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

CHANGE MASTER TO MASTER_HOST='10.20.0.41', MASTER_USER='slave_user', MASTER_PORT=3306, MASTER_PASSWORD='slavepass', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master1'; CHANGE MASTER TO MASTER_HOST='10.20.0.42', MASTER_USER='slave_user', MASTER_PORT=3306, MASTER_PASSWORD='slavepass', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master2'; START SLAVE FOR CHANNEL "master1"; START SLAVE FOR CHANNEL "master2";

Проверяем статус Slave-сервера:

SHOW SLAVE STATUS\G  *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.20.0.41                   Master_User: slave_user                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 154                Relay_Log_File: mysql-relay-bin-master1.000002                 Relay_Log_Pos: 367         Relay_Master_Log_File: mysql-bin.000001              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: 154               Relay_Log_Space: 582               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: 3                   Master_UUID: 73f11a40-b862-11e5-8b0c-1e80294d0535              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:        Last_IO_Error_Timestamp:       Last_SQL_Error_Timestamp:                 Master_SSL_Crl:             Master_SSL_Crlpath:             Retrieved_Gtid_Set:              Executed_Gtid_Set:                  Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name: master1            Master_TLS_Version:  *************************** 2. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.20.0.42                   Master_User: slave_user                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 154                Relay_Log_File: mysql-relay-bin-master2.000002                 Relay_Log_Pos: 367         Relay_Master_Log_File: mysql-bin.000001              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: 154               Relay_Log_Space: 582               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_UUID: 51164157-b6c9-11e5-bb7c-4e745964e860              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:        Last_IO_Error_Timestamp:       Last_SQL_Error_Timestamp:                 Master_SSL_Crl:             Master_SSL_Crlpath:             Retrieved_Gtid_Set:              Executed_Gtid_Set:                  Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name: master2            Master_TLS_Version:  2 rows in set (0,00 sec)

Вуаля, всё работает, а нам того и надо. Всем удачи.

ссылка на оригинал статьи https://habrahabr.ru/post/276581/