MySQL读写分离配置实战：GTID主从复制与ProxySQL代理路由分发

MySQL读写分离是数据库高可用架构中的经典方案，将写请求路由到主库、读请求分发到多个从库，有效缓解单库读写压力。GTID（全局事务标识符）复制相比传统基于日志位置的复制，自动追踪事务位置简化了故障切换流程。ProxySQL作为高性能MySQL代理，通过规则引擎实现SQL路由、连接池管理和负载均衡。本文搭建一套基于GTID复制和ProxySQL的MySQL读写分离架构。

GTID复制原理与主库配置

GTID格式为server_uuid:transaction_id，每个事务在主库执行后分配唯一GTID，从库通过GTID自动判断需要复制的事务范围，无需手动指定binlog文件名和位置。

-- 主库配置 /etc/my.cnf
[mysqld]
server-id = 1
gtid_mode = ON
enforce-gtid-consistency = ON
log-bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
log_slave_updates = ON

-- 创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@2026!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

-- 查看主库状态
SHOW MASTER STATUS;
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

从库配置与GTID复制启动

-- 从库配置 /etc/my.cnf
[mysqld]
server-id = 2
gtid_mode = ON
enforce-gtid-consistency = ON
log-bin = mysql-bin
binlog_format = ROW
log_slave_updates = ON
read_only = ON
super_read_only = ON
relay_log = relay-bin

-- 启动GTID复制
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.10',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'Repl@2026!',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;

-- 查看复制状态
SHOW REPLICA STATUS\G

-- 关键指标检查
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Retrieved_Gtid_Set 和 Executed_Gtid_Set 应持续推进
-- Seconds_Behind_Source: 理想值为0，表示无延迟

多从库场景下，每个从库的server-id必须唯一。可通过设置不同复制延迟实现读请求分级路由，如一个从库零延迟用于关键查询，另一个延迟1小时用于报表统计。

ProxySQL安装与后端MySQL配置

# 安装ProxySQL
yum install -y proxysql
systemctl start proxysql
systemctl enable proxysql

# 通过管理端口连接（默认6032端口，用户admin/admin）
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, 200),  -- 从库1，读组
    (20, '192.168.1.12', 3306, 200);  -- 从库2，读组

-- 配置监控用户（在MySQL主库创建，自动复制到从库）
-- MySQL中执行：
-- CREATE USER 'monitor'@'192.168.1.%' IDENTIFIED BY 'Monitor@2026!';
-- GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'192.168.1.%';

-- ProxySQL中配置监控
SET mysql-monitor_username = 'monitor';
SET mysql-monitor_password = 'Monitor@2026!';

-- 配置后端健康检查间隔
SET mysql-monitor_connect_interval = 2000;
SET mysql-monitor_ping_interval = 2000;
SET mysql-monitor_read_only_interval = 2000;

-- 查看监控状态
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start DESC LIMIT 5;
SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start DESC LIMIT 5;

ProxySQL后端分组与读写路由规则

ProxySQL通过hostgroup_id区分写组和读组，配合mysql_replication_hostgroups表实现自动读写分离。当检测到从库变为read_only时自动归入读组。

-- 配置复制组关系：writer组10, reader组20
INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment) 
VALUES (10, 20, '读写分离组');

-- 配置应用访问用户（在MySQL主库创建）
-- MySQL中执行：
-- CREATE USER 'appuser'@'%' IDENTIFIED BY 'App@2026!';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'%';
-- FLUSH PRIVILEGES;

-- ProxySQL中配置用户路由默认组
INSERT INTO mysql_users(username, password, default_hostgroup, active) 
VALUES ('appuser', 'App@2026!', 10, 1);

-- 规则1：SELECT语句路由到读组(20)
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*', 20, 1);

-- 规则2：FOR UPDATE的SELECT路由到写组(10)
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- 规则3：写操作路由到写组(10) - 通常default_hostgroup已处理
-- INSERT/UPDATE/DELETE默认走default_hostgroup(10)，无需额外规则

-- 规则4：指定SQL走读库（通过注释hint）
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply) 
VALUES (3, 1, '^SELECT.*\/\*.*read_only.*\*\/', 20, 1);

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

连接池调优与ProxySQL运行时管理

-- 配置连接池参数
SET mysql-max_connections = 1000;
SET mysql-default_query_delay = 0;
SET mysql-default_query_timeout = 36000000;
SET mysql-have_ssl = 'false';
SET mysql-poll_timeout = 2000;
SET mysql-connections_multiplex = 1;

-- 查询统计：分析路由命中率
SELECT hostgroup,_digest_text,count_star,
       sum_time,sum_rows_sent 
FROM stats_mysql_query_digest 
ORDER BY count_star DESC LIMIT 20;

-- 查看当前连接池状态
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR 
FROM stats_mysql_connection_pool;

-- 清空查询统计（重置计数器）
SELECT * FROM stats_mysql_query_digest RESET;

故障切换与一致性保证

主库故障时需要将写组指向新的主库。ProxySQL配合Orchestrator或MHA实现自动故障切换：

-- 手动故障切换示例：旧主库192.168.1.10故障，提升从库192.168.1.11为新主库

-- 1. 在MySQL从库192.168.1.11上停止复制并关闭只读
STOP REPLICA;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

-- 2. 在ProxySQL中更新服务器配置
DELETE FROM mysql_servers WHERE hostgroup_id = 10 AND hostname = '192.168.1.10';
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections) 
VALUES (10, '192.168.1.11', 3306, 200);

-- 3. 重新配置其他从库指向新主库
-- 在MySQL从库192.168.1.12上执行
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.11',
    SOURCE_AUTO_POSITION = 1;
START REPLICA;

-- 4. 加载ProxySQL配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

应用层通过连接ProxySQL的6033端口（数据端口）访问MySQL，对应用透明。所有读写分离逻辑由ProxySQL规则引擎处理，应用代码只需连接ProxySQL并执行SQL，无需关心SQL发往哪个后端实例。通过mysql_query_rules表可配置基于SQL类型、正则匹配、注释hint等多维度路由规则，实现精细化的读写分离策略。

GTID复制的优势在于故障切换时无需手动计算binlog位置，新主库的gtid_executed集合即为复制起点。配合ProxySQL的监控和自动路由调整，整个切换过程可在秒级完成。生产环境中建议配置至少一主两从架构，配合ProxySQL连接池和健康检查，实现读写分离的高可用数据库代理层。