Запихать Mysql в Kubernetes оказалось не очень хорошей идеей и я решил основательно пересмотреть инфраструктуру своего pet-проекта и за основу будет принят кластер из трех серверов. На каждом узле кластера будет развернут сервер Mysql в кластерном режиме.

В этом издевательстве над логикой мы настроим кластер MySQL с использованием Group Replication (встроенный механизм репликации в MySQL 5.7 и выше).

Серверная инфраструктура проекта

• s-rain-01 — 178.72.166.7
• s-rain-02 — 139.100.193.124
• s-rain-03 — 45.12.231.103

Созданные VPS в Selectel

• Три сервера с Ubuntu 24.04.
• На каждом сервере — 4 ГБ оперативной памяти и два vCPU.
• Открытые порты: 3306 (MySQL) и 33061 (Group Replication).
• Корректно настроенные DNS-имена shiskitech.ru и запись в /etc/hosts и /etc/hostname.

Установка MySQL на всех серверах

Выполните на каждом из трёх серверов:

# apt update
# apt install -y mysql-server

Запустите и включите автозапуск MySQL:

# systemctl enable mysql
# systemctl start mysql

Защитите установку MySQL (установите пароль root, удалите анонимных пользователей и т. д.):

# mysql_secure_installation

Шаг 2. Настройка конфигурации MySQL

На каждом сервере отредактируйте файл конфигурации MySQL:

# nano /etc/mysql/mysql.conf.d/mysqld.cnf

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

[mysqld]

# Уникальный идентификатор сервера (должен быть разным на каждом узле)
server-id = 1  # На node1: 1, на node2: 2, на node3: 3

# Включение бинарного лога (обязательно для репликации)
log-bin = mysql-bin

# Формат бинарного лога
binlog-format = ROW

# Режим проверки бинарного лога
binlog-checksum = NONE

# Автоматическое обновление бинарного лога
auto-increment-increment = 1  # На node1: 1, на node2: 2, на node3: 3
auto-increment-offset = 1  # На node1: 1, на node2: 2, на node3: 3

# Группа репликации
gtid-mode = ON
enforce-gtid-consistency = ON

# Разрешить репликацию с любых хостов (в продакшене ограничьте IP)
bind-address = 0.0.0.0

# Включение Group Replication
plugin-load = "group_replication.so"

#БД, которые нужно/не нужно реплицировать
#replicate-do-db = testdb
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
 
#Не вести журнал бин-лога для БД
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema

#Сохранять логи с мастера в свой бин-лог, чтобы передать слейв-серверу
log-slave-updates
#log_slave_updates = 1

# Сколько дней хранить бин-логи
expire_logs_days = 7

# Максимальный размер бин-лога
max_binlog_size = 500M

# Настройка Group Replication
group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot = off
group_replication_local_address = "NODE_IP:33061"  # Замените NODE_IP на IP текущего узла
group_replication_group_seeds = "NODE1_IP:33061,NODE2_IP:33061,NODE3_IP:33061"  # Замените IP на реальные

# Настройка портов
port = 3306

Важно: замените NODE_IP, NODE1_IP, NODE2_IP, NODE3_IP на реальные IP-адреса ваших серверов и обратите внимание, что у Selectel вам необходимо для связи хостов создать глобальный роутер и у хоста адрес из внутренней сети.

Сохраните файл и перезапустите MySQL:

# systemctl restart mysql

Шаг 3. Настройка безопасности и пользователей

На каждом сервере подключитесь к MySQL:

# mysql -u root -p

Создайте пользователя для Group Replication:

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'secure_password';
mysql> GRANT BACKUP_ADMIN ON *.* TO 'repl'@'%';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;

Шаг 4. Настройка первого узла (Primary)

Подключитесь к первому узлу (mysql-node1):

# mysql -u root -p

Выполните следующие команды:

-- Блокируем таблицы для консистентности
SET GLOBAL group_replication_bootstrap_group = ON;

-- Запускаем Group Replication
START GROUP_REPLICATION;

-- Проверяем статус
SELECT * FROM performance_schema.replication_group_members;

-- Загружаем плагин для клонирования
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

Убедитесь, что статус узла — ONLINE.

Запросим статус мастер-сервера:

