master/master mysql database cluster

Introduction

Setup a Master/Master MySql cluster is quite easy.
The procedure below explains you how to do it on a couple of Debian squeeze server.

Master / Master mysql setup procedure

The cluster will be built on two servers:

• server1: 192.168.10.101
• server2: 192.168.10.102

Diagram

Avoid DNS resolution

In order not to rely on external DNS server, we recommand using hosts file.
Furthermore, a host name has more meanings in a configuration file that an IP address.
Update /etc/hosts file on both server with cluster server IPs:

192.168.10.101 server1
192.168.10.102 server2

MySql configuration

MySql replication user

This user will be used to send to send updates to the other node.

On both servers, run the following command into the MySql shell:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'your-slave-username'@'%' IDENTIFIED BY 'yourslavepassword';
mysql> FLUSH PRIVILEGES;

In our case, your-slave-username is repl and yourslavepassord is password.

Stop MySql

On both server, stop MySql:

service mysql stop

Update MySql configuration

Edit the /etc/mysql/my.cnf file.
1. Update the bind-address line:

• For server1:

bind-address = 192.168.10.101

• For server2:

bind-address = 192.168.10.102

2. Update the [mysqld] section:

• For server1:

# server ID in the cluster configuration
server-id = 1
replicate-same-server-id=0

# Autoincrement configuration to avoid collision
auto-increment-increment=2
auto-increment-offset=1

master-host = server2
master-connect-retry = 60
# Replication credential
master-user = repl
master-password = password

log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
relay-log=/var/lib/mysql/relay-log
relay-log-index=/var/lib/mysql/relay-log.index
report-host=server1

• For server2:

# server ID in the cluster configuration
server-id = 2
replicate-same-server-id=0

# Autoincrement configuration to avoid collision
auto-increment-increment=2
auto-increment-offset=2

master-host = server1
master-connect-retry = 60
# Replication credential
master-user = repl
master-password = password

log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
relay-log=/var/lib/mysql/relay-log
relay-log-index=/var/lib/mysql/relay-log.index
report-host=server2

Start MySql

On both server, start MySql:

service mysql start

Check the result

On both servers, connect to the MySql shell and run:

SHOW SLAVE STATUS\G;

and look for the lines Slave_IO_Running and Slave_SQL_Running: both must be set to Yes.

Conclusion

If everything worked fine, you may have a Master/Master MySql cluster.

Links

Procedure inspired by: http://blogs.dixcart.com/public/technology/2010/08/guide-to-mysql-master-to-master-replication.html