MySQL 代理层：ProxySQL

标签：INT MySQL ProxySQL 代理 hostgroup mysql NULL CHECK

文章目录

• • 说明
  • 安装部署
  • • 1.1 yum 安装
    • 1.2 启停管理
    • 1.3 查询版本
    • 1.4 Admin 管理接口
  • 入门体验
  • 功能介绍
  • • 3.1 多层次配置系统
  • 读写分离
  • • 将实例接入到代理服务
    • 定义主机组之间的复制关系
    • 配置路由规则
    • 事务读的配置
    • 延迟阈值和请求转发
  • ProxySQL 核心表
  • • mysql_users
    • mysql_servers
    • mysql_replication_hostgroups
    • mysql_query_rules
  • 高级特性
  • • SQL 审计
    • SQL 黑名单
    • ProxySQL Cluster
    • • 搭建 ProxySQL Cluster
  • 参考文档

说明

MySQL 常见代理有 MySQ Proxy、Atlas、MaxScale、ProxySQ L其中 MySQL Proxy 是 MySQL 原厂研发的，没有发布过 GA 版，项目已经 9 年没有维护，官方不建议生产环境使用。另外三个项目都是第三方研发的开源代理，其中 ProxySQL 项目的 Star 最多，而且一直都在维护。本篇 SOP 主要介绍 ProxySQL 运维管理。

ProxySQL 官网：https://www.proxysql.com/

安装部署

1.1 yum 安装

以 CentOS Linux release 7.8.2003 (Core) 系统版本为例：

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

使用 yum 安装，可以指定版本：

yum install proxysql OR yum install proxysql-version

1.2 启停管理

service proxysql start   # 启动 proxysql
service proxysql stop    # 停止 proxysql
service proxysql status  # 查看 proxysql 状态

1.3 查询版本

proxysql --version

1.4 Admin 管理接口

当 ProxySQL 启动后，将会监听两个端口：
**Admin 管理接口：**默认为 6032 该端口用于管理配置 ProxySQL。
**接收业务 SQL 接口：**默认为 6033 用于服务业务端口，类似于 MySQL 的 3306 端口。

Admin 管理接口兼容 MySQL 客户端协议，所以可以直接使用 MySQL 客户端连接这个管理接口。

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

一般来讲 Admin 接口不需要额外配置，最有可能需要配置的是 Admin 用户的密码。

Admin> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin               |
+---------------------------+

修改 Admin 接口密码：

set admin-admin_credentials='admin:YouPassword';

load admin variables to runtime; -- 立即生效
save admin variables to disk; -- 持久化磁盘

入门体验

以下是测试使用的环境信息，操作系统均为 CentOS 7.9 版本。

ip 地址	角色	程序
172.16.104.56	主节点	MySQL 5.7.33
172.16.104.57	备节点	MySQL 5.7.33
172.16.104.55	代理节点	ProxySQL 2.4.3

接下来，一起简单的测试一下 ProxySQL，使用下方命令登入管理端。

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

-- 设置监控后端使用的账号信息
set mysql-monitor_username = 'monitor';
set mysql-monitor_password = 'monitor';

-- 配置生效
load mysql variables to runtime;
save mysql variables to disk;

在后端创建 ProxySQL 的监控账户：

create user 'monitor'@'%' identified with mysql_native_password 
 by 'monitor';
 
grant replication client on *.* to 'monitor'@'%';

-- group replication
grant select on performance_schema.replication_group_member_stats 
 to 'monitor'@'%';

grant select on performance_schema.replication_group_members 
 to 'monitor'@'%';

配置后端节点，写入后端节点的 ip、端口、主机组。

insert into mysql_servers (hostgroup_id, hostname, port) values (1, '172.16.104.56', 3306);

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

配置访问用户，这里的用户分为两种含义：

• 前端用户：客户端用来访问 ProxySQL 的用户。
• 后端用户：ProxySQL 用来访问后端 MySQL 节点的用户。

在此，案例中我们配置的前后端用户一致。在 MySQL 中创建后端使用的账号：

-- 创建后端用户
create user 'op_user'@'%' identified by 'abc123';
grant all privileges on *.* to 'op_user'@'%';

将账号信息录入到 ProxySQL 中：

