Master to Master Replication in MySQL: Configuration Setup

by | May 15, 2025 | Blog, MySQL

Master to Master Replication in MySQL: Configuration Setup

Master-to-master replication (also known as bidirectional replication) is a setup where two MySQL servers act as both source and replica to each other. This configuration enables read/write operations on both nodes, ensuring high availability and redundancy. However, it requires careful configuration to avoid conflicts.

Before implementing master-to-master replication, consider if it is the best approach for your application.

Master-to-Master Replication Advantages and Disadvantages

There are advantages and disadvantages to master-to-master replication.

Advantages

  • High Availability: If one master goes down, the other can continue handling reads and writes. This minimizes downtime and helps maintain service continuity.
  • Load Balancing: Read and write traffic can be distributed across both nodes. This is useful in high-traffic applications where a single master might struggle under load.
  • Redundancy and Failover: Data is continuously synced between both nodes. In the event of hardware failure, the second master already has an up-to-date copy.
  • Geographic Distribution: You can place each master in different geographic regions to serve local traffic more quickly while maintaining real-time synchronization.

Disadvantages

The major drawback of master-to-master replication is that MySQL doesn’t resolve data conflicts that can occur, such as ERROR 1032 or 1062.

If you can answer yes to any of the following statements, do not move forward with master-to-master replication. Explore the possibility of asynchronous replication or two-node group replication. 

  • You need strict consistency and cannot tolerate even minor replication delays.
  • Your app frequently updates the same data from multiple nodes; the risk of conflict is too high.
  • You want a simple, low-maintenance setup.

If master-to-master replication is suitable for your application, follow these steps to configure it and minimize data conflicts.

Before starting, make sure:

  • Both MySQL servers are installed and running.
  • The servers can communicate over the network.
  • Both servers have unique `server-id` values in their configuration files.

Steps to Configure Master-to-Master Replication in MySQL

Step 1. Configure my.cnf (or my.ini)

On Server A (server-id = 1):
[mysqld]
server-id=1
gtid-mode = ON
enforce-gtid-consistency
auto_increment_increment=2
auto_increment_offset=1

On Server B (server-id = 2):
[mysqld]
server-id=2
gtid-mode = ON
enforce-gtid-consistency
auto_increment_increment=2
auto_increment_offset=2

GTID replication is better suited for complex topologies and multi-source replication. Each transaction is assigned a unique GTID (similar to a UUID:transaction_number) and tracked throughout the replication chain. The replica knows which transactions it has already applied and picks up only new ones, preventing duplicate transaction applications.

Likewise, the auto_increment_increment and auto_increment_offset settings prevent duplicate primary keys when both servers write data with `AUTO_INCREMENT` columns.

Restart MySQL on both servers after making changes.

Step 2. Create Replication Users

On both servers, create a replication user for the other server to connect:

On Server A:

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

On Server B:

Do the same:

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Step 3. Sync Databases

To avoid data mismatch, stop writes on both servers and dump the database from one server:

mysqldump -u root -p --databases replication_db > db_dump.sql

Import this dump into the second server:

mysql -u root -p < db_dump.sql

Step 4: Configure GTID Replication

Now, set up the replication links:

On Server A:

CHANGE MASTER TO
MASTER_HOST='serverB_ip',
MASTER_USER='repl',
MASTER_PORT ='port',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION = 1;

# Or from MySQL 8.0.23:

CHANGE REPLICATION SOURCE TO
SOURCE_HOST ='serverB_ip',
SOURCE_PORT = 'port',
SOURCE_USER = 'repl',
SOURCE_PASSWORD ='password',
SOURCE_AUTO_POSITION = 1;

START SLAVE;

On Server B:

CHANGE MASTER TO
MASTER_HOST='serverA_ip',
MASTER_PORT ='port',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION = 1;

# Or from MySQL 8.0.23:

CHANGE REPLICATION SOURCE TO
SOURCE_HOST ='serverA_ip',
SOURCE_PORT = 'port',
SOURCE_USER = 'repl',
SOURCE_PASSWORD ='password',
SOURCE_AUTO_POSITION = 1;

START SLAVE;

Step 5. Verify Replication

Check the slave status on both servers:

SHOW SLAVE STATUS\G

Ensure Slave_IO_Running and Slave_SQL_Running both show “Yes.”

Tips and Considerations

  • Avoid writing to duplicate rows or tables on both servers at the same time to prevent conflicts.
  • Use conflict detection tools or third-party replication managers if needed.
  • Regularly monitor replication status and logs.

Need expert help setting up or managing MySQL replication?

Our team at Virtual-DBA specializes in MySQL configuration, optimization, and support. Whether you need help with Master-to-Master Replication, performance tuning, or ongoing database management, we’re here to help. Learn more about our MySQL services.