Mysql master-slave replication hints

This is a foundational guide for setting up a one-way, asynchronous Master-Slave (or Source-Replica) database replication for MySQL/MariaDB. This architecture is ideal for creating a read-only copy of your VoIPmonitor database for reporting, analysis, or as a simple hot backup.

Overview: What is Master-Slave Replication?

In this setup, you have two database servers:

• The Master (Source): This is your primary, production database server. All write operations (from your sniffer(s)) are performed here. The Master records every change it makes in a special file called the binary log (binlog).
• The Slave (Replica): This is a secondary server that connects to the Master, reads the binary log, and applies the exact same changes to its own copy of the data in the same order.

The result is a near real-time, read-only copy of your production database.

Use Cases

• Read Replicas: You can direct all heavy, read-only queries from the VoIPmonitor GUI to the slave, reducing the load on your primary master database.
• Hot Backups: The slave serves as a continuously updated backup. If the master fails, you can promote the slave to be the new master (though this failover is a manual process in this setup).

Step 1: Configure the Master (Source) Server

First, prepare your primary database server to produce the binary logs needed for replication.

A. Edit the Configuration File

Add the following settings to your master's `my.cnf` file (e.g., `/etc/mysql/mariadb.conf.d/50-server.cnf`).

# Configuration for the Master Server

[mysqld]
# --- General Settings ---
# The server must listen on a network interface, not just localhost
bind-address = 0.0.0.0

# --- Replication Settings ---
# A unique ID for this server in the replication topology (must be > 0)
server-id      = 1
# Enables binary logging and sets the location for the log files
log_bin        = /var/lib/mysql/mariadb-bin

After adding these lines, restart the MariaDB/MySQL service on the master server.

B. Create a Replication User

Create a dedicated user on the master that the slave will use to connect.

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

Step 2: Prepare the Slave (Replica) Server

Next, configure your secondary server.

A. Edit the Configuration File

Add the following to your slave's `my.cnf` file. Note that `log_bin` is not required here, but giving it a unique `server-id` is a best practice.

# Configuration for the Slave Server

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

# --- Replication Settings ---
# A unique ID, different from the master's
server-id      = 2
# Defines the location for the relay log, which temporarily stores changes from the master
relay_log      = /var/lib/mysql/relay-bin

Restart the MariaDB/MySQL service on the slave server after making the change.

Step 3: Create and Restore a Database Snapshot

To start replication, the slave must have an identical, point-in-time copy of the master's data.

A. On the Master: Get Status and Create Dump

This process ensures you capture the exact database state and the precise coordinates from which the slave should start replicating.

-- Step 3A.1: Lock all tables to prevent changes during the backup
FLUSH TABLES WITH READ LOCK;

-- Step 3A.2: Get the current binary log coordinates. Note these down carefully!
SHOW MASTER STATUS;

The output will look like this. You need the File and Position values.

+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000004 | 157      |              |                  |
+--------------------+----------+--------------+------------------+

While keeping the SQL session open (to hold the lock), open a new terminal window and create the database dump.

mysqldump -u root -p voipmonitor > /tmp/voipmonitor_snapshot.sql

After the dump is complete, you can release the lock on the master.

-- Step 3A.3: Unlock the tables
UNLOCK TABLES;

B. On the Slave: Copy and Restore Dump

Transfer the snapshot file to the slave server and import it.

# On your local machine or the master, copy the file to the slave
scp /tmp/voipmonitor_snapshot.sql user@slave_ip:/tmp/

# On the slave server, create the database and import the data
mysql -u root -p -e "CREATE DATABASE voipmonitor;"
mysql -u root -p voipmonitor < /tmp/voipmonitor_snapshot.sql

Step 4: Start Replication on the Slave

Now, tell the slave where to find the master and from which coordinates to begin reading.

Run these commands on the slave server's SQL prompt, using the `File` and `Position` you noted from the master

-- Modern MySQL/MariaDB syntax
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.0.10',   -- IP address of your Master server
    SOURCE_USER='replication_user',
    SOURCE_PASSWORD='your_strong_password',
    SOURCE_LOG_FILE='mariadb-bin.000004', -- File from Master's status
    SOURCE_LOG_POS=157;                  -- Position from Master's status

-- Older MySQL/MariaDB syntax
-- CHANGE MASTER TO MASTER_HOST=...

START REPLICA;
-- Older syntax: START SLAVE;

Step 5: Verify Replication Status

Check the replica's status to ensure everything is working correctly.

Run on the slave server

SHOW REPLICA STATUS\G

• (On older systems, use `SHOW SLAVE STATUS\G`)*

Look for these four key lines in the output:

*************************** 1. row ***************************
            Replica_IO_Running: Yes
           Replica_SQL_Running: Yes
         Seconds_Behind_Source: 0
                    Last_Error: 

• `Replica_IO_Running: Yes`: Confirms the slave can connect to the source and read its binary logs.
• `Replica_SQL_Running: Yes`: Confirms the slave can apply the events from the binary log to its own data.
• If both are "Yes" and `Last_Error` is empty, your replication is healthy.

AI Summary for RAG

Summary: This guide provides a comprehensive tutorial for setting up a one-way, Master-Slave (Source-Replica) database replication for MySQL/MariaDB, ideal for creating read replicas or hot backups of a VoIPmonitor database. It is structured as a five-step process. Step 1 details configuring the Master server by setting `server-id` and `log_bin` in `my.cnf` and creating a dedicated replication user. Step 2 covers configuring the Slave server with a unique `server-id` and `relay_log`. Step 3 explains the critical process of creating a consistent data snapshot by using `FLUSH TABLES WITH READ LOCK`, running `SHOW MASTER STATUS` to get binary log coordinates, creating a database dump with `mysqldump`, and then restoring it on the slave. Step 4 shows how to start the replication on the slave using the `CHANGE REPLICATION SOURCE TO` (or older `CHANGE MASTER TO`) command with the master's coordinates. Finally, Step 5 explains how to verify the health of the replication by checking the output of `SHOW REPLICA STATUS` and confirming that `Replica_IO_Running` and `Replica_SQL_Running` are both "Yes". Keywords: replication, master-slave, source-replica, database, mysql, mariadb, high availability, read replica, backup, `server-id`, `log_bin`, `mysqldump`, `CHANGE MASTER TO`, `CHANGE REplication SOURCE TO`, `SHOW SLAVE STATUS`, `SHOW REPLICA STATUS` Key Questions:

• How do I set up Master-Slave replication for my VoIPmonitor database?
• How can I create a read-only copy of my database?
• What is a binary log (`log_bin`) and why is it needed for replication?
• How do I get the master's log file and position for replication?
• What is the `CHANGE REPLICATION SOURCE TO` command?
• How can I check if MySQL replication is working correctly?
• What do `Replica_IO_Running` and `Replica_SQL_Running` mean?