MySQL Replication and High Availability

Overview

• What you’ll learn: MySQL replication concepts, asynchronous and semi-synchronous replication setup, GTID-based replication, Group Replication for multi-primary and single-primary clusters, and high-availability strategies using ProxySQL and failover mechanisms.
• Prerequisites: Lesson 41 (MySQL Installation and Administration), familiarity with MySQL configuration files and user management
• Estimated reading time: 25 minutes

Introduction

A single database server represents a single point of failure. If that server goes down — whether due to hardware failure, software crash, or maintenance — your application loses access to its data. Replication solves this problem by maintaining copies of data across multiple servers, while high-availability architectures ensure that failover happens automatically with minimal downtime.

MySQL offers several replication topologies, from traditional asynchronous primary-replica setups to the more modern Group Replication that provides built-in consensus and automatic failover. Understanding these options allows you to design database infrastructure that meets your availability and performance requirements.

In this lesson you will configure primary-replica replication from scratch, upgrade to GTID-based replication for easier management, explore semi-synchronous replication for stronger durability guarantees, and deploy Group Replication for automatic failover.

Replication Concepts

MySQL replication works by recording all data-modifying events on the primary server into a binary log (binlog). One or more replica servers read these events and apply them to their own copies of the data.

Binary Log Formats

MySQL supports three binary log formats:

• STATEMENT: Logs the actual SQL statements. Compact but can produce inconsistent results for non-deterministic functions.
• ROW: Logs the actual row changes. Larger but guarantees consistency. This is the recommended format.
• MIXED: Uses STATEMENT by default and switches to ROW when needed.

# Check current binlog format
mysql> SHOW VARIABLES LIKE 'binlog_format';

# Set in configuration
[mysqld]
binlog_format = ROW

Replication Threads

Each replica runs two main threads:

• I/O thread: Connects to the primary, reads binary log events, and writes them to the local relay log.
• SQL thread: Reads events from the relay log and applies them to the replica’s database.

Asynchronous Primary-Replica Replication

In asynchronous replication, the primary does not wait for replicas to confirm that they have received or applied changes. This is the default and provides the best write performance, but there is a risk of data loss if the primary crashes before replicas have caught up.

Configuring the Primary Server

# /etc/mysql/mysql.conf.d/mysqld.cnf on the primary
[mysqld]
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin
binlog_format          = ROW
binlog_expire_logs_seconds = 604800    # 7 days
max_binlog_size        = 100M

# Restart MySQL
$ sudo systemctl restart mysql

# Create a replication user
mysql> CREATE USER 'repl_user'@'10.0.1.%' IDENTIFIED BY 'R3pl_P@ssw0rd!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.1.%';
mysql> FLUSH PRIVILEGES;

# Record the binary log position
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      657 |              |                  |
+------------------+----------+--------------+------------------+

Configuring the Replica Server

# /etc/mysql/mysql.conf.d/mysqld.cnf on the replica
[mysqld]
server-id              = 2
relay_log              = /var/log/mysql/mysql-relay
read_only              = ON
super_read_only        = ON

# Restart MySQL
$ sudo systemctl restart mysql

# Configure the replication source
mysql> CHANGE REPLICATION SOURCE TO
       SOURCE_HOST='10.0.1.10',
       SOURCE_USER='repl_user',
       SOURCE_PASSWORD='R3pl_P@ssw0rd!',
       SOURCE_LOG_FILE='mysql-bin.000001',
       SOURCE_LOG_POS=657;

# Start replication
mysql> START REPLICA;

# Check replication status
mysql> SHOW REPLICA STATUSG
*************************** 1. row ***************************
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
        Seconds_Behind_Source: 0

GTID-Based Replication

Global Transaction Identifiers (GTIDs) assign a unique identifier to every transaction, making it easier to track replication position and perform failover without manually specifying binary log file names and positions.

Enabling GTIDs

# Add to mysqld.cnf on BOTH primary and replica
[mysqld]
gtid_mode              = ON
enforce_gtid_consistency = ON

# On the replica, configure with GTID auto-positioning
mysql> STOP REPLICA;
mysql> CHANGE REPLICATION SOURCE TO
       SOURCE_HOST='10.0.1.10',
       SOURCE_USER='repl_user',
       SOURCE_PASSWORD='R3pl_P@ssw0rd!',
       SOURCE_AUTO_POSITION=1;
mysql> START REPLICA;

# Verify GTID replication
mysql> SHOW REPLICA STATUSG
# Look for: Retrieved_Gtid_Set and Executed_Gtid_Set

With GTIDs, promoting a replica to primary during failover is significantly simpler because each server knows exactly which transactions it has executed.

Semi-Synchronous Replication

Semi-synchronous replication provides a middle ground between asynchronous replication and full synchronous replication. The primary waits until at least one replica acknowledges receipt of the binary log events before committing the transaction to the client.

# Install the semi-sync plugins
# On the primary
mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
mysql> SET GLOBAL rpl_semi_sync_source_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_source_timeout = 5000;  -- 5 second timeout

# On the replica
mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
mysql> SET GLOBAL rpl_semi_sync_replica_enabled = 1;
mysql> STOP REPLICA; START REPLICA;

# Verify semi-sync status
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';

If no replica acknowledges within the timeout period, the primary falls back to asynchronous replication to avoid blocking writes indefinitely.

Group Replication

MySQL Group Replication provides a fault-tolerant, self-healing replication topology. It uses a distributed consensus protocol (based on Paxos) to ensure that all members agree on the order of transactions.

Single-Primary vs. Multi-Primary