-- 写入用户信息
insert into mysql_users(username, password, default_hostgroup, comment) values ('op_user', 'abc123', 1, '后端用户');

-- 加载 & 持久化
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

连接代理节点，访问数据库：

mysql -u op_user -pabc123 -h 172.16.104.55 -P6033 -e'select @@hostname;'
+---------------+
| @@hostname    |
+---------------+
| 172-16-104-56 |
+---------------+

功能介绍

ProxySQL 默认的配置文件位于/etc/proxysql.cnf，第一次启动 ProxySQL 会初始化配置数据库，往后的所有配置都可以在 ProxySQL 数据库中修复，直接修改配置文件则不会生效，除非重新初始化。

3.1 多层次配置系统

ProxySQL 为了实现动态修改大部分配置项，不需要重启，设计了多级配置系统，将配置从运行时环境移到内存，并在有需求时持久化到磁盘上。

• 最底层的是 DISK 库和 CONFIG FILE，负责持久化保存配置。这里的 CONFIG FILE 就是传统的配置文件，ProxySQL 启动时，主要是从 DISK 库中读取配置加载到内存并最终加载到 runtime 生效，只有极少的几个特定配置内容是从 CONFIG FILE 中加载的，除非是第一次初始化 ProxySQL 运行环境或者 DISK 库为空。
• 中间层的是 MEMORY，表示的是内存数据库，管理接口中的 main 库。通过管理接口可以修改所有配置，都保存在内存数据库中，此时并没有生效也没有持久化，需要 load 到上层 RUNTIME 才能生效，save 到下层 DISK 才能持久化保存。
• 最上层的是 RUNTIME，它是 ProxySQL 相关线程运行时读取的数据结构。该数据结构中的配置都是已生效的配置。修改了 main 库中的配置后，必须 load 到 runtime 数据结构中才能使其生效。
  

在上面的多层配置系统图中，标注了** [1]、[2]、[3]、[4]、[5] ** 的序号。每个序号都有两个操作方向 from/to b 以下是各序号对应的操作：

• [1] LOAD FROM MEMORY / LOAD TO RUNTIME
• [2] SAVE FROM RUNTIME / SAVE TO MEMORY
• [3] LOAD FROM DISK / LOAD TO MEMORY
• [4] SAVE FROM MEMORY / SAVE TO DISK
• [5] LOAD FROM CONFIG

另外，上面的 是什么？这表示要 加载/保存 的是哪类配置。 详细如下：

+------------------------------------+
| tables                             |
+------------------------------------+
| global_variables                   |  # (1)
| mysql_collations                   |  # (N)
| mysql_group_replication_hostgroups |  # (2)
| mysql_query_rules                  |  # (3)
| mysql_query_rules_fast_routing     |  # (4)
| mysql_replication_hostgroups       |  # (5)
| mysql_servers                      |  # (6)
| mysql_users                        |  # (7)
| proxysql_servers                   |  # (8)
| scheduler                          |  # (9)
+------------------------------------+

• (1)中包含两类变量，以 amdin 为前缀的表示 admin variables，以 mysql 为前缀的表示 mysql variables。
• (2,5,6)对应的都是 mysql servers。
• (3,4)对应的是 mysql query rules。
• (7)对应的 mysql users。
• (9)对应的 scheduler。
• (N)只是一张表，保存的是 ProxySQL 支持的字符集和排序规则，不需要修改。
• (8)是 ProxySQL 的集群配置表，对应 proxysql_servers。

在 ProxySQL 客户端使用 DML/SET 语句修改配置，是直接在内存中修改的，所以需要使用命令持久化和激活，这也是官方推荐的管理方式。下面提供常见的管理语句：

1. 激活/持久化 MySQL 用户配置：

# Active current in-memory MySQL User configuration
LOAD MYSQL USERS TO RUNTIME;

# Save the current in-memory MySQL User configuration to disk
SAVE MYSQL USERS TO DISK;

2. 激活/持久化 MySQL 服务器配置和主机组配置：

# Active current in-memory MySQL Server and Replication Hostgroup configuration
LOAD MYSQL SERVERS TO RUNTIME;

# Save the current in-memory MySQL Server and Replication Hostgroup configuration to disk
SAVE MYSQL SERVERS TO DISK;

3. 激活/持久化 MySQL 查询规则：

