MySQL Master-Master Replication Setup Tutorial: Complete Dual-Master Configuration with Automatic Failover on Ubuntu VPS in 2026

MySQL Master-Master Replication Prerequisites

You need two Ubuntu VPS instances with MySQL 8.0 installed and accessible via private network. Each server requires a unique server ID, proper hostname resolution, and sufficient disk space for binary logs.

Check your MySQL version first:

mysql --version

Verify network connectivity between both servers:

ping server2_ip_address
telnet server2_ip_address 3306

Your HostMyCode VPS instances should have at least 2GB RAM and dedicated CPU cores for stable replication performance.

Configure MySQL Binary Logging and Server IDs

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

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
log-slave-updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
binlog-do-db = your_database_name
expire_logs_days = 7
max_binlog_size = 100M

On Server 2, use these settings:

[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
log-slave-updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
binlog-do-db = your_database_name
expire_logs_days = 7
max_binlog_size = 100M

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

Create Replication User Accounts

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

mysql -u root -p

CREATE USER 'replicator'@'server2_ip' IDENTIFIED WITH mysql_native_password BY 'strong_password_here';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'server2_ip';
FLUSH PRIVILEGES;

Repeat the process on Server 2, but use Server 1's IP address:

CREATE USER 'replicator'@'server1_ip' IDENTIFIED WITH mysql_native_password BY 'strong_password_here';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'server1_ip';
FLUSH PRIVILEGES;

Test the connection from each server:

mysql -h server2_ip -u replicator -p

Initialize MySQL Master-Master Replication Setup

Get the binary log position from Server 1:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note the File name and Position values. Keep this session open to maintain the lock.

In a new terminal, configure Server 2 as a slave to Server 1:

mysql -u root -p

CHANGE MASTER TO
  MASTER_HOST='server1_ip',
  MASTER_USER='replicator',
  MASTER_PASSWORD='strong_password_here',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

Return to Server 1 and unlock tables:

UNLOCK TABLES;

Now get the binary log position from Server 2:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Configure Server 1 as a slave to Server 2:

CHANGE MASTER TO
  MASTER_HOST='server2_ip',
  MASTER_USER='replicator',
  MASTER_PASSWORD='strong_password_here',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

Unlock tables on Server 2:

UNLOCK TABLES;

Verify Replication Status and Performance

Check slave status on both servers:

SHOW SLAVE STATUS\G

Look for these critical indicators:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0
• Last_Errno: 0

Test bidirectional replication by creating a test table on Server 1:

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100));
INSERT INTO test_table (data) VALUES ('from server 1');

Verify the data appears on Server 2:

USE test_replication;
SELECT * FROM test_table;

Insert data from Server 2:

INSERT INTO test_table (data) VALUES ('from server 2');

Confirm both records exist on Server 1. The auto-increment settings should assign different ID values.

Configure Automatic Failover with MHA

Install MHA (Master High Availability) for automated failover management:

sudo apt update
sudo apt install libdbi-perl libdbd-mysql-perl libconfig-tiny-perl liblog-dispatch-perl libparallel-forkmanager-perl libtime-hires-perl

Download and install MHA Node on both MySQL servers:

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node_0.58-0_all.deb
sudo dpkg -i mha4mysql-node_0.58-0_all.deb

Create MHA configuration file (/etc/mha/app1.cnf):

