[Из песочницы] 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)
Вуаля, всё работает, а нам того и надо. Всем удачи.
