Setting up MySQL circular replication in existing replication topology?

Question

I have the following MySQL replication topology set up for fault tolerance/disaster recovery with master server A and slaves B and C:

A replicates to B

A replicates to C

Both B and C are fully up to date with everything from A and have nothing in addition to that. i.e. there is nothing in B and C that is not in A.

I would like to introduce circular replication such that:

A replicates to B
B replicates to A

    A replicates to C

Changes made on B would replicate back to A. This would allow me to get more use out of a secondary server by spreading the load over two primary servers.

Primary use of each individual database on the servers would be limited to a specific server. e.g. Users of database bookingsystem always access via server B, users of database enquirysystem always use server A. There is no danger of having to deal with changes to the same database in two different locations. The replication is purely for fault tolerance should there be a problem with either server.

Can I simply set server A to slave from server B and have this working straight away, or is there further preparatory, or subsequent work needed?

Answer 1

STEP01) Add this to /etc/my.cnf on ServerB

[mysqld]
log-bin=mysql-bin

STEP02) Restart mysql on ServerB without replication running

service mysql restart --skip-slave-start

STEP03) On ServerB, run this in mysql client

SHOW MASTER STATUS;

You should see something like this:

+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 590591464 |              |                  |
+------------------+-----------+--------------+------------------+

STEP04) Record the Log File and Position from STEP03

STEP05) Restart mysql on ServerB as normal

service mysql restart

STEP06) Setup Replication User on ServerB

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ...

STEP07) On ServerA, run the following

CHANGE MASTER TO
MASTER_HOST='IP Address of ServerB',
MASTER_PORT=3306,
MASTER_USER='replication username',
MASTER_PASSWORD='replication password',
MASTER_LOG_FILE='File From STEP04',
MASTER_LOG_POS=Position From STEP04;

Suppose ServerB's IP address is 10.1.2.30, replication user/pass is 'repluser'/'replpass'

CHANGE MASTER TO
MASTER_HOST='10.1.2.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=590591464;

STEP08) Start Replication on ServerA by running

START SLAVE;

STEP09) Run SHOW SLAVE STATUS\G on ServerA

It should look something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.89.147
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000109
          Read_Master_Log_Pos: 636515875
               Relay_Log_File: relay-bin.000038
                Relay_Log_Pos: 636509693
        Relay_Master_Log_File: mysql-bin.000109
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_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: 636515875
              Relay_Log_Space: 636509885
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.01 sec)

If Slave_IO_Running and Slave_SQL_Running both say Yes, CONGRATULATIONS You've done it !!!

Give it a Try !!!