找回密码
立即注册
搜索
热搜: Java Python Linux Go
云栈社区»论坛 › 回收站「 Recycle Bin 」 › MySQL高可用架构演进：从主从复制到MGR集群的部署、运维与故障处 ...
发回帖 发新帖
linuxx

4979

积分

0

好友

688

主题

MySQL高可用架构演进：从主从复制到MGR集群的部署、运维与故障处理

发表于 5 天前 | 查看: 34| 回复: 0

MySQL架构演进示意图

一、概述

1.1 背景介绍

MySQL作为互联网应用最广泛的关系型数据库，其高可用性一直是运维工程师和架构师关注的核心焦点。随着业务规模的扩张，任何单点故障都可能引发严重的业务中断与数据丢失风险。从技术发展的脉络来看，MySQL的高可用架构也经历了一系列关键的演进：从简单灵活但略显脆弱的主从复制，到平衡安全与性能的半同步复制，再到如今以强一致性和自动化为核心的MGR（MySQL Group Replication）集群。

传统的主从复制模式在数据一致性和故障切换效率上存在瓶颈。半同步复制虽然提升了数据安全性，但往往会引入额外的性能开销。MGR作为MySQL官方推出的原生高可用与分布式解决方案，凭借其内建的自动故障检测、秒级选主以及基于多数派原则的强一致性保证，正逐渐成为企业生产环境的主流选择。理解这些技术的演进路径，对于我们构建稳定可靠的后端服务至关重要。

1.2 技术特点

  • 主从复制：经典的异步复制机制，性能开销最小，但存在数据丢失的潜在风险。它非常适合用作读写分离场景下的读扩展。
  • 半同步复制：一种折中方案，要求主库在提交事务前，必须收到至少一个从库的成功确认。它在数据安全性与系统性能之间取得了更好的平衡。
  • MGR集群：基于Paxos协议实现，支持单主或多主模式。其核心价值在于提供了开箱即用的自动故障转移能力，并为事务提供了强一致性保证。
  • 自动化运维：现代高可用架构离不开脚本化部署、全方位的监控告警以及故障自愈机制的支撑。
  • 数据一致性：技术演进的深层逻辑，就是从最终一致性逐步迈向强一致性的过程。

1.3 适用场景

  • 场景一：互联网应用的读写分离。利用主从复制实现读负载均衡，主库专注处理写请求，多个从库分担海量查询请求。
  • 场景二：金融、电商等核心业务系统。对数据一致性有极高要求，通常采用MGR单主模式，确保RPO（恢复点目标）趋近于零。
  • 场景三：全球化部署的分布式应用。可以尝试使用MGR多主模式，构建跨地域的多活架构，提升地域容灾能力。
  • 场景四：传统企业级应用升级。从旧有的主从复制架构平滑迁移至MGR集群，是系统性提升数据库可用性的常见路径。

1.4 环境要求

组件 版本要求 说明
操作系统 CentOS 7+/Ubuntu 20.04+ 推荐使用CentOS 7.9或Ubuntu 20.04 LTS
MySQL 5.7.17+/8.0+ MGR需要5.7.17+，生产环境强烈推荐MySQL 8.0.28+
硬件配置 4核8G以上 生产环境建议8核16G，并采用SSD存储
网络要求 低延迟专线 MGR节点间延迟建议<5ms，带宽100Mbps+
Python 3.6+ 用于编写自动化运维脚本

二、详细步骤

2.1 准备工作

◆ 2.1.1 系统检查

# 检查系统版本
cat /etc/os-release

# 检查资源状况
free -h
df -h

# 检查网络延迟（MGR节点间）
ping -c 10 192.168.1.11
ping -c 10 192.168.1.12
ping -c 10 192.168.1.13

# 检查端口可用性
netstat -tuln | grep -E '3306|33061'

# 设置主机名（三个节点分别执行）
hostnamectl set-hostname mysql-node1
hostnamectl set-hostname mysql-node2
hostnamectl set-hostname mysql-node3

◆ 2.1.2 安装依赖

# 更新系统包
sudo yum update -y

# 安装必要依赖
sudo yum install -y wget vim net-tools libaio numactl-libs

# 关闭防火墙或开放端口
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --permanent --add-port=33061/tcp
sudo firewall-cmd --reload

# 禁用SELinux
sudo setenforce 0
sudo sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

# 优化系统参数
cat >> /etc/sysctl.conf << EOF
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_tw_reuse = 1
net.core.somaxconn = 65535
vm.swappiness = 10
EOF
sysctl -p

2.2 核心配置

◆ 2.2.1 安装MySQL 8.0

# 下载MySQL 8.0 YUM源
wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
sudo rpm -ivh mysql80-community-release-el7-7.noarch.rpm

# 安装MySQL服务器
sudo yum install -y mysql-community-server

# 启动MySQL
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 获取初始密码
sudo grep 'temporary password' /var/log/mysqld.log

# 修改root密码
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass@2024';
FLUSH PRIVILEGES;

◆ 2.2.2 主从复制配置

主库配置（192.168.1.11）

