ProxySQL 读写分离与故障切换实战：单主双从的路由规则、健康检查与后端摘除全流程

Author： zz1984
发布时间：October 13, 2026
14 views
No comments
8287 字数
Categories：技术教程

为什么你的单机 MySQL 主从架构，缺一块 ProxySQL

个人站长做到一定体量后，单台数据库迟早扛不住。最常见的升级路线是搭一套 MySQL 主从复制：主库负责写，从库负责读，再在应用层做「写走主、读走从」的读写分离。听起来很简单，但真正落地时你会发现一堆麻烦事：应用代码里到处硬编码主从地址、从库挂了查询报错、主从切换时要改配置重启服务、连接数在从库上堆积却没人察觉。

这些问题的本质是：读写分离的「路由决策」不应该由应用负责，而应该由一个独立的中间件来统一承担。ProxySQL 就是干这件事的。它坐在应用和 MySQL 之间，用 MySQL 协议对外提供单一入口，内部维护后端节点池，根据 SQL 类型、用户、schema 等规则把请求分发到主库或从库，同时提供健康检查、故障自动摘除、查询缓存、连接池复用等能力。

本文从一个真实的单主双从场景出发，完整走一遍 ProxySQL 的部署、读写分离配置、健康检查、故障切换验证和常见坑排查。全程基于 Ubuntu 22.04 + MySQL 8.0，配置可以直接照抄改。

一、先理清 ProxySQL 的架构与端口

ProxySQL 启动后会监听三个端口，理解它们是排错的前提：

6033：SQL 流量端口。应用连的是这个，走 MySQL 协议，客户端无感知。
6032：Admin 管理端口。你用 mysql 客户端连进来执行配置命令，不是给应用用的。
6030（可选）：监控端口，用于暴露 Prometheus 指标。

关键的一点：ProxySQL 的所有配置都先写在内存（runtime）里，要经过「内存 → 运行时 → 磁盘」三级持久化才算真正生效并保存。很多人配完发现不生效，就是因为只改了内存没 LOAD 到 RUNTIME，或者改了 RUNTIME 没 SAVE 到 DISK，重启后配置全丢。记住这三条命令：

LOAD MYSQL SERVERS TO RUNTIME;   -- 把内存中的后端节点配置加载到运行时
SAVE MYSQL SERVERS TO DISK;      -- 把配置持久化到磁盘，重启不丢
LOAD MYSQL VARIABLES TO RUNTIME; -- 变量类配置同理

二、安装 ProxySQL

官方提供 apt 仓库，比从源码编译省事得多。注意 ProxySQL 版本要和 MySQL 8.0 兼容，2.5 以上的版本都没问题。

apt-get install -y lsb-release wget gnupg
wget -O - https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key | apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/$(lsb_release -sc)/ ./" \
  > /etc/apt/sources.list.d/proxysql.list
apt-get update
apt-get install -y proxysql

安装后 systemd 已注册服务，配置目录在 /etc/proxysql.cnf，但建议日常运维全部通过 Admin 端口动态修改，不要手改 cnfg 文件，否则两边不一致会导致重启时配置回滚。启用并启动：

systemctl enable proxysql
systemctl start proxysql
systemctl status proxysql --no-pager

默认 admin 账号是 admin:admin，密码太弱，第一件事就是改掉。连进管理端口：

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='ProxySQL> '

改密码（ProtoSQL 里管理员账号存在 mysql_users 表，用 admin schema）：

UPDATE global_variables SET variable_value='你的强密码' WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

三、注册后端 MySQL 节点

假设你的拓扑是：主库 10.0.0.11:3306，两个从库 10.0.0.12:3306、10.0.0.13:3306。先在后端 MySQL 上为 ProxySQL 建一个监控账号（只需要读权限），以及一个给应用用的账号。

-- 在主库执行，创建监控账号
CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

-- 应用账号（ProxySQL 会用它连后端）
CREATE USER 'appuser'@'%' IDENTIFIED BY 'AppPass456';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

回到 ProxySQL 管理端口，插入节点。注意 hostgroup_id 的约定：10 表示写组，20 表示读组，这是社区惯例，你可以自定义但要一致。

-- 写节点（主库）
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections)
VALUES (10,'10.0.0.11',3306,1,500);

-- 读节点（从库）
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections)
VALUES (20,'10.0.0.12',3306,1,300),
       (20,'10.0.0.13',3306,1,300);

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

