如何使用ProxySQL实现MySQL中间件与读写分离-mysql教程-PHP中文网

ProxySQL作为智能数据库代理，通过解析SQL语句实现读写分离，将写操作路由至主库、读操作分发到从库，并支持健康检查与自动故障切换，提升系统性能与可用性。

如何使用proxysql实现mysql中间件与读写分离

ProxySQL作为一个智能的数据库代理，能够高效地在应用程序和MySQL数据库之间建立桥梁，通过预设的规则将写入操作路由到主库，读取操作分发到从库，从而透明地实现读写分离，极大地提升了系统的性能、扩展性和可用性。

解决方案

实现MySQL中间件与读写分离，ProxySQL的核心在于其强大的查询解析和路由能力。它坐落在应用和数据库之间，拦截所有SQL请求，然后根据预定义的规则，决定将请求发送到哪个后端MySQL服务器。这个过程对应用程序来说是完全透明的，应用只需要连接ProxySQL，而无需关心后端数据库的拓扑结构。

具体来说，我们通常会配置ProxySQL：

定义后端服务器： 明确哪些是主库（Master），哪些是从库（Replica），并将它们分配到不同的主机组（Hostgroup）。例如，主库可以分到Hostgroup 1，从库分到Hostgroup 2。
配置用户： ProxySQL需要有权限连接到后端MySQL服务器的用户凭证，同时也需要为前端应用程序提供连接ProxySQL的凭证。
设置查询规则： 这是读写分离的关键。通过正则表达式或SQL模式匹配，ProxySQL能够识别出是读操作（如
```
SELECT
```
登录后复制
）还是写操作（如
```
INSERT
```
登录后复制
,
```
UPDATE
```
登录后复制
,
```
DELETE
```
登录后复制
,
```
CREATE
```
登录后复制
,
```
DROP
```
登录后复制
等）。然后，它会将读操作路由到从库所在的主机组（Hostgroup 2），将写操作路由到主库所在的主机组（Hostgroup 1）。
健康检查： ProxySQL会持续监控后端MySQL服务器的健康状况，一旦发现某个服务器不可用，它会自动将其从可用列表中移除，并将流量重新路由到健康的服务器上，实现故障切换。

通过这种方式，我们可以在不修改应用代码的前提下，轻松实现MySQL的读写分离，减轻主库的压力，提高并发处理能力。

ProxySQL在读写分离中扮演了什么角色，它与传统方案有何不同？

在我看来，ProxySQL在MySQL读写分离的生态中，扮演了一个非常关键的“智能交通警察”角色。它不仅仅是一个简单的负载均衡器，更是一个能够理解SQL语言、根据内容进行决策的智能网关。它的核心价值在于，将复杂的数据库拓扑管理、流量调度、故障切换等逻辑，从应用程序中剥离出来，集中到中间件层面处理。

与传统的读写分离方案相比，ProxySQL的优势是显而易见的。

应用层读写分离： 这种方式需要开发者在应用代码中显式地判断SQL类型，然后连接不同的数据库实例。这无疑增加了开发复杂度，代码耦合度高，维护起来也比较麻烦。一旦数据库拓扑发生变化，应用代码也需要跟着调整。ProxySQL则让应用完全无感知，只管连接ProxySQL即可。
数据库驱动层读写分离： 某些数据库连接驱动（如JDBC的一些实现）提供了读写分离的功能。虽然比应用层好一些，但它依然是客户端侧的方案，配置分散，且功能通常比较基础，比如缺乏高级的查询路由规则、连接池优化等。
基于LVS/HAProxy等四层负载均衡： 这些工具在TCP/IP层面进行负载均衡，它们无法“理解”SQL语句。因此，它们只能将所有请求均匀地分发到后端服务器，无法区分读写，也就无法实现真正的读写分离。如果强行用它们做读写分离，就得为读写分别部署独立的LVS/HAProxy集群，管理起来更复杂。
MySQL Router： 这是MySQL官方提供的一个轻量级代理，也能实现读写分离。它更简单易用，但功能相对ProxySQL来说就显得有些“简陋”了。ProxySQL提供了更强大的查询规则引擎、查询缓存、连接多路复用、高级统计和监控等功能，对于追求极致性能和灵活性的场景，ProxySQL显然是更好的选择。

