MySQL 9 Replication: Set Up Primary-Replica on Ubuntu 24.04 (2026)

Key Takeaways

• GTID is the standard: MySQL 9 with gtid_mode=ON and enforce_gtid_consistency=ON is the recommended replication setup. GTID assigns a unique ID to every committed transaction — no more binary log coordinates.
• Dedicated replication user: Create 'replicator'@'%' with only REPLICATION SLAVE privilege. Never use root for replication.
• Seconds_Behind_Source = 0 is healthy: Monitor this in SHOW REPLICA STATUS. Sustained lag means the replica can’t keep up with write load — investigate before it causes problems.
• Replicas can serve reads: Point read-heavy application queries at the replica to distribute load. Use innodb_read_only=ON to prevent accidental writes to the replica.

---

Introduction

Direct Answer: How do I set up MySQL 9 replication on Ubuntu 24.04 in 2026?

On the primary server: enable binary logging in /etc/mysql/mysql.conf.d/mysqld.cnf with server-id=1, log-bin=mysql-bin, gtid_mode=ON, enforce_gtid_consistency=ON, then restart MySQL and create a replication user: CREATE USER 'replicator'@'REPLICA_IP' IDENTIFIED BY 'strong_password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'REPLICA_IP'; FLUSH PRIVILEGES;. On the replica server: set server-id=2 (must be unique), enable the same GTID settings, restart MySQL, then run: CHANGE REPLICATION SOURCE TO SOURCE_HOST='PRIMARY_IP', SOURCE_USER='replicator', SOURCE_PASSWORD='strong_password', SOURCE_AUTO_POSITION=1; START REPLICA;. Verify with SHOW REPLICA STATUS\G — both Replica_IO_Running and Replica_SQL_Running must show Yes and Seconds_Behind_Source should reach 0.

---

Architecture Overview

┌─────────────────────────────────┐    ┌─────────────────────────────────┐
│  PRIMARY SERVER                  │    │  REPLICA SERVER                  │
│  IP: 10.0.0.1                    │    │  IP: 10.0.0.2                    │
│  MySQL 9.0                       │    │  MySQL 9.0                       │
│  server-id = 1                   │    │  server-id = 2                   │
│  All writes accepted             │───▶│  Read-only (innodb_read_only=ON) │
│  Writes binary log               │    │  Applies binary log events       │
└─────────────────────────────────┘    └─────────────────────────────────┘
      │                                       │
      ▼                                       ▼
 App writes →                           App reads →
 INSERT, UPDATE, DELETE                 SELECT queries

This guide uses two Ubuntu 24.04 servers. Replace 10.0.0.1 with your primary IP and 10.0.0.2 with your replica IP throughout.

---

Part 1: Primary Server Configuration

Ensure MySQL 9 is installed first (see How to Install MySQL 9 on Ubuntu 24.04).

# ── ON THE PRIMARY SERVER (10.0.0.1) ─────────────────────────────────────
sudo tee /etc/mysql/mysql.conf.d/replication-primary.cnf << 'EOF'
[mysqld]
# Unique server ID — must be different on every server in the topology
server-id = 1

# Binary logging — required for replication
log-bin = mysql-bin
binlog_format = ROW          # ROW format is safest for replication
expire_logs_days = 7         # Keep binary logs for 7 days
max_binlog_size = 256M

# GTID mode — Global Transaction Identifiers (recommended in MySQL 9)
gtid_mode = ON
enforce_gtid_consistency = ON

# Performance and safety
sync_binlog = 1              # Sync binlog to disk on every transaction (safest)
innodb_flush_log_at_trx_commit = 1  # Full ACID compliance

# Replica status tables in InnoDB (crash-safe)
relay_log_info_repository = TABLE
master_info_repository = TABLE
EOF

sudo systemctl restart mysql
sudo systemctl status mysql --no-pager | grep "Active:"

Expected output:

     Active: active (running)

# Verify GTID is enabled
sudo mysql -e "SHOW VARIABLES LIKE 'gtid_mode';"

Expected output:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+

# Create the replication user (replace REPLICA_IP and set a strong password)
REPLICA_IP="10.0.0.2"
REPLICATION_PASS="R3pl1c@t10n_P@ssw0rd_Change_Me"

