BLOG POSTS

MangoHost Blog / How to Set Up MySQL Master-Master Replication

How to Set Up MySQL Master-Master Replication

MySQL Master-Master replication is a sophisticated database setup where two MySQL servers act as both master and slave to each other, enabling bidirectional data synchronization and providing high availability with automatic failover capabilities. This configuration eliminates single points of failure, allows for load distribution across multiple servers, and ensures your applications can continue operating even if one database server goes down. In this guide, you’ll learn how to configure MySQL Master-Master replication from scratch, understand the underlying mechanics, troubleshoot common issues, and implement best practices for production environments.

How MySQL Master-Master Replication Works

Master-Master replication operates on the principle of circular replication, where each server maintains a binary log of all database changes and applies changes received from its replication partner. Unlike traditional Master-Slave setups, both servers can accept write operations, making them truly redundant.

The key components include:

Binary Logs: Record all database modifications in a binary format
Relay Logs: Store replicated events before applying them locally
Server IDs: Unique identifiers preventing infinite loops
Auto-increment Offsets: Prevent primary key conflicts on both servers

Feature	Master-Master	Master-Slave	Cluster (Galera)
Write Scalability	Limited (2 nodes)	Single node only	All nodes
Setup Complexity	Medium	Low	High
Conflict Resolution	Manual	N/A	Automatic
Failover Time	Seconds	Minutes	Seconds

Prerequisites and Environment Setup

Before diving into configuration, ensure you have two MySQL servers running identical versions. For this tutorial, we’ll use MySQL 8.0 on Ubuntu 20.04 systems. You’ll need root access to both servers and network connectivity between them.

Server specifications for optimal performance:

Minimum 4GB RAM per server
SSD storage for better I/O performance
Stable network connection with low latency
Synchronized system clocks (NTP recommended)

For production environments, consider using dedicated servers from MangoHost’s dedicated server solutions or high-performance VPS instances to ensure consistent performance and reliability.

Step-by-Step Implementation Guide

Step 1: Configure MySQL on Both Servers

First, modify the MySQL configuration file /etc/mysql/mysql.conf.d/mysqld.cnf on Server 1 (let’s call it mysql-master1 with IP 192.168.1.10):

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
auto-increment-increment = 2
auto-increment-offset = 1
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 0
bind-address = 0.0.0.0

# Performance optimizations
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
binlog_cache_size = 1M
max_binlog_size = 512M
expire_logs_days = 7

Configure Server 2 (mysql-master2 with IP 192.168.1.11) with these settings:

[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
auto-increment-increment = 2
auto-increment-offset = 2
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 0
bind-address = 0.0.0.0

# Performance optimizations
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
binlog_cache_size = 1M
max_binlog_size = 512M
expire_logs_days = 7

The auto-increment settings prevent primary key conflicts by ensuring Server 1 uses odd numbers (1,3,5…) and Server 2 uses even numbers (2,4,6…).

Step 2: Create Replication Users

Restart MySQL on both servers and create dedicated replication users. On Server 1:

sudo systemctl restart mysql
mysql -u root -p

CREATE USER 'replica'@'192.168.1.11' IDENTIFIED WITH mysql_native_password BY 'StrongReplicaPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.1.11';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

Note the binary log file name and position from the output. On Server 2:

sudo systemctl restart mysql
mysql -u root -p

CREATE USER 'replica'@'192.168.1.10' IDENTIFIED WITH mysql_native_password BY 'StrongReplicaPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.1.10';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

Step 3: Configure Replication Links

On Server 1, configure it to replicate from Server 2:

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='192.168.1.11',
    MASTER_USER='replica',
    MASTER_PASSWORD='StrongReplicaPassword123!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=157;
START SLAVE;

On Server 2, configure it to replicate from Server 1:

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replica',
    MASTER_PASSWORD='StrongReplicaPassword123!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=157;
START SLAVE;

Step 4: Verify Replication Status

Check replication health on both servers:

SHOW SLAVE STATUS\G

Look for these key indicators:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0 (or small number)
Last_Error: should be empty

Testing and Validation

Create a test database and table to verify bidirectional replication:

# On Server 1
CREATE DATABASE replication_test;
USE replication_test;
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    server_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_table (server_name) VALUES ('Server1');

Check if the database appears on Server 2, then insert data from Server 2:

# On Server 2
USE replication_test;
INSERT INTO test_table (server_name) VALUES ('Server2');
SELECT * FROM test_table;

You should see records from both servers, with Server 1 records having odd IDs and Server 2 records having even IDs.

Real-World Use Cases and Applications

Master-Master replication excels in several scenarios:

Geographic Distribution: Deploy servers in different data centers for regional failover
High Availability Web Applications: E-commerce sites requiring 99.9% uptime
Load Balancing: Distribute read/write operations across both servers
Development/Staging Environments: Maintain synchronized development databases

A practical example from a fintech company shows Master-Master replication reducing downtime from 4 hours annually to under 15 minutes, while handling 50,000+ transactions per hour across both nodes.

Performance Optimization and Monitoring

Monitor key performance metrics to ensure optimal replication performance:

Metric	Good Range	Warning Threshold	Action Required
Seconds Behind Master	0-1 seconds	> 5 seconds	> 30 seconds
Binlog Size Growth	< 100MB/hour	> 500MB/hour	> 1GB/hour
Slave Lag Events	0 per day	> 1 per day	> 5 per day

Implement monitoring with this script:

#!/bin/bash
# MySQL Master-Master Health Check Script

MYSQL_USER="monitor"
MYSQL_PASS="monitor_password"

check_replication_status() {
    local server=$1
    local result=$(mysql -h $server -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep -E "(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master)")
    
    echo "=== $server Status ==="
    echo "$result"
    echo ""
}

check_replication_status "192.168.1.10"
check_replication_status "192.168.1.11"

Common Issues and Troubleshooting

Here are the most frequent problems and their solutions:

Duplicate Key Errors

Despite auto-increment offsets, conflicts can still occur. Fix them with:

# Check for duplicate key errors
SHOW SLAVE STATUS\G

# Skip the problematic statement (use cautiously)
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

# Or set slave to ignore duplicate key errors
STOP SLAVE;
SET GLOBAL slave_exec_mode = 'IDEMPOTENT';
START SLAVE;

Replication Lag

When Seconds_Behind_Master increases significantly:

# Check for long-running queries
SHOW PROCESSLIST;

# Optimize slave performance
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

# Increase relay log space
SET GLOBAL max_relay_log_size = 1073741824; # 1GB

Connection Issues

Network connectivity problems manifest as:

# Check network connectivity
telnet 192.168.1.11 3306

# Reset slave connection
STOP SLAVE;
RESET SLAVE;
# Reconfigure with CHANGE MASTER TO...
START SLAVE;

Best Practices and Security Considerations

Follow these practices for production deployments:

SSL Encryption: Enable SSL for replication traffic
Firewall Rules: Restrict MySQL port access to replication partners only
Regular Backups: Replication is not a backup solution
Monitoring: Implement automated health checks and alerting
Testing: Regularly test failover procedures

Enable SSL replication:

# Generate SSL certificates first, then:
STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='192.168.1.11',
    MASTER_USER='replica',
    MASTER_PASSWORD='StrongReplicaPassword123!',
    MASTER_SSL=1,
    MASTER_SSL_CA='/etc/mysql/ssl/ca.pem',
    MASTER_SSL_CERT='/etc/mysql/ssl/client-cert.pem',
    MASTER_SSL_KEY='/etc/mysql/ssl/client-key.pem';
START SLAVE;

Advanced Configuration and Tuning

For high-performance environments, consider these advanced settings:

# In my.cnf for both servers
[mysqld]
# Reduce replication lag
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON

# Improve binlog performance  
binlog_group_commit_sync_delay = 1000
binlog_group_commit_sync_no_delay_count = 100

# Memory optimization
innodb_buffer_pool_size = 70% of available RAM
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M

Performance benchmarks show that properly tuned Master-Master setups can handle:

Up to 10,000 INSERT operations per second per server
Sub-second replication lag under normal conditions
Failover times under 30 seconds with proper monitoring

For additional details on MySQL replication, consult the official MySQL Replication Documentation and consider community resources like the Percona Database Blog for advanced optimization techniques.

Master-Master replication provides a robust foundation for high-availability database systems, but requires careful planning, monitoring, and maintenance to achieve optimal results in production environments.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.