Migrating from MySQL Dual-Master to Master‑Slave: Lessons Learned and Simple Conversion Steps

1. Pitfalls Encountered

One month ago we deployed a MySQL high‑availability architecture in a test environment, using a dual‑master + Keepalived mode. The deployment revealed many problems.

Both MySQL nodes can write, which easily leads to primary‑key duplication and replication failure.

When replication fails, the Slave_SQL_Thread stops; it resumes only after the error is resolved.

Replication errors often affect a large batch of statements, not just a single row.

The two nodes lack each other’s data, causing data gaps.

Replication lag means that after a failover the new master may not have the latest data.

When inconsistencies appear, it is unclear which node should be considered authoritative.

The root cause of these issues is that both nodes accept writes and can be switched at any time.

Possible solutions include adjusting the auto‑increment step (impact not evaluated) or using GTID (not verified), but the inherent risk of dual‑master sync remains, and handling desynchronization is a major challenge.

Why did we initially choose dual‑master?

The original goal was high availability: if one MySQL node fails, the other can take over, providing a seamless experience for users and giving operators time to troubleshoot. When the old master recovers, it can immediately become a slave without reconfiguration.

After a month of trial, we decided to switch to a classic master‑slave architecture.

2. Downgrading Dual‑Master to Master‑Slave

Dual‑Master Mode

Both master nodes run Keepalived and expose a virtual IP (VIP). Only one node holds the VIP at any time; the other stays on standby.

Master‑Slave Mode

In this mode the slave is read‑only.

Characteristics of a one‑master‑one‑slave setup:

One master node handles client traffic; the slave synchronizes via the master’s binlog.

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

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

Key differences from the dual‑master solution:

Switching to a new master requires a script to make the former slave writable.

After the switch, the old master must be configured not to replicate from the new master.

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

These additional manual steps make the master‑slave mode more operationally involved during failures.

In practice, failover is handled by a monitoring script that detects master downtime; if detected, the slave is promoted, and Keepalived on the new master takes over the VIP.

There are two ways to perform the conversion:

Simple method: manual switch; requires human intervention when the master fails.

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

This article covers only the simple, manual approach; the complex method will be discussed in a future post.

3. Simple Conversion Procedure

The manual master‑slave switch flow is illustrated below:

Key configuration steps:

1. Stop Keepalived on the slave node to prevent it from automatically taking over the VIP. If the master fails, manual intervention will be required to promote the slave and then restart Keepalived.

systemctl status keepalived

2. Keep Keepalived running on the master node so that MySQL connection information remains unchanged.

3. Stop the replication thread on the master (node1) .

STOP SLAVE

4. Set the slave (node2) to read‑only mode.

# modify my.cnf file
read_only = 1

5. Revoke the master’s permission to replicate to the slave.

6. Remove Keepalived from the slave’s startup scripts.

# edit rc.local
sudo vim /etc/rc.local
# remove the line
systemctl start keepalived

4. Conclusion

The dual‑master high‑availability setup introduced many pitfalls; without deep MySQL expertise it is hard to manage. After a month of hands‑on experience, the author chose a simpler one‑master‑one‑slave configuration, albeit without automatic failover.

Because the original configuration was built for dual‑master, additional adjustments are required to switch to master‑slave. The non‑high‑availability manual mode is used for now, while a fully automated solution will be covered in the next article.