Setting Up Master-Master Replication On Four Nodes With MySQL 5 On Debian Etch

Setting Up Master-Master Replication On Four Nodes With MySQL 5 On Debian Etch

Version 1.0
Author: Falko Timme

This tutorial explains how you can set up MySQL master-master replication on four MySQL nodes (running on Debian Etch). The difference to a two node master-master replication (which is explained here) 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.

Since version 5, MySQL comes with built-in support for master-master replication, solving the problem that can happen with self-generated keys. In former MySQL versions, the problem with master-master replication was that conflicts arose immediately if node A and node B both inserted an auto-incrementing key on the same table. The advantages of master-master replication over the traditional master-slave replication are 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 do not issue any guarantee that this will work for you!

 

1 Preliminary Note

In this tutorial I will show how to replicate the database exampledb on four MySQL nodes:

• server1.example.com: IP address 192.168.0.100
• server2.example.com: IP address 192.168.0.101
• server3.example.com: IP address 192.168.0.102
• server4.example.com: IP address 192.168.0.103

Each node is a master and a slave at the same time. All four systems are running Debian Etch; however, the configuration should apply to almost all distributions with little or no modifications.

Replication will work in a circle, i.e., the replication goes from server1 to server2, from server2 to server3, from server3 to server4, and from server4 back to server1:

... --> server1 --> server2 --> server3 --> server4 --> server1 --> ... 

 

2 Installing MySQL 5.0

If MySQL 5.0 isn't already installed on server1 to server4, install it now:

server1/server2/server3/server4:

apt-get 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:

server1/server2/server3/server4:

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:

server1/server2/server3/server4:

/etc/init.d/mysql restart

Then check with

server1/server2/server3/server4:

netstat -tap | grep mysql

that MySQL is really listening on all interfaces:

server1:~# netstat -tap | grep mysql
tcp        0      0 *:mysql                 *:*                     LISTEN     2671/mysqld
server1:~#

Afterwards, set a MySQL password for the user [email protected]:

server1/server2/server3/server4:

mysqladmin -u root password yourrootsqlpassword

Next we create MySQL passwords for [email protected], [email protected], [email protected], and [email protected]:

server1:

mysqladmin -h server1.example.com -u root password yourrootsqlpassword

server2:

mysqladmin -h server2.example.com -u root password yourrootsqlpassword

server3:

mysqladmin -h server3.example.com -u root password yourrootsqlpassword

server4:

mysqladmin -h server4.example.com -u root password yourrootsqlpassword

Now we set up a replication user slaveuser_for_s2 that can be used by server2 to access the MySQL database on server1:

server1:

mysql -u root -p

On the MySQL shell, run the following commands:

GRANT REPLICATION SLAVE ON *.* TO 'slaveuser_for_s2'@'%' IDENTIFIED BY 'slave_user_for_server2_password';
FLUSH PRIVILEGES;
quit;

Then we set up a replication user slaveuser_for_s3 that can be used by server3 to access the MySQL database on server2...

server2:

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'slaveuser_for_s3'@'%' IDENTIFIED BY 'slave_user_for_server3_password';
FLUSH PRIVILEGES;
quit;

... and a replication user slaveuser_for_s4 that can be used by server4 to access the MySQL database on server3...

server3:

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'slaveuser_for_s4'@'%' IDENTIFIED BY 'slave_user_for_server4_password';
FLUSH PRIVILEGES;
quit;

... and finally a replication user slaveuser_for_s1 that can be used by server1 to access the MySQL database on server4:

server4:

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'slaveuser_for_s1'@'%' IDENTIFIED BY 'slave_user_for_server1_password';
FLUSH PRIVILEGES;
quit;

 

3 Some Notes

In the following I will assume that the database exampledb is already existing on server1, and that there are tables with records in it.

Before we start setting up the replication, we create an empty database exampledb on server2, server3, and server4:

server2/server3/server4:

mysql -u root -p

CREATE DATABASE exampledb;
quit;