Налаштування реплікації 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 дні.