# Active current in-memory MySQL Query Rule configuration
LOAD MYSQL QUERY RULES TO RUNTIME;

# Save the current in-memory MySQL Query Rule configuration to disk
SAVE MYSQL QUERY RULES TO DISK;

4. 激活/持久化 MySQL 管理变量：

# Active current in-memory MySQL Variable configuration
LOAD MYSQL VARIABLES TO RUNTIME;

# Save the current in-memory MySQL Variable configuration to disk
SAVE MYSQL VARIABLES TO DISK;

5. 激活/持久化 ProxySQL 管理变量：

# Active current in-memory ProxySQL Admin Variable configuration
LOAD ADMIN VARIABLES TO RUNTIME;

# Save the current in-memory ProxySQL Admin Variable configuration to disk
SAVE ADMIN VARIABLES TO DISK;

读写分离

在入门体验中，已完成监控账号、业务账号的创建和配置，并将主节点完成接入。本小节，将介绍一个高频的使用场景，就是读写分离。

将实例接入到代理服务

下图，是当前的 mysql_servers 表的配置，只添加了主库。

Admin> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 172.16.104.56 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

现在将备库也接入进来：

insert into mysql_servers (hostgroup_id, hostname, port) values (2, '172.16.104.57', 3306);

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

定义主机组之间的复制关系

主机之间的复制关系是通过 hostgroup_id 来绑定的，关系定义使用的是 mysql_replication_hostgroups 表。

insert into mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, check_type, comment) values(1, 2, 'read_only', 'op 集群');

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

ProxySQL 会基于后端节点 check_type 的值，来动态调整它所属的主机组，所以必须保证从库的 read_only 参数为 on 的状态。

配置路由规则

路由的规则是在 mysql_query_rules 中配置：

insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) values (1, 1, '^SELECT.*FOR UPDATE$', 1, 1);
insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) values (2, 1, '^SELECT', 2, 1);

• rule_id：是规则 ID。
• active：表示是否启动规则。
• match_pattern：表示匹配规则，支持正则匹配。
• apply：设置为 1，如果当前的规则匹配，则不再进行后续其他规则的匹配。
• destination_hostgroup：SQL转发目的 hostgroup。

上方配置的路由规则效果如下：

• 所有的 SELECT FOR UPDATE 操作将发往主库执行。
• 其他所有的 SELECT 操作将发往备库执行。
• 除此之外的所有操作将默认发往主库处理。

配置完成后，验证一下：

mysql -u op_user -pabc123 -h 172.16.104.55 -P6033

-- 测试执行 SQL 语句
select * from test_semi;
select * from test_semi where a = 10 for update;

连接管理节点，查看日志：

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

-- 查询 SQL 执行情况
select hostgroup, digest_text from stats.stats_mysql_query_digest order by first_seen desc;
+-----------+------------------------------------------------+
| hostgroup | digest_text                                    |
+-----------+------------------------------------------------+
| 1         | select * from test_semi where a = ? for update |
| 2         | select * from test_semi                        |
+-----------+------------------------------------------------+

事务读的配置

接下来我们验证另外一个操作，显式开启事务后执行 SELECT 操作。

begin;
select * from test_semi;
update test_semi set c = 123 where a = 10;
select * from test_semi;
commit;

执行结果可以通过 stats.stats_mysql_query_digest 表进行观测。这里直接说结论，显式开启事务后，会直接路由到主库执行，该行为由 mysql_users 表中的 transaction_persistent 参数有关。创建用户时，如果不指定 transaction_persistent 参数，其默认值为 1，表示事务开启后，所有的操作都会在事务开启的主机组中执行，此时会忽略所有的路由规则。
我们将 transaction_persistent 设置为 0 测试一下。

update mysql_users set transaction_persistent = 0 where username = 'op_user';

-- 配置生效
LOAD MYSQL USERS TO RUNTIME;

开启事务后，执行查询可以看到是路由到了备库，不过 transaction_persistent = 1 更符合我们对事务的认知习惯。

Admin> select hostgroup, digest_text from stats.stats_mysql_query_digest order by first_seen desc;
+-----------+----------------------------------------+
| hostgroup | digest_text                            |
+-----------+----------------------------------------+
| 1         | commit                                 |
| 1         | update test_semi set c = ? where a = ? |
| 2         | select * from test_semi                |
| 1         | begin                                  |
+-----------+----------------------------------------+

