Setting Up Circular Replication

Last update on February 22 2025 12:59:58 (UTC/GMT +8 hours)

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.

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

Previous MySQL Exercise: Check Replication Consistency.
Next MySQL Exercise: Handle Replication Errors.