Circular replication with 4 nodes
1 Preliminary Note
In this tutorial I will show how to replicate the database manish on four MySQL nodes:
server1.example.com: IP address 192.168.1.1
server2.example.com: IP address 192.168.1.2
server3.example.com: IP address 192.168.1.3
server4.example.com: IP address 192.168.1.4
Each node is a master and a slave at the same time. All four systems are running centos5; however the configuration should apply to almost all distributions with little or no modifications.
Replication will work in a circle,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:
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.
server1/server2/server3/server4:
vi /etc/mysql/my.cnf
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 5543/mysqld
Afterthis, set a MySQL password for the user root@localhost:
server1/server2/server3/server4:
mysqladmin -u root password password
Next we create MySQL passwords for root@server1.example.com, root@server2.example.com, root@server3.example.com, and root@server4.example.com:
server1:
mysqladmin -h server1.example.com -u root password password
server2:
mysqladmin -h server2.example.com -u root password password
server3:
mysqladmin -h server3.example.com -u root password password
server4:
mysqladmin -h server4.example.com -u root password password
Now we set up a replication user repl_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 'repl_s2'@'%' IDENTIFIED BY 'repl2_password';
FLUSH PRIVILEGES;
quit;
Then we set up a replication user repl_s3 that can be used by server3 to access the MySQL database on server2...
server2:
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'repl_s3'@'%' IDENTIFIED BY 'repl_password';
FLUSH PRIVILEGES;
quit;
... and a replication user repl_s4 that can be used by server4 to access the MySQL database on server3...
server3:
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'repl_s4'@'%' IDENTIFIED BY 'repl_password';
FLUSH PRIVILEGES;
quit;
finally a replication user repl_s1 that can be used by server1 to access the MySQL database on server4:
server4:
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'repl_s1'@'%' IDENTIFIED BY 'repl_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;
4 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 server1 to server2, but not to server3 and server4.
server1:
vi /etc/mysql/my.cnf
[...]
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 4
auto-increment-offset = 1
server2:
vi /etc/mysql/my.cnf
[...]
[mysqld]
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 4
auto-increment-offset = 2
server3:
vi /etc/mysql/my.cnf
[...]
[mysqld]
server-id = 3
replicate-same-server-id = 0
auto-increment-increment = 4
auto-increment-offset = 3
server4:
vi /etc/mysql/my.cnf
[...]
[mysqld]
server-id = 4
replicate-same-server-id = 0
auto-increment-increment = 4
auto-increment-offset = 4
server1/server2/server3/server4:
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
server1:
mysql -u root -p
On the MySQL shell, run the following commands:
server1:
USE exampledb;
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 | exampledb | |
+------------------+----------+--------------+------------------+
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 server2, server3, and server4 (using scp):
server1:
cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.1.2:/tmp
scp snapshot.sql root@192.168.1.3:/tmp
scp snapshot.sql root@192.168.1.4:/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:
server1:
UNLOCK TABLES;
quit;
4.1 Setting Up Replication On server2
(This chapter is for server2 only!)
On server2, we can now import the SQL dump snapshot.sql like this:
server2:
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql
Afterwards, we must find out about the master status of server2 as well and write it down:
mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 1067 | exampledb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
Then unlock the tables:
UNLOCK TABLES;
and run the following command to make server2 a slave of server1 (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 server1!):
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slaveuser_for_s2', MASTER_PASSWORD='slave_user_for_server2_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: 192.168.1.1
Master_User: repl_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: db
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.01 sec)
mysql>
Afterwards, you can leave the MySQL shell on server2:
quit
4.2 Setting Up Replication On server3
On server3, we can now import the SQL dump snapshot.sql like this:
server3:
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql
Afterwards, we must find out about the master status of server3 as well and write it down:
mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 1067 | exampledb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
Then unlock the tables:
UNLOCK TABLES;
and run the following command to make server3 a slave of server2 (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 server2!):
CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='slaveuser_for_s3', MASTER_PASSWORD='slave_user_for_server3_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: 192.168.1.2
Master_User: repl_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: db
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1067
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
mysql>
Afterwards, you can leave the MySQL shell on server3:
quit
Now the replication from server2 to server3 is set up. Next we must configure replication from server3 to server4.
4.3 Setting Up Replication On server4
(This chapter is for server4 only!)
On server4, we can now import the SQL dump snapshot.sql like this:
server4:
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql
Afterwards, we must find out about the master status of server4 as well and write it down:
mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 1067 | exampledb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
UNLOCK TABLES;
and run the following command to make server4 a slave of server3 (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 server3!):
CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='repl_s4', MASTER_PASSWORD='slave_user_for_server4_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: 192.168.1.3
Master_User: repl_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: exampledb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1067
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
mysql>
Afterwards, you can leave the MySQL shell on server3:
quit
Now the replication from server3 to server4 is set up. Finally we must configure replication from server4 to server1 to close the replication circle.
4.4 Setting Up Replication On server1
(This chapter is for server1 only!)
To do this, we stop the slave on server1 and make it a slave of server4:
server1:
mysql -u root -p
STOP SLAVE;
Make sure that you use the values of the SHOW MASTER STATUS; command that you ran on server4 in the following command:
CHANGE MASTER TO MASTER_HOST='192.168.0.103', MASTER_USER='slaveuser_for_s1', MASTER_PASSWORD='slave_user_for_server1_password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=1067;
Then start the slave on server1:
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: 192.168.0.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: exampledb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1067
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
mysql>
Afterwards you can leave the MySQL shell:
quit
