Why MySQL Master‑Master Isn't Recommended and How to Build a Safe Dual‑Master Setup with dbops

The author, a seasoned MySQL operator, notes that the dbops playbook suite supports MySQL deployment on various architectures but deliberately omits native master‑master support because it is not considered a production‑grade design.

Why Master‑Master Is Not Recommended

Two main dual‑master patterns exist: single‑write dual‑master (using keepalived with a virtual IP to ensure only one node writes) and dual‑write dual‑master (both masters accept writes). The single‑write approach is relatively safe, but the dual‑write approach introduces serious risks:

Backup script confusion: Scripts that rely on SHOW SLAVE STATUS cannot distinguish which node holds the VIP, leading to duplicate backups.

Dirty‑data writes on replicas: Accidental writes to a replica can propagate to the primary, causing corruption.

Split‑brain complications: After a network partition, both masters may diverge, and without proper handling the duplicated binlog traffic can cause data loss.

Update‑loss anomalies: Concurrent updates on both masters can result in four possible final states, making data consistency unpredictable.

Common Workarounds

Several mitigation strategies are discussed:

Separate auto‑increment offsets: Setting different auto_increment_offset and auto_increment_increment values avoids primary‑key collisions but does not solve update‑loss issues.

Partition by table or database: Assign distinct tables or databases to each master, effectively turning the setup into multiple independent single‑master clusters.

Use MySQL Group Replication (MGR): MGR provides built‑in conflict detection and consensus, preventing many dual‑master pitfalls, though it still cannot resolve DDL conflicts.

Deploying a Test Dual‑Master with dbops

The author demonstrates a hands‑on experiment using the dbops playbooks to create a three‑node MySQL cluster (one master, two slaves) and then manually configure reverse replication to form a master‑master loop.

# Inventory (hosts.ini)
[dbops_mysql]
192.168.199.171 ansible_user=root ansible_ssh_pass='gta@2015'
192.168.199.172 ansible_user=root ansible_ssh_pass='gta@2015'
192.168.199.173 ansible_user=root ansible_ssh_pass='gta@2015'

# Variables (var_master_slave.yml)
master_ip: 192.168.199.171
slave_ips:
  - 192.168.199.172
  - 192.168.199.173
sub_nets: 1%

After running ansible-playbook master_slave.yml, the author uses the built‑in db3306 shortcut to log into each MySQL instance and executes CHANGE MASTER TO … FOR CHANNEL 'master173' on server 172, then the opposite on server 173, establishing a bidirectional replication channel.

Verifying Replication Loop

Steps include creating a test database on the original master, confirming its appearance on both slaves, and inspecting relay logs on server 172 to see the binlog event being received again (GTID b0ae588e-0eb4-11ee-b23f-000c297b0a30:3). The author then restarts the SQL thread on the channel and explains why GTID mode prevents the event from being re‑executed, whereas traditional binlog replication would cause duplicate‑apply errors.

Root Cause of Looping

MySQL prevents replication loops by checking the server_id of each event. In the three‑node example (IDs 171, 172, 173), events generated by 171 are accepted by 172 and 173 because their server_id differs. When 173 sends the event back to 172, the server_id is still 171, so 172 accepts it again. GTID mode records that the GTID has already been applied, thus skipping execution and avoiding data inconsistency, though the event still traverses the network multiple times.

Conclusion

Master‑master MySQL setups are fraught with hidden dangers, especially with dual‑write configurations. While a manual reverse‑replication loop can be built for testing, production environments should prefer single‑write dual‑master with VIP failover or modern clustering solutions like MySQL Group Replication. The article provides concrete commands, configuration snippets, and diagnostic queries to help readers understand and experiment with these concepts safely.