MYSQL CIRCULAR REPLICATION

1) Install MySQL Server on each node.

               # apt-get update && apt-get install openssh-server

               # apt-get install mysql-server

               # mysql_secure_installation

2) First create Database (DB) on each node which have to be replicate

 

3) On all nodes we will change configuration file i.e. /etc/mysql/my.cnf

WEB-1 (NODE 1):

#bind-address                 = 127.0.0.1

server-id = 1

replicate-same-server-id = 0

auto-increment-increment = 3

auto-increment-offset = 1

replicate-do-db = database_name

log_bin = /var/log/mysql/mysql-bin.log

binlog-do-db = database_name

log-slave-updates = true

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

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

(Insert above lines in [mysqld] section)

 

WEB-2 (NODE 2):

#bind-address                 = 127.0.0.1

server-id = 2

replicate-same-server-id = 0

auto-increment-increment = 3

auto-increment-offset = 2

replicate-do-db = database_name

log_bin = /var/log/mysql/mysql-bin.log

binlog-do-db = database_name

log-slave-updates = true

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

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

 

WEB-3 (NODE 3):

#bind-address                 = 127.0.0.1

server-id = 3

replicate-same-server-id = 0

auto-increment-increment = 3

auto-increment-offset = 3

replicate-do-db = database_name

log_bin = /var/log/mysql/mysql-bin.log

binlog-do-db = database_name

log-slave-updates = true

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

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

4) Restart MySql services on all nodes

               # /etc/init.d/mysql restart

 

5) On each node of MySQL shell

               # mysql –u root –p

mysql > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'PASSWORD';

mysql > FLUSH PRIVILEGES;

mysql > STOP SLAVE;

mysql > RESET SLAVE ALL;

mysql > quit;

 

6) On WEB-1 (NODE 1) MySQL shell

mysql > FLUSH TABLES WITH READ LOCK;

mysql > SHOW MASTER STATUS;

This will show binary log file and position on web1, note down it for further use on web2.

mysql > UNLOCK TABLES;

mysql > quit;

 

7) On WEB-2 (NODE 2) configure WEB-1 (NODE 1) as master

mysql > STOP SLAVE;

mysql > FLUSH TABLES WITH READ LOCK;

mysql > SHOW MASTER STATUS;

This will show binary log file and position on web2, note down it for further use on web3.

mysql > UNLOCK TABLES;

mysql > CHANGE MASTER TO MASTER_HOST='IP', MASTER_USER='SLAVE_USER', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='BINARY_FILE', MASTER_LOG_POS=LOG_POSITION;

mysql > START SLAVE;

mysql > SHOW SLAVE STATUS\G;

mysql > quit;

 

8) On WEB-3 (NODE 3) configure WEB-2 (NODE 2) as master

mysql > STOP SLAVE;

mysql > FLUSH TABLES WITH READ LOCK;

mysql > SHOW MASTER STATUS;

This will show binary log file and position on web3, note down it for further use on web1.

mysql > UNLOCK TABLES;

mysql > CHANGE MASTER TO MASTER_HOST='IP', MASTER_USER='SLAVE_USER', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='BINARY_FILE', MASTER_LOG_POS=LOG_POSITION;

mysql > START SLAVE;

mysql > SHOW SLAVE STATUS\G;

mysql > quit;

 

9) On WEB-1 (NODE 1) configure WEB-3 (NODE 3) as master

mysql > STOP SLAVE;

mysql > CHANGE MASTER TO MASTER_HOST='IP', MASTER_USER='SLAVE_USER', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='BINARY_FILE', MASTER_LOG_POS=LOG_POSITION;

mysql > START SLAVE;

mysql > SHOW SLAVE STATUS\G;
mysql > quit;

 

10) For testing insert rows in one table and it will reflect to other nodes also.