MySQL падал каждую неделю: Galera Cluster для строительного портала с 2 млн визитов

Наши инженеры подключились к серверам и провели детальный аудит:

• СУБД — MySQL 8.0.35, одиночный сервер на выделенной машине (32 ГБ RAM, 8 vCPU, NVMe SSD 500 ГБ)
• Приложение — PHP 8.2 (Laravel), 4 веб-сервера за Nginx-балансировщиком
• Нагрузка на БД — до 3 000 запросов в секунду в пиках, 80% SELECT, 20% INSERT/UPDATE
• Размер БД — 47 ГБ данных, крупнейшая таблица products — 12 млн строк
• Резервное копирование — mysqldump по cron раз в сутки в 3:00 (блокировал таблицы на 8–12 минут)
• Репликация — отсутствует, single point of failure

Анализ slow query log показал, что 40% медленных запросов — это сложные JOIN по каталогам с полнотекстовым поиском. Ещё 30% — массовые INSERT при импорте прайс-листов от поставщиков (ежедневно около 500 000 строк).

# Типичная картина в пиковые часы
mysqladmin -u root -p status
Uptime: 432001  Threads: 287  Questions: 891203847
Slow queries: 14523  Opens: 89123  Open tables: 1024

# max_connections = 300, а в пике 287 — на грани
SHOW STATUS LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 298   |
+----------------------+-------+

Мы рассмотрели несколько вариантов кластеризации:

Выбор пал на MariaDB Galera Cluster по нескольким причинам:

• Синхронная репликация — все три ноды всегда содержат одинаковые данные, нет задержки реплики
• Multi-Master — запись возможна на любую ноду, что упрощает архитектуру
• Автоматический failover — при падении ноды кластер продолжает работать без ручного вмешательства
• Совместимость с MySQL — приложение на Laravel не потребовало изменений кода
• Зрелость решения — Galera используется в production более 10 лет, включая крупные инсталляции

Архитектура, которую мы спроектировали:

# Целевая архитектура
┌──────────────────────────────────────────────────────┐
│                    HAProxy (2 шт.)                    │
│              Virtual IP: 10.0.1.100:3306             │
│         (Keepalived VRRP для отказоустойчивости)      │
└───────────┬──────────────┬──────────────┬────────────┘
            │              │              │
     ┌──────▼──────┐ ┌────▼──────┐ ┌─────▼─────┐
     │   Node 1    │ │  Node 2   │ │  Node 3   │
     │ MariaDB     │ │ MariaDB   │ │ MariaDB   │
     │ 10.0.1.11   │ │ 10.0.1.12 │ │ 10.0.1.13 │
     │ (Write)     │ │ (Read)    │ │ (Read)    │
     └─────────────┘ └───────────┘ └───────────┘
            │              │              │
            └──────── Galera wsrep ───────┘
                 (Синхронная репликация)

На каждой из трёх нод выполнили одинаковую установку:

# Добавляем репозиторий MariaDB 11.4 LTS
sudo apt install -y apt-transport-https curl
curl -fsSL 'https://mariadb.org/mariadb_release_signing_key.asc' | sudo gpg --dearmor -o /usr/share/keyrings/mariadb-keyring.gpg

echo "deb [signed-by=/usr/share/keyrings/mariadb-keyring.gpg] https://dlm.mariadb.com/repo/mariadb-server/11.4/repo/ubuntu jammy main" | sudo tee /etc/apt/sources.list.d/mariadb.list

sudo apt update
sudo apt install -y mariadb-server mariadb-client galera-4 mariadb-backup

# Останавливаем MariaDB до конфигурирования кластера
sudo systemctl stop mariadb

# Проверяем версию
mariadbd --version
# mariadbd  Ver 11.4.2-MariaDB for debian-linux-gnu on x86_64

Также на каждой ноде открыли необходимые порты в файрволе:

# Порты Galera Cluster
sudo ufw allow from 10.0.1.0/24 to any port 3306  # MySQL
sudo ufw allow from 10.0.1.0/24 to any port 4567  # Galera replication
sudo ufw allow from 10.0.1.0/24 to any port 4568  # IST (Incremental State Transfer)
sudo ufw allow from 10.0.1.0/24 to any port 4444  # SST (State Snapshot Transfer)
sudo ufw reload

