Как настроить репликацию базы данных для высокой доступности

Постановка задачи

Необходимо обеспечить, чтобы ваша база данных могла выдерживать сбои, обеспечивать масштабирование чтения и сохранять долговечность данных на нескольких серверах или в нескольких регионах.

Когда использовать каждый шаблон

Шаблон	Сценарий использования	Компромиссы
Primary-Replica	Масштабирование чтения, простой failover	Задержка репликации, ручной failover
Multi-Primary	Масштабирование записи, географическое распределение	Сложность разрешения конфликтов
Synchronous	Нулевые потери данных	Более медленные записи, требуется низкая задержка
Asynchronous	Более высокая производительность	Потенциальная потеря данных при сбое

Репликация MySQL/Percona

Шаг 1: Настройте primary-сервер

Отредактируйте /etc/my.cnf:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_do_db = myapp_production
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# Репликация на основе GTID (рекомендуется)
gtid_mode = ON
enforce_gtid_consistency = ON

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

CREATE USER 'replicator'@'replica-host' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'replica-host';
FLUSH PRIVILEGES;

Шаг 2: Настройте replica-сервер

Отредактируйте /etc/my.cnf:

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
read_only = ON
# Репликация на основе GTID
gtid_mode = ON
enforce_gtid_consistency = ON

Шаг 3: Инициализируйте репликацию

На replica:

CHANGE MASTER TO
MASTER_HOST = 'primary-host',
MASTER_USER = 'replicator',
MASTER_PASSWORD = 'secure_password',
MASTER_AUTO_POSITION = 1;
START SLAVE;

Шаг 4: Проверьте статус репликации

SHOW SLAVE STATUS\G

Проверьте:

• SlaveIORunning: Yes
• SlaveSQLRunning: Yes
• SecondsBehindMaster: 0 (или близко к 0)

Semi-Synchronous репликация

Для более строгих гарантий долговечности:

На primary:

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- 1 секунда

На replica:

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

Streaming-репликация PostgreSQL

Шаг 1: Настройте primary-сервер

Отредактируйте postgresql.conf:

wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
synchronous_commit = on
synchronous_standby_names = 'standby1' # Для синхронной репликации

Отредактируйте pg_hba.conf:

host replication replicator replica-ip/32 scram-sha-256

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

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

Шаг 2: Создайте базовую резервную копию

На replica:

pg_basebackup -h primary-host -U replicator -D /var/lib/pgsql/data -Fp -Xs -P -R

Флаг -R создаёт standby.signal и настраивает postgresql.auto.conf.

Шаг 3: Настройте replica

Отредактируйте postgresql.conf (при необходимости):

hot_standby = on
primary_conninfo = 'host=primary-host user=replicator password=secure_password'

Шаг 4: Запустите replica и выполните проверку

systemctl start postgresql
# На primary проверьте подключённые replicas
SELECT * FROM pg_stat_replication;
# На replica проверьте статус восстановления
SELECT pg_is_in_recovery();

Автоматический failover с Patroni

Для production используйте Patroni для автоматического failover:

# patroni.yml
scope: postgres-cluster
namespace: /service/
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: node1:8008
etcd:
hosts: etcd1:2379,etcd2:2379,etcd3:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
wal_level: replica
hot_standby: "on"
max_wal_senders: 5
max_replication_slots: 5
postgresql:
listen: 0.0.0.0:5432
connect_address: node1:5432
data_dir: /var/lib/postgresql/data
authentication:
replication:
username: replicator
password: secure_password
superuser:
username: postgres
password: admin_password

Репликация MySQL в Kubernetes

Использование Percona Operator

apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBCluster
metadata:
name: mysql-cluster
spec:
crVersion: 1.13.0
secretsName: mysql-secrets
pxc:
size: 3
image: percona/percona-xtradb-cluster:8.0
resources:
requests:
memory: 1G
cpu: 600m
volumeSpec:
persistentVolumeClaim:
resources:
requests:
storage: 100Gi
haproxy:
enabled: true
size: 2
image: percona/percona-xtradb-cluster-operator:1.13.0-haproxy
proxysql:
enabled: false

Строка подключения

mysql://user:password@mysql-cluster-haproxy:3306/database

Репликация PostgreSQL в Kubernetes

Использование CloudNativePG Operator

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: postgres-cluster
spec:
instances: 3
postgresql:
parameters:
max_connections: "300"
shared_buffers: "256MB"
storage:
size: 100Gi
storageClass: fast-ssd
backup:
barmanObjectStore:
destinationPath: s3://backups/postgres
s3Credentials:
accessKeyId:
name: s3-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: s3-creds
key: SECRET_ACCESS_KEY

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

Ключевые метрики для мониторинга

1. Задержка репликации

``` sql -- MySQL SHOW SLAVE STATUS\G -- Проверьте SecondsBehindMaster

-- PostgreSQL SELECT clientaddr, state, sentlsn, writelsn, flushlsn, replaylsn, pgwallsndiff(sentlsn, replaylsn) AS lagbytes FROM pgstat_replication; ```

1. Статус подключения

`` sql -- MySQL: проверьте SlaveIORunning и SlaveSQLRunning -- PostgreSQL: проверьте состояние pgstatreplication ``

1. Позиция Binary Log/WAL

Правила оповещений

# Правила оповещений Prometheus
groups:
- name: database-replication
rules:
- alert: ReplicationLagHigh
expr: mysql_slave_seconds_behind_master > 60
for: 5m
labels:
severity: warning
annotations:
summary: "MySQL replication lag is {{ $value }} seconds"
- alert: ReplicationStopped
expr: mysql_slave_sql_running == 0 OR mysql_slave_io_running == 0
for: 1m
labels:
severity: critical
annotations:
summary: "MySQL replication has stopped"

Процедуры failover

Ручной failover (MySQL)

1. Остановите запись на primary

`` sql SET GLOBAL read_only = ON; FLUSH TABLES WITH READ LOCK; ``

1. Убедитесь, что replica догнала primary

`` sql -- На replica SHOW SLAVE STATUS\G -- Дождитесь SecondsBehindMaster = 0 ``

1. Повысьте replica до primary

`` sql STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only = OFF; ``

1. Обновите строки подключения приложения

Ручной failover (PostgreSQL)

1. Повысьте standby

`` bash pgctl promote -D /var/lib/pgsql/data # Или SELECT pgpromote(); ``

1. Перенастройте старый primary как standby (если восстановлен)

Чек-лист лучших практик

• [ ] Используйте репликацию на основе GTID (MySQL) для упрощения failover
• [ ] Настройте мониторинг задержки репликации
• [ ] Регулярно тестируйте процедуры failover
• [ ] Используйте semi-synchronous репликацию для критичных данных
• [ ] Храните binlogs/WAL достаточно долго для восстановления
• [ ] Документируйте и автоматизируйте процедуры failover
• [ ] Используйте пул соединений (PgBouncer, ProxySQL)
• [ ] Настройте read replicas в приложении для масштабирования чтения
• [ ] Делайте резервные копии как primary, так и replicas
• [ ] Мониторьте дисковое пространство для логов и данных

Связанные статьи Wiki

• MySQL/Percona Replication
• PostgreSQL Installation
• DBMS Replication managers
• MySQL Kubernetes
• PostgreSQL Kubernetes