MySQL ProxySQL与负载均衡

ProxySQL 是高性能 MySQL 中间件，提供读写分离、负载均衡、连接池、查询缓存等核心能力。

架构概览

Bash

┌─────────────────────────────────────────────┐
│              应用层                          │
│    App1    App2    App3    AppN             │
└──────┬──────┬──────┬──────┬────────────────┘
       │      │      │      │
       └──────┴──────┴──────┘
              │
              ▼
┌─────────────────────────────────────────────┐
│            ProxySQL (中间件)                 │
│  ┌────────────────────────────────────────┐ │
│  │  Connection Pool | Query Cache        │ │
│  │  Query Routing | Load Balancing       │ │
│  └────────────────────────────────────────┘ │
└──────┬───────────────┬───────────────┬──────┘
       │               │               │
       ▼               ▼               ▼
┌──────────┐    ┌──────────┐    ┌──────────┐
│  Master  │    │  Slave1  │    │  Slave2  │
│  写入    │    │  只读    │    │  只读    │
└──────────┘    └──────────┘    └──────────┘

核心功能

功能	说明
读写分离	自动路由写请求到主库，读请求到从库
负载均衡	多从库间轮询、加权轮询
连接池	复用连接，减少连接开销
查询缓存	缓存查询结果，减少数据库压力
查询路由	基于规则路由到不同节点
故障转移	自动摘除故障节点

安装部署

安装 ProxySQL

Bash

# CentOS/RHEL
yum install proxysql

# Ubuntu/Debian
apt-get install proxysql

# 启动服务
systemctl start proxysql
systemctl enable proxysql

管理接口

SQL

# 连接管理端口（默认 6032）
mysql -u admin -padmin -h 127.0.0.1 -P 6032

# 默认账号：admin / admin
# 管理数据库：main

核心配置

配置层次结构

SQL

MySQL Servers (mysql_servers)
        ↓
MySQL Users (mysql_users)
        ↓
Query Rules (mysql_query_rules)
        ↓
MySQL Variables (mysql_variables)

1. 配置后端服务器

SQL

-- 插入主库
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, comment)
VALUES (10, '192.168.1.101', 3306, 1, 'master');

-- 插入从库
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, comment)
VALUES (20, '192.168.1.102', 3306, 1, 'slave1');

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, comment)
VALUES (20, '192.168.1.103', 3306, 1, 'slave2');

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

字段说明：

字段	说明
hostgroup_id	主机组 ID，10=写组，20=读组
hostname	MySQL 服务器地址
port	MySQL 端口
weight	权重，用于负载均衡

2. 配置用户

SQL

-- 插入应用用户
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('app_user', 'app_password', 10, 1);

-- 加载配置
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

字段说明：

字段	说明
username	MySQL 用户名
password	MySQL 密码（明文或加密）
default_hostgroup	默认主机组
transaction_persistent	事务保持，同一事务路由同一节点

3. 配置读写分离规则

SQL

-- 规则1：写操作路由到主库（hostgroup 10）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^INSERT', 10, 1),
       (2, 1, '^UPDATE', 10, 1),
       (3, 1, '^DELETE', 10, 1),
       (4, 1, '^REPLACE', 10, 1);

-- 规则2：读操作路由到从库（hostgroup 20）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (5, 1, '^SELECT', 20, 1);

