w3resource

Setting Up Circular Replication


Set Up Circular Replication

Write MySQL queries to configure circular replication between three MySQL servers.

Solution:

-- On Server A:
SET GLOBAL binlog_format = 'ROW';
CREATE USER 'circ_rep_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'circ_rep_user'@'%';
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

-- On Server B (similar for Server C):
SET GLOBAL binlog_format = 'ROW';
CREATE USER 'circ_rep_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'circ_rep_user'@'%';
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

-- Configuration for each server pointing to the next:
-- On Server A:
CHANGE MASTER TO 
    MASTER_HOST='server_B_ip',
    MASTER_PORT=3306,
    MASTER_USER='circ_rep_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- On Server B:
CHANGE MASTER TO 
    MASTER_HOST='server_C_ip',
    MASTER_PORT=3306,
    MASTER_USER='circ_rep_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- On Server C:
CHANGE MASTER TO 
    MASTER_HOST='server_A_ip',
    MASTER_PORT=3306,
    MASTER_USER='circ_rep_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- Start slaves on all servers:
START SLAVE;

Explanation:

  • Purpose of the Query:
    • Sets up a circular replication where each server replicates to the next in the chain.
  • Key Components:
    • Binary logging setup, replication user creation, and master configuration for circular flow.
  • Real-World Application:
    • Provides redundancy and can distribute write operations, though it increases complexity.

Notes:

  • Circular replication requires careful management to avoid loops or conflicts.

For more Practice: Solve these Related Problems:

  • Write MySQL to establish circular replication with an additional server, creating a four-server loop.
  • Write MySQL to configure circular replication where each server has a unique replication user.

Go to:


PREV : Check Replication Consistency.
NEXT : Handle Replication Errors.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.