一、概述

1.1 背景介绍

MySQL作为互联网应用最广泛的关系型数据库，其高可用性一直是运维工程师关注的核心问题。随着业务规模的扩大，单点故障可能导致严重的业务中断和数据丢失。MySQL高可用架构经历了从简单的主从复制到半同步复制，再到MGR（MySQL Group Replication）集群的演进过程。

传统的主从复制存在数据一致性风险和故障切换时间长的问题，半同步复制虽然提升了数据安全性但性能开销较大，而MGR作为MySQL官方推出的原生高可用解决方案，提供了自动故障检测、自动选主和强一致性保证，已成为生产环境的主流选择。

1.2 技术特点

• • 主从复制：异步复制机制，性能最优但存在数据丢失风险，适合读写分离场景

• • 半同步复制：至少一个从库确认后才提交，平衡了性能与数据安全性

• • MGR集群：基于Paxos协议的多主或单主模式，提供自动故障转移和强一致性保证

• • 自动化运维：支持脚本化部署、监控告警和故障自愈

• • 数据一致性：从最终一致性到强一致性的技术演进路径

1.3 适用场景

• • 场景一：互联网应用的读写分离架构，使用主从复制实现读负载均衡，主库处理写请求，从库处理查询请求

• • 场景二：金融、电商等对数据一致性要求高的核心业务系统，采用MGR单主模式确保数据零丢失

• • 场景三：全球化部署的分布式应用，使用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';
SETGLOBAL rpl_semi_sync_master_enabled =1;
SETGLOBAL rpl_semi_sync_master_timeout =1000;

-- 从库安装半同步插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SETGLOBAL 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

sudocp /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

classMySQLCluster:
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

defget_master_conn(self):
"""获取主库连接（写操作）"""
returnself.master_pool.connection()

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

defexecute_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()

defexecute_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高可用

场景描述：银行核心交易系统，要求数据零丢失，故障自动切换时间<30秒。采用MGR三节点单主模式，所有写操作在主节点执行，自动同步到其他节点，主节点故障时自动选举新主。

实现步骤：

1. 1. 部署MGR三节点集群：按照2.2.5和2.3.1步骤部署

2. 2. 配置应用连接池：使用MySQL Router实现自动路由

3. 3. 监控告警配置：使用Prometheus+Grafana监控集群状态

4. 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避免单个文件过大

  • • 定期清理过期binlog：PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

• • 优化点三：MGR流控调优

  -- 根据业务TPS调整流控阈值
  SETGLOBAL group_replication_flow_control_certifier_threshold =50000;
  SETGLOBAL 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连接
  ALTERUSER'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对网络延迟敏感，节点间延迟应<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的语句	设置binlog_format=ROW，避免CREATE TABLE ... SELECT

◆ 4.2.3 兼容性问题

• • 版本兼容：MySQL 5.7的MGR不支持多主模式的某些特性，建议使用MySQL 8.0.17+

• • 平台兼容：MGR在Windows平台支持有限，生产环境推荐Linux

• • 组件依赖：使用MySQL Router需与MySQL版本匹配，如MySQL 8.0.28配套Router 8.0.28

---

五、故障排查和监控

5.1 故障排查

◆ 5.1.1 日志查看

# 查看MySQL错误日志
sudotail -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"

# 查看慢查询日志
sudotail -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. 1. 开启并行复制：SET GLOBAL slave_parallel_workers=8;

2. 2. 优化主库大事务，拆分为小事务

3. 3. 升级从库硬件，使用SSD存储

4. 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. 1. 检查网络延迟：ping -c 100 192.168.1.11

2. 2. 增加超时时间：SET GLOBAL group_replication_member_expel_timeout=10;

3. 3. 启用自动重连：SET GLOBAL group_replication_autorejoin_tries=5;

4. 4. 检查系统资源：top, iostat -x 1

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

• • 症状：应用报错"Transaction was aborted"

• • 排查：查看冲突统计

  SELECT*FROM performance_schema.replication_group_member_stats\G
  -- 关注COUNT_CONFLICTS_DETECTED字段
  

• • 解决：

  1. 1. 优化应用逻辑，避免多节点同时更新同一行

  2. 2. 单主模式下所有写操作路由到主节点

  3. 3. 使用乐观锁或分布式锁协调并发

◆ 5.1.3 调试模式

# 开启MGR调试日志
mysql -uroot -p <<EOF
SET GLOBAL group_replication_communication_debug_options='GCS_DEBUG_ALL';
EOF

# 查看详细日志
sudotail -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.yml
global:
scrape_interval:15s

scrape_configs:
-job_name:'mysql'
static_configs:
-targets: ['192.168.1.11:9104', '192.168.1.12:9104', '192.168.1.13:9104']

# mysqld_exporter启动（每个MySQL节点）
# 创建监控用户
CREATEUSER'exporter'@'localhost'IDENTIFIEDBY'ExporterPass@2024'WITHMAX_USER_CONNECTIONS3;
GRANTPROCESS,REPLICATIONCLIENT,SELECTON*.*TO'exporter'@'localhost';

# 启动exporter
exportDATA_SOURCE_NAME='exporter:ExporterPass@2024@(localhost:3306)/'
nohupmysqld_exporter&

# Grafana面板推荐
# Dashboard ID: 7362 (MySQL Overview)
# Dashboard ID: 14057 (MySQL Group Replication)

5.3 备份与恢复

◆ 5.3.1 备份策略

#!/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"

# 全量备份（使用XtraBackup）
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"增量备份完成"
}

# 压缩和上传
compress_upload() {
    tar czf ${BACKUP_DIR}/backup_${DATE}.tar.gz ${BACKUP_DIR}/*_${DATE}

# 上传到OSS（示例）
# aws s3 cp ${BACKUP_DIR}/backup_${DATE}.tar.gz s3://mysql-backups/

# 删除原始备份目录
rm -rf ${BACKUP_DIR}/*_${DATE}
}

# 清理过期备份
cleanup_old_backups() {
    find ${BACKUP_DIR} -name "backup_*.tar.gz" -mtime +30 -delete
}

# 执行备份
DAY_OF_WEEK=$(date +%u)
if [ ${DAY_OF_WEEK} -eq 7 ]; then
# 周日执行全量备份
    full_backup
else
# 其他时间执行增量备份
    incremental_backup
fi

compress_upload
cleanup_old_backups

echo"备份流程完成"

◆ 5.3.2 恢复流程

1. 1. 停止MySQL服务

   sudo systemctl stop mysqld
   

2. 2. 清理数据目录

   sudorm -rf /var/lib/mysql/*
   

3. 3. 解压备份文件

   cd /data/backup/mysql
   tar xzf backup_20240115_020000.tar.gz
   

4. 4. 准备全量备份

   xtrabackup --prepare --target-dir=/data/backup/mysql/full_20240115_020000
   

5. 5. 应用增量备份（如有）

   xtrabackup --prepare \
       --target-dir=/data/backup/mysql/full_20240115_020000 \
       --incremental-dir=/data/backup/mysql/incr_20240116_020000
   

6. 6. 恢复数据

   xtrabackup --copy-back --target-dir=/data/backup/mysql/full_20240115_020000
   sudochown -R mysql:mysql /var/lib/mysql
   

7. 7. 启动MySQL

   sudo systemctl start mysqld
   

8. 8. 验证数据完整性

   mysql -uroot -p -e "SELECT COUNT(*) FROM ecommerce.orders;"
   

---

附录

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 --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: 二进制日志文件名前缀，默认在datadir目录

• • binlog_format: 日志格式，STATEMENT/ROW/MIXED，MGR必须用ROW

• • sync_binlog: 1表示每次事务提交同步到磁盘，0性能最优但可能丢数据

• • gtid_mode: 启用GTID（全局事务标识符），简化复制拓扑管理

• • rpl_semi_sync_master_timeout: 半同步等待超时时间（毫秒），默认10000

MGR专用参数

• • group_replication_group_name: 组UUID，使用uuidgen生成，同一集群必须相同

• • group_replication_single_primary_mode: ON=单主模式，OFF=多主模式

• • group_replication_flow_control_mode: 流控模式，QUOTA/DISABLED

• • group_replication_member_expel_timeout: 节点超时踢出时间（秒），默认5秒

性能调优参数

• • innodb_buffer_pool_size: InnoDB缓冲池大小，建议为物理内存60-70%

• • innodb_log_file_size: 重做日志文件大小，大事务场景建议1G以上

• • max_connections: 最大连接数，根据并发需求设置，每连接约消耗256KB内存

• • table_open_cache: 表缓存数量，高并发场景建议4096+