ProxySQL的独特之处在于它的“查询意识”（Query Awareness）。它能解析SQL语句，根据语句的类型、内容甚至用户来动态路由，这使得它不仅能做读写分离，还能做查询重写、查询缓存，甚至实现一定程度的防火墙功能。这种集中式的管理和强大的功能，让数据库架构的维护变得更加灵活和高效。

如何配置ProxySQL实现基础的读写分离和故障切换？

配置ProxySQL实现基础的读写分离和故障切换，通常需要通过其管理接口进行。ProxySQL启动后，会监听一个管理端口（默认为6032），我们可以通过MySQL客户端连接到这个端口来配置它。

以下是一个简化的配置流程和示例：

连接ProxySQL管理界面：
```
mysql -u admin -padmin -h 127.0.0.1 -P 6032
```
登录后复制
（默认用户名密码都是admin，端口6032）

添加后端MySQL用户： ProxySQL需要用这些用户连接到你的实际MySQL服务器。

INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('proxysql_monitor', 'your_monitor_password', 1, 0);
INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('app_user', 'your_app_password', 1, 0);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

登录后复制

proxysql_monitor

登录后复制

用于ProxySQL监控后端MySQL，

app_user

登录后复制

是应用程序连接ProxySQL时使用的用户，这里我们先将其

default_hostgroup

登录后复制

设为0，后面通过规则来指定。

添加后端MySQL服务器： 将主库和从库分别添加到不同的主机组。假设主库IP是

192.168.1.10

登录后复制

，从库IP是

192.168.1.11

登录后复制

和

192.168.1.12

登录后复制

，端口都是3306。

-- 主库 (Hostgroup 1)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES (1, '192.168.1.10', 3306, 2000);
-- 从库 (Hostgroup 2)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES (2, '192.168.1.11', 3306, 2000);
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES (2, '192.168.1.12', 3306, 2000);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

登录后复制

max_connections

登录后复制

是ProxySQL允许连接到该后端服务器的最大连接数。

配置监控用户和参数： ProxySQL需要一个拥有足够权限的用户来监控后端MySQL的健康状况。

火龙果写作
用火龙果，轻松写作，通过校对、改写、扩展等功能实现高质量内容生产。

106

查看详情
```
UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='your_monitor_password' WHERE variable_name='mysql-monitor_password';
-- 确保监控是开启的
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-monitor_enabled';
LOAD GLOBAL VARIABLES TO RUNTIME; SAVE GLOBAL VARIABLES TO DISK;
```
登录后复制
ProxySQL会定期向后端MySQL发送心跳查询（如
```
SELECT 1
```
登录后复制
或
```
SELECT @@read_only
```
登录后复制
）来判断其健康状态。如果主库变为只读（例如，在MGR或Galera集群中），ProxySQL也能感知到并调整路由。

定义查询规则： 这是读写分离的核心。

-- 规则优先级很重要，数字越小优先级越高
-- 1. 匹配所有写操作，路由到主库 (Hostgroup 1)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (10, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP|TRUNCATE|SET|START|COMMIT|ROLLBACK|LOCK|UNLOCK|CALL)', 1, 1);

-- 2. 匹配所有读操作 (SELECT)，路由到从库 (Hostgroup 2)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (20, 1, '^SELECT', 2, 1);

-- 3. 如果有需要强制到主库的SELECT，可以设置更高优先级规则
-- INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (5, 1, '^SELECT .*FOR UPDATE', 1, 1);

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

登录后复制

match_digest

登录后复制

使用正则表达式匹配SQL语句的开头，

destination_hostgroup

登录后复制

指定目标主机组，

apply=1

登录后复制

表示匹配到此规则后就停止匹配其他规则。

