Deploying MySQL Primary–Replica Replication on Ubuntu 24.04 (On-Premises Guide)

April 11, 2026

Deploying MySQL Primary–Replica Replication on Ubuntu 24.04 (On-Premises Guide)

Setting up MySQL replication (Primary → Replica) on Ubuntu 24.04 is a solid strategy for improving high availability, 

redundancy, backup readiness, and read scalability in on-premises environments.

This guide walks through a production-oriented setup of MySQL replication, along with practical DBA considerations

and a custom diagnostic script for monitoring replication health.

Architecture Overview

Our replication topology follows a standard Primary → Replica architecture:

• Primary (Master): Handles write operations

• Replica (Slave): Read-only node that continuously replicates from the primary

Components Configured:

• Binary logging on Primary

• Unique server IDs

• Dedicated replication user

• Initial data synchronization

• SSL-secured replication transport

• Replica read-only enforcement

Prerequisites: Configure SCP/SSH Access Between Nodes

Before beginning replication setup, ensure secure file transfer is configured between both servers.

Recommended: SSH Key Authentication

On Source Server (epc-mysql-rep-node1):

Check for existing SSH key:

ls ~/.ssh/id_rsa.pub

If not present, generate one:

ssh-keygen -t ed25519

Copy Key to Target Server (epc-mysql-rep-node2):

ssh-copy-id root@epc-mysql-rep-node2

If automated copy fails:

cat ~/.ssh/id_ed25519.pub

Copy the output manually on the replica node into:

/root/.ssh/authorized_keys

Step 1 – Install MySQL on Both Servers

Install MySQL packages:

sudo apt update

sudo apt install mysql-server -y

Verify installation:

mysql --version

Step 2 – Secure MySQL Installation

Run the security hardening wizard:

sudo mysql_secure_installation

Then login:

sudo mysql

Step 3 – Configure Primary Server

Edit MySQL configuration:

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

Add/modify:

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

bind-address = 0.0.0.0

# Optional database-specific replication filter

binlog_do_db = mydb

Restart MySQL:

sudo systemctl restart mysql

Step 4 – Create Replication User

On the primary server:

CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPassword!';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

FLUSH PRIVILEGES;

Step 5 – Obtain Binary Log Coordinates

Lock database for consistent snapshot:

FLUSH TABLES WITH READ LOCK;

Retrieve current binary log position:

SHOW MASTER STATUS;

Example output:

File: mysql-bin.000001

Position: 157

Important: Keep this session open until backup completes.

Step 6 – Export Primary Database

In a separate terminal:

mysqldump -u root -p --all-databases --master-data > dump.sql

Transfer backup to replica:

scp dump.sql user@replica-ip:/tmp/

Unlock tables:

UNLOCK TABLES;

Step 7 – Configure Replica Server

Edit replica MySQL config:

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

Add:

server-id = 2

relay-log = /var/log/mysql/mysql-relay-bin.log

read_only = 1

super_read_only = 1

Restart MySQL:

sudo systemctl restart mysql

Step 8 – Import Backup Into Replica

mysql -u root -p < /tmp/dump.sql

Step 9 – Configure SSL-Secured Replication

Enable secure transport on source:

SHOW VARIABLES LIKE 'require_secure_transport';

SET GLOBAL require_secure_transport = ON;

Configure replica replication source:

CHANGE MASTER TO

    MASTER_HOST='PRIMARY_IP',

    MASTER_USER='repl',

    MASTER_PASSWORD='StrongPassword!',

    MASTER_LOG_FILE='mysql-bin.000001',

    MASTER_LOG_POS=157,

    SOURCE_SSL=1;

Step 10 – Start Replication

START REPLICA;

Step 11 – Verify Replication Status

Run:

SHOW REPLICA STATUS\G

Healthy output should show:

Replica_IO_Running: Yes

Replica_SQL_Running: Yes

Seconds_Behind_Source: 0

Recommended Production Enhancements

1. Enable GTID-Based Replication

GTID simplifies failover and recovery.

gtid_mode = ON

enforce_gtid_consistency = ON

2. Use Row-Based Replication

Recommended for consistency.

binlog_format = ROW

3. Enforce Replica Read-Only

Prevent accidental writes:

SET GLOBAL super_read_only = 1;

4. Restrict Firewall Access

Allow only replica host:

ufw allow from <replica-ip> to any port 3306

5. Monitor Replication Health

Use:

SHOW REPLICA STATUS\G

regularly or automate via script.

Custom MySQL Replication Diagnostic Script

To streamline replication checks, I developed the following Bash diagnostic utility.

This script provides at-a-glance visibility into:

• Server identity

• Replica/Primary role detection

• Replication thread status

• Upstream source details

• SQL/IO errors

• Relay log status

• Active process list

• Health summary

Replication Diagnostic Script

mysql_repl_diag.sh

#!/bin/bash

# ==============================

# CONFIG (edit if needed)

# ==============================

read -p "Enter MySQL User: " MYSQL_USER

read -s -p "Enter MySQL Password: " MYSQL_PASS

echo ""

read -p "Enter MySQL Port [3306]: " MYSQL_PORT

MYSQL_PORT=${MYSQL_PORT:-3306}