配置监控账号密码，让 ProxySQL 能定期探测后端的复制状态：

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonitorPass123' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

四、配置应用账号与读写分离规则

ProxySQL 自己也要认识应用账号。这里最容易踩的坑是密码格式：mysql_users 表里的 password 默认支持明文，但对哈希前缀敏感。最省心的做法是先把变量 admin-hash_passwords 设为 false，用明文写入，再 LOAD 生效。

-- 让 ProxySQL 接受明文密码写入（内部仍会处理）
UPDATE global_variables SET variable_value='false' WHERE variable_name='admin-hash_passwords';
LOAD ADMIN VARIABLES TO RUNTIME;

INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent)
VALUES ('appuser','AppPass456',10,1);   -- 默认走写组

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

其中 transaction_persistent=1 非常重要：它保证一个事务内的所有语句都发往同一个 hostgroup，避免「写主读从」把事务拆散导致数据不一致。这是生产环境必开项。

接下来用查询规则（query rules）实现读写分离。核心思路：把 SELECT 路由到读组 20，其余默认走写组 10。

-- 精确匹配 SELECT，路由到读组
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE',10,1);   -- 加锁读必须回主库，优先级最高

INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (2,1,'^SELECT',20,1);               -- 普通 SELECT 走从库

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

这里有两个必须注意的点。第一，SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 一定要回主库，否则在从库上加锁毫无意义，写完主库后从库数据又没同步。上面把规则 1 的 rule_id 排在前、apply=1（命中即停），就是保证它优先于普通 SELECT 规则。第二，match_digest 里的正则匹配的是归一化后的 SQL（数字和字符串被替换成 ?），所以 ^SELECT 能覆盖所有普通查询。

五、健康检查与故障自动摘除

ProxySQL 内置三层健康检查：连接检查（connect）、ping 检查、复制延迟检查（replication lag）。默认配置基本够用，但生产环境建议调整两个参数：

-- 从库复制延迟超过 60 秒就摘除，避免读到过期数据
UPDATE global_variables SET variable_value='60' WHERE variable_name='mysql-monitor_slave_lag_when_not_reaching_master';
-- 健康检查间隔
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_ping_interval';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

验证健康检查是否生效，查监控表：

SELECT hostgroup_id,hostname,port,status FROM runtime_mysql_servers;
SELECT * FROM mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

正常节点 status 是 ONLINE。如果某个从库挂掉，稍等几秒再看，它会变成 SHUNNED（被摘除）或 OFFLINE_HARD，流量自动切到剩余健康节点。

六、故障切换实战验证

配完不能只看配置对不对，必须做真实演练。第一步，验证读写分离确实生效。连 6033 端口，执行查询后看路由统计：

-- 通过 ProxySQL 执行几条 SQL
mysql -uappuser -pAppPass456 -h127.0.0.1 -P6033 myapp -e "SELECT COUNT(*) FROM users; SELECT @@server_id;"

然后回管理端口查路由命中：

SELECT hostgroup, srv_host, digest_text, count_star
FROM stats_mysql_query_digest
ORDER BY count_star DESC LIMIT 10;

如果 SELECT 语句的 hostgroup=20、写语句是 10，说明读写分离成功。

第二步，模拟从库故障。直接在从库 10.0.0.12 上 stop 掉 MySQL，再持续对 ProxySQL 发读请求，观察是否报错、是否自动切换到 10.0.0.13。正常情况下，已建立的连接可能报一次错，新连接会被路由到健康节点。注意 ProxySQL 不做 SQL 层重试，所以应用侧仍需要基础的连接重试逻辑。

第三步，模拟主库宕机做一次主从切换。这是杀伤力最大的场景：主库挂了之后，你要手动把一个从库提升为新主库（STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only=OFF;），再在 ProxySQL 里把新主库的 hostgroup 调整到 10，旧主库降为 20。因为 ProxySQL 的 hostgroup 是配置项而非自动感知，这套「主从切换」目前仍是半自动的，除非配合 Orchestrator 这类工具做自动故障转移。这一点必须在规划时就认知清楚，别以为装了 ProxySQL 就万事大吉。

七、几个必踩的坑