# /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_checksum = NONE

# 二进制日志保留时间
expire_logs_days = 7
max_binlog_size = 500M

# 复制优化
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 性能优化
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
max_connections = 1000

从库配置（192.168.1.12/13）

# /etc/my.cnf
[mysqld]
server-id = 2 # 第二个从库设置为3
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_checksum = NONE
read_only = ON
super_read_only = ON

# 中继日志配置
relay_log = relay-bin
relay_log_recovery = ON
relay_log_purge = ON

# 性能优化
innodb_buffer_pool_size = 4G
max_connections = 1000

说明：GTID模式可以简化主从切换，ROW格式确保数据一致性，sync_binlog=1 和 innodb_flush_log_at_trx_commit=1 确保了事务的持久性。

◆ 2.2.3 配置主从复制关系

# 在主库创建复制账号
mysql -uroot -p
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED WITH mysql_native_password BY 'Repl@Pass2024';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

# 查看主库状态
SHOW MASTER STATUS;

# 在从库配置复制
CHANGE MASTER TO
  MASTER_HOST='192.168.1.11',
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@Pass2024',
  MASTER_AUTO_POSITION=1;

# 启动复制
START SLAVE;

# 检查复制状态
SHOW SLAVE STATUS\G

◆ 2.2.4 半同步复制配置

-- 主库安装半同步插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;

-- 从库安装半同步插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启复制使配置生效
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- 查看半同步状态
SHOW STATUS LIKE 'Rpl_semi_sync%';

◆ 2.2.5 MGR集群配置

节点配置（所有节点）

# /etc/my.cnf
[mysqld]
# 基础配置
server-id = 1 # 每个节点不同：1， 2， 3
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE
log_bin = binlog
log_slave_updates = ON
binlog_format = ROW
master_info_repository = TABLE
relay_log_info_repository = TABLE

# MGR配置
plugin_load_add = 'group_replication.so'
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = "192.168.1.11:33061" # 每个节点不同
loose-group_replication_group_seeds = "192.168.1.11:33061,192.168.1.12:33061,192.168.1.13:33061"
loose-group_replication_bootstrap_group = OFF

# 单主模式
loose-group_replication_single_primary_mode = ON
loose-group_replication_enforce_update_everywhere_checks = OFF

# 流控配置
loose-group_replication_flow_control_mode = QUOTA
loose-group_replication_flow_control_certifier_threshold = 25000
loose-group_replication_flow_control_applier_threshold = 25000

# 性能优化
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
max_connections = 1000

参数说明：

  • server-id：每个节点唯一标识，范围1-4294967295
  • group_replication_group_name：组复制UUID，使用 uuidgen 生成
  • group_replication_local_address：本节点用于组内通信的地址和端口
  • group_replication_group_seeds：所有节点的通信地址列表
  • group_replication_single_primary_mode：ON为单主模式，OFF为多主模式
  • group_replication_bootstrap_group：仅在初始化集群第一个节点时设为ON

2.3 启动和验证

◆ 2.3.1 启动MGR集群

# 重启所有节点MySQL
sudo systemctl restart mysqld
# 在所有节点创建复制用户
mysql -uroot -p
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'Repl@Pass2024';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
GRANT CONNECTION_ADMIN ON *.* TO 'repl'@'%';
GRANT BACKUP_ADMIN ON *.* TO 'repl'@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

# 配置复制通道
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Repl@Pass2024' FOR CHANNEL 'group_replication_recovery';

# 第一个节点（node1）启动集群
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

# 其他节点（node2/node3）加入集群
START GROUP_REPLICATION;

# 查看集群状态
SELECT * FROM performance_schema.replication_group_members;

◆ 2.3.2 功能验证

# 验证集群成员状态
mysql -uroot -p -e "SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;"
# 预期输出：所有节点STATE为ONLINE，一个PRIMARY两个SECONDARY
# 测试数据同步
# 在主节点
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE test (id INT PRIMARY KEY， name VARCHAR(50));
INSERT INTO test VALUES (1, 'test-data');

# 在从节点验证
SELECT * FROM testdb.test;

# 测试故障转移
# 停止主节点MySQL (在操作系统执行)
# sudo systemctl stop mysqld

# 在其他节点查看自动选主结果
SELECT MEMBER_HOST, MEMBER_ROLE FROM performance_schema.replication_group_members;

三、示例代码和配置

3.1 完整配置示例

◆ 3.1.1 MGR生产环境配置文件

# 文件路径：/etc/my.cnf
[mysqld]
# ========== 基础配置 ==========
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid_file = /var/run/mysqld/mysqld.pid
user = mysql

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

# ========== 二进制日志 ==========
server-id = 1
log-bin = /var/lib/mysql/binlog
binlog_format = ROW
binlog_checksum = NONE
sync_binlog = 1
expire_logs_days = 7
max_binlog_size = 500M

# GTID配置
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

# ========== MGR配置 ==========
plugin_load_add = 'group_replication.so'
transaction_write_set_extraction = XXHASH64

# 组复制基础配置
loose-group_replication_group_name = "8a94f357-aab4-11e6-a94d-00259053e4c0"
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = "192.168.1.11:33061"
loose-group_replication_group_seeds = "192.168.1.11:33061,192.168.1.12:33061,192.168.1.13:33061"
loose-group_replication_bootstrap_group = OFF

# 单主模式
loose-group_replication_single_primary_mode = ON
loose-group_replication_enforce_update_everywhere_checks = OFF

# 流控和性能
loose-group_replication_flow_control_mode = QUOTA
loose-group_replication_flow_control_certifier_threshold = 25000
loose-group_replication_flow_control_applier_threshold = 25000
loose-group_replication_member_expel_timeout = 5
loose-group_replication_autorejoin_tries = 3

# ========== InnoDB配置 ==========
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_open_files = 4000
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# ========== 连接和缓存 ==========
max_connections = 2000
max_connect_errors = 1000000
table_open_cache = 4096
table_definition_cache = 2048
thread_cache_size = 100

# ========== 查询优化 ==========
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# ========== 慢查询日志 ==========
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON

# ========== 复制配置 ==========
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

◆ 3.1.2 MGR自动化部署脚本

#!/bin/bash
# 脚本功能：自动化部署MySQL MGR集群
# 文件名：deploy_mgr.sh

set -e

# 配置变量
MYSQL_VERSION="8.0.32"
MYSQL_ROOT_PASS="RootPass@2024"
REPL_USER="repl"
REPL_PASS="Repl@Pass2024"
GROUP_NAME="8a94f357-aab4-11e6-a94d-00259053e4c0"

# 节点信息（修改为实际IP）
NODE1_IP="192.168.1.11"
NODE2_IP="192.168.1.12"
NODE3_IP="192.168.1.13"
LOCAL_IP=$(hostname -I | awk '{print $1}')

# 确定节点ID
if [ "$LOCAL_IP" == "$NODE1_IP" ]; then
    SERVER_ID=1
    IS_BOOTSTRAP=true
elif [ "$LOCAL_IP" == "$NODE2_IP" ]; then
    SERVER_ID=2
    IS_BOOTSTRAP=false
else
    SERVER_ID=3
    IS_BOOTSTRAP=false
fi

echo "=== 开始部署MySQL MGR节点 $SERVER_ID ==="

# 1. 安装MySQL
install_mysql() {
echo "步骤1: 安装MySQL ${MYSQL_VERSION}"

# 下载并安装MySQL YUM源
if [ ! -f mysql80-community-release-el7-7.noarch.rpm ]; then
        wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
fi

sudo rpm -ivh mysql80-community-release-el7-7.noarch.rpm || true
sudo yum install -y mysql-community-server

echo "MySQL安装完成"
}

# 2. 配置my.cnf
configure_mysql() {
echo "步骤2: 配置MySQL参数"

sudo systemctl stop mysqld || true

cat > /tmp/my.cnf << EOF
[mysqld]
server-id = ${SERVER_ID}
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 二进制日志
log-bin = binlog
binlog_format = ROW
binlog_checksum = NONE
sync_binlog = 1

# GTID
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

# MGR
plugin_load_add = 'group_replication.so'
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "${GROUP_NAME}"
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = "${LOCAL_IP}:33061"
loose-group_replication_group_seeds = "${NODE1_IP}:33061,${NODE2_IP}:33061,${NODE3_IP}:33061"
loose-group_replication_bootstrap_group = OFF
loose-group_replication_single_primary_mode = ON

# 性能优化
innodb_buffer_pool_size = 4G
max_connections = 1000

# 复制
master_info_repository = TABLE
relay_log_info_repository = TABLE
EOF

sudo cp /tmp/my.cnf /etc/my.cnf
echo "配置文件已更新"
}

# 3. 初始化和启动MySQL
initialize_mysql() {
echo "步骤3: 启动MySQL"

sudo systemctl start mysqld
sudo systemctl enable mysqld

# 获取临时密码并修改root密码
    TEMP_PASS=$(sudo grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}')

    mysql -uroot -p"${TEMP_PASS}" --connect-expired-password << EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY '${MYSQL_ROOT_PASS}';
FLUSH PRIVILEGES;
EOF

echo "MySQL已启动，root密码已设置"
}

# 4. 配置MGR用户
setup_mgr_user() {
echo "步骤4: 配置MGR复制用户"

    mysql -uroot -p"${MYSQL_ROOT_PASS}" << EOF
SET SQL_LOG_BIN=0;
CREATE USER '${REPL_USER}'@'%' IDENTIFIED WITH mysql_native_password BY '${REPL_PASS}';
GRANT REPLICATION SLAVE ON *.* TO '${REPL_USER}'@'%';
GRANT CONNECTION_ADMIN ON *.* TO '${REPL_USER}'@'%';
GRANT BACKUP_ADMIN ON *.* TO '${REPL_USER}'@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO '${REPL_USER}'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='${REPL_USER}', MASTER_PASSWORD='${REPL_PASS}' FOR CHANNEL 'group_replication_recovery';
EOF

echo "MGR用户配置完成"
}

