Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 9, 2010

An Introduction to Multi-Master MySQL

By Sean Hull

Multi-master replication for MySQL is easy to setup, is similar to master-slave replication and provides a host of features and options not available with the regular master-slave setup. Add to that the MMM database management tool, and you have a flexible, highly available MySQL cluster on which to host your application.


Multi-master replication for MySQL provides a host of new features and options not available with the regular master-slave setup.  Also known as circular replication, with multi-master replication you have one active and one passive server, but both configured in otherwise exactly the same way.  

We're going to introduce this configuration, then show you how to use the Multi-master Replication Manager for MySQL. 

Easy Failover with Multi-Master Replication

Multi-master replication above and beyond anything else, gives you very easy and simple failover.  Why is that?  Because you don't need to make any configuration changes.  Here's how.

With standard MySQL replication, the master database receives changes, and all the while the slave database watches its binary log file for activity.  When it sees activity there, it copies those transactions using the IO thread from the master's binary log, to its own relay log file.  It is then that the slave's SQL thread kicks in, and applies those transactions one-by-one in a serial fashion to the slave database.  This happens in the order the transactions were committed. 

In that configuration, if you want to promote a slave to a master, you have to disable the slave, change some other settings, possibly change read-only mode, and or the server_id if that hasn't been set.  

Enter Multi-master replication.  Here we have the master and slave setup as above.   Db2 is a slave of db1 for example.  However, in addition with this setup you also make db1 a slave to db2.  As above, the transactions flow from master to slave.  They then flow back upstream to the master.  The master sees the server_id in the transaction as its OWN server_id and knows not to apply this transaction.  In other words, db1 will only apply a transaction that ORIGINATED on db2.    In all other ways, the servers are configured exactly the same way.  In fact you don't even *NEED* to enable read-only mode, though we recommend it.  

Setting up Master-Master Replication

Begin by setting up master-slave replication as you normally would.  In this setup, also make a few additions.  Be sure to set:

server_id = 12345       # could use last digits of IP address, must be globally unique

log_slave_updates = 1

read_only = 1

log_bin = /path/to/mysql-bin.log


Go ahead and set these; be sure your replication user exists on both master and slave, restart master and slave, and make sure replication is happening to the slave.  

There is now only one last step.  On the *master* go ahead and issue the same CHANGE MASTER TO command that you would normally use pointing db2 to your master db1, but in reverse so the master points to the slave.  Be sure to grab the master_log_file and position on the slave so you can run this command:

mysql>  change master to master_user='abc', master_password='abc', master_host='db2', master_log_file='mysql-bin.000001', master_log_pos='12345';

Then issue:

mysql> start slave;
mysql> show slave status\G;

Look for these two lines:

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Building Highly Available MySQL with MMM

I'm sure you can see the advantages of MySQL Multi-master replication already.  But there's a lot more in store for you.  Next up you'll want to take a look at MMM.  This toolset provides a monitoring daemon, and agents that run on the various MySQL nodes.  It monitors activity and makes sure your master is available.  If it becomes unavailable, it will automatically switch your application to point to your alternate master.  

MMM exposes a virtual IP, which your application can use to connect.  That VIP will always point to the writer *role* whichever database in your multi-master setup that currently holds the role.  There are also reader roles with associated IP addresses for your application to use.  MMM manages all of these for you.

What's more, if the monitor daemon dies for some reason, the currently assigned VIPs are persistent.  So obviously, you'll want to monitor to make sure the that process is running, but it won't bring down your application if it dies.

Setting up MMM

First setup the EPEL repository.  A quick Google search should get you details on this.  Next use yum or apt-get to install your packages.  For yum:

$ yum install mysql-mmm-agent
$ yum install mysql-mmm-monitor
$ yum install mysql-mmm-tools

There are a few files to edit to configure MMM, but they're not too bad.

First, create an mmm_common.conf file and copy to /etc/mysql-mmm/ on monitor server as well as each server running the agent daemon:

active_master_role      writer
<host default>
    cluster_interface       eth0
    pid_path                /var/run/mysql-mmm/mmmd_agent.pid
    bin_path                /usr/libexec/mysql-mmm/
    replication_user        replication
    replication_password    abc
    agent_user              mmm_agent
    agent_password          abc
<host db1>
    mode    master
    peer    db2
<host db2>
    mode    master
    peer    db1
<role writer>
    hosts   db1, db2
    mode    exclusive
<role reader>
    hosts   db1, db2
    mode    balanced

Next edit the mmm_agent.conf file on each machine it will run on (adjust the "this" line as needed).

include mmm_common.conf
this db1

Lastly edit the mmm_mon.conf file on the box it runs on.

include mmm_common.conf
    pid_path            /var/run/mysql-mmm/mmmd_mon.pid
    bin_path            /usr/libexec/mysql-mmm
    status_path         /var/lib/mysql-mmm/mmmd_mon.status
    ping_ips  ,
    auto_set_online     60
<host default>
    monitor_user        mmm_monitor
    monitor_password    abc
debug 0

Got all those files edited?  Great, start the agent on each MySQL server.

$ /etc/init.d/mysql-mmm-agent start

Then on the monitor box.

$ /etc/init.d/mysql-mmm-monitor start

Once you're up and running, use the control tool.  Try these commands:

[root@mon init.d]# mmm_control show
  db1( master/ONLINE. Roles: reader(, writer(
  db2( master/ONLINE. Roles: reader(
[root@mon init.d]# mmm_control move_role writer db2
OK: Role 'writer' has been moved from 'db1' to 'db2'. Now you can wait some time and check new roles info!
[root@mon init.d]# mmm_control show
  db1( master/ONLINE. Roles: reader(
  db2( master/ONLINE. Roles: reader(, writer(
[root@mon init.d]# mmm_control move_role writer db1
OK: Role 'writer' has been moved from 'db2' to 'db1'. Now you can wait some time and check new roles info!
[root@mon init.d]# mmm_control show
  db1( master/ONLINE. Roles: reader(, writer(
  db2( master/ONLINE. Roles: reader(

Consistency Checks With Maatkit

We've discussed MySQL replication in depth in the past, and particularly how replication can get out of sync.  Just as with any MySQL replication topology, Multi-master has that same limitation.  Given that, be sure you are using the Maatkit mk-table-checksum and mk-table-sync technologies to alert you if they get out of sync, and fix them when they do.  

Mk-table-checksum works by calculating the checksums of all your tables on the master and gathering those into a table.  That table then propagates through replication as well.  You then run another check command on the slave side (or current passive master) to verify that all is ok.  See Fixing MySQL Replication for details.


As you can see, Multi-master replication is not overly complicated to setup and a lot like master-slave replication.  Despite that, it provides a whole host of advantages like icing on an already great cake.  Add to that the MMM management tool, and you have a very flexible highly available MySQL cluster to host your application on top of.

Additional Resources

MySQL 17.6.10. MySQL Cluster Replication: Multi-Master and Circular Replication
Installing and Managing Multi Master Replication Manager (MMM) for MySQL High Availability

» See All Articles by Columnist Sean Hull

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM