Mysql master-master replication hints

This is an expert-level guide for configuring two MySQL/MariaDB servers in a Master-Master replication topology. This setup is a common foundation for creating a high-availability (HA) database environment for VoIPmonitor.

Overview: What is Master-Master Replication?

In a Master-Master setup, two database servers are configured to be both a master and a slave to each other. Any write operation (like an `INSERT` or `UPDATE`) performed on Server 1 is automatically replicated to Server 2, and any write performed on Server 2 is replicated back to Server 1.

Why Use This Setup?

• High Availability: If one server fails, the other can immediately take over, as it already contains an up-to-date copy of all the data. This is often used with a load balancer like Keepalived or HAProxy.
• Read Load Balancing: You can direct read-heavy queries (like those from the GUI) to one server, while write-heavy operations (from the sniffer) go to the other.

Critical Concept: Auto-Increment Offsets To prevent both masters from trying to create a new row with the same `ID` (a primary key conflict), we must configure them to use different auto-increment values. One server will use odd numbers (1, 3, 5, ...) and the other will use even numbers (2, 4, 6, ...). This is achieved with the `auto_increment_increment` and `auto_increment_offset` settings.

Prerequisites

• Two servers with MariaDB or MySQL installed (this guide was tested with MariaDB 10.3 and MySQL 8.0).
• The VoIPmonitor database schema should be identical on both servers. It is best to start with a fresh dump from one server loaded onto the other.
• Network connectivity between the two servers on port 3306.

Step 1: Configure the First Master (Node 1)

Edit the MariaDB/MySQL configuration file (e.g., `/etc/mysql/mariadb.conf.d/50-server.cnf`) on your first server.

# Configuration for Node 1

[mysqld]
# --- General Settings ---
bind-address = 0.0.0.0

# --- Replication Settings ---
server-id      = 1
log_bin        = /var/lib/mysql/mariadb-bin
expire_logs_days = 10
slave-skip-errors = all # Use with caution, good for VoIPmonitor to avoid stopping on minor errors

# --- CRITICAL: Auto-Increment Settings for Master 1 (Odd Numbers) ---
auto_increment_increment = 2
auto_increment_offset    = 1

# ... (add any other of your standard database performance settings) ...

Step 2: Configure the Second Master (Node 2)

Edit the configuration file on your second server. It is nearly identical, but with a different `server-id` and `auto_increment_offset`.

# Configuration for Node 2

[mysqld]
# --- General Settings ---
bind-address = 0.0.0.0

# --- Replication Settings ---
server-id      = 2
log_bin        = /var/lib/mysql/mariadb-bin
expire_logs_days = 10
slave-skip-errors = all

# --- CRITICAL: Auto-Increment Settings for Master 2 (Even Numbers) ---
auto_increment_increment = 2
auto_increment_offset    = 2

# ... (add any other of your standard database performance settings) ...

After configuring both servers, restart the MariaDB/MySQL service on both machines.

Step 3: Create Replication Users

On each server, you need to create a dedicated user that the other server will use to connect for replication.

On Server 1, create the user for Server 2 to connect with

CREATE USER 'master1'@'%' IDENTIFIED BY 'your_replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'master1'@'%';

On Server 2, create the user for Server 1 to connect with

CREATE USER 'master2'@'%' IDENTIFIED BY 'your_replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'master2'@'%';

Step 4: Get Master Status from Both Nodes

Before configuring the slaves, you need to get the current binary log coordinates from each master. It's best practice to briefly lock tables to ensure you get a consistent snapshot.

On Server 1

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note down the `File` and `Position` values (e.g., `mariadb-bin.000004` and `52234945`). Keep this session open.

On Server 2

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note down the `File` and `Position` values from this server. Keep this session open as well.

Step 5: Configure Replication

Now, use the coordinates from each server to configure the other as its slave.

On Server 2's SQL prompt, use the coordinates from Server 1

CHANGE MASTER TO 
    MASTER_HOST='10.0.0.1', -- IP of Server 1
    MASTER_USER='master1',
    MASTER_PASSWORD='your_replication_password',
    MASTER_LOG_FILE='mariadb-bin.000004', -- File from Server 1
    MASTER_LOG_POS=52234945;              -- Position from Server 1

START SLAVE;

On Server 1's SQL prompt, use the coordinates from Server 2

CHANGE MASTER TO 
    MASTER_HOST='10.0.0.2', -- IP of Server 2
    MASTER_USER='master2',
    MASTER_PASSWORD='your_replication_password',
    MASTER_LOG_FILE='mariadb-bin.000005', -- File from Server 2
    MASTER_LOG_POS=12214311;              -- Position from Server 2

START SLAVE;

Finally, release the locks on both servers

UNLOCK TABLES;

Step 6: Verify Replication Status

Check the slave status on both servers to ensure replication is working correctly.

Run on either server

SHOW SLAVE STATUS\G

You will see a lot of output, but you must look for these two key lines:

*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
                   Last_Error: 

• `Slave_IO_Running: Yes`: Confirms that the slave can successfully connect to the master and read its binary logs.
• `Slave_SQL_Running: Yes`: Confirms that the slave can successfully apply the events from the master's binary log.
• `Seconds_Behind_Master`: Should be `0` or a very low number.
• `Last_Error`: Should be empty.

If both `_Running` states are "Yes" on both servers, your Master-Master replication is successfully configured.

AI Summary for RAG

Summary: This guide provides an expert-level tutorial for configuring a Master-Master replication setup for MySQL/MariaDB, a common foundation for a high-availability VoIPmonitor database. It explains the core concept, where two servers replicate to each other, and highlights the critical importance of using `auto_increment_increment` and `auto_increment_offset` to prevent primary key conflicts. The guide is structured as a six-step process: 1 & 2) Configuring each server's `my.cnf` with a unique `server-id` and the correct auto-increment offset. 3) Creating dedicated replication users with `REPLICATION SLAVE` privileges on each node. 4) Using `FLUSH TABLES WITH READ LOCK` and `SHOW MASTER STATUS` to get a consistent binary log position from both masters. 5) Using the `CHANGE MASTER TO` command on each server to point it to the other as its master, using the coordinates from the previous step. 6) Verifying the health of the replication by checking the output of `SHOW SLAVE STATUS\G` and ensuring both `Slave_IO_Running` and `Slave_SQL_Running` are "Yes". Keywords: high availability, ha, database, replication, master-master, mysql, mariadb, `auto_increment_increment`, `auto_increment_offset`, `server-id`, `log_bin`, `CHANGE MASTER TO`, `SHOW SLAVE STATUS`, `REPLICATION SLAVE` Key Questions:

• How do I set up Master-Master replication for my VoIPmonitor database?
• What is the purpose of `auto_increment_offset` in a multi-master setup?
• How can I prevent primary key conflicts in a replicated database?
• What are the steps to configure a server as a replication slave?
• How do I check if MySQL or MariaDB replication is working?
• What do `Slave_IO_Running` and `Slave_SQL_Running` mean?
• How to get the master log file and position for `CHANGE MASTER TO` command?