# 5. 启动MGR
start_mgr() {
echo "步骤5: 启动组复制"

if [ "$IS_BOOTSTRAP" = true ]; then
echo "初始化MGR集群（Bootstrap节点）"
        mysql -uroot -p"${MYSQL_ROOT_PASS}" << EOF
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
EOF
else
echo "加入MGR集群"
sleep 10  # 等待第一个节点完成初始化
        mysql -uroot -p"${MYSQL_ROOT_PASS}" << EOF
START GROUP_REPLICATION;
EOF
fi

echo "组复制已启动"
}

# 6. 验证集群状态
verify_cluster() {
echo "步骤6: 验证集群状态"

sleep 5
    mysql -uroot -p"${MYSQL_ROOT_PASS}" -e "SELECT * FROM performance_schema.replication_group_members;"
}

# 执行部署流程
main() {
    install_mysql
    configure_mysql
    initialize_mysql
    setup_mgr_user
    start_mgr
    verify_cluster

echo "=== MGR节点 ${SERVER_ID} 部署完成 ==="
echo "请在其他节点执行相同脚本完成集群部署"
}

main

3.2 实际应用案例

◆ 案例一：电商系统主从读写分离

场景描述：某电商平台日均订单10万笔，查询请求量是写入请求的20倍以上。采用经典的一主两从架构，主库处理订单创建、支付等核心写操作，两个从库则分担商品浏览、订单查询、用户画像等大量读请求，通过应用层逻辑或中间件（如ProxySQL）实现智能的读写分离。

实现代码：

# MySQL读写分离连接池（Python）
import pymysql
from dbutils.pooled_db import PooledDB

class MySQLCluster:
def __init__(self):
# 主库连接池（写）
self.master_pool = PooledDB(
            creator=pymysql,
            maxconnections=50,
            host='192.168.1.11',
            port=3306,
            user='app_user',
            password='AppPass@2024',
            database='ecommerce',
            charset='utf8mb4'
        )

# 从库连接池（读）
self.slave_pools = [
            PooledDB(
                creator=pymysql,
                maxconnections=100,
                host='192.168.1.12',
                port=3306,
                user='app_user',
                password='AppPass@2024',
                database='ecommerce',
                charset='utf8mb4'
            ),
            PooledDB(
                creator=pymysql,
                maxconnections=100,
                host='192.168.1.13',
                port=3306,
                user='app_user',
                password='AppPass@2024',
                database='ecommerce',
                charset='utf8mb4'
            )
        ]
self.slave_index = 0

def get_master_conn(self):
"""获取主库连接（写操作）"""
return self.master_pool.connection()

def get_slave_conn(self):
"""获取从库连接（读操作，轮询负载均衡）"""
        conn = self.slave_pools[self.slave_index].connection()
self.slave_index = (self.slave_index + 1) % len(self.slave_pools)
return conn

def execute_write(self, sql, params=None):
"""执行写操作"""
        conn = self.get_master_conn()
try:
with conn.cursor() as cursor:
                cursor.execute(sql, params)
                conn.commit()
return cursor.lastrowid
finally:
            conn.close()

def execute_read(self, sql, params=None):
"""执行读操作"""
        conn = self.get_slave_conn()
try:
with conn.cursor(pymysql.cursors.DictCursor) as cursor:
                cursor.execute(sql, params)
return cursor.fetchall()
finally:
            conn.close()

# 使用示例
db_cluster = MySQLCluster()

# 写操作：创建订单
order_id = db_cluster.execute_write(
"INSERT INTO orders (user_id, total_amount, status) VALUES (%s， %s， %s)",
    (1001, 299.99, 'pending')
)

# 读操作：查询订单
orders = db_cluster.execute_read(
"SELECT * FROM orders WHERE user_id = %s ORDER BY created_at DESC LIMIT 10",
    (1001,)
)

运行结果：

写操作TPS: 1200/s（主库）
读操作QPS: 24000/s（两个从库分担）
主从延迟: <100ms
系统可用性: 99.9%（主库故障需手动切换）

◆ 案例二：金融核心系统MGR高可用

场景描述：银行核心交易系统，要求数据零丢失（RPO=0），故障自动切换时间（RTO）小于30秒。采用MGR三节点单主模式，所有写操作均指向主节点，并自动同步到其他两个从节点。当主节点发生故障时，集群能在秒级内自动完成新主选举，应用几乎无感知。

实现步骤：

  1. 部署MGR三节点集群：严格按照上文2.2.5和2.3.1的步骤进行部署和配置。
  2. 配置应用连接池：使用 MySQL Router 作为中间件，实现应用无感知的自动故障路由。
  3. 监控告警配置：搭建Prometheus+Grafana监控体系，对集群状态、性能指标进行全方位监控。
  4. 定期演练：每月在业务低峰期执行一次计划内的故障切换演练，验证高可用流程。

MySQL Router配置示例：