延迟阈值和请求转发

上方是一个比较简单的读写分离配置，实际上我们还会遇到如下问题：

• 如果备库宕机了，所有转发的查询都会失败。
• 如果备库的延迟过高，查询会读取到旧数据。

接下来我们通过配置 延迟阈值 和 路由权重 来解决。

• 备库宕机了，所有的请求都会转发到主库。
• 为备库设置延迟阈值，如果延迟大于该值，请求会自动转发到主库。

-- 写入一个规则
insert into mysql_servers (hostgroup_id, hostname, port) values (2, '172.16.104.56', 3306);

-- 修改备库配置
update mysql_servers set weight = 100, max_replication_lag = 30 where hostname = '172.16.104.57';

第一条 SQL 将主库加入到了 reader_hostgroup 中，这样当备库出现故障的时候，查询会自动路由到主库中。
第二条 SQL 调大了备库的权重，这样只有极少部分 SQL 会路由到主库，同时将 max_replication_lag 调整到了 30，表示延迟如果大于 30 的时候，查询会路由到主库。

ProxySQL 核心表

本节会介绍 ProxySQL 中常用的表，熟悉这些表中字段的含义。

mysql_users

该表用来配置用户信息。

CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend)) 

• username & password：用户名和密码。
• active：是否激活账号。
• use_ssl：设置为 1，则强制用户使用 SSL 证书进行身份验证。
• default_hostgroup：默认的主机组。如果没有匹配的路由规则或路由规则没配置，请求会转发到默认主机组。
• default_schema：默认的 schema。如果不设置，则由 mysql-default_schema 决定。
• schema_locked：目前未实现。
• transaction_persistent：设置为 1，则代表一个事务内的所有请求都会转发到第一个主机组内执行。
• fast_forward：设置为 1，则表示由该用户发起的 SQL 会跳过重写、缓存 等查询处理层，执行转发到后端。
• backend & frontend：当前没有实现前后端分离，默认为 1 即可。
• max_connections：账号的最大连接数，默认为 1。
• attributes：目前未实现。
• comment：注释。

mysql_servers

用于配置后端的 MySQL 节点。

CREATE TABLE mysql_servers (
    hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
    gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
    compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port) )

• hostgroup_id：主机组 ID。
• hostname：后端节点的主机名或 IP。
• port：后端节点的端口。
• gtid_port：不详。
• status：节点的状态，有 ONLINE、SHUNNED、OFFLINE_SOFT、OFFLINE_HARD 四个取值。
  • ONLINE：节点状态正常，可对外服务。
  • SHUNNED：节点暂时离线，无法对外服务。
  • OFFLINE_SOFT：节点离线，在离线前会等待该节点的事务执行完。
  • OFFLINE_HARD：节点离线，如果当前节点有事务正在执行会 Kill 掉。
• weight：读写分离的权重，权重越高被分发的请求就越多。
• compression：是否开启压缩。
• max_connections：限制 ProxySQL 到后端节点的最大连接数。
• max_replication_lag：主备的延迟阈值。一旦超过该值，该节点的状态会被设置为 SHUNNED，直到延迟恢复。
• use_ssl：是否开启 SSL 连接，针对的是 ProxySQL 与后端之间的连接。
• max_latency_ms：ProxySQL 会定期对后端节点进行 ping 操作，如果 ping 的响应时间超过该参数，则会将该节点从连接池中剔除。
• comment：注释。

mysql_replication_hostgroups

用于配置复制关系的表。

CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
    check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))

• writer_hostgroup：定义可写的主机组。
• reader_hostgroup：定义只读的主机组。
• check_type：检测类型，提供了多种选择，基本都是通过 read_only 的值来区分的。
• comment：注释。

将主库和备库的 hostgroup 配置到 mysql_replication_hostgroups 表中后，ProxySQL 会检测主备库的 read_only 状态。
当检测到 read_only 从 OFF 变成 ON 时，会将 writer_hostgroup 中的主机添加的 reader_hostgrup 中。
反之，当 read_only 从 ON 变成 OFF 时，会将其调整到 writer_hostgroup 组中。
由此可见，在 ProxySQL 中，一定要将备库的 read_only 设置为 on，否则可能会出现双写。
当一个后端 MySQL 实例的 read_only 状态从 ON 变成 OFF 时，会将该实例加入到 writer_hostgroup 中，同时该实例依然位于 read_hostgroup 中。如果需要将该实例从 read_hostgroup 中移除，需要将参数 mysql-monitor_writer_is_also_reader 设置为 false。