故障切换机制： ProxySQL的故障切换是自动的。当ProxySQL的监控线程检测到某个后端MySQL服务器不健康时（例如，连接超时、无法响应查询），它会自动将该服务器标记为
```
OFFLINE_SOFT
```
登录后复制
或
```
OFFLINE_HARD
```
登录后复制
，并停止向其发送新的查询。对于主库，如果它挂了，ProxySQL会停止向Hostgroup 1发送写请求。你需要有外部机制（如MGR、Galera、Keepalived+MHA等）来提升一个从库为主库，然后手动或通过脚本更新ProxySQL中的主库配置。ProxySQL本身不负责数据库层面的主从切换，它只负责代理层的流量调度。

完成这些配置后，你的应用程序就可以连接到ProxySQL的监听端口（默认为6033），ProxySQL会根据你设定的规则，自动将读写请求分发到正确的主机组。

ProxySQL的哪些高级特性可以进一步优化MySQL性能和可用性？

ProxySQL的强大之处远不止基础的读写分离。它提供了一系列高级特性，能够进一步榨取MySQL的性能潜力，并显著提升系统的可用性和弹性。

查询缓存 (Query Caching)： ProxySQL可以在代理层缓存查询结果。对于那些频繁执行且结果不经常变化的
```
SELECT
```
登录后复制
语句，ProxySQL可以直接从缓存中返回结果，而无需实际触达后端MySQL服务器。这能极大地降低数据库负载，减少响应时间。你可以通过
```
mysql_query_rules
```
登录后复制
中的
```
cache_ttl
```
登录后复制
（缓存生存时间）和
```
cache_hits
```
登录后复制
（命中次数）等参数来精细控制哪些查询应该被缓存，以及缓存多久。不过，使用查询缓存需要非常谨慎，因为它可能导致数据不一致，尤其是在数据更新频繁的场景。
查询重写 (Query Rewriting)： 这是一个非常强大的功能。ProxySQL可以在将查询发送到后端数据库之前，动态地修改SQL语句。例如，你可以：
- 为没有
```
LIMIT
```
  登录后复制
  子句的
```
SELECT
```
  登录后复制
  语句自动添加
```
LIMIT
```
  登录后复制
  ，防止全表扫描。
- 将某些旧的、性能不佳的查询模式重写为更优化的新模式。
- 在特定条件下，将某个表名的查询重定向到另一个表（例如，灰度发布或A/B测试）。
- 强制为所有写入操作添加
```
/* ProxySQL Write */
```
  登录后复制
  注释，方便在MySQL慢查询日志中识别来源。
连接多路复用 (Connection Multiplexing)： ProxySQL能够接收来自大量客户端的连接，然后使用较少数量的持久连接去连接后端MySQL服务器。这意味着，即使前端有数千个应用连接，后端MySQL也只需要维护几十到几百个连接。这大大减少了MySQL服务器的连接开销和资源消耗，提高了其处理并发请求的能力，尤其是在短连接、高并发的Web应用场景下效果显著。
流量限制与QoS (Throttling & QoS)： ProxySQL允许你对特定用户、特定查询或特定主机组的流量进行限制。例如，你可以限制某个报告工具每秒只能执行多少次查询，防止它耗尽数据库资源。这对于保障关键业务的SLA（服务等级协议）至关重要，避免“雪崩效应”。
动态配置与运行时管理： ProxySQL的大部分配置都可以在运行时通过管理接口进行修改，而无需重启服务。这意味着你可以实时调整路由规则、添加/移除服务器、修改监控参数，而不会对线上业务造成任何中断。这种灵活性对于生产环境的运维来说是极其宝贵的。
统计信息与监控集成： ProxySQL提供了丰富的内部统计信息，包括查询计数、响应时间、缓存命中率、后端服务器状态等。这些数据可以通过其管理接口查询，也可以集成到Prometheus、Grafana等监控系统中，帮助我们全面了解数据库代理层的运行状况和性能瓶颈。
SSL/TLS 支持： ProxySQL可以配置为与客户端和后端MySQL服务器之间使用SSL/TLS加密连接，增强数据传输的安全性。