sudo mysql << SQL
CREATE USER IF NOT EXISTS 'replicator'@'${REPLICA_IP}' IDENTIFIED WITH mysql_native_password BY '${REPLICATION_PASS}';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'${REPLICA_IP}';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'replicator'@'${REPLICA_IP}';
SQL

Expected output:

+-------------------------------------------------------------------------------------------+
| Grants for replicator@10.0.0.2                                                            |
+-------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `replicator`@`10.0.0.2`                                |
+-------------------------------------------------------------------------------------------+

# Check primary status and note the GTID set
sudo mysql -e "SHOW MASTER STATUS\G"

Expected output:

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 856
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 4e6a3124-c1af-11ee-9900-000c29a4e7b1:1-5

Note the Executed_Gtid_Set — this is the set of transactions committed on the primary.

---

Part 2: Take Initial Snapshot for Replica

For a database with existing data, create a consistent snapshot before starting the replica:

# ── ON THE PRIMARY SERVER ─────────────────────────────────────────────────
# Export all databases (locks tables briefly — off-peak only for production)
sudo mysqldump \
  --all-databases \
  --master-data=2 \
  --single-transaction \
  --quick \
  --flush-logs \
  --routines \
  --triggers \
  | gzip > /tmp/primary-snapshot.sql.gz

echo "Snapshot size: $(du -sh /tmp/primary-snapshot.sql.gz | cut -f1)"

# Transfer to replica (replace with your replica IP)
scp /tmp/primary-snapshot.sql.gz ubuntu@10.0.0.2:/tmp/

Expected output:

Snapshot size: 47M
primary-snapshot.sql.gz                    100%   47MB  12.3MB/s   00:03

---

Part 3: Replica Server Configuration

# ── ON THE REPLICA SERVER (10.0.0.2) ─────────────────────────────────────
sudo tee /etc/mysql/mysql.conf.d/replication-replica.cnf << 'EOF'
[mysqld]
# Unique server ID — different from primary
server-id = 2

# GTID mode — must match primary
gtid_mode = ON
enforce_gtid_consistency = ON

# Read-only — prevent accidental writes to replica
read_only = ON
super_read_only = ON         # Also blocks SUPER users (except replication)

# Relay log — stores binary log events received from primary
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
log_replica_updates = ON     # Log replicated transactions in replica's own binlog

# Crash-safe replica configuration
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = ON
EOF

sudo systemctl restart mysql
sudo systemctl status mysql --no-pager | grep "Active:"

Expected output:

     Active: active (running)

# Restore the primary snapshot
gunzip < /tmp/primary-snapshot.sql.gz | sudo mysql
echo "Snapshot restored"

# Verify databases were restored
sudo mysql -e "SHOW DATABASES;" | grep -v information_schema

Expected output:

Database
myapp
mysql
sys

---

Part 4: Start Replication

# ── ON THE REPLICA SERVER ─────────────────────────────────────────────────
PRIMARY_IP="10.0.0.1"
REPLICATION_PASS="R3pl1c@t10n_P@ssw0rd_Change_Me"

sudo mysql << SQL
STOP REPLICA;

-- Configure replica to connect to primary
-- SOURCE_AUTO_POSITION=1 enables GTID-based automatic position (recommended)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='${PRIMARY_IP}',
  SOURCE_PORT=3306,
  SOURCE_USER='replicator',
  SOURCE_PASSWORD='${REPLICATION_PASS}',
  SOURCE_AUTO_POSITION=1;

START REPLICA;
SQL

Expected output:

Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected

# Check replication status
sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E "Running|Behind|Error|Channel|GTID"

Expected output (healthy replication):

        Replica_IO_Running: Yes       ← Connected to primary
       Replica_SQL_Running: Yes       ← Applying events
    Seconds_Behind_Source: 0         ← Fully caught up
               Last_Error:           ← No errors

Both Replica_IO_Running and Replica_SQL_Running must be Yes. Seconds_Behind_Source: 0 means the replica has applied all transactions from the primary.

---

Part 5: Verify Replication is Working

