MySQL主从复制架构搭建与读写分离中间件配置实战

MySQL主从复制是数据库高可用和读扩展的基础架构，通过binlog日志将主库的写操作异步同步到从库，实现数据冗余备份和读写分离。在高并发业务场景中，主库承担写请求，多个从库分担读请求，有效降低单库压力。MySQL 8.0提供了基于GTID的复制管理、并行复制增强、原子DDL等特性，大幅简化了复制架构的运维复杂度。本文从主从复制搭建、GTID配置、并行复制优化、读写分离中间件四个层面展开实战。

MySQL主从复制环境搭建与binlog配置

主从复制依赖binlog（二进制日志）记录主库所有写操作。配置前需确保主从库MySQL版本一致，server-id全局唯一，binlog格式设置为ROW模式以支持复杂语句精确复制。

# 主库配置 /etc/mysql/conf.d/master.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_expire_logs_seconds = 604800  # 7天过期

# 从库配置 /etc/mysql/conf.d/slave.cnf
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
relay-log = relay-bin
relay-log-index = relay-bin.index
read_only = ON
super_read_only = ON
# 并行复制配置
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON

# 重启MySQL使配置生效
systemctl restart mysqld

# 主库创建复制账户
mysql -u root -p << 'EOF'
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'ReplP@ssw0rd!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

-- 查看主库状态
SHOW MASTER STATUS\G
-- 记录 File 和 Position 值
EOF

# 从库配置复制源（基于GTID）
mysql -u root -p << 'EOF'
-- 基于GTID的复制配置（推荐）
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.10',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'ReplP@ssw0rd!',
    SOURCE_AUTO_POSITION = 1,
    SOURCE_CONNECT_RETRY = 10;

-- 启动复制
START REPLICA;

-- 查看复制状态
SHOW REPLICA STATUS\G
-- 关键字段：
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Seconds_Behind_Master: 0
-- Retrieved_Gtid_Set / Executed_Gtid_Set
EOF

GTID复制管理与复制故障恢复

GTID（Global Transaction Identifier）为每个事务分配全局唯一ID，格式为server_uuid:sequence_number。GTID复制无需手动记录binlog文件和位置，从库自动从主库拉取缺失事务，大幅简化故障切换和复制重建流程。

# GTID复制管理常用操作

# 查看已执行的GTID集合
SELECT @@global.gtid_executed;

# 查看GTID模式
SELECT @@global.gtid_mode, @@global.enforce_gtid_consistency;

# 从库跳过错误事务（谨慎使用）
-- 方法1：注入空事务跳过指定GTID
STOP REPLICA;
SET GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:123';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

-- 方法2：使用gtid_purged跳过（需要先停止复制并重置）
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL gtid_purged = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-123';
CHANGE REPLICATION SOURCE TO ...;
START REPLICA;

# 复制延迟排查
-- 查看从库延迟详情
SHOW REPLICA STATUS\G
-- Seconds_Behind_Master > 0 表示存在延迟

-- 查看SQL线程执行状态
SELECT * FROM performance_schema.replication_applier_status_by_worker;
-- 关注 SERVICE_STATE, LAST_APPLIED_TRANSACTION, LAST_APPLIED_TRANSACTION_END_TIME

-- 查看IO线程读取状态
SELECT * FROM performance_schema.replication_connection_status;

# 复制中断后重建（基于GTID自动定位）
STOP REPLICA;
RESET REPLICA ALL;
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.10',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'ReplP@ssw0rd!',
    SOURCE_AUTO_POSITION = 1;
START REPLICA;
SHOW REPLICA STATUS\G

MySQL并行复制优化与复制延迟治理

传统MySQL复制中从库SQL线程单线程执行relay log，导致从库写性能远低于主库，产生复制延迟。MySQL 8.0引入基于LOGICAL_CLOCK的并行复制，允许同一组内的事务在从库并行执行，显著提升复制吞吐量。

# 并行复制配置优化

# 主库：增大binlog group commit组大小
[mysqld]
binlog_group_commit_sync_delay = 1000        # 微秒，攒批等待
binlog_group_commit_sync_no_delay_count = 12 # 攒批数量阈值

# 从库：并行复制工作线程配置
[mysqld]
slave_parallel_workers = 16                  # 并行worker数量
slave_parallel_type = LOGICAL_CLOCK           # 基于逻辑时钟并行
slave_preserve_commit_order = ON             # 保持提交顺序一致
binlog_transaction_dependency_tracking = WRITESET  # 依赖追踪方式
transaction_write_set_extraction = XXHASH64  # 写集合提取算法

