Why We Dropped MySQL Dual‑Master for Simpler Master‑Slave: Lessons & Step‑by‑Step Guide

1. Problems Encountered

One month ago we deployed a MySQL high‑availability architecture using dual‑master plus Keepalived. During the trial we ran into many issues:

Both MySQL nodes could write, leading to primary‑key collisions and replication failures.

When replication failed, the Slave_SQL_Thread stopped and could only resume after fixing the error.

Replication errors often affected large batches of statements, not just a single record.

The two nodes lacked each other's data.

Replication lag meant that after a failover the new master did not contain the latest data.

When inconsistencies appeared, it was unclear which node held the correct data.

The root cause is that both nodes accept writes and can be switched at any time, which makes synchronization fragile.

Possible mitigations such as adjusting auto‑increment step size or using GTID were considered but not fully validated, and the inherent risk of dual‑master sync remains.

Why did we initially choose dual‑master?

The original goal was high availability: if one MySQL node fails, the other takes over transparently, giving operators time to troubleshoot. After a month of experience, we decided to switch to a classic master‑slave setup.

In dual‑master mode each node acts as both master and slave; moving to a single master and a read‑only slave is effectively a downgrade, and the following sections describe how we performed the conversion.

2. Switching Dual‑Master to Master‑Slave

Dual‑Master Mode

The architecture is illustrated below:

Both master nodes run Keepalived, exposing a single virtual IP (VIP). Only one node holds the VIP at a time, while the other stays on standby.

Master‑Slave Mode

In contrast, the slave node is read‑only.

Key characteristics of a one‑master‑one‑slave setup:

One master node handles client writes; the slave replicates via the master’s binlog.

The slave is read‑only and can serve reporting or other read‑heavy queries.

If the master fails, the slave can be promoted to master, providing another form of high availability.

Differences compared with dual‑master:

Switching requires a script to change the slave to read‑write.

After the switch, the former master must be configured to stop replicating from the old master.

When the old master recovers, it must be set to read‑only and start replicating from the new master.

Thus, master‑slave mode introduces some manual steps during failures.

Typical failover process: a monitoring script detects master failure, promotes the slave to master, and the recovered old master becomes a read‑only replica of the new master, while Keepalived on the new master takes over the VIP.

There are two ways to adopt master‑slave mode:

Simple method: manual failover; operators switch roles when the master crashes.

Complex method: automated high‑availability failover with read/write splitting.

This article covers only the simple manual method; the automated approach will be explained in a future post.

3. Simple Method to Convert to Master‑Slave

The manual conversion workflow is shown below:

Key differences from dual‑master failover: the slave is read‑only, Keepalived is not started automatically, and manual intervention is required for role changes.

Configuration steps:

① Stop Keepalived on the slave node to prevent it from automatically taking over the VIP. If the master fails, manual intervention will be needed. After the slave is promoted, restart Keepalived on it. systemctl status keepalived ② Keep Keepalived running on the master node so that MySQL connection details remain unchanged.

③ On the master node (node1), stop the MySQL replication thread. STOP SLAVE ④ Set the slave node (node2) to read‑only mode.

# modify my.cnf file
read_only = 1

⑤ Remove the replication privileges that allow node1 to replicate to node2.

⑥ Remove Keepalived from the boot start‑up scripts on node1.

# modify startup config
sudo vim /etc/rc.local
# remove the following line
systemctl start keepalived

4. Summary

The dual‑master high‑availability setup introduced many pitfalls that are hard to resolve without deep MySQL expertise. After a month of hands‑on experience, we concluded that a single master with a read‑only slave is simpler and more reliable for our needs.

Because the original configuration was built for dual‑master, several settings had to be adjusted to achieve the master‑slave mode, and we opted for a non‑HA implementation due to project time constraints.

Future posts will cover the automated high‑availability master‑slave architecture and its detailed configuration steps.