-- 规则3：事务内读路由到主库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (6, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- 加载配置
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

4. 配置主机组

SQL

-- 查看主机组
SELECT * FROM mysql_hostgroups;

-- 配置主机组（写入组=10，读取组=20）
INSERT INTO mysql_hostgroups (writer_hostgroup, reader_hostgroup, comment)
VALUES (10, 20, 'read_write_split');

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

负载均衡策略

加权轮询

SQL

-- 设置从库权重
UPDATE mysql_servers SET weight = 3 WHERE hostname = '192.168.1.102';
UPDATE mysql_servers SET weight = 1 WHERE hostname = '192.168.1.103';

-- 权重 3:1 比例分配请求

最小连接数

SQL

-- 在 mysql_variables 中配置
UPDATE global_variables SET variable_value = 'least_conn_global'
WHERE variable_name = 'mysql-monitor_reader_lag_use';

LOAD MYSQL VARIABLES TO RUNTIME;

健康检查

配置监控

SQL

-- 监控用户（需要在后端 MySQL 创建）
-- 在 MySQL 主库执行
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';

-- ProxySQL 配置
UPDATE global_variables SET variable_value = 'monitor'
WHERE variable_name = 'mysql-monitor_username';

UPDATE global_variables SET variable_value = 'monitor_password'
WHERE variable_name = 'mysql-monitor_password';

-- 健康检查间隔
UPDATE global_variables SET variable_value = '2000'
WHERE variable_name = 'mysql-monitor_connect_interval';

-- 加载配置
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

查看健康状态

SQL

-- 查看服务器状态
SELECT hostgroup_id, hostname, port, status, weight, queries
FROM runtime_mysql_servers;

-- 查看连接池状态
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree
FROM stats_mysql_connection_pool;

连接池配置

SQL

-- 连接池参数
UPDATE global_variables SET variable_value = 100
WHERE variable_name = 'mysql-max_connections';

UPDATE global_variables SET variable_value = 20
WHERE variable_name = 'mysql-default_max_latency_ms';

-- 每个后端最大连接数
UPDATE mysql_servers SET max_connections = 50 WHERE hostgroup_id = 10;

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

查询缓存

SQL

-- 启用查询缓存
UPDATE global_variables SET variable_value = 'true'
WHERE variable_name = 'mysql-query_cache_size_MB';

-- 配置缓存规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, cache_ttl, apply)
VALUES (100, 1, '^SELECT.*FROM config', 60000, 1);  -- 缓存60秒

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

实战配置示例

完整读写分离配置

text

-- 1. 清空现有配置
DELETE FROM mysql_servers;
DELETE FROM mysql_users;
DELETE FROM mysql_query_rules;

-- 2. 配置服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
(10, '192.168.1.101', 3306, 1),  -- 主库
(20, '192.168.1.102', 3306, 2),  -- 从库1，权重2
(20, '192.168.1.103', 3306, 1);  -- 从库2，权重1

-- 3. 配置用户
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('app', 'password', 10, 1);

-- 4. 配置查询规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, '^INSERT', 10, 1),
(2, 1, '^UPDATE', 10, 1),
(3, 1, '^DELETE', 10, 1),
(4, 1, '^SELECT.*FOR UPDATE', 10, 1),
(5, 1, '^SELECT', 20, 1);

-- 5. 加载并保存
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

运维命令

text

-- 查看统计信息
SELECT * FROM stats_mysql_commands_counters;
SELECT * FROM stats_mysql_connection_pool;
SELECT * FROM stats_mysql_query_digest;

-- 重置统计
SELECT * FROM stats_mysql_query_digest_reset;

-- 在线禁用节点
UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = '192.168.1.102';
LOAD MYSQL SERVERS TO RUNTIME;

-- 强制禁用（断开连接）
UPDATE mysql_servers SET status = 'OFFLINE_HARD' WHERE hostname = '192.168.1.102';
LOAD MYSQL SERVERS TO RUNTIME;

与其他方案对比

特性	ProxySQL	MySQL Router	MaxScale	HAProxy
读写分离	✅	✅	✅	❌
查询路由	✅	✅	✅	❌
查询缓存	✅	❌	✅	❌
连接池	✅	✅	✅	✅
协议解析	MySQL	MySQL	MySQL	TCP
性能	高	中	高	最高

要点总结

核心功能：读写分离、负载均衡、连接池、查询缓存
配置流程：服务器 → 用户 → 查询规则 → 加载运行时
主机组划分：写组（10）、读组（20）分离
负载均衡：权重轮询、最小连接数
健康检查：监控用户、定期探测、自动摘除故障节点
事务保持：transaction_persistent=1 确保事务完整性

📝 发现内容有误？点击此处直接编辑