MySQL Master-Master Replication
In my Setup I will explains how you can set up MySQL master-master replication on four MySQL nodes .The difference to a two node master-master replication is that if you have more than two nodes, the replication goes in a circle, i.e., with four nodes, the replication goes from node1 to node2, from node2 to node3, from node3 to node4, and from node4 to node1. The advantages of master-master replication over the traditional master-slave replication is that you don’t have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master.

I will show how to replicate the database “sampled” on four MySQL nodes:
• machine1.domain.com: IP address 172.16.1.100
• machine2.domain.com: IP address 172.16.1.101
• machine3.domain.com: IP address 172.16.1.102
• machine4.domain.com: IP address 172.16.1.103
Each node is a master and a slave at the same time. Replication will work in a circle, i.e., the replication goes from machine1 to machine2, from machine2 to machine3, from machine3 to machine4, and from machine4 back to machine1:
… –> machine1 –> machine2 –> machine3 –> machine4 –> machine1 –> …

If MySQL 5.0 isn’t already installed on machine1 to machine4, install it now:
machine1/machine2/machine3/machine4:
yum install mysql-server-5.0 mysql-client-5.0
To make sure that the replication can work, we must make MySQL listen on all interfaces, therefore we comment out the line bind-address = 127.0.0.1 in /etc/mysql/my.cnf:
machine1/machine2/machine3/machine4:
vi /etc/mysql/my.cnf
[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
[...]
Restart MySQL afterwards:
machine1/machine2/machine3/machine4:
/etc/init.d/mysql restart
Then check with
machine1/machine2/machine3/machine4:
netstat -tap | grep mysql
that MySQL is really listening on all interfaces:
machine1:~# netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 2671/mysqld
machine1:~#
Afterwards, set a MySQL password for the user root@localhost:
machine1/machine2/machine3/machine4:
mysqladmin -u root password myrootsqlpassword
Next we create MySQL passwords for root@machine1.domain.com, root@machine2.domain.com, root@machine3.domain.com, and root@machine4.domain.com:
machineN:
mysqladmin -h machineN.domain.com -u root password myrootsqlpassword

Now we set up a replication user “slaveuser_for_machineN” that can be used by machineN to access the MySQL database on machineN-1:
E.g. On machine1 ,slaveuser_for_machine2
On machine4 , slaveuser_for_machine1
First we will set the configuration on Machine1 , Replace N by 2 ,3,4,1 (maintain order)…..
Machine1/machine2/machine3/machine4:
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO ‘slaveuser_for_machineN’@'%’ IDENTIFIED BY ‘slave_user_for_machineN_password’;
FLUSH PRIVILEGES;
quit;

I assume that the database sampledb is already existing on machine1, and that there are tables with records in it.
Before we start setting up the replication, we create an empty database sampledb on machine2, machine3, and machine4:
machine2/machine3/machine4:
mysql -u root -p
CREATE DATABASE sampledb;
quit;

Setting Up Replication
Now we set up master-master replication in /etc/mysql/my.cnf. The crucial configuration options for master-master replication are auto_increment_increment and auto_increment_offset:
• auto_increment_increment controls the increment between successive AUTO_INCREMENT values.
• auto_increment_offset determines the starting point for AUTO_INCREMENT column values.
Let’s assume we have N MySQL nodes (N=4 in this example), then auto_increment_increment has the value N on all nodes, and each node must have a different value for auto_increment_offset (1, 2, …, N).
We also need to configure log-slave-updates because otherwise replication will work only, for example, from machine1 to machine2, but not to machine3 and machine4.
Now let’s configure our four MySQL nodes , :
#vim /etc/my.cnf

machine1: auto-increment-offset=1
master-host = 172.16.1.103
master-user = slaveuser_for_machine1
master-password= slave_user_for_machine1_password
machine2: auto-increment-offset=2
master-host = 172.16.1.100
master-user = slaveuser_for_machine2
master-password= slave_user_for_machine2_password
machine3: auto-increment-offset=3
master-host = 172.16.1.101
master-user = slaveuser_for_machine3
master-password= slave_user_for_machine3_password
machine4: auto-increment-offset=4
master-host = 172.16.1.102
master-user = slaveuser_for_machine4
master-password= slave_user_for_machine4_password

vi /etc/mysql/my.cnf
Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options):

Sample configuration file for machine 1-4
[...]
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 4
auto-increment-offset = 1 or 2 or 3 or 4

master-host = 172.16.1.100 or 101 or 102 or 103
master-user = slaveuser_for_machine1 / 2/ 3/ 4
master-password = slave_user_for_machine1/2/3/4_password
master-connect-retry = 60
replicate-do-db = sampledb

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = sampledb
log-slave-updates

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10
max_binlog_size = 500M
[...]
Then restart MySQL:
/etc/init.d/mysql restart
Before we continue, we must make sure that no slave processes are running on machine1 to machine4:
machine1/machine2/machine3/machine4:
/usr/bin/mysqladmin –user=root –password=myrootsqlpassword stop-slave
Next we lock the sampledb database on machine1, find out about the master status of machine1, create an SQL dump of sampledb (that we will import into sampledb on machine2, machine3, and machine4 so that all four databases contain the same data), and unlock the database so that it can be used again:
machine1:
mysql -u root -p
On the MySQL shell, run the following commands:
machine1:
USE sampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
The last command should show something like this (please write it down, we’ll need it later on):
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000010 | 98 | sampledb | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