Ключевой файл конфигурации — одинаковый на всех нодах с единственным отличием в адресе и имени ноды:

# /etc/mysql/mariadb.conf.d/60-galera.cnf (Node 1)
[mysqld]
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=20G
innodb_log_file_size=1G
innodb_file_per_table=1

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="stroyportal_cluster"
wsrep_cluster_address="gcomm://10.0.1.11,10.0.1.12,10.0.1.13"

# Galera Node Configuration
wsrep_node_address="10.0.1.11"
wsrep_node_name="galera-node1"

# Galera Synchronization Configuration
wsrep_sst_method=mariabackup
wsrep_sst_auth=sst_user:S3cureSST_Pass!

# Galera Performance Tuning
wsrep_slave_threads=8
wsrep_certify_nonPK=1
wsrep_max_ws_rows=0
wsrep_max_ws_size=2G
wsrep_retry_autocommit=3

# Connection limits
max_connections=500
wait_timeout=600
interactive_timeout=600

# Query cache (отключён для Galera)
query_cache_size=0
query_cache_type=0

Разберём критически важные параметры:

• binlog_format=ROW — обязательно для Galera, построчная репликация гарантирует консистентность
• innodb_autoinc_lock_mode=2 — чередующийся режим автоинкремента, необходим для multi-master
• innodb_flush_log_at_trx_commit=2 — компромисс между производительностью и надёжностью (Galera сама обеспечивает консистентность через wsrep)
• wsrep_sst_method=mariabackup — неблокирующий State Snapshot Transfer при добавлении ноды
• wsrep_slave_threads=8 — параллельное применение транзакций с других нод (по числу ядер)

Инициализация кластера начинается строго с первой ноды — она становится «донором» для остальных:

# === Node 1 (10.0.1.11): инициализация кластера ===
sudo galera_new_cluster

# Проверяем статус кластера
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

# Создаём пользователя для SST
mysql -u root -p -e "
  CREATE USER 'sst_user'@'localhost' IDENTIFIED BY 'S3cureSST_Pass!';
  GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sst_user'@'localhost';
  FLUSH PRIVILEGES;
"

# === Node 2 (10.0.1.12): присоединение к кластеру ===
sudo systemctl start mariadb
# Node 2 автоматически запросит SST от Node 1
# В логе: WSREP: SST succeeded

# === Node 3 (10.0.1.13): присоединение к кластеру ===
sudo systemctl start mariadb

# Проверяем с любой ноды
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_%';" | grep -E 'cluster_size|cluster_status|connected|ready|state_comment'
+----------------------------+----------------+
| wsrep_cluster_size         | 3              |
| wsrep_cluster_status       | Primary        |
| wsrep_connected            | ON             |
| wsrep_ready                | ON             |
| wsrep_local_state_comment  | Synced         |
+----------------------------+----------------+

Статус Primary означает, что кластер имеет кворум. Все три ноды в состоянии Synced — готовы принимать запросы.

HAProxy установили на двух отдельных серверах для отказоустойчивости, с Keepalived для общего виртуального IP:

# Установка HAProxy
sudo apt install -y haproxy keepalived

# /etc/haproxy/haproxy.cfg
global
    log /dev/log local0
    log /dev/log local1 notice
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin
    stats timeout 30s
    user haproxy
    group haproxy
    daemon
    maxconn 4096

defaults
    log     global
    mode    tcp
    option  tcplog
    option  dontlognull
    timeout connect 5000ms
    timeout client  50000ms
    timeout server  50000ms
    retries 3

# Статистика HAProxy
listen stats
    bind *:8404
    mode http
    stats enable
    stats uri /haproxy-stats
    stats auth admin:H4pr0xy_Stats!
    stats refresh 10s

# Backend для ЗАПИСИ — только одна нода (writer)
frontend mysql_write_front
    bind *:3306
    default_backend mysql_write_back

backend mysql_write_back
    balance roundrobin
    option mysql-check user haproxy_check
    server galera1 10.0.1.11:3306 check weight 1
    server galera2 10.0.1.12:3306 check weight 1 backup
    server galera3 10.0.1.13:3306 check weight 1 backup

# Backend для ЧТЕНИЯ — все три ноды
frontend mysql_read_front
    bind *:3307
    default_backend mysql_read_back

backend mysql_read_back
    balance leastconn
    option mysql-check user haproxy_check
    server galera1 10.0.1.11:3306 check
    server galera2 10.0.1.12:3306 check
    server galera3 10.0.1.13:3306 check

Ключевой момент — разделение портов: 3306 для записи (один активный сервер, остальные — backup), 3307 для чтения (все три ноды с балансировкой по наименьшему числу подключений).

Чтобы HAProxy сам не стал точкой отказа, мы настроили два экземпляра с общим виртуальным IP через VRRP:

# /etc/keepalived/keepalived.conf (HAProxy Server 1 — MASTER)
vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 2
}

vrrp_instance VI_MYSQL {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 101
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass Kpalived_Secret
    }
    virtual_ipaddress {
        10.0.1.100/24
    }
    track_script {
        chk_haproxy
    }
}

# На втором HAProxy-сервере — state BACKUP, priority 100

Пользователь для проверки здоровья нод со стороны HAProxy:

# Выполняем на любой ноде Galera (реплицируется автоматически)
mysql -u root -p -e "
  CREATE USER 'haproxy_check'@'10.0.1.%' IDENTIFIED BY '';
  FLUSH PRIVILEGES;
"

Теперь приложение подключается к виртуальному IP 10.0.1.100: порт 3306 для записи, 3307 для чтения. При падении любой ноды Galera или любого HAProxy — сервис остаётся доступным.

В Laravel-приложении СтройПортала настроили два подключения к БД:

// config/database.php (Laravel)
'mysql' => [
    'read' => [
        'host' => '10.0.1.100',
        'port' => 3307,  // HAProxy read backend
    ],
    'write' => [
        'host' => '10.0.1.100',
        'port' => 3306,  // HAProxy write backend
    ],
    'sticky' => true,  // После записи читаем с writer (избегаем race condition)
    'driver' => 'mysql',
    'database' => 'stroyportal',
    'username' => 'app_user',
    'password' => env('DB_PASSWORD'),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => 'InnoDB',
],

Параметр sticky => true критически важен: после любой операции записи все последующие чтения в рамках этого HTTP-запроса идут на writer-ноду, исключая чтение устаревших данных.

Для минимизации простоя использовали mariabackup вместо mysqldump — он не блокирует таблицы и работает значительно быстрее:

# 1. Снимаем полный бэкап с продакшен MySQL
mariabackup --backup --target-dir=/backup/full \
  --host=old-mysql.stroyportal.local \
  --user=backup_user --password='BackupPass123'

# 2. Подготавливаем бэкап (apply log)
mariabackup --prepare --target-dir=/backup/full

# 3. Останавливаем MariaDB на Node 1
sudo systemctl stop mariadb

# 4. Очищаем datadir и копируем данные
sudo rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir=/backup/full
sudo chown -R mysql:mysql /var/lib/mysql

# 5. Запускаем кластер с импортированными данными
sudo galera_new_cluster

# 6. Проверяем данные
mysql -u root -p -e "SELECT COUNT(*) FROM stroyportal.products;"
+----------+
| COUNT(*) |
+----------+
| 12483291 |
+----------+

# 7. Присоединяем Node 2 и Node 3 — они автоматически получат данные через SST

Переключение выполнялось в ночное окно обслуживания (воскресенье, 3:00):

1. Перевод сайта в режим обслуживания (php artisan down)
2. Финальная синхронизация данных (инкрементальный бэкап за последние часы)
3. Обновление конфигурации приложения на новые подключения
4. Проверка чтения и записи на каждой ноде
5. Снятие режима обслуживания

Общий простой составил 12 минут — меньше, чем средний инцидент с падением MySQL в предыдущем режиме.

# Проверка репликации — создаём запись на Node 1, читаем на Node 3
# Node 1:
mysql -u root -p -e "INSERT INTO stroyportal.migration_test VALUES (1, NOW(), 'test');"

# Node 3 (мгновенно):
mysql -u root -p -e "SELECT * FROM stroyportal.migration_test;"
+----+---------------------+---------+
| id | created_at          | payload |
+----+---------------------+---------+
|  1 | 2026-02-16 03:07:45 | test    |
+----+---------------------+---------+

Galera использует алгоритм виртуальной синхронности с компонентом кворума (PC — Primary Component). При 3 нодах:

• 3 из 3 работают — полный кворум, все операции доступны
• 2 из 3 работают — кворум сохранён, кластер продолжает работу
• 1 из 3 работает — кворум потерян, нода переходит в non-Primary и отклоняет запросы

Дополнительно мы настроили параметры защиты:

# Дополнение к /etc/mysql/mariadb.conf.d/60-galera.cnf

# Защита от split-brain
wsrep_provider_options="pc.weight=1; evs.suspect_timeout=PT10S; evs.inactive_timeout=PT30S; evs.keepalive_period=PT3S; gmcast.peer_timeout=PT10S; pc.recovery=TRUE"

# Автоматическое восстановление после сбоя
wsrep_auto_increment_control=ON
wsrep_cluster_address="gcomm://10.0.1.11,10.0.1.12,10.0.1.13"

Параметр pc.recovery=TRUE критически важен: при перезапуске ноды она считывает сохранённое состояние Primary Component из файла gvwstate.dat и автоматически присоединяется к кластеру.

Стандартная проверка mysql-check в HAProxy проверяет только доступность MySQL, но не статус кластера. Мы написали расширенный скрипт для xinetd:

#!/bin/bash
# /usr/local/bin/galera_healthcheck.sh
# Проверка здоровья ноды Galera для HAProxy (HTTP check)

MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="haproxy_check"
ERR_FILE="/dev/null"

SYNCED=$(mysql -h ${MYSQL_HOST} -P ${MYSQL_PORT} -u ${MYSQL_USER} \
  --connect-timeout=5 -N -e "SHOW STATUS LIKE 'wsrep_local_state';" 2>${ERR_FILE} \
  | awk '{print $2}')

READY=$(mysql -h ${MYSQL_HOST} -P ${MYSQL_PORT} -u ${MYSQL_USER} \
  --connect-timeout=5 -N -e "SHOW STATUS LIKE 'wsrep_ready';" 2>${ERR_FILE} \
  | awk '{print $2}')

CLUSTER_STATUS=$(mysql -h ${MYSQL_HOST} -P ${MYSQL_PORT} -u ${MYSQL_USER} \
  --connect-timeout=5 -N -e "SHOW STATUS LIKE 'wsrep_cluster_status';" 2>${ERR_FILE} \
  | awk '{print $2}')

if [ "${SYNCED}" = "4" ] && [ "${READY}" = "ON" ] && [ "${CLUSTER_STATUS}" = "Primary" ]; then
    echo -e "HTTP/1.1 200 OK\r\nContent-Type: text/plain\r\n\r\nGalera node synced."
    exit 0
else
    echo -e "HTTP/1.1 503 Service Unavailable\r\nContent-Type: text/plain\r\n\r\nGalera node not ready."
    exit 1
fi

Скрипт проверяет три условия: wsrep_local_state = 4 (Synced), wsrep_ready = ON и wsrep_cluster_status = Primary. Только при выполнении всех трёх HAProxy считает ноду здоровой.

Мы настроили инкрементальное резервное копирование с одной из read-нод, чтобы не нагружать writer:

#!/bin/bash
# /opt/scripts/galera_backup.sh
# Запуск: ежедневно в 2:00 с Node 3 (read-нода)

BACKUP_DIR="/backup/mariadb"
FULL_DIR="${BACKUP_DIR}/full"
INC_DIR="${BACKUP_DIR}/incremental"
LOG_FILE="/var/log/galera_backup.log"
DAY_OF_WEEK=$(date +%u)  # 1=Пн, 7=Вс
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=14

exec >> "${LOG_FILE}" 2>&1
echo "=== Backup started: $(date) ==="

