Настройка репликации Master-Slave для базы данных веб-приложения

Настройка репликации Master-Slave для базы данных веб-приложения

Репликация Master-Slave (Primary-Replica в современной терминологии) — асинхронная или синхронная доставка данных с основного сервера на один или несколько реплик. Используется для масштабирования чтения, снижения нагрузки на мастер и создания hot-standby для быстрого failover.

Когда нужна Master-Slave репликация

• Запросы на чтение составляют более 60–70% нагрузки
• Необходим hot-standby для быстрого восстановления при сбое мастера
• Аналитические запросы нагружают production-базу
• Бэкапы должны сниматься без нагрузки на мастер

PostgreSQL: Streaming Replication

Настройка на мастере

# postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
synchronous_commit = on  # для синхронной репликации
# synchronous_standby_names = 'replica1'  # если нужна синхронная

Создание пользователя репликации:

CREATE USER replication_user REPLICATION LOGIN ENCRYPTED PASSWORD 'strong_password';

Разрешение подключения реплики в pg_hba.conf:

host replication replication_user 192.168.1.0/24 md5

Инициализация реплики через pg_basebackup

# На сервере реплики
pg_basebackup -h master-db-host -U replication_user \
  -D /var/lib/postgresql/14/main \
  -P -Xs -R

# -R создаёт файл standby.signal и postgresql.auto.conf с параметрами

Содержимое автоматически созданного postgresql.auto.conf на реплике:

primary_conninfo = 'host=master-db-host port=5432 user=replication_user password=strong_password'

Запуск реплики:

systemctl start postgresql
# Реплика автоматически входит в режим hot-standby

Проверка репликации

На мастере:

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

На реплике:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
SELECT pg_is_in_recovery(); -- должно вернуть true

MySQL/MariaDB репликация

Конфигурация мастера

# /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

Создание пользователя репликации:

CREATE USER 'replication'@'192.168.1.%'
  IDENTIFIED WITH mysql_native_password BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%';
FLUSH PRIVILEGES;

Получение позиции для инициализации реплики:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Записать: File и Position
UNLOCK TABLES;

Конфигурация реплики

# /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
read_only = ON
super_read_only = ON  # запрет записи даже для SUPER

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

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='replication',
  MASTER_PASSWORD='strong_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4;

START SLAVE;
SHOW SLAVE STATUS\G

GTID-репликация (предпочтительно для MySQL 5.7+):

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='replication',
  MASTER_PASSWORD='strong_password',
  MASTER_AUTO_POSITION=1;

Маршрутизация запросов в приложении

PostgreSQL: pgBouncer + read/write split

# pgbouncer.ini
[databases]
myapp_write = host=master-db port=5432 dbname=myapp
myapp_read  = host=replica-db port=5432 dbname=myapp

В приложении использовать разные DSN для чтения и записи:

// Laravel: config/database.php
'pgsql' => [
    'read' => ['host' => 'replica-db'],
    'write' => ['host' => 'master-db'],
    ...
]

MySQL: ProxySQL

-- ProxySQL: добавление серверов
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, 'master-db', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'replica-db', 3306);

-- Правила маршрутизации
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT', 20),  -- SELECT → реплики
       (2, 1, '.*', 10);       -- остальное → мастер

Мониторинг лага репликации

Критическая метрика — replication lag. При лаге более 30 секунд реплика не должна обслуживать читающие запросы, требующие свежих данных.

Метрика в Prometheus через postgres_exporter:

# Алерт при лаге более 60 секунд
- alert: PostgresReplicationLag
  expr: pg_replication_lag_seconds > 60
  annotations:
    summary: "Replication lag {{ $value }}s on {{ $labels.instance }}"

Срок выполнения

Настройка PostgreSQL streaming replication с одной репликой — 1 день. С ProxySQL/pgBouncer и read/write split в приложении — 2–3 дня.