# 多线程复制状态监控
SELECT 
    worker_id,
    thread_id,
    service_state,
    last_seen_transaction,
    last_applied_transaction,
    last_applied_transaction_start_pos,
    last_applied_transaction_end_pos,
    applying_transaction,
    applying_transaction_start_pos
FROM performance_schema.replication_applier_status_by_worker;

# 复制延迟监控脚本
#!/bin/bash
# check_replication_lag.sh
SLAVE_HOST="192.168.1.11"
MYSQL_USER="monitor"
MYSQL_PASS="MonitorPass!"

LAG=$(mysql -h "$SLAVE_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "
    SELECT Seconds_Behind_Master 
    FROM information_schema.processlist 
    WHERE Info LIKE '%Slave SQL%' 
    LIMIT 1
" -s -N 2>/dev/null)

if [ -z "$LAG" ] || [ "$LAG" = "NULL" ]; then
    echo "CRITICAL: Replication stopped or not running"
    exit 2
elif [ "$LAG" -gt 300 ]; then
    echo "CRITICAL: Replication lag ${LAG}s exceeds 300s"
    exit 2
elif [ "$LAG" -gt 60 ]; then
    echo "WARNING: Replication lag ${LAG}s exceeds 60s"
    exit 1
else
    echo "OK: Replication lag ${LAG}s"
    exit 0
fi

ProxySQL读写分离中间件配置

ProxySQL是高性能MySQL代理中间件，支持读写分离、连接池、查询路由、多目标集群管理。应用连接ProxySQL而非直连MySQL，ProxySQL根据SQL类型自动路由到主库或从库。

# ProxySQL安装与配置（Docker方式）
docker run -d --name proxysql     -p 6033:6033 -p 6032:6032     -v /etc/proxysql/proxysql.cnf:/etc/proxysql.cnf     proxysql/proxysql:2.7

# 连接ProxySQL管理接口
mysql -h 127.0.0.1 -P 6032 -u admin -padmin

# 配置后端MySQL服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections)
VALUES 
    (10, '192.168.1.10', 3306, 200),  # 主库 - 写组
    (20, '192.168.1.11', 3306, 100),  # 从库1 - 读组
    (20, '192.168.1.12', 3306, 100),  # 从库2 - 读组
    (20, '192.168.1.13', 3306, 100); # 从库3 - 读组

# 配置主从复制关系（用于自动故障切换感知）
INSERT INTO mysql_replication_hostgroups(
    writer_hostgroup, reader_hostgroup, check_type, comment
) VALUES (10, 20, 'read_only', 'master-slave cluster');

# 配置读写分离路由规则
# 规则1：所有写操作路由到主库(hostgroup 10)
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES 
    (1, 1, '^SELECT.*FOR UPDATE', 10, 1),  # SELECT FOR UPDATE走主库
    (2, 1, '^INSERT', 10, 1),
    (3, 1, '^UPDATE', 10, 1),
    (4, 1, '^DELETE', 10, 1),
    (5, 1, '^CREATE', 10, 1),
    (6, 1, '^ALTER', 10, 1),
    (7, 1, '^DROP', 10, 1),
    (8, 1, '^SELECT', 20, 1);  # 普通SELECT走从库

# 配置监控账户
INSERT INTO mysql_users(username, password, default_hostgroup, active)
VALUES ('app_user', 'AppP@ss1', 10, 1);

# 配置后端健康检查
SET mysql-monitor_username = 'monitor';
SET mysql-monitor_password = 'MonitorPass!';
SET mysql-monitor_read_only_interval = 1000;  # 毫秒

# 加载配置到运行时并持久化
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL REPLICATION HOSTGROUPS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE MYSQL USERS TO DISK;

# 应用连接ProxySQL（端口6033）
# 应用端只需配置一个数据库地址：
# host: 127.0.0.1, port: 6033, user: app_user, password: AppP@ss1
# ProxySQL自动根据SQL类型路由到主库或从库

# 查看路由统计
SELECT hostgroup, count_star, sum_time, digest_text 
FROM stats_mysql_query_digest 
ORDER BY count_star DESC 
LIMIT 20;

# 查看连接池状态
SELECT hostgroup, srv_host, ConnUsed, ConnFree, ConnOK, ConnERR
FROM stats_mysql_connection_pool;

读写分离架构中需注意主从延迟导致的数据一致性问题。对实时性要求高的查询（如写入后立即读取）应强制路由到主库，可通过在SQL中添加注释标签实现：SELECT /*master*/ …让ProxySQL识别并路由到主库。ProxySQL支持基于正则表达式的精细路由规则，可根据表名、SQL模式、用户身份等条件灵活配置分流策略，满足不同业务场景的一致性要求。