# /etc/mysqlrouter/mysqlrouter.conf
[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO

# 读写端口（连接主节点）
[routing:primary]
bind_address = 0.0.0.0:6446
destinations = 192.168.1.11:3306,192.168.1.12:3306,192.168.1.13:3306
routing_strategy = first-available
mode = read-write

# 只读端口（连接所有节点）
[routing:secondary]
bind_address = 0.0.0.0:6447
destinations = 192.168.1.11:3306,192.168.1.12:3306,192.168.1.13:3306
routing_strategy = round-robin
mode = read-only
# 启动MySQL Router
sudo systemctl start mysqlrouter
sudo systemctl enable mysqlrouter

# 应用连接到Router
mysql -h127.0.0.1 -P6446 -uapp_user -p  # 写操作
mysql -h127.0.0.1 -P6447 -uapp_user -p  # 读操作

四、最佳实践和注意事项

4.1 最佳实践

◆ 4.1.1 性能优化

  • 优化点一：InnoDB缓冲池调优 
    # 设置为物理内存的60-70%
innodb_buffer_pool_size = 16G  # 24G内存的服务器
innodb_buffer_pool_instances = 16  # 与CPU核数匹配
# 查看缓冲池命中率（应>99%）
mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';"
  • 优化点二：二进制日志优化
    • 使用SSD存储binlog，显著减少写入延迟。
    • 设置合理的 max_binlog_size (如500M-1G)，避免单个文件过大影响复制和备份。
    • 定期清理过期binlog：PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);
  • 优化点三：MGR流控调优 
    -- 根据业务TPS调整流控阈值
SET GLOBAL group_replication_flow_control_certifier_threshold = 50000;
SET GLOBAL group_replication_flow_control_applier_threshold = 50000;
-- 监控流控状态
SELECT * FROM performance_schema.replication_group_member_stats\G

◆ 4.1.2 安全加固

  • 安全措施一：访问控制 
    # 删除匿名用户和测试数据库
mysql -uroot -p << EOF
DELETE FROM mysql.user WHERE User='';
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;
EOF
# 创建应用账号并限制权限
CREATE USER 'app_user'@'10.0.%.%' IDENTIFIED BY 'StrongPass@2024';
GRANT SELECT， INSERT， UPDATE， DELETE ON ecommerce.* TO 'app_user'@'10.0.%.%';
# 禁用root远程登录
UPDATE mysql.user SET Host='localhost' WHERE User='root';
  • 安全措施二：SSL加密连接 
    -- 生成SSL证书
mysql_ssl_rsa_setup --datadir=/var/lib/mysql
    -- 强制SSL连接
ALTER USER 'app_user'@'10.0.%.%' REQUIRE SSL;
-- 验证SSL状态
SHOW STATUS LIKE 'Ssl_cipher';
  • 安全措施三：审计日志 
    # my.cnf中启用审计插件
plugin-load-add=audit_log.so
audit_log_file=/var/log/mysql/audit.log
audit_log_policy=QUERIES
audit_log_rotate_on_size=100M

◆ 4.1.3 高可用配置

  • HA方案一：MGR+MySQL Router自动故障转移
    • MySQL Router能自动检测主节点故障，并将应用连接路由到新的主节点。
    • 应用无需修改任何连接配置，实现真正的透明故障转移。
    • 故障切换时间通常在10秒以内。
  • HA方案二：Keepalived+VIP实现主从切换 (适用于传统主从) 
    # Keepalived配置示例（主库）
vrrp_script check_mysql {
    script "/usr/local/bin/check_mysql.sh"
    interval 2
    weight -20
}
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.100/24
    }
    track_script {
        check_mysql
    }
}
  • 备份策略：全量+增量备份 
    #!/bin/bash
# 每天全量备份脚本
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d)
# 使用XtraBackup进行热备份
xtrabackup --backup \
 --user=backup_user \
 --password=BackupPass@2024 \
 --target-dir=${BACKUP_DIR}/full_${DATE}
# 压缩备份
tar czf ${BACKUP_DIR}/full_${DATE}.tar.gz ${BACKUP_DIR}/full_${DATE}
rm -rf ${BACKUP_DIR}/full_${DATE}
# 保留最近7天的备份
find ${BACKUP_DIR} -name "full_*.tar.gz" -mtime +7 -delete

4.2 注意事项

◆ 4.2.1 配置注意事项

⚠️ 警告：生产环境切换或升级架构前，必须在测试环境进行充分的兼容性、性能和故障演练测试，建议验证周期不少于2周。

  • ❗ 注意事项一：MGR对网络延迟极为敏感，节点间RTT建议小于5ms。跨地域部署需谨慎评估网络质量。
  • ❗ 注意事项二：从主从复制升级到MGR，必须确保所有节点的GTID集合完全一致，建议在业务绝对低峰期操作。
  • ❗ 注意事项三：在MGR单主模式下，若需手动切换主节点，必须先停止原主的 group_replication，再在其他节点发起选主，避免脑裂。
  • ❗ 注意事项四：修改 innodb_buffer_pool_size 等核心参数需要重启MySQL实例，生产环境务必提前规划变更窗口。
  • ❗ 注意事项五：开启半同步复制会增加事务的响应时间，需根据业务SLA（服务等级协议）合理调整 rpl_semi_sync_master_timeout 参数。

