Set Up MySQL Master-Master Replication on Ubuntu VPS

Understanding MySQL Master-Master Replication

MySQL master-master replication creates two-way synchronization between database servers. Both servers accept writes simultaneously, providing redundancy and load distribution across your database infrastructure.

Unlike traditional master-slave setups, both servers act as masters and slaves. Changes made to either database automatically replicate to the other.

This architecture works well for high-traffic VPS deployments where database availability matters. Your Ubuntu VPS needs specific networking and configuration changes to handle bidirectional replication safely.

We'll configure automatic conflict resolution and monitoring to prevent data inconsistencies.

Prerequisites and Server Preparation

You need two Ubuntu VPS instances running MySQL 8.0 or newer. Each server requires a unique server-id and proper network connectivity.

Update your system packages first:

sudo apt update
sudo apt upgrade -y

Install MySQL if not present:

sudo apt install mysql-server mysql-client -y
sudo systemctl enable mysql
sudo systemctl start mysql

Secure your MySQL installation on both servers:

sudo mysql_secure_installation

Configure firewall rules to allow MySQL traffic between servers. Replace the IP addresses with your actual server IPs:

sudo ufw allow from SERVER_2_IP to any port 3306
sudo ufw allow from SERVER_1_IP to any port 3306

Configuring MySQL Server Settings

Edit the MySQL configuration file on Server 1. Open /etc/mysql/mysql.conf.d/mysqld.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these settings under the [mysqld] section for Server 1:

server-id = 1
log-bin = mysql-bin
binlog-do-db = your_database_name
auto-increment-increment = 2
auto-increment-offset = 1
bind-address = 0.0.0.0

For Server 2, use these settings:

server-id = 2
log-bin = mysql-bin
binlog-do-db = your_database_name
auto-increment-increment = 2
auto-increment-offset = 2
bind-address = 0.0.0.0

The auto-increment settings prevent primary key conflicts. Server 1 generates odd numbers (1, 3, 5...) while Server 2 generates even numbers (2, 4, 6...).

Restart MySQL on both servers:

sudo systemctl restart mysql

For detailed database performance considerations, review our MySQL performance tuning guide.

Creating Replication Users

Connect to MySQL on Server 1 and create a replication user:

mysql -u root -p

Create the replication user with proper privileges:

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

Record the master status for configuration reference:

SHOW MASTER STATUS;

Note the File and Position values. You'll need these for the other server's configuration.

Repeat this process on Server 2, creating an identical replication user with the same credentials. This symmetric setup ensures both servers can authenticate replication connections.

Configuring MySQL Master-Master Replication

On Server 1, configure it to replicate from Server 2. Replace the IP address, log file, and position with Server 2's values:

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='SERVER_2_IP',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='strong_password_here',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;
START SLAVE;

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

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='SERVER_1_IP',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='strong_password_here',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;
START SLAVE;

Verify replication status on both servers:

SHOW SLAVE STATUS\G

Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes. Both must show "Yes" for proper replication.

Testing Replication Functionality

Create a test database on Server 1:

CREATE DATABASE replication_test;
USE replication_test;
CREATE TABLE test_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert test data on Server 1:

INSERT INTO test_data (message) VALUES ('Data from Server 1');

Check if the data appears on Server 2:

USE replication_test;
SELECT * FROM test_data;

Now insert data on Server 2:

INSERT INTO test_data (message) VALUES ('Data from Server 2');

Verify both records exist on Server 1. This confirms bidirectional replication works correctly.

Monitor the auto-increment behavior. Server 1 should assign odd IDs while Server 2 assigns even IDs, preventing conflicts.

Monitoring and Troubleshooting

Regular monitoring prevents replication issues from becoming data problems. Check slave status frequently:

SHOW SLAVE STATUS\G

Key metrics to monitor:

• Seconds_Behind_Master - Should remain low (under 5 seconds)
• Last_Error - Should be empty
• Slave_IO_Running - Must be "Yes"
• Slave_SQL_Running - Must be "Yes"

Common issues include network connectivity problems and binary log conflicts. If replication stops, check the error log:

sudo tail -f /var/log/mysql/error.log

For connection issues, verify firewall rules and MySQL user permissions. Network interruptions between servers can break replication and require manual restart.

Set up automated monitoring scripts to alert you when replication lag exceeds acceptable thresholds. Consider our automated backup strategies as an additional safety measure.

Security Hardening for Replication

Secure the replication connection using SSL encryption. Generate SSL certificates on both servers:

sudo mysql_ssl_rsa_setup --uid=mysql

Configure SSL in the master connection settings:

CHANGE MASTER TO
    MASTER_HOST='SERVER_IP',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='strong_password_here',
    MASTER_SSL=1,
    MASTER_SSL_CA='/var/lib/mysql/ca.pem',
    MASTER_SSL_CERT='/var/lib/mysql/client-cert.pem',
    MASTER_SSL_KEY='/var/lib/mysql/client-key.pem';

Restrict replication user access to specific IP addresses:

DROP USER 'replica_user'@'%';
CREATE USER 'replica_user'@'SERVER_1_IP' IDENTIFIED BY 'strong_password_here';
CREATE USER 'replica_user'@'SERVER_2_IP' IDENTIFIED BY 'strong_password_here';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'SERVER_1_IP';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'SERVER_2_IP';

Implement additional security measures following our VPS security hardening practices.

Frequently Asked Questions

What happens if one server goes offline during master-master replication?

The remaining server continues operating normally. When the offline server reconnects, it automatically catches up by processing missed binary log entries. No data loss occurs if both servers maintain their binary logs.

Can I add more than two servers to master-master replication?

MySQL master-master replication works best with exactly two servers. For multiple servers, consider master-slave replication with one master and multiple slaves, or MySQL Group Replication for more complex topologies.

How do I handle auto-increment conflicts in master-master replication?

Configure different auto-increment-offset values and set auto-increment-increment to the number of masters. For two servers, use increment=2 with offsets 1 and 2 to generate non-conflicting primary keys.

What's the recommended backup strategy for master-master replication?

Take regular backups from both servers since they might contain slightly different data due to replication timing. Use consistent snapshot methods like mysqldump with --master-data or MySQL Enterprise Backup.