14.4.2 用户认证与权限模型

用户认证与权限模型是 ProxySQL 进行安全接入与访问控制的核心。它通过维护前端用户表进行身份校验，并在认证通过后依据用户属性、规则组与主机组映射进行访问约束与路由决策，既保证安全可审计，又兼顾连接性能与运维可控。

原理草图（认证与路由决策链路）：

认证流程与关键字段#

核心字段常见含义：
- username：前端用户
- password：密码或密码哈希
- active：是否启用
- default_hostgroup：默认路由主机组
- default_schema：默认库
- transaction_persistent：事务持久化（事务期间固定后端）
- max_connections：用户连接上限
- default_query_rules_group：默认规则组

查看与初始化#

进入管理端并查看用户表：

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "SELECT * FROM mysql_users\G"

命令解释：
- -P6032 连接管理端口（管理库）。
- mysql_users 存放前端用户定义。

用户配置示例（可执行）#

场景：新增 app_ro 只读用户，默认路由到主机组 10，并绑定规则组 100。

-- 1) 登录管理端
mysql -u admin -padmin -h 127.0.0.1 -P6032

-- 2) 新增用户
INSERT INTO mysql_users(
  username, password, active, default_hostgroup,
  default_schema, transaction_persistent, max_connections,
  default_query_rules_group, comment
) VALUES (
  'app_ro', 'app_ro_pwd', 1, 10,
  'appdb', 1, 200,
  100, 'read-only user'
);

-- 3) 刷新与持久化
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

预期效果：
- 客户端使用 app_ro 连接 ProxySQL 即可被路由至主机组 10。
- 未配置规则组时，默认规则组 100 生效。

规则组示例（限制写入）#

为 app_ro 绑定的规则组 100 添加只读限制：

INSERT INTO mysql_query_rules(
  rule_id, active, match_pattern, destination_hostgroup, apply, comment
) VALUES
(1001, 1, '^SELECT', 10, 1, 'route SELECT to HG10'),
(1002, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP)', 0, 1, 'block writes');

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

规则解释：
- destination_hostgroup=10：读请求路由到读库主机组 10。
- destination_hostgroup=0：拒绝或默认不匹配（常用于阻断写操作）。

前端用户与后端用户映射示例#

若后端 MySQL 需要使用不同账号，建议在后端创建 app_ro 并授权只读：

-- 在后端 MySQL 执行
CREATE USER 'app_ro'@'%' IDENTIFIED BY 'app_ro_pwd';
GRANT SELECT ON appdb.* TO 'app_ro'@'%';
FLUSH PRIVILEGES;

常见排错与定位#

1) 认证失败（Access denied）#

检查用户是否加载到运行时：

SELECT username, active FROM mysql_users WHERE username='app_ro';
SELECT * FROM runtime_mysql_users WHERE username='app_ro';

解释：
- runtime_mysql_users 为运行时表，若缺失说明未执行 LOAD MYSQL USERS TO RUNTIME。

2) 路由异常（未按规则组生效）#

检查规则组是否生效：

SELECT * FROM runtime_mysql_query_rules WHERE rule_id IN (1001,1002);
SHOW PROCESSLIST;

解释：
- runtime_mysql_query_rules 若为空或规则未命中，需检查 match_pattern 正则是否正确。

3) 连接数被拒绝#

查看用户连接限制与当前连接：

SELECT username, max_connections FROM mysql_users WHERE username='app_ro';
SELECT * FROM stats_mysql_connection_pool WHERE user='app_ro';

安装与最小可运行示例#

若本机未安装 ProxySQL，可用以下方式快速启动并验证用户认证：

# 安装（以CentOS/RHEL为例）
sudo yum install -y proxysql

# 启动
sudo systemctl enable --now proxysql

# 验证管理端可用
mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "SELECT 1;"

练习#

创建 app_rw 用户，允许写入，路由到主机组 20，并设置 max_connections=50。
将 app_ro 的 default_schema 改为 reportdb 并生效到运行时。
设计一个规则组，使得 SELECT ... FOR UPDATE 也被阻止。