Imagine your online store operates in Europe and Asia. Writing to the database from Asia via Europe takes over 100 ms — critical for catalog and cart performance. We faced this with an online retailer having 500k products and solved it by implementing Master-Master replication. Now writes are local in each region, and data syncs between nodes without loss. With over 5 years of replication tuning and 30+ high-complexity projects, we deliver robust solutions.
Why Master-Master Over Master-Slave?
Master-Slave is the classic setup: one node writes, others read. But when the application runs in a distributed environment, write latency becomes a bottleneck. Master-Master lifts this limitation: every node can accept writes. According to Galera documentation, synchronous multi-master replication ensures strong consistency and zero failover time. Galera Cluster switches in <1 sec — 10x faster than manual Master-Slave failover.
When Is Master-Master Needed?
- Applications in different regions must write locally with synchronization.
- Fault tolerance without a single point of failure is required.
- Write latency through a single master exceeds the tolerable 50 ms.
- ROI of 2–3 months at loads over 10k requests per second.
How to Set Up a Galera Cluster on Three Nodes
- Install Galera on all nodes (e.g., Ubuntu 22.04).
- Configure
/etc/mysql/conf.d/galera.cnfas shown below. - Initialize the first node with
galera_new_cluster. - Start MySQL on the other nodes — they join automatically.
- Check status:
SHOW STATUS LIKE 'wsrep_cluster_size';— should return 3.
# /etc/mysql/conf.d/galera.cnf [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" SST configuration details
For SST you can use rsync or xtrabackup. In production we recommend xtrabackup — it does not block tables during full synchronization.How to Avoid Data Loss During Replication?
Data loss in multi-master can occur if a node fails before synchronization. To minimize risks, use synchronous replication (Galera) with automatic recovery after failure. In BDR, set up replication slots with a delay no longer than 1 second. Regular backups with xtrabackup or pg_dump reduce losses to under 1 minute in case of complete cluster failure.
Setting Up PostgreSQL BDR
BDR (Bi-Directional Replication) is an extension for asynchronous multi-master replication in PostgreSQL. It suits tasks where eventual consistency is acceptable (sync delay up to 1–2 seconds).
-- Enable the extension CREATE EXTENSION bdr; -- Initialize the first node SELECT bdr.bdr_group_create( local_node_name := 'node1', node_external_dsn := 'host=192.168.1.10 port=5432 dbname=myapp' ); -- Join the 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' ); Comparison: Galera vs BDR
| Parameter | Galera Cluster | PostgreSQL BDR |
|---|---|---|
| Replication type | Synchronous | Asynchronous |
| Consistency | Strong | Eventual |
| Write latency | High (network dependent) | Low |
| DDL support | Locks the cluster | Non-blocking |
| License | GPL | PostgreSQL license |
Resolving Write Conflicts
Conflicts arise when two nodes modify the same record simultaneously. In the retailer project, we applied regional partitioning — each table handles its own geographic segment. This eliminated overlaps and reduced conflicts by 95%.
| Strategy | Principle | Application |
|---|---|---|
| Last Write Wins | Latest timestamp wins | Non-critical data, IoT |
| Origin wins | Source node wins | Regional data |
| Custom resolver | Business logic merge | Complex aggregates |
| Application-level | Deterministic keys | Requires architectural effort |
Load Balancing and Monitoring
We use ProxySQL for even request distribution. Simple configuration:
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); Monitoring is done via Prometheus + Grafana. Key metrics: wsrep_local_recv_queue_avg (transaction apply queue should be <1) and wsrep_local_cert_failures (certification conflicts near zero).
Limitations and Pitfalls
- Galera does not support MyISAM or MEMORY tables.
- AUTO_INCREMENT requires
innodb_autoinc_lock_mode=2. - DDL locks the cluster — use
pt-online-schema-change. - Inter-node latency >5 ms reduces write performance by 30–40%. For low-latency networks, use dedicated links.
Comprehensive Turnkey Setup
We offer a full cycle: load analysis, solution selection, node deployment, replication and load balancer configuration, stress testing, monitoring setup, documentation, and team training. Warranty support: 1 month.
Timeline: 3–4 working days for a three-node cluster. Pricing is determined individually based on complexity and node count.
Order a replication audit — we will identify bottlenecks and propose optimizations. Contact us for a consultation on your architecture.
Further reading: Galera Cluster.