Master-Master replication allows two MySQL servers to act as both master and slave simultaneously, enabling bidirectional data synchronization. This setup enhances high availability and distributes write loads across nodes. Below is a step-by-step guide to configure MySQL Master-Master replication.

Prerequisites

  • Two servers (Server A and Server B) with MySQL installed.
  • Network connectivity between both servers (port 3306 open).
  • Identical MySQL versions on both servers (recommended).

Adjust Firewall Rules

Allow traffic between both servers:

1
2
3
4
5
# On Server A
sudo ufw allow from server_b_ip to any port 3306

# On Server B
sudo ufw allow from server_a_ip to any port 3306

Install MySQL on Both Servers

Follow the same steps as your Master-Slave guide to install MySQL and set the root password on both servers:

1
2
3
sudo apt update
sudo apt-get install mysql-server -y
sudo mysql_secure_installation

Configure MySQL on Both Servers

Edit /etc/mysql/mysql.conf.d/mysqld.cnf on both servers:

Server A Configuration

1
2
3
4
5
6
7
8
[mysqld]
bind-address = server_a_ip
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
auto_increment_increment = 2  # Avoids primary key conflicts
auto_increment_offset = 1
binlog_format = ROW

Server B Configuration

1
2
3
4
5
6
7
8
[mysqld]
bind-address = server_b_ip
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
auto_increment_increment = 2
auto_increment_offset = 2
binlog_format = ROW

Restart MySQL on both servers:

1
sudo systemctl restart mysql

Create Replication Users

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

1
2
3
4
5
6
7
8
9
-- On Server A:
CREATE USER 'repl_user'@'server_b_ip' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'server_b_ip';
FLUSH PRIVILEGES;

-- On Server B:
CREATE USER 'repl_user'@'server_a_ip' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'server_a_ip';
FLUSH PRIVILEGES;

Initialize Data Sync

Dump the database on Server A:

1
mysqldump -u root -p --master-data --all-databases > masterdata.sql

Import the dump into Server B:

1
mysql -u root -p < masterdata.sql

Configure Replication on Both Servers

On Server A:

1
2
3
4
5
6
7
8
STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST = 'server_b_ip',
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',  -- From Server B's SHOW MASTER STATUS
    MASTER_LOG_POS = 154;
START SLAVE;

On Server B:

1
2
3
4
5
6
7
8
STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST = 'server_a_ip',
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',  -- From Server A's SHOW MASTER STATUS
    MASTER_LOG_POS = 154;
START SLAVE;

Verify Replication Status

Check replication health on both servers:

1
SHOW SLAVE STATUS \G;

Ensure both Slave_IO_Running and Slave_SQL_Running show Yes.

Test Bidirectional Replication

Test Writes on Server A:

1
2
3
4
CREATE DATABASE mm_test;
USE mm_test;
CREATE TABLE data (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(100));
INSERT INTO data (msg) VALUES ('Written on Server A');

Test Writes on Server B:

1
2
USE mm_test;
INSERT INTO data (msg) VALUES ('Written on Server B');

Verify Data on Both Servers:

1
SELECT * FROM mm_test.data;

Both servers should display:

1
2
3
4
5
6
+----+---------------------+
| id | msg                 |
+----+---------------------+
| 1  | Written on Server A |
| 2  | Written on Server B |
+----+---------------------+

Troubleshooting

  1. Duplicate Key Errors:

    Confirm auto_increment_increment and auto_increment_offset are set correctly.

  2. Replication Lag:

     Monitor Seconds_Behind_Master in SHOW SLAVE STATUS.

  3. Connection Issues:

     Verify firewall rules and MySQL user permissions.

Conclusion

MySQL Master-Master replication provides high availability and write scalability but requires careful conflict management. Use this setup for:

  • Failover scenarios (no single point of failure).
  • Distributed write loads (e.g., regional data centers).
  • Cross-region redundancy.

    • Always test conflict resolution strategies under load!

    Happy Coding

    ```