配置改了不生效：99% 是忘了 LOAD TO RUNTIME，或者忘了 SAVE TO DISK 导致重启回滚。养成「改完必 Load 必 Save」的习惯。
应用连不上 6033：检查 mysql_users 里是否有该账号、default_hostgroup 是否指向了存在的 hostgroup。另注意密码若含特殊字符，在 XML/命令行里要正确处理。
从库读到主库刚写的数据不一致：这是读写分离的固有问题（主从延迟）。对策是事务持久化（transaction_persistent）、把「写后立即读」的关键查询用规则强制走主库，或应用侧做延迟兜底。
连接数打满：ProxySQL 会复用后端连接，前端连接数不等于后端连接数。要调 mysql-max_connections 和每个 server 的 max_connections，并监控 stats_mysql_connection_pool。
SELECT FOR UPDATE 走到了从库：检查查询规则优先级，务必把加锁读规则放在普通 SELECT 规则之前，且 apply=1。

八、监控与告警

ProxySQL 内置 Prometheus 指标导出（需在 admin 里启用 mysql-monitor 相关配置并开启 6030 端口 exporter），也可以直接周期性查询 stats_* 表落库形成趋势。个人站最实用的三个指标：查询路由分布（20 组 vs 10 组比例）、后端节点健康状态、连接池使用率。一旦读组某个节点突然掉 0，说明它已经被摘除，要立刻排查从库是否宕机或延迟过高。

把这套跑通后，你的 MySQL 架构就从「应用硬编码主从地址」进化成了「中间件统一调度」。以后再扩容从库，只需在 ProxySQL 里加一行 INSERT，应用完全无感——这才是个人站长数据库架构该有的样子。

Last modification：October 13th, 2026 at 10:26 pm

ProxySQL 读写分离与故障切换实战：单主双从的路由规则、健康检查与后端摘除全流程

zz1984 • 2026 年 10 月 13 日

为什么你的单机 MySQL 主从架构，缺一块 ProxySQL

一、先理清 ProxySQL 的架构与端口

ProxySQL 启动后会监听三个端口，理解它们是排错的前提：

6033：SQL 流量端口。应用连的是这个，走 MySQL 协议，客户端无感知。
6032：Admin 管理端口。你用 mysql 客户端连进来执行配置命令，不是给应用用的。
6030（可选）：监控端口，用于暴露 Prometheus 指标。

LOAD MYSQL SERVERS TO RUNTIME;   -- 把内存中的后端节点配置加载到运行时
SAVE MYSQL SERVERS TO DISK;      -- 把配置持久化到磁盘，重启不丢
LOAD MYSQL VARIABLES TO RUNTIME; -- 变量类配置同理

二、安装 ProxySQL

官方提供 apt 仓库，比从源码编译省事得多。注意 ProxySQL 版本要和 MySQL 8.0 兼容，2.5 以上的版本都没问题。

apt-get install -y lsb-release wget gnupg
wget -O - https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key | apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/$(lsb_release -sc)/ ./" \
  > /etc/apt/sources.list.d/proxysql.list
apt-get update
apt-get install -y proxysql

systemctl enable proxysql
systemctl start proxysql
systemctl status proxysql --no-pager

默认 admin 账号是 admin:admin，密码太弱，第一件事就是改掉。连进管理端口：

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='ProxySQL> '

改密码（ProtoSQL 里管理员账号存在 mysql_users 表，用 admin schema）：

UPDATE global_variables SET variable_value='你的强密码' WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

三、注册后端 MySQL 节点

-- 在主库执行，创建监控账号
CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

-- 应用账号（ProxySQL 会用它连后端）
CREATE USER 'appuser'@'%' IDENTIFIED BY 'AppPass456';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

回到 ProxySQL 管理端口，插入节点。注意 hostgroup_id 的约定：10 表示写组，20 表示读组，这是社区惯例，你可以自定义但要一致。

-- 写节点（主库）
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections)
VALUES (10,'10.0.0.11',3306,1,500);

-- 读节点（从库）
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections)
VALUES (20,'10.0.0.12',3306,1,300),
       (20,'10.0.0.13',3306,1,300);

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

配置监控账号密码，让 ProxySQL 能定期探测后端的复制状态：

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonitorPass123' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

四、配置应用账号与读写分离规则

-- 让 ProxySQL 接受明文密码写入（内部仍会处理）
UPDATE global_variables SET variable_value='false' WHERE variable_name='admin-hash_passwords';
LOAD ADMIN VARIABLES TO RUNTIME;

INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent)
VALUES ('appuser','AppPass456',10,1);   -- 默认走写组

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

接下来用查询规则（query rules）实现读写分离。核心思路：把 SELECT 路由到读组 20，其余默认走写组 10。

