MySQL: How do you set up master-master replication in MySQL?
Setting up master-master replication in MySQL is very similar to how we set up master/slave replication. There is obviously pros and cons about using master/master replication. But this is not a post which discuses advantages and disadvantages for using master/master replication. One of the differences between master/master set up and master/slave is that in master/master set up, you have true redundancy. If one server dies, second server can take all the inserts/selects. In master/slave setup, if master dies, you will have to go through steps to make slave become the master. Master/master set up we are going to set up is essentially master/slave and slave/master. Meaning, if you had two servers, db0 and db1, your setup will be db0(master)/db1(slave) and also db0(slave)/db1(master). Here are some assumptions:
Master1 server ip: 10.0.0.1 Master2 server ip: 10.0.0.2 Slave username: slaveuser Slave pw: slavepw Your data directory is: /usr/local/mysql/var/
Let us go through the steps you must take on Master1 to enable it to act as master and slave by using following configuration which goes under [mysqld] section:
# let’s make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2 auto_increment_offset=1 # Replication Master Server # binary logging is required for replication log-bin=master1-bin binlog-ignore-db=mysql binlog-ignore-db=test # required unique id between 1 and 2^32 - 1 server-id = 1 #following is the slave settings so this server can connect to master2 master-host = 10.0.0.2 master-user = slaveuser master-password = slavepw master-port = 3306
Following is the configuration which goes on master2 under [mysqld] section:
# let's make it so auto increment columns behave by having different increments on both servers auto_increment_increment=2 auto_increment_offset=2 # Replication Master Server # binary logging is required for replication log-bin=master2-bin binlog-ignore-db=mysql binlog-ignore-db=test # required unique id between 1 and 2^32 - 1 server-id = 2 #following is the slave settings so this server can connect to master2 master-host = 10.0.0.1 master-user = slaveuser master-password = slavepw master-port = 3306
On master1 server, go to mysql> prompt and add the appropriate user:
mysql> grant replication slave on *.* to slaveuser@'10.0.0.2' identified by 'slavepw';
On master2 server do the same but allow right ip:
mysql> grant replication slave on *.* to slaveuser@'10.0.0.1' identified by 'slavepw';
Restart both of the master servers and check slave status:
mysql> show slave status;
That is all you have to do to set up the replication. Of course there are a lot more configuration options but this should get your replication going and you can tweak from here on.