if [ "${DAY_OF_WEEK}" -eq 7 ]; then
    # Воскресенье — полный бэкап
    echo "Running full backup..."
    rm -rf "${FULL_DIR}"
    mariabackup --backup --target-dir="${FULL_DIR}" \
        --user=backup_user --password='BackupPass123' \
        --galera-info
    echo "Full backup completed: $(du -sh ${FULL_DIR} | awk '{print $1}')"
else
    # Будни — инкрементальный бэкап
    echo "Running incremental backup..."
    mkdir -p "${INC_DIR}/${DATE}"
    mariabackup --backup --target-dir="${INC_DIR}/${DATE}" \
        --incremental-basedir="${FULL_DIR}" \
        --user=backup_user --password='BackupPass123' \
        --galera-info
    echo "Incremental backup completed: $(du -sh ${INC_DIR}/${DATE} | awk '{print $1}')"
fi

# Удаление старых инкрементальных бэкапов
find "${INC_DIR}" -maxdepth 1 -mtime +${RETENTION_DAYS} -exec rm -rf {} \;

echo "=== Backup finished: $(date) ==="

Флаг --galera-info сохраняет позицию wsrep, что позволяет при необходимости восстановить ноду точно в состояние кластера на момент бэкапа.

Для мониторинга кластера мы развернули стек Prometheus + Grafana с mysqld_exporter на каждой ноде:

# Установка mysqld_exporter на каждой ноде
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.1.linux-amd64.tar.gz
sudo mv mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter /usr/local/bin/

# Пользователь для мониторинга
mysql -u root -p -e "
  CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'Exporter_Pass!';
  GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
  FLUSH PRIVILEGES;
"

# /etc/default/mysqld_exporter
DATA_SOURCE_NAME='exporter:Exporter_Pass!@(localhost:3306)/'

# systemd unit
# /etc/systemd/system/mysqld_exporter.service
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
Type=simple
EnvironmentFile=/etc/default/mysqld_exporter
ExecStart=/usr/local/bin/mysqld_exporter \
  --collect.galera \
  --collect.global_status \
  --collect.info_schema.tables \
  --collect.slave_status \
  --web.listen-address=":9104"
Restart=always

[Install]
WantedBy=multi-user.target

Ключевые метрики, которые мы вынесли на дашборд Grafana:

• mysql_global_status_wsrep_cluster_size — количество нод в кластере (алерт при < 3)
• mysql_global_status_wsrep_local_recv_queue_avg — средняя очередь применения (алерт при > 0.5)
• mysql_global_status_wsrep_local_send_queue_avg — средняя очередь отправки
• mysql_global_status_wsrep_flow_control_paused — доля времени в flow control (алерт при > 0.1)
• mysql_global_status_wsrep_cert_deps_distance — параллелизм применения транзакций
• mysql_global_status_wsrep_local_bf_aborts — конфликты сертификации (откаты)

Настроили алерты в Alertmanager с отправкой в Telegram-канал команды эксплуатации. Время реакции на инцидент сократилось с часов до минут.

На основе этого проекта мы сформировали набор рекомендаций:

• Всегда нечётное количество нод — 3 или 5, никогда 2 (невозможно определить кворум)
• NVMe SSD обязательны — Galera чувствительна к задержкам диска из-за синхронной репликации
• Разделяйте write и read — не полагайтесь на multi-master для записи на все ноды одновременно
• Тестируйте failover до продакшена — убедитесь, что приложение корректно обрабатывает переключение
• Мониторьте flow control — если нода не успевает применять транзакции, она тормозит весь кластер
• Не забывайте про бэкапы — Galera защищает от падения нод, но не от ошибок оператора

Текущая архитектура позволяет горизонтально масштабировать чтение: добавление 4-й и 5-й ноды — вопрос нескольких часов. Для дальнейшего роста мы спроектировали roadmap:

• Этап 2 — шардирование крупнейших таблиц (products, search_index) через ProxySQL
• Этап 3 — географически распределённый кластер с нодами в московском и петербургском дата-центрах
• Этап 4 — переход на MariaDB Xpand для нативного шардирования

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