# ── ON THE PRIMARY SERVER ─────────────────────────────────────────────────
# Create a test database and table
sudo mysql << 'SQL'
CREATE DATABASE IF NOT EXISTS replication_test;
USE replication_test;
CREATE TABLE IF NOT EXISTS test_events (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    message    VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO test_events (message) VALUES ('Replication test 1'), ('Replication test 2');
SELECT * FROM test_events;
SQL

Expected output:

+----+--------------------+---------------------+
| id | message            | created_at          |
+----+--------------------+---------------------+
|  1 | Replication test 1 | 2026-04-22 13:00:01 |
|  2 | Replication test 2 | 2026-04-22 13:00:01 |
+----+--------------------+---------------------+

# ── ON THE REPLICA SERVER ─────────────────────────────────────────────────
# Immediately check that data appeared on replica
sudo mysql -e "SELECT * FROM replication_test.test_events;"

Expected output:

+----+--------------------+---------------------+
| id | message            | created_at          |
+----+--------------------+---------------------+
|  1 | Replication test 1 | 2026-04-22 13:00:01 |
|  2 | Replication test 2 | 2026-04-22 13:00:01 |
+----+--------------------+---------------------+

Data replicated successfully from primary to replica.

---

Part 6: Monitoring Replication

# ── Monitoring script — run on replica server ─────────────────────────────
cat > /usr/local/bin/check-replication.sh << 'SCRIPT'
#!/bin/bash
# Check MySQL replication health — exit 1 if unhealthy (for Nagios/monitoring)
set -euo pipefail

STATUS="$(sudo mysql -Nse "SHOW REPLICA STATUS\G")"

IO_RUNNING="$(echo "$STATUS" | awk '/Replica_IO_Running/ {print $2}')"
SQL_RUNNING="$(echo "$STATUS" | awk '/Replica_SQL_Running/ {print $2}')"
LAG="$(echo "$STATUS" | awk '/Seconds_Behind_Source/ {print $2}')"
LAST_ERROR="$(echo "$STATUS" | awk '/Last_Error/ {$1=""; print substr($0,2)}' | head -1)"

echo "=== MySQL Replication Status: $(hostname) ==="
echo "  IO Thread:   $IO_RUNNING"
echo "  SQL Thread:  $SQL_RUNNING"
echo "  Lag:         ${LAG}s"
[[ -n "$LAST_ERROR" ]] && echo "  Last Error:  $LAST_ERROR"

if [[ "$IO_RUNNING" != "Yes" || "$SQL_RUNNING" != "Yes" ]]; then
  echo "CRITICAL: Replication is not running!"
  exit 2
elif [[ "$LAG" != "NULL" && "$LAG" -gt 300 ]]; then
  echo "WARNING: Replication lag is ${LAG}s (>5 minutes)"
  exit 1
else
  echo "OK: Replication is healthy"
  exit 0
fi
SCRIPT

sudo chmod +x /usr/local/bin/check-replication.sh
sudo bash /usr/local/bin/check-replication.sh

Expected output:

=== MySQL Replication Status: replica-server ===
  IO Thread:   Yes
  SQL Thread:  Yes
  Lag:         0s
OK: Replication is healthy

---

Part 7: Manual Failover (Promoting Replica to Primary)

When the primary fails, promote the replica:

# ── ON THE REPLICA SERVER ─────────────────────────────────────────────────
# Step 1: Stop replication and verify it's caught up
sudo mysql -e "STOP REPLICA;"
sudo mysql -e "SHOW REPLICA STATUS\G" | grep "Seconds_Behind_Source"

Expected output:

Seconds_Behind_Source: 0

Must be 0 before promoting. If not, wait or investigate why the replica is lagging.

# Step 2: Promote — disable read-only mode
sudo mysql << 'SQL'
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;
-- Clear replication config
RESET REPLICA ALL;
SQL

echo "Replica promoted to primary"
sudo mysql -e "SHOW VARIABLES LIKE 'read_only';"

Expected output:

Replica promoted to primary
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+

# Step 3: Update application connection strings to point to new primary (10.0.0.2)
# Update your .env or application config:
# DB_HOST=10.0.0.2

# Step 4: When original primary recovers, configure it as a replica of the new primary
# (On original primary — 10.0.0.1, now becoming a replica)
sudo mysql << SQL
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.2',   -- New primary
  SOURCE_USER='replicator',
  SOURCE_PASSWORD='${REPLICATION_PASS}',
  SOURCE_AUTO_POSITION=1;
START REPLICA;
SQL

---

Troubleshooting

Replica_IO_Running: Connecting — replica can’t connect to primary

Checks:

# On replica — test TCP connection to primary
nc -zv 10.0.0.1 3306 && echo "Port 3306 reachable" || echo "Port 3306 blocked"
# On primary — check MySQL is listening
sudo ss -tlnp | grep 3306
# Check bind-address in primary my.cnf (should be 0.0.0.0 or primary's IP)
sudo mysql -e "SHOW VARIABLES LIKE 'bind_address';"

Last_Error: Error 'Duplicate entry' for key — split-brain

Cause: Writes went to both primary and replica (split-brain situation). Fix:

# Skip the problematic transaction on the replica
sudo mysql -e "STOP REPLICA;"
sudo mysql -e "SET GLOBAL gtid_next = 'GTID_OF_FAILING_TRANSACTION';"
sudo mysql -e "BEGIN; COMMIT;"  # Empty transaction to skip it
sudo mysql -e "SET GLOBAL gtid_next = 'AUTOMATIC';"
sudo mysql -e "START REPLICA;"
# Then investigate why writes went to the replica

High replication lag

Causes: Single-threaded replica SQL thread can’t keep up with write load. Fix: Enable parallel replication (MySQL 9 default for GTID mode):

SET GLOBAL replica_parallel_workers = 4;       -- Match CPU cores
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
STOP REPLICA SQL_THREAD; START REPLICA SQL_THREAD;

---

Conclusion

MySQL 9 primary-replica GTID replication is running: binary logging on the primary streams every committed transaction to the replica, the replica applies them asynchronously, and Seconds_Behind_Source: 0 confirms it’s caught up. The monitoring script flags IO/SQL thread failures and lag over 5 minutes.

Connect reads from your application to the replica to distribute load, and see How to Install MySQL 9 on Ubuntu 24.04 for the base installation this guide extends.

---

People Also Ask

What is the difference between MySQL replication and MySQL clustering (InnoDB Cluster)?

MySQL replication (this guide) is asynchronous — the primary commits a transaction and the replica applies it shortly after. There can be a lag, and on failover some transactions may be lost if the primary crashes before the replica receives them. InnoDB Cluster (MySQL’s built-in HA solution) uses synchronous replication via Group Replication — a transaction is only committed after a majority of nodes confirm it, so no data is lost on failover. Use replication for read scaling and backup. Use InnoDB Cluster for zero data loss high availability. InnoDB Cluster requires a minimum of 3 nodes.

How do I replicate only specific databases in MySQL 9?

Add to the primary’s mysqld.cnf: binlog_do_db=myapp (whitelist) or binlog_ignore_db=test (blacklist). Add to the replica’s config: replicate_do_db=myapp or replicate_ignore_db=test. Restart MySQL on both servers. With GTID mode, be careful — filtering can cause GTID inconsistencies if the same server IDs are used across different filtered topologies.

What is SOURCE_AUTO_POSITION=1 in CHANGE REPLICATION SOURCE?

SOURCE_AUTO_POSITION=1 tells the replica to use GTID-based automatic positioning instead of manual binary log file/position tracking. When this is set, the replica sends its GTID_EXECUTED set to the primary, which responds with only the transactions the replica hasn’t seen yet. This makes failover trivial — you don’t need to find the right binary log position on the new primary. It’s the default and recommended mode for MySQL 9 replication.

---

Further Reading

• How to Install MySQL 9 on Ubuntu 24.04 — install MySQL before configuring replication
• How to Install PostgreSQL 17 on Ubuntu 24.04 — PostgreSQL replication alternative
• Ubuntu 24.04 LTS Server Setup Checklist — harden the servers running MySQL

---

Tested on: 2× Ubuntu 24.04 LTS (Hetzner CX22). MySQL 9.0.1. Last verified: April 22, 2026.