set mysql-monitor_writer_is_also_reader='false';
load mysql variables to runtime;

mysql_query_rules

用于配置路由关系的表。

CREATE TABLE mysql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535),
    digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT CHECK (flagOUT >= 0),
    replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
    cache_timeout INT CHECK(cache_timeout >= 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED CHECK (timeout >= 0),
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED CHECK (delay >=0),
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    gtid_from_hostgroup INT UNSIGNED,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR)

• rule_id：指定规则的 ID。该值越小，越先匹配。
• active：是否启用规则。
• username：基于用户名进行匹配。
• schemaname：基于 schema 名进行匹配。
• flagIN & flagOUT：定义规则的入口和出口，用于实现链式匹配规则。
• client_addr：基于客户端地址进行匹配。通过 client_addr 实现简单的白名单功能。
• proxy_addr & proxy_port：如果 ProxySQL 部署的服务器中有多个 IP 地址，可匹配来自指定 IP 的流量。
• digest：基于 Query ID 进行匹配。
• match_digest & match_pattern：基于正则表达式进行匹配，match_digest 是匹配模版化后的 SQL，match_pattern 是匹配原 SQL。
• negate_match_pattern：设置为 1，表示没匹配上 match_digest & match_pattern 的规则才为真。
• re_modifiers：设置正则引起的修饰符。
• replace_pattern：替换后的文本，用于查询重写。
• destination_hostgroup：规则目标的主机组。
• cache_ttl：设置结果集的缓存时长，单位是毫秒。
• cache_empty_result：是否缓存空的结果集。
• cache_timeout：暂未实现。
• reconnect：暂无实现。
• timeout：定义查询的超时时长，单位是毫秒。如果查询在指定时间没有完成，则会被 ProxySQL kill 掉，如果不指定该字段，则由全局参数控制该行为 mysql-default_query_timeout 默认为 10 小时。
• retries：查询失败重试的次数。
• delay：定义查询延迟执行的时长，单位是毫秒。
• mirror_flagOUT & mirror_hostgroup：用于景象功能。
• error_msg：匹配规则的操作将返回 error_msg，用于实现黑名单的功能。
• log：是否将匹配规则的查询记录到审计日志中。不指定的话，则由全局参数 mysql-eventlog_defalut_log 决定，默认为 0。
• apply：若设置为 1 的话，则操作匹配到该规则时，会直接被转发给后端节点处理，不会再进行其他规则的匹配。

高级特性

本小节，介绍一下 ProxySQL 实用的高级特性。

SQL 审计

ProxySQL 可将流经它的 SQL 语句全部记录下来，用于审计分析和问题定位。是一个非常实用的功能，不过数据库流量如果比较大的话，会占用很大的存储空间。

Admin> show variables like 'mysql-eventslog%';
+-----------------------------+-----------+
| Variable_name               | Value     |
+-----------------------------+-----------+
| mysql-eventslog_filename    |           |
| mysql-eventslog_filesize    | 104857600 |
| mysql-eventslog_default_log | 0         |
| mysql-eventslog_format      | 1         |
+-----------------------------+-----------+

各参数含义如下：

• mysql-eventslog_filename：日志前缀名。默认为空，代表 SQL 审计没有开启。
• mysql-eventslog_filesize：日志的最大大小。超过此限制，会对日志进行切割，默认为 100MB。
• mysql-eventslog_default_log：是否开启操作审计，默认为 0 不记录。
• mysql-eventslog_format：日志的格式。1 表示二进制格式 2 表示 JSON 格式。如果要查看二进制格式的内容，必须使用专用的解析工具 eventslog_reader_sample。

下面我们进行一个测试：

set mysql-eventslog_filename = 'query';
set mysql-eventslog_format = 2;
set mysql-eventslog_default_log = 1;

LOAD MYSQL VARIABLES TO RUNTIME;