◆ 4.2.2 常见错误

错误现象 原因分析 解决方案
Slave_IO_Running: No 网络不通或主库binlog已被清理 检查网络连通性，尝试重新同步：RESET SLAVE; START SLAVE;
MGR节点状态 UNREACHABLE 网络分区或节点宕机 检查网络和MySQL进程，必要时从集群中踢出问题节点：SELECT group_replication_set_as_primary('健康节点UUID')
ERROR 3100 (HY000): out of memory innodb_buffer_pool_size 设置超过物理内存 调低buffer pool大小，或为服务器增加物理内存
Waiting for semi-sync ACK 从库延迟严重或半同步超时设置过长 优化从库性能，或将 rpl_semi_sync_master_timeout 调整为1000ms等合理值
GTID已执行但数据不一致 binlog_format 非ROW，或执行了不兼容GTID的语句（如CREATE TABLE ... SELECT） 确保 binlog_format=ROW，避免使用不安全的SQL语句

◆ 4.2.3 兼容性问题

  • 版本兼容：MySQL 5.7版本的MGR对多主模式的支持有功能限制，生产环境建议使用MySQL 8.0.17及以上版本。
  • 平台兼容：MGR在Windows平台上的支持和稳定性有限，生产环境强烈推荐使用Linux发行版。
  • 组件依赖：使用MySQL Router时，需确保其版本与MySQL Server版本匹配，例如MySQL 8.0.28最好配套使用MySQL Router 8.0.28。

五、故障排查和监控

5.1 故障排查

◆ 5.1.1 日志查看

# 查看MySQL错误日志（首要排查点）
sudo tail -f /var/log/mysqld.log

# 查看MGR相关日志
sudo grep "group_replication" /var/log/mysqld.log | tail -50

# 查看复制错误
mysql -uroot -p -e "SHOW SLAVE STATUS\G" | grep -E "Error|Running"

# 查看慢查询日志
sudo tail -f /var/lib/mysql/slow-query.log

◆ 5.1.2 常见问题排查

问题一：主从复制延迟过大

# 诊断命令
mysql -uroot -p << EOF
SHOW SLAVE STATUS\G
SELECT * FROM performance_schema.replication_applier_status_by_worker;
EOF

解决方案：

  1. 开启并行复制：SET GLOBAL slave_parallel_workers=8;
  2. 优化主库，将大事务拆分为小事务提交。
  3. 升级从库硬件，特别是使用SSD存储。
  4. 验证结果：再次执行 SHOW SLAVE STATUS\G，观察 Seconds_Behind_Master 是否降至0或极低值。

问题二：MGR节点频繁退出集群

# 诊断命令
mysql -uroot -p << EOF
SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME='group_replication_applier'\G
EOF

解决方案：

  1. 检查网络延迟和丢包率：ping -c 100 192.168.1.11
  2. 适当增加节点 expulsion 超时时间：SET GLOBAL group_replication_member_expel_timeout=10;
  3. 启用自动重连尝试：SET GLOBAL group_replication_autorejoin_tries=5;
  4. 检查节点系统资源（CPU、内存、IO）：top, iostat -x 1

问题三：MGR事务冲突导致回滚

  • 症状：应用端报错 "Transaction was aborted"。
  • 排查：查看冲突统计信息 
    SELECT * FROM performance_schema.replication_group_member_stats\G
-- 重点关注 COUNT_CONFLICTS_DETECTED 字段
  • 解决：
    1. 优化应用逻辑，避免多个节点同时更新同一行数据（在单主模式下，确保所有写操作都路由到主节点）。
    2. 对于无法避免的并发更新场景，考虑使用乐观锁（版本号）或分布式锁进行协调。

◆ 5.1.3 调试模式

# 开启MGR详细调试日志（会产生大量日志，谨慎使用）
SET GLOBAL group_replication_communication_debug_options='GCS_DEBUG_ALL';
# 查看详细日志
sudo tail -f /var/log/mysqld.log | grep -i "group_replication"
-- 问题排查后，务必关闭调试模式
SET GLOBAL group_replication_communication_debug_options='GCS_DEBUG_NONE';

5.2 性能监控

◆ 5.2.1 关键指标监控

# 查看实时TPS/QPS
mysqladmin -uroot -p extended-status -r -i 1 | grep -E "Questions|Com_commit"

# 查看复制延迟
mysql -uroot -p -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"

# 查看MGR事务队列长度
mysql -uroot -p << EOF
SELECT MEMBER_ID， COUNT_TRANSACTIONS_IN_QUEUE， COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE
FROM performance_schema.replication_group_member_stats;
EOF

# 查看当前连接数
mysql -uroot -p -e "SHOW STATUS LIKE 'Threads_connected';"

# 查看InnoDB引擎状态（包含锁、事务等信息）
mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G"

◆ 5.2.2 监控指标说明

