MySQL Master-Master Replication

In my opinion, having dual-writable master MySQL databases (in a replication configuration) is not worth the hassle. There are a host of problems, enough that you should seriously consider what you’re trying to gain when attempting it. However, the master-master replication scheme still has some very good uses when used in an active-passive way. The two most compelling reasons for me are:

1. Keeping a “hot spare”. You have an additional database already configured as a master. This might not seem like much, since you “almost” gain the same thing in a master-slave setup. However, if your master-master includes slave servers, this topology provides a very high degree of fault tolerance. Especially when each master-server pair is geographically separated.

2. Making changes to the database. Certain changes made to the database may require a long time to complete, particularly if you have a very large database. In the master-master setup, you can take one of the servers “offline” (by telling the other master not to replicate its changes), and make the changes necessary. Then bring it back online, after the changes have been made, make it the active master, and let the other master perform the changes.

This could be seen as an added benefit of keeping a “hot spare”.

The good news is that setting this up is identical to setting up master-slave replication, you simply do it twice. Each master is essentially the master of and a slave to the other database. To keep it active-passive, one of the databases will need to be made read-only. Here are the changes you will need to the my.cnf configuration file:

Active Master my.cnf

log_bin = mysql-bin
server_id = 1001
relay_log = mysql-relay-bin
log_slave_updates = 1

Passive Master my.cnf

log_bin = mysql-bin
server_id = 1002
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1  # Notice this line

Then set up the replication user accounts, as described in this post: Simple MySQL Master-Slave Replication

Finally you issue the slave directives, and start the slave process:

Active Master ‘change master’

# Active master is slave to passive host
CHANGE MASTER TO MASTER_HOST='passive.mysql.host',
MASTER_USER='rep_user',
MASTER_PASSWORD='reppassword',
MASTER_LOG_FILE='mysql-bin-000001',
MASTER_LOG_POS=0;

start slave;

Passive Master ‘change master’

# Passive master is slave to active host
CHANGE MASTER TO MASTER_HOST='active.mysql.host',
MASTER_USER='rep_user',
MASTER_PASSWORD='reppassword',
MASTER_LOG_FILE='mysql-bin-000001',
MASTER_LOG_POS=0;

start slave;