[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
user=root
password=mysql_root_password
ssh_user=ubuntu
repl_user=replicator
repl_password=strong_password_here

[server1]
hostname=server1_ip
port=3306

[server2]
hostname=server2_ip
port=3306

Set up SSH key authentication between servers for passwordless access.

Implement Conflict Resolution Strategies

Create a conflict detection script (/usr/local/bin/check_conflicts.sh):

#!/bin/bash

mysql -u root -p$MYSQL_ROOT_PASSWORD -e "
SELECT 
  CHANNEL_NAME,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_worker 
WHERE LAST_ERROR_NUMBER != 0;"

if [ $? -ne 0 ]; then
  echo "Replication conflict detected"
  # Send alert via webhook or email
fi

Make the script executable:

sudo chmod +x /usr/local/bin/check_conflicts.sh

Schedule regular conflict checks:

crontab -e
# Add this line:
*/5 * * * * /usr/local/bin/check_conflicts.sh

For automatic conflict resolution, configure pt-heartbeat to detect replication lag:

sudo apt install percona-toolkit

pt-heartbeat --database=test_replication --create-table --user=root --password=mysql_root_password

Monitor Dual-Master Replication Health

Create a comprehensive monitoring script (/usr/local/bin/mysql_replication_monitor.sh):

#!/bin/bash

LOG_FILE="/var/log/mysql_replication_health.log"
DATE=$(date)

echo "[$DATE] Checking replication health" >> $LOG_FILE

# Check slave status
SLAVE_STATUS=$(mysql -u root -p$MYSQL_ROOT_PASSWORD -e "SHOW SLAVE STATUS\G" 2>/dev/null)

IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [[ "$IO_RUNNING" != "Yes" ]] || [[ "$SQL_RUNNING" != "Yes" ]]; then
  echo "[$DATE] CRITICAL: Replication stopped" >> $LOG_FILE
  # Send alert
fi

if [[ "$SECONDS_BEHIND" -gt 60 ]]; then
  echo "[$DATE] WARNING: Replication lag $SECONDS_BEHIND seconds" >> $LOG_FILE
fi

echo "[$DATE] IO: $IO_RUNNING, SQL: $SQL_RUNNING, Lag: ${SECONDS_BEHIND}s" >> $LOG_FILE

Set up system monitoring with basic alerting:

crontab -e
# Add:
*/2 * * * * /usr/local/bin/mysql_replication_monitor.sh

Monitor binary log disk usage:

mysql -u root -p -e "SHOW BINARY LOGS;"

Purge old binary logs when disk space runs low:

PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

Test Failover and Recovery Procedures

Simulate a failure scenario by stopping MySQL on Server 1:

sudo systemctl stop mysql

Verify that Server 2 continues accepting writes:

mysql -h server2_ip -u root -p
INSERT INTO test_replication.test_table (data) VALUES ('failover test');

Restart Server 1 and check if it catches up:

sudo systemctl start mysql
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master

The database high availability architecture should handle this without issue.

For planned maintenance, gracefully promote one server:

# On the server to promote
STOP SLAVE;
RESET SLAVE ALL;

# On other servers
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='new_master_ip';
START SLAVE;

Performance Tuning for Dual-Master Configuration

Optimize MySQL for replication performance by adjusting these parameters:

# In /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
slave_parallel_workers = 4
slave_preserve_commit_order = 1
relay_log_recovery = 1
innodb_buffer_pool_size = 1G  # 70% of available RAM
max_connections = 200

Monitor replication performance:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Com_insert%';"
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Binlog%';"

The database performance tuning guide provides additional optimization strategies.

Consider using HostMyCode managed VPS hosting for automated monitoring and maintenance of your dual-master configuration.

Frequently Asked Questions

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

Configure different auto-increment settings on each server. Use auto-increment-increment = 2 with auto-increment-offset = 1 on the first server and auto-increment-offset = 2 on the second server. This ensures unique ID generation.

What happens if both masters go down simultaneously?

With proper binary log retention and consistent backups, you can restore from the most recent backup and replay binary logs to recover data. Always maintain regular backups and test recovery procedures.

How can I monitor replication lag in real-time?

Use the SHOW SLAVE STATUS command and monitor the Seconds_Behind_Master value. Set up automated alerts when lag exceeds acceptable thresholds, typically 30-60 seconds for most applications.

Should I use statement-based or row-based replication?

Use row-based replication (binlog-format = ROW) for master-master setups. It provides more consistent results and better conflict detection compared to statement-based replication.

How do I safely restart replication after a conflict?

First identify the conflicting transaction using SHOW SLAVE STATUS. Skip the problematic transaction with SET GLOBAL sql_slave_skip_counter=1, then START SLAVE. Always investigate and resolve the underlying cause of conflicts.