{"client":"172.16.104.56:38474","digest":"0xDC257DF652F9B5E6","duration_us":1764,"endtime":"2024-06-26 14:43:39.203870","endtime_timestamp_us":1719384219203870,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from fractest1","rows_affected":0,"rows_sent":1,"schemaname":"test","server":"172.16.104.57:3306","starttime":"2024-06-26 14:43:39.202106","starttime_timestamp_us":1719384219202106,"thread_id":2,"username":"op_user"}

SQL 黑名单

当有一类 SQL 突然并发较高，打满数据库资源时，往往会导致数据库整体变慢。此时处理的方法，往往是让研发定位接口，然后停掉相关业务，及时止血，但也需要一定的时间，此时业务可能已经受损。
如果使用 ProxySQL 可以通过 SQL 黑名单的方式解决。接下来我们介绍通过 SQL 指纹来屏蔽 SQL 语句。
状态库 stats_mysql_query_digest 表中，记录了一些 TOP SQL 的信息，可从该表中获取 SQL 指纹。

select * from stats.stats_mysql_query_digest;

-- digest 字段是 SQL 指纹
-- 0xDC257DF652F9B5E6 | select * from fractest1

获取指纹后，需要写入路由规则，需要注意的是，路由规则是按 rule_id 顺序匹配的，所以屏蔽规则需要放在 rule_id 小的规则中，之前演示 rule_id = 1 和 2，所以限制改大。

Admin> update mysql_query_rules set rule_id = 11 where rule_id = 1;
Query OK, 1 row affected (0.00 sec)

Admin> update mysql_query_rules set rule_id = 12 where rule_id = 2;
Query OK, 1 row affected (0.00 sec)

插入屏蔽规则：·

insert into mysql_query_rules (rule_id, destination_hostgroup, digest, error_msg, active, apply, comment) values (1, 1, '0xDC257DF652F9B5E6', 'request denied by rule', 1, 1, 'request denied by rule');

load mysql query rules to runtime;
save mysql query rules to disk;

验证屏蔽效果：

op_user@mysql 15:15:  [test]>select * from fractest1;
ERROR 1148 (42000): request denied by rule

ProxySQL Cluster

ProxySQL 作为一个中间价，本身是无状态的，如果代理节点出现故障，那就会导致业务无法访问。所以线上一般会部署多个节点，使用 VIP 或者 LVS 来进行故障切换。既然涉及到多个节点，ProxySQL 的配置调整需要实时同步，这样将流量切换到任意一个代理都是正确可用的。这就是接下来 ProxySQL Cluster 的作用。

搭建 ProxySQL Cluster

开启 proxysql 的集群功能，需要：

• 配置 cluster 账号，用于查询对比集群内各 proxysql 节点的配置信息
• 配置 proxysql_servers，将集群内的 proxysql 节点信息添加到 proxysql_servers 表

以上操作需要到组成集群的每一个 proxysql 节点上执行。
通过参数 admin-cluster_username 和 admin-cluster_password 设置 cluster 账号。不能使用 admin 账号作为cluster 账号，因为 admin 账号只能在本地(127.0.0.1)登陆。
同时还需要将 cluster 账号添加到参数 admin-admin_credentials 中。

set admin-admin_credentials = 'admin:admin;clusteradmin:passadmin';

set admin-cluster_username='clusteradmin';
set admin-cluster_password='passadmin';

load admin variables to runtime;
save admin variables to disk;

将组成 proxysql 集群的多个节点的信息添加到 proxysql_servers 表。

mysql> show create table proxysql_servers\G
*************************** 1. row ***************************
       table: proxysql_servers
Create Table: CREATE TABLE proxysql_servers (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 6032,
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostname, port) )
1 row in set (0.00 sec)

insert into proxysql_servers values('172.16.104.55', 6032, 1, 'proxysql node 1');
insert into proxysql_servers values('172.16.104.56', 6032, 1, 'proxysql node 2');


LOAD PROXYSQL SERVERS TO RUNTIME;
save PROXYSQL SERVERS TO disk;

参考文档

【1】ProxySQL Multi layer configuration system

标签：INT,MySQL,ProxySQL,代理,hostgroup,mysql,NULL,CHECK
From： https://blog.csdn.net/qq_42768234/article/details/140125080