• Single-Primary mode: Only one member accepts writes; all others are read-only. If the primary fails, the group automatically elects a new primary. This is the recommended mode for most workloads.
• Multi-Primary mode: All members accept writes. Conflict detection is handled by the group. This requires careful application design to avoid excessive conflicts.

Configuring Group Replication

# /etc/mysql/mysql.conf.d/mysqld.cnf (each member)
[mysqld]
server-id                         = 1    # unique per member
gtid_mode                         = ON
enforce_gtid_consistency          = ON
binlog_checksum                   = NONE
log_bin                           = /var/log/mysql/mysql-bin
binlog_format                     = ROW

# Group Replication settings
plugin_load_add                   = group_replication.so
group_replication_group_name      = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot   = OFF
group_replication_local_address   = "10.0.1.10:33061"
group_replication_group_seeds     = "10.0.1.10:33061,10.0.1.11:33061,10.0.1.12:33061"
group_replication_single_primary_mode = ON

# Bootstrap the group on the first member
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

# Join additional members
mysql> START GROUP_REPLICATION;

# Check group membership
mysql> SELECT * FROM performance_schema.replication_group_members;

High-Availability Architectures

ProxySQL for Connection Routing

ProxySQL is a high-performance MySQL proxy that provides connection pooling, read/write splitting, and automatic failover routing:

# Install ProxySQL
$ sudo apt install proxysql -y

# Configure backend servers via the ProxySQL admin interface
$ mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Add MySQL servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (10, '10.0.1.10', 3306);   -- writer
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (20, '10.0.1.11', 3306);   -- reader
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (20, '10.0.1.12', 3306);   -- reader

-- Configure read/write split rules
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT', 20);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Monitoring Replication Health

# Check replication lag
mysql> SHOW REPLICA STATUSG
# Key fields:
#   Seconds_Behind_Source
#   Replica_IO_Running
#   Replica_SQL_Running

# Monitor Group Replication
mysql> SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE
       FROM performance_schema.replication_group_members;

# Check for replication errors
$ sudo tail -100 /var/log/mysql/error.log | grep -i 'replication|replica|error'

Key Takeaways

• Asynchronous replication is the simplest setup — the primary streams binary log events to replicas without waiting for acknowledgment.
• GTID-based replication eliminates the need to track binary log file names and positions manually, simplifying failover.
• Semi-synchronous replication provides stronger durability by requiring at least one replica to acknowledge receipt before the primary commits.
• Group Replication provides automatic failover using distributed consensus; single-primary mode is recommended for most workloads.
• ProxySQL adds connection pooling, read/write splitting, and automatic failover routing in front of MySQL clusters.
• Always monitor replication lag (Seconds_Behind_Source) and ensure both I/O and SQL threads are running.

What’s Next

In Lesson 43, you will install and configure PostgreSQL on Ubuntu Server, learning its architecture, authentication system, user roles, database management, and essential administration commands.

# 主伺服器上的 /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin
binlog_format          = ROW
binlog_expire_logs_seconds = 604800

# 建立複寫使用者
mysql> CREATE USER 'repl_user'@'10.0.1.%' IDENTIFIED BY 'R3pl_P@ssw0rd!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.1.%';

# 記錄二進位日誌位置
mysql> SHOW MASTER STATUS;

# 從伺服器上的設定
[mysqld]
server-id              = 2
relay_log              = /var/log/mysql/mysql-relay
read_only              = ON

# 設定複寫來源
mysql> CHANGE REPLICATION SOURCE TO
       SOURCE_HOST='10.0.1.10',
       SOURCE_USER='repl_user',
       SOURCE_PASSWORD='R3pl_P@ssw0rd!',
       SOURCE_LOG_FILE='mysql-bin.000001',
       SOURCE_LOG_POS=657;

# 啟動複寫
mysql> START REPLICA;

# 檢查複寫狀態
mysql> SHOW REPLICA STATUSG

# 在主伺服器和從伺服器的 mysqld.cnf 中新增
[mysqld]
gtid_mode              = ON
enforce_gtid_consistency = ON

# 在主伺服器上安裝外掛
mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
mysql> SET GLOBAL rpl_semi_sync_source_enabled = 1;

# 在從伺服器上安裝外掛
mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
mysql> SET GLOBAL rpl_semi_sync_replica_enabled = 1;

# プライマリの /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin
binlog_format          = ROW

# レプリケーションユーザーの作成
mysql> CREATE USER 'repl_user'@'10.0.1.%' IDENTIFIED BY 'R3pl_P@ssw0rd!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.1.%';

# バイナリログ位置の記録
mysql> SHOW MASTER STATUS;

# レプリカの設定
[mysqld]
server-id              = 2
relay_log              = /var/log/mysql/mysql-relay
read_only              = ON

# レプリケーションソースの設定
mysql> CHANGE REPLICATION SOURCE TO
       SOURCE_HOST='10.0.1.10',
       SOURCE_USER='repl_user',
       SOURCE_PASSWORD='R3pl_P@ssw0rd!',
       SOURCE_LOG_FILE='mysql-bin.000001',
       SOURCE_LOG_POS=657;

# レプリケーションの開始
mysql> START REPLICA;

# レプリケーションステータスの確認
mysql> SHOW REPLICA STATUSG

# プライマリとレプリカの両方の mysqld.cnf に追加
[mysqld]
gtid_mode              = ON
enforce_gtid_consistency = ON

# プライマリでプラグインをインストール
mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
mysql> SET GLOBAL rpl_semi_sync_source_enabled = 1;

# レプリカでプラグインをインストール
mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
mysql> SET GLOBAL rpl_semi_sync_replica_enabled = 1;