MysqlHow-ToIntermediate · 4 min read

How to Set Up Master Master Replication in MySQL

To set up master master replication in MySQL, configure two MySQL servers as masters by enabling binary logging and setting unique server IDs on both. Then, create replication users and configure each server to replicate from the other using CHANGE MASTER TO commands, enabling auto_increment_increment and auto_increment_offset to avoid key conflicts.

📐

Syntax

Master master replication requires configuring each server as both a master and a slave. Key commands include:

server-id: Unique ID for each server.
log_bin: Enables binary logging.
auto_increment_increment and auto_increment_offset: Prevent auto-increment key collisions.
CHANGE MASTER TO: Sets replication source details.
START SLAVE: Starts replication process.

sql

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=row
auto_increment_increment=2
auto_increment_offset=1

-- On second server:
[mysqld]
server-id=2
log_bin=mysql-bin
binlog_format=row
auto_increment_increment=2
auto_increment_offset=2

-- Replication setup commands on each server:
CHANGE MASTER TO MASTER_HOST='other_server_ip', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;

💻

Example

This example shows how to configure two MySQL servers (Server1 and Server2) for master master replication. Each server is set with unique server-id and auto-increment settings. Then, replication users are created, and each server is configured to replicate from the other.

sql

-- On Server1 (IP: 192.168.1.1)
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=row
auto_increment_increment=2
auto_increment_offset=1

-- Create replication user on Server1
CREATE USER 'repl_user'@'192.168.1.2' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.2';
FLUSH PRIVILEGES;

-- On Server2 (IP: 192.168.1.2)
[mysqld]
server-id=2
log_bin=mysql-bin
binlog_format=row
auto_increment_increment=2
auto_increment_offset=2

-- Create replication user on Server2
CREATE USER 'repl_user'@'192.168.1.1' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.1';
FLUSH PRIVILEGES;

-- On Server1, configure slave to Server2
CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;

-- On Server2, configure slave to Server1
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;

Output

Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Slave_IO_Running: Yes Slave_SQL_Running: Yes

⚠️

Common Pitfalls

Common mistakes when setting up master master replication include:

Using the same server-id on both servers, causing replication conflicts.
Not setting auto_increment_increment and auto_increment_offset, leading to duplicate primary keys.
Forgetting to create replication users with proper privileges.
Not starting the slave threads after configuration.
Incorrect MASTER_LOG_FILE or MASTER_LOG_POS values causing replication errors.

sql

-- Wrong: Same server-id on both servers
[mysqld]
server-id=1

-- Right: Unique server-id on each server
-- On Server1
[mysqld]
server-id=1

-- On Server2
[mysqld]
server-id=2

📊

Quick Reference

Summary tips for master master replication setup:

Set unique server-id on each server.
Enable log_bin and use binlog_format=row for safer replication.
Configure auto_increment_increment and auto_increment_offset to avoid key conflicts.
Create replication users with REPLICATION SLAVE privileges.
Use CHANGE MASTER TO to point each server to the other.
Start replication with START SLAVE and monitor with SHOW SLAVE STATUS.

✅

Key Takeaways

Always assign unique server IDs to each MySQL server in master master replication.

Enable binary logging and set binlog_format to ROW for reliable replication.

Configure auto_increment_increment and auto_increment_offset to prevent key collisions.

Create replication users with proper privileges on both servers.

Use CHANGE MASTER TO and START SLAVE commands to establish and start replication.