MySQL/MariaDB Replication: A Guide from Setup to Disaster Recovery - 헤이든의 전산실 (Hayden's Server Room)

If you’ve ever managed a database in production, you’ve probably asked yourself: “What happens if this server goes down?” The most practical answer to that question is replication. In this guide, we’ll walk through how to set up replication in MySQL and MariaDB, and how to recover when things go wrong.

MySQL-MariaDB

MySQL Community vs Enterprise Edition: Licensing Guide [2025]

MySQL [CVE-2024-21087] Database Server Privilege Escalation Vulnerability Patch

Table of Contents

1. What Is Database Replication and Why Do You Need It?

Replication is a technology that automatically copies data from one database server (the source) to one or more other servers (the replicas). In simple terms, every change on the source server is applied to the replica servers in near real-time.

Why Replication Matters

Replication becomes essential in the following scenarios:

High Availability When the source server fails, you can promote a replica to take over, minimizing downtime. For services that require 24/7 uptime—like e-commerce platforms or financial systems—this capability is critical.

Read Scale-out All writes go to the source, while reads are distributed across multiple replicas. This significantly improves overall performance, especially for read-heavy web applications.

Safe Backups Running backups on a replica means you don’t impact the source server’s performance. Your production database keeps serving requests normally while backups run.

Separating Analytics Workloads Heavy analytical queries can run on replicas without slowing down your production server.

2. Types of Replication: Which One Should You Choose?

MySQL and MariaDB offer several replication methods.

Asynchronous Replication

This is the default mode. The source commits transactions without waiting for any replica to acknowledge receipt.

Pros:

Fastest performance
Not affected by network latency

Cons:

Potential data loss if the source crashes before replicas catch up

Semi-Synchronous Replication

The source waits for at least one replica to acknowledge receiving the event before committing.

-- MySQL 8.0.26+: Source server
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

-- MySQL 8.0.26+: Replica server
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

-- MySQL 8.0.25 and earlier: Source server
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- MySQL 8.0.25 and earlier: Replica server
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

Pros:

Better data integrity
Significantly reduced risk of data loss

Cons:

Slight performance overhead (typically 2-10%)

Replication Positioning: Binary Log Position vs GTID

There are two methods for tracking replication progress.

Aspect	Binary Log Position	GTID
Tracking	Log filename + position	Globally unique transaction ID
Failover	Manual position specification	Auto-positioning
Topology changes	Complex	Simple
Minimum version	All versions	MySQL 5.6+, MariaDB 10.0+
Recommendation	Legacy environments	Recommended for new setups

GTID (Global Transaction Identifier) assigns a unique ID to each transaction, making it easy to track replication position automatically. For new deployments, GTID is strongly recommended.

3. Pre-Configuration Checklist: Verify Before You Start

Before diving into configuration, verify the following:

Network Connectivity

# Test connection from replica to source
ping source_server_ip
telnet source_server_ip 3306

Firewall Configuration Port 3306 must be open.

# CentOS/RHEL
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

# Ubuntu/Debian
sudo ufw allow 3306/tcp

Version Compatibility Check version compatibility between source and replica. Generally, the replica should be the same version or newer than the source.

SELECT VERSION();

4. Configuring the Source (Master) Server

Let’s start with the source server configuration.

my.cnf (or my.ini) Configuration

Configuration file locations vary by OS:

Linux (MySQL): /etc/my.cnf or /etc/mysql/my.cnf
Linux (MariaDB): /etc/mysql/mariadb.conf.d/50-server.cnf

MySQL 8.0 Configuration

[mysqld]
# Unique server ID (1 to 2^32-1, must be unique within the replication group)
server-id = 1

# Enable binary logging (essential for replication)
log_bin = mysql-bin

# Binary log format (ROW recommended)
binlog_format = ROW

# Enable GTID mode (recommended)
gtid_mode = ON
enforce_gtid_consistency = ON

# Allow external connections (use with caution)
bind-address = 0.0.0.0

MariaDB 10.x Configuration

[mysqld]
# Unique server ID
server-id = 1

# Enable binary logging
log_bin = mysql-bin

# Base name for log files (MariaDB-specific, avoids hostname dependency)
log-basename = master1

# Binary log format
binlog_format = ROW

# Allow external connections
bind-address = 0.0.0.0

Note: MariaDB does not use gtid_mode or enforce_gtid_consistency options. MariaDB’s GTID is automatically available when binary logging is enabled.

Restart MySQL/MariaDB after making changes:

sudo systemctl restart mysql
# or
sudo systemctl restart mariadb

Creating a Replication User

Create a dedicated user for replication only.

-- MySQL 8.0+
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

-- For better security, restrict to specific IPs
CREATE USER 'replication_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.1.%';
FLUSH PRIVILEGES;

Checking Current Binary Log Position

This is required when not using GTID:

-- MySQL 8.4+
SHOW BINARY LOG STATUS;

-- MySQL 8.0 and earlier
SHOW MASTER STATUS;

Example output:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 862      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Note the File and Position values—you’ll need them when configuring the replica.

5. Configuring the Replica (Slave) Server

my.cnf Configuration

MySQL 8.0 Replica Configuration

[mysqld]
# Unique server ID (different from source)
server-id = 2

# Relay log settings
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

# Read-only mode (recommended for replicas)
read_only = ON

# Enable GTID mode (same as source)
gtid_mode = ON
enforce_gtid_consistency = ON

# Log replica updates (required for chain replication)
# MySQL 8.0.26+: log_replica_updates, earlier: log_slave_updates
log_replica_updates = ON

MariaDB Replica Configuration

[mysqld]
# Unique server ID
server-id = 2

# Relay log settings
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

# Read-only mode
read_only = ON

# Log replica updates
log_slave_updates = ON

Restart the service after configuration.

Connecting to the Source Server

GTID Method (Recommended)

-- MySQL 8.0.23+
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'source_server_ip',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'replication_user',
    SOURCE_PASSWORD = 'StrongPassword123!',
    SOURCE_AUTO_POSITION = 1;

-- MySQL 8.0.22 and earlier
CHANGE MASTER TO
    MASTER_HOST = 'source_server_ip',
    MASTER_PORT = 3306,
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'StrongPassword123!',
    MASTER_AUTO_POSITION = 1;

Binary Log Position Method

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'source_server_ip',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'replication_user',
    SOURCE_PASSWORD = 'StrongPassword123!',
    SOURCE_LOG_FILE = 'mysql-bin.000003',
    SOURCE_LOG_POS = 862;

Starting Replication

-- MySQL 8.0.22+
START REPLICA;

-- MySQL 8.0.21 and earlier, or MariaDB
START SLAVE;

6. Verifying and Monitoring Replication Status

Monitoring replication health is essential.

Checking Replication Status

-- MySQL 8.0.22+
SHOW REPLICA STATUS\G

-- MySQL 8.0.21 and earlier, or MariaDB
SHOW SLAVE STATUS\G

Key fields to check:

Replica_IO_Running: Yes        -- IO thread is running
Replica_SQL_Running: Yes       -- SQL thread is running
Last_Error:                    -- Should be empty
Seconds_Behind_Source: 0       -- Replication lag (lower is better)

Both values must be Yes for replication to work correctly. If either shows No, check Last_Error for details.

Monitoring Replication Lag

-- Check replication lag details
SELECT 
    CHANNEL_NAME,
    SOURCE_UUID,
    LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
    APPLYING_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;

Simple Monitoring Script

#!/bin/bash
# replication_monitor.sh
while true; do
    clear
    echo "=== MySQL Replication Status ==="
    echo "Time: $(date)"
    mysql -e "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running|Replica_SQL_Running|Seconds_Behind|Last_Error"
    sleep 5
done

7. Setting Up Replication with Existing Data

In production environments, you’ll often need to set up replication on databases that already contain data.

Using mysqldump

Backing Up on the Source Server

# MySQL 8.0.26+ (GTID method)
mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers --events \
    --source-data=2 --set-gtid-purged=ON > full_backup.sql

# MySQL 8.0.25 and earlier (GTID method)
mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers --events \
    --master-data=2 --set-gtid-purged=ON > full_backup.sql

# Binary Log Position method (all versions)
mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers --events \
    --master-data=2 > full_backup.sql

Note: Setting --source-data (or --master-data) to 2 includes replication coordinates as comments. Setting it to 1 includes them as executable SQL statements.

Restoring on the Replica

mysql -u root -p < full_backup.sql

Starting Replication

Check the backup file header for log file and position, then:

-- GTID method
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'source_ip',
    SOURCE_USER = 'replication_user',
    SOURCE_PASSWORD = 'password',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;

For Large Databases

For databases tens of gigabytes or larger, consider using Percona XtraBackup:

# Backup on source
xtrabackup --backup --target-dir=/backup/full

# Prepare the backup
xtrabackup --prepare --target-dir=/backup/full

# Transfer and restore on replica
rsync -avpP /backup/full/ replica_server:/var/lib/mysql/

8. Troubleshooting Common Replication Issues

Here are the most common replication problems and how to fix them.

Scenario 1: Replication Stopped with an Error

First, check the error:

SHOW REPLICA STATUS\G

For “Table doesn’t exist” errors:

You can skip the problematic event and resume replication:

-- Stop replication
STOP REPLICA;  -- MySQL 8.0.22+, earlier: STOP SLAVE;

-- Skip one event
-- MySQL 8.0.26+
SET GLOBAL sql_replica_skip_counter = 1;
-- MySQL 8.0.25 and earlier, or MariaDB
SET GLOBAL sql_slave_skip_counter = 1;

-- Restart replication
START REPLICA;  -- MySQL 8.0.22+, earlier: START SLAVE;

Warning: This is a temporary fix. It may cause data inconsistency, so address the root cause.

Scenario 2: Duplicate Key Error

-- When Last_Error shows "Duplicate entry"
STOP REPLICA;  -- or STOP SLAVE;

-- Either delete the conflicting record on the replica,
-- or skip the event
SET GLOBAL sql_slave_skip_counter = 1;  -- Works on all versions
START REPLICA;  -- or START SLAVE;

Scenario 3: Data Inconsistency Between Source and Replica

Use Percona Toolkit to check and fix data consistency:

# Check data consistency
pt-table-checksum --host=source_server --user=root --password=xxx

# Sync inconsistent data
pt-table-sync --execute --sync-to-master replica_server

Scenario 4: Rebuilding Replication from Scratch

When recovery isn’t possible, reset replication completely:

-- On the replica
STOP REPLICA;
RESET REPLICA ALL;  -- or RESET SLAVE ALL;

-- Then take a fresh dump from source and restore
-- Follow the steps in Section 7

Scenario 5: IO Thread Connection Failure

-- Adjust retry settings for network issues
CHANGE REPLICATION SOURCE TO
    SOURCE_CONNECT_RETRY = 10,    -- Retry every 10 seconds
    SOURCE_RETRY_COUNT = 86400;   -- Maximum retry attempts

START REPLICA;

9. MariaDB-Specific Differences

MariaDB replication is mostly identical to MySQL, but there are a few differences.

Terminology

MariaDB started transitioning from MASTER/SLAVE terminology to PRIMARY/REPLICA before MySQL did. However, the old commands still work for backward compatibility.

-- Both work in MariaDB
CHANGE MASTER TO ...  -- Still works
START REPLICA;        -- MariaDB 10.5+

MariaDB GTID

MariaDB uses a different GTID format than MySQL:

MySQL GTID: source_uuid:transaction_id
MariaDB GTID: domain_id-server_id-sequence_number

-- MariaDB GTID replication setup
CHANGE MASTER TO
    MASTER_HOST = 'source_ip',
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'password',
    MASTER_USE_GTID = slave_pos;

Replicating from MySQL to MariaDB

Replication from MySQL 5.5 to MariaDB generally works smoothly. However, replicating from MySQL 8.0 to MariaDB may require additional configuration. You might need to set binlog_checksum to NONE:

# On the MariaDB replica
binlog_checksum = NONE

10. Production Tips for Stable Replication

Minimizing Replication Lag

-- Enable parallel replication (MySQL 5.7+)
-- MySQL 8.0.26+
SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';

-- MySQL 8.0.25 and earlier
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

-- MariaDB 10.0+
SET GLOBAL slave_parallel_threads = 4;

Managing Binary Logs

-- Set binary log retention period (7 days)
SET GLOBAL binlog_expire_logs_seconds = 604800;

-- Manual cleanup
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

Enforcing Read-Only Mode

Prevent accidental writes on replicas:

SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;  -- Blocks even SUPER privilege users

Replication Filtering

Replicate or exclude specific databases:

# my.cnf
# Replicate only specific databases
replicate-do-db = important_db

# Exclude specific databases
replicate-ignore-db = test_db

Wrapping Up

MySQL/MariaDB replication may seem complex at first, but it becomes a powerful tool once you understand the fundamentals. Key takeaways:

Use GTID for easier management
server-id must be unique across all servers
Monitor replication status regularly
Test your failover procedures before you need them

Setting up replication is just the beginning. In production, you’ll also need proper monitoring, alerting, and documented runbooks for incident response. But with the fundamentals covered here, you’ll be well-prepared to build on that foundation.

Feel free to drop a comment if you have questions or want to learn more about any specific topic.

References

Post Views: 1

1. What Is Database Replication and Why Do You Need It?

Why Replication Matters

2. Types of Replication: Which One Should You Choose?

Asynchronous Replication

Semi-Synchronous Replication

Replication Positioning: Binary Log Position vs GTID

3. Pre-Configuration Checklist: Verify Before You Start

4. Configuring the Source (Master) Server

my.cnf (or my.ini) Configuration

MySQL 8.0 Configuration

MariaDB 10.x Configuration

Creating a Replication User

Checking Current Binary Log Position

5. Configuring the Replica (Slave) Server

my.cnf Configuration

MySQL 8.0 Replica Configuration

MariaDB Replica Configuration

Connecting to the Source Server

GTID Method (Recommended)

Binary Log Position Method

Starting Replication

6. Verifying and Monitoring Replication Status

Checking Replication Status

Monitoring Replication Lag

Simple Monitoring Script

7. Setting Up Replication with Existing Data

Using mysqldump

Backing Up on the Source Server

Restoring on the Replica

Starting Replication

For Large Databases

8. Troubleshooting Common Replication Issues

Scenario 1: Replication Stopped with an Error

Scenario 2: Duplicate Key Error

Scenario 3: Data Inconsistency Between Source and Replica

Scenario 4: Rebuilding Replication from Scratch

Scenario 5: IO Thread Connection Failure

9. MariaDB-Specific Differences

Terminology

MariaDB GTID

Replicating from MySQL to MariaDB

10. Production Tips for Stable Replication

Minimizing Replication Lag

Managing Binary Logs

Enforcing Read-Only Mode

Replication Filtering

Wrapping Up

References

이 글 공유하기:

관련

Leave a ReplyCancel reply