指标名称 正常范围 告警阈值 说明
TPS 根据业务基线 低于基线30% 每秒事务数，突降可能意味着应用或数据库故障
Seconds_Behind_Master 0-5秒 >30秒 主从复制延迟，反映数据同步健康状况
Threads_connected <max_connections*80% >max_connections*90% 当前连接数，过高会耗尽资源并影响性能
Innodb_buffer_pool_wait_free 0 >100 缓冲池页等待，持续非零说明缓冲池可能不足
COUNT_TRANSACTIONS_IN_QUEUE 0-100 >1000 MGR中待认证的事务队列积压

◆ 5.2.3 Prometheus监控配置

现代运维体系中，使用Prometheus进行指标采集是标准做法。

# prometheus.yml 配置片段
scrape_configs:
- job_name: 'mysql'
  static_configs:
  - targets: ['192.168.1.11:9104', '192.168.1.12:9104', '192.168.1.13:9104']
-- 在每个MySQL节点上创建监控专用用户
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'ExporterPass@2024' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS， REPLICATION CLIENT， SELECT ON *.* TO 'exporter'@'localhost';
# 启动 mysqld_exporter
export DATA_SOURCE_NAME='exporter:ExporterPass@2024@(localhost:3306)/'
nohup mysqld_exporter &

Grafana面板推荐：

  • Dashboard ID: 7362 (MySQL Overview)
  • Dashboard ID: 14057 (MySQL Group Replication)

5.3 备份与恢复

◆ 5.3.1 备份策略

一个健壮的备份策略应包含全量和增量备份。以下是一个基于XtraBackup的自动化脚本示例：

#!/bin/bash
# 完整备份脚本：backup_mysql.sh
set -e

BACKUP_USER="backup_user"
BACKUP_PASS="BackupPass@2024"
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_HOST="192.168.1.11"

# 全量备份（周日执行）
full_backup() {
echo "开始全量备份: ${DATE}"
    xtrabackup --backup \
        --user=${BACKUP_USER} \
        --password=${BACKUP_PASS} \
        --host=${MYSQL_HOST} \
        --target-dir=${BACKUP_DIR}/full_${DATE} \
        --parallel=4
echo "${DATE}" > ${BACKUP_DIR}/last_full_backup
echo "全量备份完成"
}

# 增量备份（周一至周六执行）
incremental_backup() {
    LAST_FULL=$(cat ${BACKUP_DIR}/last_full_backup)
echo "开始增量备份: ${DATE} (基于 ${LAST_FULL})"
    xtrabackup --backup \
        --user=${BACKUP_USER} \
        --password=${BACKUP_PASS} \
        --host=${MYSQL_HOST} \
        --target-dir=${BACKUP_DIR}/incr_${DATE} \
        --incremental-basedir=${BACKUP_DIR}/full_${LAST_FULL} \
        --parallel=4
echo "增量备份完成"
}

# 执行备份
DAY_OF_WEEK=$(date +%u)
if [ ${DAY_OF_WEEK} -eq 7 ]; then
    full_backup
else
    incremental_backup
fi

# 后续可添加压缩、上传到云存储、清理旧备份等逻辑
echo "备份流程完成"

◆ 5.3.2 恢复流程