mysql>
Now don’t leave the MySQL shell, because if you leave it, the database lock will be removed, and this is not what we want right now because we must create a database dump now. While the MySQL shell is still open, we open a second command line window where we create the SQL dump snapshot.sql and transfer it to machine2, machine3, and machine4 (using scp):
machine1:
cd /tmp
mysqldump -u root -pmyrootsqlpassword –opt sampledb > snapshot.sql
scp snapshot.sql root@172.16.1.101:/tmp
scp snapshot.sql root@172.16.1.102:/tmp
scp snapshot.sql root@172.16.1.103:/tmp
Afterwards, you can close the second command line window. On the first command line window, we can now unlock the database and leave the MySQL shell:
machine1:
UNLOCK TABLES;
quit;

4.1 Setting Up Replication On machine2
On machine2, we can now import the SQL dump snapshot.sql like this:
machine2:
/usr/bin/mysqladmin –user=root –password=myrootsqlpassword stop-slave
cd /tmp
mysql -u root -pmyrootsqlpassword sampledb SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000010 | 1067 | sampledb | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

mysql>
Then unlock the tables:
UNLOCK TABLES;
and run the following command to make machine2 a slave of machine1 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on machine1!):
CHANGE MASTER TO MASTER_HOST=’172.16.1.100′, MASTER_USER=’slaveuser_for_machine2’, MASTER_PASSWORD=’slave_user_for_machine2_password’, MASTER_LOG_FILE=’mysql-bin.000010′, MASTER_LOG_POS=98;
Finally start the slave:
START SLAVE;
Then check the slave status:
SHOW SLAVE STATUS \G
It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.100
Master_User: slaveuser_for_s2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 98
Relay_Log_File: slave-relay.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: sampledb
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235

we will configure replication from machine2 to machine3.

4.2 Setting Up Replication On machine3
On machine3, we can now import the SQL dump snapshot.sql like this:
/usr/bin/mysqladmin –user=root –password=myrootsqlpassword stop-slave
cd /tmp
mysql -u root -pmyrootsqlpassword sampledb SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000010 | 1067 | sampledb | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

mysql>
Then unlock the tables:
UNLOCK TABLES;
and run the following command to make machine3 a slave of machine2 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on machine2!):
CHANGE MASTER TO MASTER_HOST=’172.16.1.101′, MASTER_USER=’slaveuser_for_machine3′, MASTER_PASSWORD=’slave_user_for_machine3_password’, MASTER_LOG_FILE=’mysql-bin.000010′, MASTER_LOG_POS=1067;
Finally start the slave:
START SLAVE;
Then check the slave status:
SHOW SLAVE STATUS \G
It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.101
Master_User: slaveuser_for_s3
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 1067
Relay_Log_File: slave-relay.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: sampledb
Exec_Master_Log_Pos: 1067
Relay_Log_Space: 235

Next we must configure replication from machine3 to machine4.

4.3 Setting Up Replication On machine4
On machine4, we can now import the SQL dump snapshot.sql like this:
/usr/bin/mysqladmin –user=root –password=myrootsqlpassword stop-slave
cd /tmp
mysql -u root -pmyrootsqlpassword sampledb SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000010 | 1067 | sampledb | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

mysql>
Then unlock the tables:
UNLOCK TABLES;
and run the following command to make machine4 a slave of machine3 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on machine3!):
CHANGE MASTER TO MASTER_HOST=’172.16.1.102′, MASTER_USER=’slaveuser_for machen4′, MASTER_PASSWORD=’slave_user_for_machine4_password’, MASTER_LOG_FILE=’mysql-bin.000010′, MASTER_LOG_POS=1067;
Finally start the slave:
START SLAVE;
Then check the slave status:
SHOW SLAVE STATUS \G
It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.102
Master_User: slaveuser_for_s4
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 1067
Relay_Log_File: slave-relay.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: sampled
Exec_Master_Log_Pos: 1067
Relay_Log_Space: 235

1 row in set (0.00 sec)

Finally we must configure replication from machine4 to machine1 to close the replication circle.

4.4 Setting Up Replication On machine1
To do this, we stop the slave on machine1 and make it a slave of machine4:
mysql -u root -p
STOP SLAVE;
Make sure that you use the values of the SHOW MASTER STATUS; command that you ran on machine4 in the following command:
CHANGE MASTER TO MASTER_HOST=’172.16.1.103′, MASTER_USER=’slave_user_for_machine1′, MASTER_PASSWORD=’slave_user_for_machine1_password’, MASTER_LOG_FILE=’mysql-bin.000010′, MASTER_LOG_POS=1067;
Then start the slave on machine1:
START SLAVE;
Then check the slave status:
SHOW SLAVE STATUS \G
It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.103
Master_User: slaveuser_for_s1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 1067
Relay_Log_File: slave-relay.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: sampledb:
Exec_Master_Log_Pos: 1067
Relay_Log_Space: 235
1 row in set (0.00 sec)

If nothing went wrong, MySQL master-master replication should now be working. If it isn’t, please check /var/log/syslog for MySQL errors.