-- 精确匹配 SELECT，路由到读组
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE',10,1);   -- 加锁读必须回主库，优先级最高

INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (2,1,'^SELECT',20,1);               -- 普通 SELECT 走从库

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

五、健康检查与故障自动摘除

ProxySQL 内置三层健康检查：连接检查（connect）、ping 检查、复制延迟检查（replication lag）。默认配置基本够用，但生产环境建议调整两个参数：

-- 从库复制延迟超过 60 秒就摘除，避免读到过期数据
UPDATE global_variables SET variable_value='60' WHERE variable_name='mysql-monitor_slave_lag_when_not_reaching_master';
-- 健康检查间隔
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_ping_interval';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

验证健康检查是否生效，查监控表：

SELECT hostgroup_id,hostname,port,status FROM runtime_mysql_servers;
SELECT * FROM mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

正常节点 status 是 ONLINE。如果某个从库挂掉，稍等几秒再看，它会变成 SHUNNED（被摘除）或 OFFLINE_HARD，流量自动切到剩余健康节点。

六、故障切换实战验证

配完不能只看配置对不对，必须做真实演练。第一步，验证读写分离确实生效。连 6033 端口，执行查询后看路由统计：

-- 通过 ProxySQL 执行几条 SQL
mysql -uappuser -pAppPass456 -h127.0.0.1 -P6033 myapp -e "SELECT COUNT(*) FROM users; SELECT @@server_id;"

然后回管理端口查路由命中：

SELECT hostgroup, srv_host, digest_text, count_star
FROM stats_mysql_query_digest
ORDER BY count_star DESC LIMIT 10;

如果 SELECT 语句的 hostgroup=20、写语句是 10，说明读写分离成功。

七、几个必踩的坑

配置改了不生效：99% 是忘了 LOAD TO RUNTIME，或者忘了 SAVE TO DISK 导致重启回滚。养成「改完必 Load 必 Save」的习惯。
应用连不上 6033：检查 mysql_users 里是否有该账号、default_hostgroup 是否指向了存在的 hostgroup。另注意密码若含特殊字符，在 XML/命令行里要正确处理。
从库读到主库刚写的数据不一致：这是读写分离的固有问题（主从延迟）。对策是事务持久化（transaction_persistent）、把「写后立即读」的关键查询用规则强制走主库，或应用侧做延迟兜底。
连接数打满：ProxySQL 会复用后端连接，前端连接数不等于后端连接数。要调 mysql-max_connections 和每个 server 的 max_connections，并监控 stats_mysql_connection_pool。
SELECT FOR UPDATE 走到了从库：检查查询规则优先级，务必把加锁读规则放在普通 SELECT 规则之前，且 apply=1。

ProxySQL 读写分离与故障切换实战：单主双从的路由规则、健康检查与后端摘除全流程

为什么你的单机 MySQL 主从架构，缺一块 ProxySQL

一、先理清 ProxySQL 的架构与端口

二、安装 ProxySQL

三、注册后端 MySQL 节点

四、配置应用账号与读写分离规则

五、健康检查与故障自动摘除

六、故障切换实战验证

七、几个必踩的坑

八、监控与告警

相关文章

Leave a Comment Cancel reply

自建CF节点之反代Cloudflare加速网站(Gost/iptables转发)

技术学习路径与资源导航

LayerStack：香港原生IP服务器无限流量四网直连外加双向cn2

js底部飘浮广告代码

lnmp 去除防跨域问题

PHP 500 错误排查实战：从 FPM 错误日志、OOM Killer 到 opcache 生效的六类成因

网站多语言国际化实战：子目录 URL 结构、hreflang 标注、Nginx 伪静态与多语言 SEO 全流程

HTTP/3 (QUIC) 部署实战：Nginx 1.25+ 原生配置、Alt-Svc 生效验证与五个踩坑排查

TMT小鸡，斯巴达同机房新购测速（秋水老大的脚本）

Nginx 安全加固与性能优化完全指南：HTTPS、防盗链、限流与缓存实战

ProxySQL 读写分离与故障切换实战：单主双从的路由规则、健康检查与后端摘除全流程

为什么你的单机 MySQL 主从架构，缺一块 ProxySQL

一、先理清 ProxySQL 的架构与端口

二、安装 ProxySQL

三、注册后端 MySQL 节点

四、配置应用账号与读写分离规则

五、健康检查与故障自动摘除

六、故障切换实战验证

七、几个必踩的坑

八、监控与告警