Setting Up Multi-Master Replication

Multi-Master Replication Setup

Write SQL queries to set up a multi-master replication environment.

Solution:

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

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

-- Configure Server 1 as slave of Server 2:
CHANGE MASTER TO 
    MASTER_HOST='server_2_ip',
    MASTER_PORT=3306,
    MASTER_USER='multi_master_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;
START SLAVE;

-- Configure Server 2 as slave of Server 1:
CHANGE MASTER TO 
    MASTER_HOST='server_1_ip',
    MASTER_PORT=3306,
    MASTER_USER='multi_master_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;
START SLAVE;

Explanation:

• Purpose of the Query:
• Establishes a setup where multiple servers can act as both masters and slaves, allowing writes to any node.
• Key Components:
• Binary logging and user creation on each server with necessary permissions.
• Each server is configured as a slave to the other, creating a circular replication.
• Real-World Application:
• Useful for high availability and write distribution in environments where any node might need to accept writes.

Notes:

• Multi-master setups can introduce complexities like conflict resolution.
• Ensure conflict resolution mechanisms are in place.

For more Practice: Solve these Related Problems:

• Write SQL queries to configure a three-node multi-master replication setup.
• Write SQL to set up multi-master replication with conflict detection mechanisms.

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

Previous MySQL Exercise: Check Replication Lag.
Next MySQL Exercise: Implement Galera Cluster for MySQL.