当发生数据丢失或损坏时，恢复流程至关重要。假设我们有一个周日的全量备份和周一的增量备份：

  1. 停止MySQL服务：sudo systemctl stop mysqld
  2. 清理数据目录：sudo rm -rf /var/lib/mysql/*
  3. 准备全量备份： 
    xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/full_20240115_020000
  4. 应用增量备份： 
    xtrabackup --prepare --apply-log-only \
    --target-dir=/data/backup/mysql/full_20240115_020000 \
    --incremental-dir=/data/backup/mysql/incr_20240116_020000
  5. 最终准备并恢复数据： 
    xtrabackup --prepare --target-dir=/data/backup/mysql/full_20240115_020000
xtrabackup --copy-back --target-dir=/data/backup/mysql/full_20240115_020000
sudo chown -R mysql:mysql /var/lib/mysql
  6. 启动MySQL并验证： 
    sudo systemctl start mysqld
mysql -uroot -p -e "SELECT COUNT(*) FROM ecommerce.orders;"

六、总结

6.1 技术要点回顾

  • ✅ 主从复制：读扩展的基石，配置简单，但故障切换需手动介入，存在数据丢失窗口。
  • ✅ 半同步复制：在数据安全与性能间取得平衡的实用方案，是向强一致性迈进的重要一步。
  • ✅ MGR集群：代表了MySQL原生高可用的未来，自动故障转移和强一致性使其成为核心系统的优先选择。
  • ✅ 监控运维：再好的架构也离不开眼睛。建立完善的监控体系（如Prometheus+Grafana）和可靠的备份恢复机制，是高可用的生命线。
  • ✅ 架构演进：技术的选择应匹配业务发展阶段。从主从到MGR的演进应是平滑、可控的，最大程度避免业务中断。

6.2 进阶学习方向

  1. MySQL 8.0新特性深入：持续研究Clone Plugin、Redo Log归档、不可见索引、函数索引等新特性，将其应用于生产环境优化。
  2. 分布式数据库架构：当单集群MySQL无法满足需求时，了解如Vitess、TiDB等分布式数据库解决方案，学习分库分表、分布式事务等知识。
  3. MySQL内核源码分析：对于想成为专家的开发者，深入源码理解InnoDB存储引擎、优化器、复制模块的工作原理，是解决问题的终极武器。

6.3 参考资料

  • MySQL官方文档（永远是第一手资料）
  • 《MySQL High Availability》 by O’Reilly
  • Percona Database Performance Blog
  • MySQL Group Replication官方文档
  • 阿里云数据库内核月报

附录

A. 命令速查表

# 复制状态查看
SHOW MASTER STATUS;                    -- 查看主库状态
SHOW SLAVE STATUS\G                    -- 查看从库状态
SHOW BINARY LOGS;                      -- 查看binlog列表

# MGR集群管理
START GROUP_REPLICATION;               -- 启动组复制
STOP GROUP_REPLICATION;                -- 停止组复制
SELECT * FROM performance_schema.replication_group_members;  -- 查看集群成员

# 性能诊断
SHOW PROCESSLIST;                      -- 查看当前连接/会话
SHOW ENGINE INNODB STATUS\G            -- 查看InnoDB详细状态（含锁、事务等）
SHOW VARIABLES LIKE '%buffer%';        -- 查看缓冲区相关配置

# 备份恢复 (XtraBackup)
xtrabackup --backup --target-dir=/backup/full  -- 执行全量备份
xtrabackup --prepare --target-dir=/backup/full -- 准备备份集以用于恢复
xtrabackup --copy-back --target-dir=/backup/full -- 将备份集拷贝回数据目录

B. 配置参数详解

复制相关参数

  • server-id: 服务器唯一标识，范围1-4294967295，集群内必须唯一。
  • log-bin: 启用二进制日志并指定文件名前缀，是复制的基础。
  • binlog_format: 日志格式，STATEMENT/ROW/MIXED。MGR必须使用ROW格式。
  • sync_binlog: 控制binlog刷盘策略。1最安全（每次提交同步刷盘），0性能最好但可能丢失最近的事务。
  • gtid_mode: 启用全局事务标识符，极大简化了复制拓扑管理和故障切换。
  • rpl_semi_sync_master_timeout: 半同步模式下，主库等待从库确认的超时时间（毫秒）。

MGR专用参数

  • group_replication_group_name: 集群的唯一标识，使用uuidgen生成，所有节点必须相同。
  • group_replication_single_primary_mode: ON为单主模式（推荐），OFF为多主模式。
  • group_replication_flow_control_mode: 流控模式，QUOTA（配额）或DISABLED。
  • group_replication_member_expel_timeout: 判定一个节点失联并将其踢出集群的等待时间（秒）。

性能调优参数

  • innodb_buffer_pool_size: InnoDB缓冲池大小，用于缓存数据和索引。建议设置为物理内存的60-70%。
  • innodb_log_file_size: 重做日志文件大小。设置较大可提升写性能，但会增加恢复时间。
  • max_connections: 允许的最大客户端连接数。需根据应用并发量设置，每个连接会占用内存。
  • table_open_cache: 表缓存数量，高并发访问多表的场景应适当调大。

C. 术语表

术语 英文 解释
主从复制 Master-Slave Replication 主库的变更通过二进制日志异步同步到从库的基础机制
GTID Global Transaction Identifier 全局事务标识符，为每个事务分配全局唯一ID，简化复制管理
MGR MySQL Group Replication MySQL组复制，基于Paxos协议实现的原生高可用与强一致方案
半同步复制 Semi-Sync Replication 主库提交事务前，需等待至少一个从库接收并写入Relay Log
二进制日志 Binary Log (binlog) 记录所有引起数据变更的SQL语句，用于复制、审计和恢复
中继日志 Relay Log 从库I/O线程从主库拉取的binlog内容的本地存储
故障转移 Failover 当主节点失效时，将服务自动或手动切换到备用节点的过程
读写分离 Read-Write Splitting 将写操作定向到主库，读操作分散到一个或多个从库，以提升系统整体吞吐量

关于云栈社区：本文深入探讨了MySQL高可用架构的核心原理与生产实践。如果你想与更多同行交流数据库运维、系统架构等技术话题，欢迎访问云栈社区，这里汇聚了丰富的技术资源和活跃的开发者社群。




上一篇：Helm Chart高级开发：自定义模板函数与多环境依赖管理实战（Helm 3.10+）
下一篇：Leo AI 实操：如何用文本提示直接生成可制造的 CAD 装配体？
MySQL, 高可用架构, MGR集群, 生产实践, 数据库运维

相关帖子
返回列表
高级模式
B Color Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

手机版|小黑屋|网站地图|云栈社区 ( 苏ICP备2022046150号-2 )

GMT+8, 2026-4-7 21:31 , Processed in 1.104840 second(s), 42 queries , Gzip On.

Powered by Discuz! X3.5

© 2025-2026 云栈社区.

快速回复 返回顶部 返回列表