read -p "Enter MySQL Host/IP [localhost]: " MYSQL_HOST

MYSQL_HOST=${MYSQL_HOST:-localhost}

echo ""

export MYSQL_PWD="${MYSQL_PASS}"

MYSQL_CMD="mysql -u${MYSQL_USER} -h${MYSQL_HOST} -P${MYSQL_PORT} -e"

echo "========================================"

echo " MySQL Replication Diagnostic Report"

echo "========================================"

echo ""

# ==============================

# SERVER IDENTITY

# ==============================

echo "- SERVER IDENTITY -"

$MYSQL_CMD "

SELECT 

    @@hostname        AS hostname,

    @@server_id       AS server_id,

    @@version         AS version,

    @@read_only       AS read_only,

    @@super_read_only AS super_read_only,

    @@log_bin         AS binlog_enabled;

"

# ==============================

# ROLE DETECTION

# ==============================

echo ""

echo "- ROLE DETECTION -"

$MYSQL_CMD "

SELECT

    CASE

        WHEN EXISTS (

            SELECT 1

            FROM performance_schema.replication_connection_status

            WHERE SERVICE_STATE='ON'

        ) THEN 'REPLICA'

        WHEN @@log_bin = 1 THEN 'PRIMARY'

        ELSE 'STANDALONE'

    END AS role;

"

# ==============================

# REPLICA STATUS (CRITICAL)

# ==============================

echo ""

echo "- SHOW REPLICA STATUS -"

export MYSQL_PWD="${MYSQL_PASS}"

mysql -u${MYSQL_USER} -h${MYSQL_HOST} -P${MYSQL_PORT} -e "SHOW REPLICA STATUS\G"

# ==============================

# UPSTREAM INFO

# ==============================

echo ""

echo "- UPSTREAM -"

$MYSQL_CMD "

SELECT 

    c.CHANNEL_NAME,

    c.HOST,

    c.PORT,

    c.USER,

    s.SERVICE_STATE

FROM performance_schema.replication_connection_configuration c

JOIN performance_schema.replication_connection_status s

    ON c.CHANNEL_NAME = s.CHANNEL_NAME;

"

# ==============================

# SQL THREAD + ERRORS

# ==============================

echo ""

echo "- SQL THREAD STATUS -"

$MYSQL_CMD "

SELECT 

    CHANNEL_NAME,

    SERVICE_STATE

FROM performance_schema.replication_applier_status;

"

echo ""

echo "- SQL ERRORS -"

$MYSQL_CMD "

SELECT 

    CHANNEL_NAME,

    LAST_ERROR_NUMBER,

    LAST_ERROR_MESSAGE

FROM performance_schema.replication_applier_status_by_worker

WHERE LAST_ERROR_NUMBER != 0;

"

# ==============================

# RELAY + BINLOG

# ==============================

echo ""

echo "- RELAY LOG -"

$MYSQL_CMD "SHOW VARIABLES LIKE 'relay_log%';"

echo ""

echo "- BINLOG -"

$MYSQL_CMD "SHOW VARIABLES LIKE 'log_bin';"

$MYSQL_CMD "SHOW VARIABLES LIKE 'log_slave_updates';"

# ==============================

# PROCESSLIST

# ==============================

echo ""

echo "- ACTIVE PROCESSLIST -"

$MYSQL_CMD "

SELECT 

    ID, USER, HOST, DB, COMMAND, TIME, STATE

FROM information_schema.processlist

WHERE COMMAND NOT IN ('Sleep');

"

# ==============================

# RED FLAGS

# ==============================

echo ""

echo "- HEALTH SUMMARY -"

$MYSQL_CMD "

SELECT 

    CASE 

        WHEN EXISTS (

            SELECT 1 

            FROM performance_schema.replication_connection_status 

            WHERE SERVICE_STATE != 'ON'

        ) THEN 'IO THREAD ISSUE'

        ELSE 'IO THREAD OK'

    END AS io_health,

    CASE 

        WHEN EXISTS (

            SELECT 1 

            FROM performance_schema.replication_applier_status 

            WHERE SERVICE_STATE != 'ON'

        ) THEN 'SQL THREAD ISSUE'

        ELSE 'SQL THREAD OK'

    END AS sql_health;

"

echo ""

echo "========================================"

echo " END OF REPORT"

echo "========================================"

Conclusion

Implementing MySQL replication on Ubuntu 24.04 provides a robust foundation for:

• Disaster recovery

• Backup offloading

• Reporting workloads

• Read scaling

• High availability architecture

Combined with proper monitoring, security hardening, and diagnostic tooling, this creates a production-ready on-prem 

MySQL replication environment.

Search This Blog

Kaysar Iqbal

Deploying MySQL Primary–Replica Replication on Ubuntu 24.04 (On-Premises Guide)

Comments

Post a Comment

Popular Posts

Scripts to take MySQL 8.* full and incremental backups using mysqldump

Deploy MySQL and Xtrabackup in Docker

Recover a MySQL database from backups taken by mysqldump

PostgreSQL 13 logical replication configuration

Zero-Downtime MySQL to Aurora Migration: An Enterprise-Grade AWS DMS Runbook