Master-Master MySQL Replication with MMM: Setup, Configuration, and HA Testing

Introduction

MMM (Master‑Master replication manager for MySQL) is a flexible Perl script suite for monitoring MySQL replication, handling failover, and managing master‑master configurations where only one node is writable at a time.

Pros and Cons

Advantages: high availability, good scalability, automatic failover, and data consistency by allowing only one writable master.

Disadvantage: the monitor node is a single point of failure, which can be mitigated by combining with Keepalived for HA.

Working Principle

The MMM monitor provides multiple virtual IPs (VIPs), including one writable VIP and several readable VIPs. These IPs are bound to available servers; when a server fails, the monitor migrates the VIPs to other servers.

Experimental Topology

# Note: System environment CentOS 6.6 # VIP 172.16.10.30 is the writable VIP; the other three groups are readable VIPs. # Writable VIP switches only between masters; readable VIPs can switch between masters and slaves. # Front‑end applications connect to any readable VIP for reads and to the writable VIP for writes.

DB1 Configuration

[root@node1 ~]# vim /etc/mysql/my.cnf

datadir = /mydata/data
log-bin = /mydata/binlogs/master-bin   # binary log location
relay-log = /mydata/relaylogs/relay-bin # relay log location
binlog_format = mixed
server-id = 1
auto_increment_offset = 1   # start value for auto‑increment in dual‑master
auto_increment_increment = 2 # step for auto‑increment in dual‑master
log_slave_updates = 1      # enable writing updates from slaves to binary log
sync_binlog = 1            # ensure transaction log is flushed to disk
skip-name-resolve          # disable DNS reverse lookup
read_only = 1

[root@node1 ~]# service mysqld start

DB2 Configuration

[root@node2 ~]# vim /etc/mysql/my.cnf

datadir = /mydata/data
log-bin = /mydata/binlogs/master-bin
relay-log = /mydata/relaylogs/relay-bin
binlog_format = mixed
server-id = 11
auto_increment_offset = 2
auto_increment_increment = 2
log_slave_updates = 1
sync_binlog = 1
skip-name-resolve
read_only = 1

[root@node2 ~]# service mysqld start

DB3 Configuration

[root@scholar ~]# vim /etc/mysql/my.cnf

datadir = /mydata/data
relay-log = /mydata/relaylogs/relay-bin
server-id = 111
read_only = 1
skip-name-resolve

[root@scholar ~]# service mysqld start

Server Connections

DB1 ↔ DB2, DB1 ↔ DB3, DB2 ↔ DB1 connections are illustrated below:

Master‑Slave Test

Data consistency is verified; the master‑slave replication part is complete.

MMM Configuration

Installation Packages

Authorize Monitoring and Proxy Users

Monitor Configuration

DB1‑DB3 Configuration

Start Services

Check Current Status

Check Node VIP Status

High Availability Test

Simulating a failure of DB1 shows the VIP being transferred to another node, confirming failover functionality.

After the test, the VIP has moved to a different node. Further testing can be performed as needed.

Conclusion

This experiment demonstrates MySQL/MariaDB read/write splitting and high availability using MMM. Interested readers may explore integrating Keepalived for a highly available monitor, though that is beyond the scope of this guide.