Migrating MySQL Dual-Master High Availability to Master‑Slave Architecture: Lessons Learned and Simple Conversion Steps

1. Pitfalls Encountered

One month ago we deployed a MySQL high‑availability architecture in the test environment, using a dual‑master + Keepalived mode. During the trial we ran into 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 can only resume after the error is resolved.

Replication errors usually affect a large batch of statements, not just a single record.

The two nodes may lack each other’s data.

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

When data becomes inconsistent, it is unclear which node should be considered authoritative.

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

Possible solutions such as adjusting auto‑increment step size or using GTID were considered, but the inherent risk of dual‑master synchronization remains.

Why did we choose dual‑master in the first place?

The original goal was high availability: if one MySQL node fails, the other can take over transparently, giving operators time to investigate. After a month of trial, we decided to switch to a traditional master‑slave setup.

2. From Dual‑Master to Master‑Slave

Dual‑Master Mode

The dual‑master architecture provides a virtual IP (VIP) via Keepalived; only one node holds the VIP at a time while the other stays on standby.

Master‑Slave Mode

In master‑slave mode the slave is read‑only and synchronizes data from the master via binlog.

Key differences:

Only one node writes; the other reads.

The slave can serve read‑only queries such as reporting.

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

Compared with dual‑master, master‑slave requires manual or scripted steps to switch roles and to stop syncing from the old master.

3. Simple Manual Conversion Process

The following steps describe a straightforward, non‑automatic conversion from dual‑master to master‑slave:

Stop Keepalived on the node that will become the slave to prevent it from automatically taking over the VIP. systemctl status keepalived

Keep Keepalived running on the node that will remain the master so client connection information does not change.

On the master (node1) stop the MySQL replication thread: STOP SLAVE

On the slave (node2) set MySQL to read‑only mode by editing my.cnf : # modify my.cnf file read_only = 1

Remove the replication privileges of node1 on node2.

Remove Keepalived from the startup scripts of the former slave (node1) so it does not automatically acquire the VIP: # edit startup configuration sudo vim /etc/rc.local # remove the line systemctl start keepalived

After these changes the environment operates as a simple master‑slave pair without automatic failover.

4. Conclusion

The dual‑master high‑availability setup introduced many complexities and pitfalls that are hard to resolve without deep MySQL expertise. After a month of hands‑on experience, the author chose a single‑master, single‑slave architecture for its simplicity, while noting that a more advanced high‑availability solution will be covered in a future article.