MySQL 代理层：ProxySQL-易微帮

说明

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 语句修改配置，是直接在内存中修改的，所以需要使用命令持久化和激活，这也是官方推荐的管理方式。下面提供常见的管理语句：

激活/持久化 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;

激活/持久化 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;

激活/持久化 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;

激活/持久化 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;

激活/持久化 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

MySQL 代理层：ProxySQL

文章目录

说明