mysql> show master status; 
+------------------+----------+--------------+---------------------------------------------+------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set                                                                  |
+------------------+----------+--------------+---------------------------------------------+------------------------------------------------------------------------------------+
| mysql-bin.000003 |      233 |              | information_schema,mysql,performance_schema | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2,
b624ddc1-60a8-11f1-a308-fa163ea808b8:1-5 |
+------------------+----------+--------------+---------------------------------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Шаг 5. Добавление остальных узлов

Для второго узла (mysql-node2)

Подключитесь к mysql-node2:

# mysql -u root -p

Выполните:

-- Отключаем bootstrap (только для первого узла)
SET GLOBAL group_replication_bootstrap_group = OFF;

-- Клонируем базу с мастера
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
SET GLOBAL clone_valid_donor_list = '10.10.10.2:3306';
CLONE INSTANCE FROM 'repl'@'10.10.10.2':3306 IDENTIFIED BY 'secure_password';

-- Запускаем Group Replication

CHANGE REPLICATION SOURCE TO SOURCE_USER='repl',  SOURCE_PASSWORD='secure_password' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

-- Проверяем статус
SELECT * FROM performance_schema.replication_group_members;

На этапе подключения slave-узлов меняйте переметр group_replication_group_seeds сначала два узла участвующие в репликации потом добавляем третий.

Для третьего узла (mysql-node3)

Повторите шаги для mysql-node2.

Шаг 6. Проверка работоспособности кластера

На любом узле выполните:

SELECT * FROM performance_schema.replication_group_members;

Ожидаемый результат: все три узла должны иметь статус ONLINE.

Создайте тестовую базу данных и таблицу на одном узле:

CREATE DATABASE test_cluster;
USE test_cluster;
CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test_table (id, name) VALUES (1, 'Test');

Проверьте, что данные синхронизированы на других узлах:

USE test_cluster;
SELECT * FROM test_table;

Статус кластера:

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5f75347a-60b9-11f1-bd56-fa163ea808b8 | s-rain-01   |        3306 | ONLINE       | PRIMARY     | 8.0.46         | XCom                       |
| group_replication_applier | bcd33b10-60a8-11f1-8903-fa163e6ebcc3 | s-rain-02   |        3306 | RECOVERING   | SECONDARY   | 8.0.46         | XCom                       |
| group_replication_applier | c1f043e7-60a8-11f1-bf6e-fa163ee378b1 | s-rain-03   |        3306 | RECOVERING   | SECONDARY   | 8.0.46         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

Шаг 7. Настройка автоматического запуска

На каждом узле включите автоматический запуск Group Replication:

SET PERSIST group_replication_start_on_boot = ON;

В конфиге серверов меняем параметр.

group_replication_start_on_boot = on

Шаг 8. Дополнительные настройки и мониторинг

Настройка мониторинга

Создайте скрипт для мониторинга состояния кластера:

sudo nano /usr/local/bin/check_mysql_cluster.sh

#!/bin/bash

mysql -e "SELECT * FROM performance_schema.replication_group_members\G" | grep -E "(MEMBER_STATE|MEMBER_HOST)"

Сделайте скрипт исполняемым:

sudo chmod +x /usr/local/bin/check_mysql_cluster.sh

Настройка резервного копирования

Используйте mysqldump для регулярного бэкапа:

mysqldump -u root -p --single-transaction --all-databases > /backup/mysql_backup_$(date +%Y%m%d).sql

Шаг 9. Устранение типичных проблем

Проблема: узел не присоединяется к кластеру

1. Проверьте, что порты 3306 и 33061 открыты:

   sudo ufw allow 3306
   sudo ufw allow 33061

2. Убедитесь, что group_replication_local_address и group_replication_group_seeds указаны корректно.
3. Проверьте логи MySQL:

   sudo tail -f /var/log/mysql/error.log

Проблема: ошибка синхронизации данных

1. Проверьте GTID:

   SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

2. При необходимости сбросьте GTID (только в тестовой среде):

   RESET MASTER;

Важные замечания

1. Количество узлов: для Group Replication рекомендуется нечётное количество узлов (3, 5 и т. д.) для обеспечения кворума.
2. Сетевые задержки: убедитесь, что между узлами минимальные сетевые задержки (менее 10 мс).
3. Резервное копирование: регулярно создавайте бэкапы, так как Group Replication не заменяет резервное копирование.
4. Безопасность: используйте сложные пароли, ограничьте доступ к MySQL только с доверенных IP-адресов.
5. Мониторинг: настройте мониторинг (например, с помощью Prometheus + MySQL Exporter) для отслеживания состояния кластера.