Master-slave database replication for web application

Setting Up Master-Slave Replication for Web Application Databases

Master-Slave (Primary-Replica in modern terminology) replication — asynchronous or synchronous data delivery from main server to one or more replicas. Used for read scaling, reducing master load, and creating hot-standby for quick failover.

When Master-Slave replication is needed

• Read queries constitute more than 60–70% of load
• Hot-standby needed for quick recovery if master fails
• Analytical queries burden production database
• Backups should run without master load

PostgreSQL: Streaming Replication

Master setup

# postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
synchronous_commit = on  # for synchronous replication
# synchronous_standby_names = 'replica1'  # if synchronous needed

Create replication user:

CREATE USER replication_user REPLICATION LOGIN ENCRYPTED PASSWORD 'strong_password';

Allow replica connection in pg_hba.conf:

host replication replication_user 192.168.1.0/24 md5

Initialize replica via pg_basebackup

# On replica server
pg_basebackup -h master-db-host -U replication_user \
  -D /var/lib/postgresql/14/main \
  -P -Xs -R

# -R creates standby.signal and postgresql.auto.conf with parameters

Auto-created postgresql.auto.conf content on replica:

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

Start replica:

systemctl start postgresql
# Replica automatically enters hot-standby mode

Check replication

On master:

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

On replica:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
SELECT pg_is_in_recovery(); -- should return true

MySQL/MariaDB replication

Master configuration

# /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 replication user:

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;

Get position for replica initialization:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Write down: File and Position
UNLOCK TABLES;

Replica configuration

# /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  # forbid writes even for SUPER

Start replication:

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 replication (preferred for MySQL 5.7+):

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

Request routing in application

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

Use different DSN for read and write in application:

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

MySQL: ProxySQL

-- ProxySQL: add servers
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);

-- Routing rules
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT', 20),  -- SELECT → replicas
       (2, 1, '.*', 10);       -- rest → master

Monitor replication lag

Critical metric — replication lag. If lag exceeds 30 seconds, replica shouldn't serve queries requiring fresh data.

Prometheus metric via postgres_exporter:

# Alert if lag exceeds 60 seconds
- alert: PostgresReplicationLag
  expr: pg_replication_lag_seconds > 60
  annotations:
    summary: "Replication lag {{ $value }}s on {{ $labels.instance }}"

Timeline

Setup of PostgreSQL streaming replication with one replica — 1 day. With ProxySQL/pgBouncer and read/write split in application — 2–3 days.