Master-master database replication for web application

Setting Up Master-Master Replication for Web Application Databases

Master-Master (Multi-Primary) replication — scheme where writes are possible on multiple nodes simultaneously. More complex than Master-Slave, solves geographically distributed write tasks and high availability with immediate failover.

When Master-Master is justified

Most web applications work fine on Master-Slave. Master-Master is needed when:

• Applications in different regions must write to local DB with subsequent sync
• Zero switch time is required if one master fails
• Write cannot be directed through single point without unacceptable delays

MySQL: Galera Cluster

Galera — synchronous multi-primary replication for MySQL/MariaDB. All nodes accept writes, transactions apply on all nodes before commit.

# /etc/mysql/conf.d/galera.cnf (on each node)
[mysqld]
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
bind-address = 0.0.0.0

# Galera Provider
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = "production_cluster"
wsrep_cluster_address = "gcomm://192.168.1.10,192.168.1.11,192.168.1.12"
wsrep_sst_method = rsync

# Unique for each node
wsrep_node_address = "192.168.1.10"
wsrep_node_name = "node1"

Initialize cluster on first node:

galera_new_cluster
# On other nodes — normal start
systemctl start mysql

Check status:

SHOW STATUS LIKE 'wsrep_%';
-- wsrep_cluster_size should show node count
-- wsrep_ready should be ON

PostgreSQL: BDR (Bi-Directional Replication)

BDR — extension from pgEdge, supports async multi-master for PostgreSQL:

-- Connect extension
CREATE EXTENSION bdr;

-- Initialize first node
SELECT bdr.bdr_group_create(
  local_node_name := 'node1',
  node_external_dsn := 'host=192.168.1.10 port=5432 dbname=myapp'
);

-- Join second node
SELECT bdr.bdr_group_join(
  local_node_name := 'node2',
  node_external_dsn := 'host=192.168.1.11 port=5432 dbname=myapp',
  join_using_dsn := 'host=192.168.1.10 port=5432 dbname=myapp'
);

Alternative — Patroni + Postgres in synchronous replication mode. This is not true multi-master, but provides automatic failover with replica promotion to master in seconds.

Resolving write conflicts

Main difficulty of Master-Master — conflicts when two nodes simultaneously change same record.

Resolution strategies:

For most cases, better to prevent conflicts: each region writes own data (different tables or row-level partitioning by region_id).

Nginx/HAProxy for write balancing

upstream mysql_masters {
    server 192.168.1.10:3306 weight=1;
    server 192.168.1.11:3306 weight=1;
}

For Galera: all three nodes accept writes, ProxySQL handles distribution:

INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)
VALUES (10, '192.168.1.10', 3306, 1),
       (10, '192.168.1.11', 3306, 1),
       (10, '192.168.1.12', 3306, 1);

Monitor Galera

-- Queue of incoming transactions to apply (should be small)
SHOW STATUS LIKE 'wsrep_local_recv_queue_avg';

-- Certification conflicts (should be near zero)
SHOW STATUS LIKE 'wsrep_local_cert_failures';

-- Node status
SHOW STATUS LIKE 'wsrep_cluster_size';

Limitations

• Galera doesn't support MyISAM and MEMORY tables
• AUTO_INCREMENT requires innodb_autoinc_lock_mode=2 + wsrep_auto_increment_control=ON
• DDL operations (ALTER TABLE) block cluster — use pt-online-schema-change or gh-ost
• Latency between nodes directly affects write performance in Galera

Timeline

Setup of Galera Cluster on three nodes with ProxySQL — 3–4 business days. Including load testing and monitoring setup.