Bidirectional Replication - in MySQL

Introduction

I know that their are somany articles and guideline are available in the internet regarding to the implementation of Master - Slave Replication. In Master - Slave replication, Changes in master will reflect in Slave. but any changes made in the slave Database will not reflect back in Master , this article will help you to implement Bidirectional Replication.( ie., changes made either in Master or in Slave both will reflect in Both the servers.

This article includes :

1. Configuring Master for Replication
2. Configuring Slave for Replication
3. Establish Master - Slave Replication
4. Establish Bidirectional Replication

Background

You can refer this article by Aadhar Joshi for implementing Master slave replication or else you can follow the simple steps below. :

Consider the following :

• configuring the master on system A(192.168.1.30)
• configuring the slave on system B(192.168.1.29)
• We can achieve this using the following steps

Step 1 : setup the master in system A

• open mysql configuration file in master system , default location for this file is C:\Program Files\MySQL\MySQL Server 5.0\my.ini

• In SERVER SECTION replace [mysqld] with [mysqld]

  server-id=1
  
  	log-bin=mysql-bin
  
  	innodb_flush_log_at_trx_commit=1
  
  	sync_binlog=1
  
  	binlog_do_db= demo
  
  	port=3306

   

• Then save the configuration file and then close it

• Restart the MySQL server to reflect the changes.

Step 2 : setup the Slave in system B :

• open mysql configuration file in slave system , default location for this file is C:\Program Files\MySQL\MySQL Server 5.0\my.ini
• In SERVER SECTION replace [mysqld] with

[mysqld]

server-id=2

log-bin=mysql-bin

innodb_flush_log_at_trx_commit=1

sync_binlog=1

• Then save the configuration file and then close it
• Restart the MySQL server to reflect the changes.

where :
server-id=1 → Unique identifier for the server. It must be different for master and slave
log-bin=mysql-bin → For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1.
binlog_do_db= demo → database to replicate
port=3306 → default port number for the local host.

Step 3 : creating replication user on master

1. Open mysql command prompt

2. mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY 'replication';

3. Now we use the ‘show master status’ statement to determine the current binary log file name and position. Remember this carefully! In our example we get the following output.

4. <font size="3" style="font-size: 13pt">mysql> show master status;</font>

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000153 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5. Follow up with: mysql> FLUSH PRIVILEGES;

6. select database : mysql> USE newdatabase;

7. lock the database to prevent any new changes: FLUSH TABLES WITH READ LOCK;

Step 4: Connect from the slave to the master :

1. Open mysql command prompt

2. Stop the slave : Stop slave;

3. Give the command

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.30',
-> MASTER_USER='replicator',
-> MASTER_PASSWORD='replication',
-> MASTER_LOG_FILE='mysql-bin.000153',
-> MASTER_LOG_POS=106;

4. Restart the slave to begin the replication : Start Slave;

Upto this same as Master - Slave Replication.

Directions to implement bidirectional Replication

Step 1 : creating replication Master_user on Slave

1. Open mysql command prompt slave (192.168.1.29)

2. mysql> GRANT REPLICATION SLAVE ON *.* TO 'master_replicator'@'%' IDENTIFIED BY 'master';

3. Now we use the ‘show master status’ statement to determine the current binary log file name and position. Remember this carefully! In our example we get the following output.

4. <font size="3" style="font-size: 13pt">mysql> show master status;</font>

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000153 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5. Follow up with: mysql> FLUSH PRIVILEGES;

6. select database : mysql> USE newdatabase;

7. lock the database to prevent any new changes: FLUSH TABLES WITH READ LOCK;

Step 2: Connect from the master_user to the slave (192.168.1.30):

1. Open mysql command prompt in Master

2. Stop the slave : Stop slave;

3. Give the command

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.29',
-> MASTER_USER='master_replicator',
-> MASTER_PASSWORD='master',
-> MASTER_LOG_FILE='mysql-bin.000153',
-> MASTER_LOG_POS=106;

4. Restart the slave to begin the replication : Start Slave;

Now Give the command for checking the status in bot systems :

SHOW SLAVE STATUS\G;

You Can see now for system 192.168.1.30 :

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.1.29

                Master_User: slave_user

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000013

        Read_Master_Log_Pos: 98

             Relay_Log_File: PC-relay-bin.000074

              Relay_Log_Pos: 235

      Relay_Master_Log_File: mysql-bin.000013

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB: demo

        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)

ERROR:

No query specified

Points of Interest

Now you can do Database operations like insertion deletion updation drop truncation etc in both databases and check whether it is working or not.

Important points to be remember:

1. server_Id must be different for Master and Slave

2. Veryfy the Log files are correctly mentioned

3. check after giving the command

SHOW SLAVE STATUS\G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_IO_State: Waiting for master to send event