27 MySQL ProxySQL中间件

# 1 数据库中间件简介

介于应⽤和数据库之间，进⾏数据处理与交互的中间服务。

什么场景下会⽤到数据库中间件呢？

1. 要扩展读能⼒。 
 	2.  要扩展写能⼒。 
 	3. 需要⼀些安全能⼒，例如防⽕墙，审计。 
 	4. 管理上的便利性。例如后端节点的平滑上下线，SQL ⿊名单等。

## 1.1 数据库中间件的两种模式

### 1.1.1 代理模式

### 1.1.2 客户端模式

## 1.2 常⽤的数据库中间件介绍

# 2 ProxySQL 的安装

> ProxySQL Github地址：https://github.com/sysown/proxysql
>
> ProxySQL 地址：https://www.proxysql.com/
>
> 软件包地址：https://github.com/sysown/proxysql/releases

```bash
root@ProxySQL:~# curl -LO https://github.com/sysown/proxysql/releases/download/v2.6.2/proxysql_2.6.2-dbg-ubuntu22_amd64.deb
root@ProxySQL:~# dpkg -i proxysql_2.6.2-dbg-ubuntu22_amd64.deb
root@ProxySQL:~# systemctl start proxysql
root@ProxySQL:~# netstat -lntup| grep proxysql
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2064/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2064/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2064/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2064/proxysql       
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      2064/proxysql
```

其中，

- 6032：管理端⼝，默认的管理⽤户和密码是 admin。

- 6033：ProxySQL 对外服务端⼝。

通过 RPM 包安装，默认的配置⽂件是 /etc/proxysql.cnf。

```bash
root@ProxySQL:~# grep -Ev '^#|^$' /etc/proxysql.cnf
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
	admin_credentials="admin:admin"
	mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
	threads=4
	max_connections=2048
	default_query_delay=0
	default_query_timeout=36000000
	have_compress=true
	poll_timeout=2000
	interfaces="0.0.0.0:6033"
	default_schema="information_schema"
	stacksize=1048576
	server_version="5.5.30"
	connect_timeout_server=3000
	monitor_username="monitor"
	monitor_password="monitor"
	monitor_history=600000
	monitor_connect_interval=60000
	monitor_ping_interval=10000
	monitor_read_only_interval=1500
	monitor_read_only_timeout=500
	ping_interval_server_msec=120000
	ping_timeout_server=500
	commands_stats=true
	sessions_sort=true
	connect_retries_on_failure=10
}
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)
```

# 3 ProxySQL 简单⼊⻔

## 1 配置后端节点

主要是配置 mysql_servers 表。

```sql
root@ProxySQL:~# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL> '
ProxySQL> insert into mysql_servers(hostgroup_id,hostname,port) values(1,'172.31.7.110',3306);
Query OK, 1 row affected (0.00 sec)

ProxySQL> load mysql servers to runtime;
Query OK, 0 rows affected (0.02 sec)

ProxySQL> save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
```

插⼊后端节点的 IP 和 PORT，并将其主机组 ID 设置为 1。

## 2 配置访问⽤户

MySQL 中配置后端⽤户：

```sql
create user root@'%' identified by '123456';
grant all privileges on *.* to 'root'@'%' WITH GRANT OPTION;

root@ProxySQL:~# mysql -h172.31.7.110 -P3306 -uroot -p123456
mysql> create user proxy_user@'%' identified with mysql_native_password by 'proxy_pass';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on *.* to proxy_user@'%';
Query OK, 0 rows affected (0.01 sec)

#################################################################################
create user proxy_user@'%' identified with mysql_native_password by 'proxy_pass';
grant all on *.* to proxy_user@'%';
```

ProxySQL 中配置前端⽤户，主要是配置 mysql_users 表。

```sql
root@ProxySQL:~# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL> '
ProxySQL> insert into mysql_users(username,password,default_hostgroup) values('proxy_user','proxy_pass',1);
Query OK, 1 row affected (0.00 sec)

ProxySQL> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

ProxySQL> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
```

如果没有匹配得上的路由规则，默认会将请求转发给 default_hostgroup 。

## 3 验证配置的效果

执⾏⼏个简单 SQL，看能否达到预期效果。

```bash
# 注 ProxySQL 主机的IP 是172.31.7.66
root@ProxySQL:~# mysql -h172.31.7.66 -uproxy_user -pproxy_pass -P 6033 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+
| @@hostname     |
+----------------+
| db01.hosts.com |
+----------------+
root@ProxySQL:~# mysql -h172.31.7.66 -uproxy_user -pproxy_pass -P 6033 -e "create database test"
root@ProxySQL:~# mysql -h172.31.7.66 -uproxy_user -pproxy_pass -P 6033 -e "create table test.t1(id int primary key,c1 varchar(10))"
root@ProxySQL:~# mysql -h172.31.7.66 -uproxy_user -pproxy_pass -P 6033 -e "insert into test.t1 values(1,'a')"
root@ProxySQL:~# mysql -h172.31.7.66 -uproxy_user -pproxy_pass -P 6033 -e "select * from test.t1"
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
+----+------+
```

⼀个简单的 ProxySQL 环境搭建完毕，后续针对 ProxySQL 6033 端⼝的所有请求都会转发给 192.168.79.10:3306 这个 MySQL 实例。

## 4 多层配置系统

![img](https://img.sunrisenan.com/img/2024/05/15/165453362.png)

- RUNTIME ：基于内存的数据结构，保存着 ProxySQL 当前正在使⽤的配置，⽆法直接修改
- MEMORY ：基于内存的 SQLite3 数据库。
- DISK：基于磁盘的 SQLite3 数据库。
- CONFIG FILE：配置⽂件。

ProxySQL 在启动的时候，会⾸先通过配置⽂件确认数据⽬录，接着，判断数据⽬录中是否存在 proxysql.db。如 果存在，则从 proxysql.db 中加载 ProxySQL 的配置信息。如果不存在，则从配置⽂件中加载 ProxySQL 的配置信 息。

```sql
root@ProxySQL:~# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL> '
ProxySQL> show databases;
+-----+------------------+-------------------------------------+
| seq | name             | file                                |
+-----+------------------+-------------------------------------+
| 0   | main             |                                     |
| 2   | disk             | /var/lib/proxysql/proxysql.db       |
| 3   | stats            |                                     |
| 4   | monitor          |                                     |
| 5   | stats_history    | /var/lib/proxysql/proxysql_stats.db |
| 6   | myhgm            |                                     |
| 7   | monitor_internal |                                     |
+-----+------------------+-------------------------------------+
7 rows in set (0.00 sec)
```

main 包含了 RUNTIME 和 MEMORY 层的配置信息，disk 包含了 DISK 层的配置信息。

RUNTIME、MEMORY 和 DISK 这三层的配置信息都是相互独⽴的。如果要将下层的配置信息加载到上层，需使⽤ LOAD 命令。相反，如果要将上层的配置信息持久化到下层，需使⽤ SAVE 命令。

在使⽤这两个命令时，既可以通过 FROM ⼦句指定源层，也可以通过 TO ⼦句指定⽬标层。

```sql
LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME 将配置信息从 MEMORY 加载到 RUNTIME
SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME 将配置信息从 RUNTIME 持久化到 MEMORY
LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK 将配置信息从 DISK 加载到 MEMORY
SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK 将配置信息从 MEMORY 持久化到 DISK
LOAD MYSQL USERS FROM CONFIG 将配置信息从配置⽂件加载到 MEMORY
```

除了 MYSQL USERS，其它常⽤的配置信息有：MYSQL SERVERS、MYSQL QUERY RULES、MYSQL VARIABLES、ADMIN VARIABLES。

# 4 ProxySQL高级特性

## 4.1 Scheduler

定时器。

```sql
ProxySQL> show create table scheduler\G
*************************** 1. row ***************************
       table: scheduler
Create Table: CREATE TABLE scheduler (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
    filename VARCHAR NOT NULL,
    arg1 VARCHAR,
    arg2 VARCHAR,
    arg3 VARCHAR,
    arg4 VARCHAR,
    arg5 VARCHAR,
    comment VARCHAR NOT NULL DEFAULT '')
1 row in set (0.00 sec)
```

这⾥⾯，

- interval_ms：脚本执⾏的时间间隔，单位 ms。
- filename：脚本名，必须是绝对路径，且有可执⾏权限。
- arg1.. arg5：最多可设置 5 个参数。

脚本能否正常运⾏，可通过错误⽇志（ /var/lib/proxysql/proxysql.log ）来判断。

## 4.2 SQL 审计

各参数的具体含义如下：

- mysql-eventslog_filename：⽇志前缀名。默认为空，代表 SQL 审计没有开启。
- mysql-eventslog_filesize：⽇志的最⼤⼤⼩。默认 100 MB。
- mysql-eventslog_default_log：是否将操作记录在审计⽇志中。默认为 0，不记录。
- mysql-eventslog_format：⽇志的格式。设置为 1 是⼆进制格式，设置为 2 是 JSON 格式。

```sql
ProxySQL> set mysql-eventslog_filename='/var/lib/proxysql/proxysql_query';
Query OK, 1 row affected (0.00 sec)

ProxySQL> set mysql-eventslog_default_log=1;
Query OK, 1 row affected (0.00 sec)

ProxySQL> set mysql-eventslog_format=2;
Query OK, 1 row affected (0.00 sec)

ProxySQL> load mysql variables to runtime;
Query OK, 0 rows affected (0.02 sec)

#########################################################################
set mysql-eventslog_filename='/var/lib/proxysql/proxysql_query';
set mysql-eventslog_default_log=1;
set mysql-eventslog_format=2;
load mysql variables to runtime;
```

审计⽇志的内容如下

```bash
root@ProxySQL:~# mysql -h172.31.7.66 -uproxy_user -pproxy_pass -P 6033 -e "select * from test.t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
+----+------+

root@ProxySQL:~# tail /var/lib/proxysql/proxysql_query.00000001 | jq
{
  "client": "172.31.7.113:40414",
  "digest": "0x226CD90D52A2BA0B",
  "duration_us": 0,
  "endtime": "2024-05-15 17:08:47.123010",
  "endtime_timestamp_us": 1715764127123010,
  "event": "COM_QUERY",
  "hostgroup_id": -1,
  "query": "select @@version_comment limit 1",
  "rows_sent": 0,
  "schemaname": "information_schema",
  "starttime": "2024-05-15 17:08:47.123010",
  "starttime_timestamp_us": 1715764127123010,
  "thread_id": 9,
  "username": "proxy_user"
}
{
  "client": "172.31.7.113:40414",
  "digest": "0x67A9FB367B49CAB9",
  "duration_us": 4002,
  "endtime": "2024-05-15 17:08:47.128973",
  "endtime_timestamp_us": 1715764127128973,
  "event": "COM_QUERY",
  "hostgroup_id": 1,
  "query": "select * from test.t1",
  "rows_affected": 0,
  "rows_sent": 1,
  "schemaname": "information_schema",
  "server": "172.31.7.110:3306",
  "starttime": "2024-05-15 17:08:47.124971",
  "starttime_timestamp_us": 1715764127124971,
  "thread_id": 9,
  "username": "proxy_user"
}
```

## 4.3 SQL ⿊名单

在 ProxySQL 中，实现 SQL ⿊名单的⽅法有两种：

1. mysql_query_rules 中的 error_msg 字段。
 	2. Firewall whitelist。这个功能是 ProxySQL 2.0.9 引⼊的。

看下⾯这个 Demo，基于⽅法 1 实现的 SQL ⿊名单功能。

```sql
ProxySQL> insert into mysql_query_rules(rule_id,active,match_pattern,error_msg,apply) values (100,1,'^select \* from employees\.employees$','query not allowed',1);
Query OK, 1 row affected (0.00 sec)

ProxySQL> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

root@ProxySQL:~# mysql -h172.31.7.66 -uproxy_user -pproxy_pass -P 6033
mysql> select * from employees.employees;
ERROR 1148 (42000): query not allowed
```

## 4.4 查询改写

```SQL
select * from employees.dept_emp
```

查询改写还是基于路由规则表来实现。

ProxySQL> delete from mysql_query_rules;
Query OK, 1 row affected (0.01 sec)

ProxySQL> insert into
    -> mysql_query_rules(rule_id,active,match_digest,match_pattern,replace_pattern,apply)
    -> values
    -> (1,1,'^select \* from employees\.dept_emp$','dept_emp','dept_emp limit 10',0);
Query OK, 1 row affected (0.00 sec)

ProxySQL> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

#######################################################
select * from stats_mysql_query_digest_reset limit 1;
delete from mysql_query_rules;
insert into
mysql_query_rules(rule_id,active,match_digest,match_pattern,replace_pattern,apply)
values
(1,1,'^select \* from employees\.dept_emp$','dept_emp','dept_emp limit 10',0);
load mysql query rules to runtime;
```

其中，

- match_digest：指定匹配规则。只有满⾜规则的 SQL 才能改写。
- match_pattern：旧字符串。
- replace_pattern：新字符串。
- apply：设置为 0，则继续匹配其它路由规则。建议设置为 0，这样的话，就⽆需关⼼改写后的 SQL 会转发到 哪个后端节点。

接下来看看改写的效果。

```BASH
root@ProxySQL:~# mysql -h172.31.7.66 -uproxy_user -pproxy_pass -P 6033
mysql> select * from employees.dept_emp;   #这里会自动加上limit 10

从 digest_text 的内容来看，SQL 已改写成功。

上⾯虽然指定了 match_digest ，但对于 ProxySQL 的查询改写来说，必需的字段其实只有两个：match_pattern 和 replace_pattern，上⾯的 match_digest 只是⽤来过滤。

# 5 如何部署读写分离环境

读写分离是中间件的⼀个⾼频使⽤场景。

## 5.1 配置后端节点

```sql
root@ProxySQL:~# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL> '
ProxySQL> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.31.7.110',3306);
Query OK, 1 row affected (0.01 sec)

ProxySQL> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'172.31.7.111',3306);
Query OK, 1 row affected (0.00 sec)

ProxySQL> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'172.31.7.112',3306);
Query OK, 1 row affected (0.00 sec)

ProxySQL> load mysql servers to runtime;
Query OK, 0 rows affected (0.04 sec)

ProxySQL> save mysql servers to disk;
Query OK, 0 rows affected (0.06 sec)

##########################################################################
# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL> '
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.31.7.110',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'172.31.7.111',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'172.31.7.112',3306);
load mysql servers to runtime;
save mysql servers to disk;
```

## 5.2 配置⽤户

这⾥，需要配置两个⽤户：监控⽤户和业务访问⽤户。

⾸先，创建业务访问⽤户。

```sql
# mysql(主从复制  只在主库执行 确定能同步到从库)
create user split_user@'%' identified with mysql_native_password by 'split_pass';
grant create,select,insert,delete,update on *.* to split_user@'%';

mysql> create user split_user@'%' identified with mysql_native_password by 'split_pass';
Query OK, 0 rows affected (0.02 sec)

mysql> grant create,select,insert,delete,update on *.* to split_user@'%';
Query OK, 0 rows affected (0.01 sec)

# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL> '
insert into mysql_users(username, password, default_hostgroup) values('split_user','split_pass',10);
load mysql users to runtime;
save mysql users to disk;

ProxySQL> insert into mysql_users(username, password, default_hostgroup)
    -> values('split_user','split_pass',10);
Query OK, 1 row affected (0.00 sec)

ProxySQL> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

ProxySQL> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
```

接着，创建监控⽤户。

该⽤户⽤于检测 MySQL 节点的健康状态，监控的范围包括：

1. 后端节点的连通性。
2. 后端节点 read_only 的值。
3. 后端节点的主从延迟情况。

监控的结果会记录在 monitor 库中。

在 ProxySQL 中，监控⽤户及密码分别由参数 mysql-monitor_username、mysql-monitor_password 决定，这 两个参数的默认值为 monitor。

这⾥，保持 ProxySQL 中默认的账号密码不变，只在 MySQL 中创建对应的⽤户。

```bash
mysql> create user 'monitor'@'%' identified with mysql_native_password by 'monitor';
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication client on *.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)

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

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

```sql
ProxySQL> insert into mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup,check_type, comment) values (10,20,'super_read_only','repl_group_1');
Query OK, 1 row affected (0.00 sec)

ProxySQL> load mysql servers to runtime;
Query OK, 0 rows affected (0.05 sec)

ProxySQL> save mysql servers to disk;
Query OK, 0 rows affected (0.05 sec)

########################################
insert into mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup,check_type, comment) values (10,20,'super_read_only','repl_group_1');
load mysql servers to runtime;
save mysql servers to disk;
```

其中，

- writer_hostgroup：写节点（主库）所在的主机组。
- reader_hostgroup：读节点（从库）所在的主机组。
- check_type：判断读节点还是写节点的标准。
- comment：注释。

注意，必须确保读节点（ 从库）的 super_read_only 处于开启状态。

```sql
mysql> SELECT@@super_read_only;
+-------------------+
| @@super_read_only |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

# 检查当前 super_read_only 的值：
SELECT @@super_read_only;

# 设置 super_read_only 的值为 1：
SET GLOBAL super_read_only = 1;

# 检查是否成功设置为只读模式：
SELECT @@super_read_only;
```

## 5.4 配置路由规则

```sql
ProxySQL> delete from mysql_query_rules;   #上面只有个查询改写 这里给删掉
Query OK, 1 row affected (0.00 sec)

ProxySQL> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values (1,1,'^SELECT.*FOR UPDATE$',10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQL> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (2,1,'^SELECT',20,1);
Query OK, 1 row affected (0.00 sec)
```

其中，

- rule_id：规则 ID。在进⾏规则匹配时，会按照规则 ID 从⼩到⼤的顺序依次匹配。
- active：是否启⽤规则。
- match_digest：指定匹配规则，⽀持正则匹配。
- destination_hostgroup：指定⽬标主机组。匹配上规则的操作将发往该主机组处理。
- apply：设置为 1，如果当前的规则匹配，则不再进⾏后续其它规则的匹配。

## 5.5  验证读写分离的效果

```sql
# mysql -h172.31.7.66 -usplit_user -psplit_pass -P6033
mysql> create database slowtech;
Query OK, 1 row affected (0.01 sec)

mysql> create table slowtech.t1(id int, c1 varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into slowtech.t1 values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update slowtech.t1 set c1='c' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from slowtech.t1 where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from slowtech.t1 for update;
+------+------+
| id   | c1   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from slowtech.t1;
+------+------+
| id   | c1   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.01 sec)
```

验证操作分别是在哪个节点上执⾏的。

```sql
ProxySQL> select hostgroup,digest_text from stats.stats_mysql_query_digest order by first_seen;
+-----------+------------------------------------------------+
| hostgroup | digest_text                                    |
+-----------+------------------------------------------------+
| 10        | select @@version_comment limit ?               |
| 10        | create database slowtech                       |
| 10        | create table slowtech.t1(id int,c1 varchar(?)) |
| 10        | insert into slowtech.t1 values(?,?),(?,?)      |
| 10        | drop database slowtech                         |
| 10        | update slowtech.t1 set c1=? where id=?         |
| 10        | delete from slowtech.t1 where id=?             |
| 10        | select * from slowtech.t1 for update           |
| 20        | select * from slowtech.t1                      |
+-----------+------------------------------------------------+
9 rows in set (0.04 sec)

```

可以看到，除了最后⼀个 SELECT 操作是在从库上执⾏，其它所有操作都是在主库上执⾏的。

## 5.6 验证事务读

⾸先清空 stats.stats_mysql_query_digest 表中的数据。

```sql
ProxySQL> select * from stats.stats_mysql_query_digest_reset limit 1;
```

继续来验证。测试 SQL 如下，

```sql
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from slowtech.t1 where id=1;
Empty set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

############################
begin;
select * from slowtech.t1 where id=1;
commit;
```

这个 SELECT 操作竟然是在主库上执⾏的，不应该匹配规则 2 么？这个实际上与账号创建时 transaction_persistent 的值有关。

transaction_persistent 为 1 意味着⼀个事务开始时的第⼀条操作是在某个主机组内执⾏的，则这个事务后续的其 它操作都将转发到这个主机组内执⾏，此时会忽略任何路由规则。

以上是 ProxySQL 中实现读写分离的基本步骤。

虽然实现了读写分离，但这种⽅式还是过于简单，没有考虑以下两种常⻅场景：

1. 从库宕机。
2. 主从延迟。

针对上⾯这两种常⻅场景，通常的解决思路是：

1. 从库宕机，读请求转发到主库执⾏。
2. 主从延迟设置阈值。超过阈值的读请求转发到主库执⾏。

这两个需求，ProxySQL 同样也能满⾜，具体 SQL 如下：

```sql
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'172.31.7.110',3306);
update mysql_servers set weight=100,max_replication_lag=10 where hostgroup_id=20 and hostname='172.31.7.111';
update mysql_servers set weight=100,max_replication_lag=10 where hostgroup_id=20 and hostname='172.31.7.112';
load mysql servers to runtime;
save mysql servers to disk;

#############################################
ProxySQL> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'172.31.7.110',3306);
Query OK, 1 row affected (0.00 sec)

ProxySQL> update mysql_servers set weight=100,max_replication_lag=10 where hostgroup_id=20 and hostname='172.31.7.111';
Query OK, 1 row affected (0.01 sec)

ProxySQL> update mysql_servers set weight=100,max_replication_lag=10 where hostgroup_id=20 and hostname='172.31.7.112';
Query OK, 1 row affected (0.01 sec)

ProxySQL> load mysql servers to runtime;
Query OK, 0 rows affected (0.04 sec)

ProxySQL> save mysql servers to disk;
Query OK, 0 rows affected (0.04 sec)
```

这⼏条 SQL 的作⽤如下：

1. 将主库添加到 reader_hostgroup 中。当从库出现故障时，读请求能转发到主库执⾏。
2. 提⾼从库的权重（weight）。这样即使主库也在 reader_hostgroup 中，也不会承担太多的读请求。
3. 设置从库可允许的最⼤延迟时间（max_replication_lag）。主从延迟超过这个值，读请求会转发到主库执 ⾏。

最后我们看看调整后的配置。

```sql
ProxySQL> select hostgroup_id,hostname,port,status,weight,max_replication_lag from mysql_servers;
+--------------+--------------+------+--------+--------+---------------------+
| hostgroup_id | hostname     | port | status | weight | max_replication_lag |
+--------------+--------------+------+--------+--------+---------------------+
| 1            | 172.31.7.110 | 3306 | ONLINE | 1      | 0                   |
| 20           | 172.31.7.111 | 3306 | ONLINE | 100    | 10                  |
| 20           | 172.31.7.112 | 3306 | ONLINE | 100    | 10                  |
| 10           | 172.31.7.110 | 3306 | ONLINE | 1      | 0                   |
| 20           | 172.31.7.110 | 3306 | ONLINE | 1      | 0                   |
+--------------+--------------+------+--------+--------+---------------------+
5 rows in set (0.00 sec)
```

⾄此，⼀个⽣产可⽤的 ProxySQL 读写分离环境搭建完毕。

# 6 ProxySQL 作者推崇的读写分库⽅案

下⾯我们看看 ProxySQL 作者推崇的读写分库⽅案。还是基于之前的环境，我们看看具体的操作步骤。

## 6.1 清除之前的路由规则

```sql
ProxySQL> delete from mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)

ProxySQL> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

################################
delete from mysql_query_rules;
load mysql query rules to runtime;
```

因为没有路由规则，所有的操作都会转发到默认主机组，即主库执⾏。

## 6.2 统计 stats.stats_mysql_query_digest 中耗时较久的 SELECT 操作

```sql
ProxySQL> select hostgroup,digest_text,digest,count_star,sum_time from stats.stats_mysql_query_digest where digest_text like 'select%' order by sum_time desc limit 5;
+-----------+----------------------------------+--------------------+------------+----------+
| hostgroup | digest_text                      | digest             | count_star | sum_time |
+-----------+----------------------------------+--------------------+------------+----------+
| 10        | select * from slowtech.t1        | 0x97b21ac9e26ce1cc | 1          | 108649   |
| 10        | select user,host from mysql.user | 0xf02b330c823d739  | 1          | 1910     |
+-----------+----------------------------------+--------------------+------------+----------+
```

这⾥的 sum_time 是总的执⾏时间，单位微秒。

##  6.3 将耗时较久的查询放到从库执⾏

以第⼀条 SQL 为例。

```sql
insert into mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
values (1,1,'0x97b21ac9e26ce1cc',20,1);
load mysql query rules to runtime;
select * from stats.stats_mysql_query_digest_reset limit 1;
```

再次执⾏第⼀条 SQL，并查看统计信息。

可以看到，这条 SQL 转发到了从库执⾏。

# 7 ProxySQL Cluster 架构图

![image-20240516091826703](https://img.sunrisenan.com/img/2024/05/16/091829666.png)

在任意⼀个节点上对 mysql_users，mysql_servers，mysql_query_rules，proxysql_servers，global_variables 这 5 张表进⾏操作，ProxySQL Cluster 都能将修改的内容都会很快（近实时）同步到其它节点上。

## 7.1 如何搭建 ProxySQL Cluster

下⾯，以两个节点（172.31.7.110 和 172.31.7.111）为例，搭建⼀个 ProxySQL Cluster。

### 7.1.1 安装 ProxySQL

```bash
# curl -LO https://github.com/sysown/proxysql/releases/download/v2.6.2/proxysql_2.6.2-dbg-ubuntu22_amd64.deb
# dpkg -i proxysql_2.6.2-dbg-ubuntu22_amd64.deb
# systemctl start proxysql
# netstat -lntup| grep proxysql
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2064/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2064/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2064/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2064/proxysql       
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      2064/proxysql
```

### 7.1.2 配置 ProxySQL Cluster 检测⽤户

只有配置了这个⽤户，才能检测其它 Cluster 节点的状态。所有节点上都要配置。

```sql
# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL> '
set admin-admin_credentials='admin:admin;cluster_check:cluster_check_pass';
set admin-cluster_username='cluster_check';
set admin-cluster_password='cluster_check_pass';
load admin variables to runtime;
save admin variables to disk;
```

### 7.1.3 配置 ProxySQL Cluster 节点信息

所有节点上都要配置。

```sql
insert into proxysql_servers (hostname,port,weight,comment) values ('172.31.7.110',6032,100,'proxysql1');
insert into proxysql_servers (hostname,port,weight,comment) values ('172.31.7.111',6032,100,'proxysql2');
load proxysql servers to runtime;
save proxysql servers to disk;
```

⾄此，ProxySQL Cluster 搭建完毕。

### 7.1.4  验证 ProxySQL Cluster 的效果

```sql
# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL1> '
insert into mysql_users(username, password, default_hostgroup) values('test_user','123456',10);
load mysql users to runtime;
save mysql users to disk;
```

查看另外⼀个节点 mysql_users 表的内容。

确实同步过来了。再来看看该节点对应的⽇志。

```bash
root@db02:~# tailf /var/lib/proxysql/proxysql.log
2024-05-16 09:56:43 ProxySQL_Cluster.cpp:1013:set_checksums(): [INFO] Cluster: detected a peer 172.31.7.110:6032 with mysql_users version 2, epoch 1715824600, diff_check 3. Own version: 1, epoch: 1715824183. Proceeding with remote sync
2024-05-16 09:56:43 ProxySQL_Cluster.cpp:3363:get_peer_to_sync_mysql_users(): [INFO] Cluster: detected peer 172.31.7.110:6032 with mysql_users version 2, epoch 1715824600
2024-05-16 09:56:43 ProxySQL_Cluster.cpp:1494:pull_mysql_users_from_peer(): [INFO] Cluster: Fetching MySQL Users from peer 172.31.7.110:6032 started. Expected checksum: 0x49D86EC814FD22AC
2024-05-16 09:56:43 ProxySQL_Cluster.cpp:1521:pull_mysql_users_from_peer(): [INFO] Cluster: Fetching MySQL Users from peer 172.31.7.110:6032 completed
2024-05-16 09:56:43 ProxySQL_Cluster.cpp:1548:pull_mysql_users_from_peer(): [INFO] Cluster: Computed checksum for MySQL Users from peer 172.31.7.110:6032 : 0x49D86EC814FD22AC
2024-05-16 09:56:43 ProxySQL_Cluster.cpp:1560:pull_mysql_users_from_peer(): [INFO] Cluster: Loading to runtime MySQL Users from peer 172.31.7.110:6032
2024-05-16 09:56:43 ProxySQL_Admin.cpp:11690:__refresh_users(): [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x49D86EC814FD22AC', with epoch '1715824600'
2024-05-16 09:56:43 ProxySQL_Cluster.cpp:1569:pull_mysql_users_from_peer(): [INFO] Cluster: Saving to disk MySQL Users from peer 172.31.7.110:6032
2024-05-16 09:56:44 ProxySQL_Cluster.cpp:642:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.31.7.111:6032, version 2, epoch 1715824600, checksum 0x49D86EC814FD22AC . Not syncing yet ...
2024-05-16 09:56:44 ProxySQL_Cluster.cpp:648:set_checksums(): [INFO] Cluster: checksum for mysql_users from peer 172.31.7.111:6032 matches with local checksum 0x49D86EC814FD22AC , we won't sync.
```

```sql
ProxySQL2> select * from runtime_checksums_values;
+-------------------+---------+------------+--------------------+
| name              | version | epoch      | checksum           |
+-------------------+---------+------------+--------------------+
| admin_variables   | 2       | 1715824210 | 0x6036CA37310CC601 |
| mysql_query_rules | 1       | 1715824183 | 0x0000000000000000 |
| mysql_servers     | 1       | 1715824183 | 0x0000000000000000 |
| mysql_users       | 2       | 1715824600 | 0x49D86EC814FD22AC |
| mysql_variables   | 1       | 1715824183 | 0x1857AF986F7593C0 |
| proxysql_servers  | 2       | 1715824247 | 0x284BC58D20F347B9 |
| mysql_servers_v2  | 1       | 1715824183 | 0x0000000000000000 |
+-------------------+---------+------------+--------------------+
7 rows in set (0.02 sec)
```

## 7.2 如何添加⼀个新的节点

添加⼀个新的节点-172.31.7.112。

（1）在 172.31.7.112 上配置 ProxySQL Cluster 检测⽤户：

```sql
# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL3> '
set admin-admin_credentials='admin:admin;cluster_check:cluster_check_pass';
set admin-cluster_username='cluster_check';
set admin-cluster_password='cluster_check_pass';
load admin variables to runtime;
save admin variables to disk;
```

（2）在 172.31.7.112 上添加集群任意⼀个节点的配置信息：

```sql
proxysql3> insert into proxysql_servers (hostname,port,weight,comment) values ('172.31.7.110',6032,100,'proxysql1');
proxysql3> load proxysql servers to runtime;
```

这⾥添加的是 172.31.7.110。 此时，172.31.7.112 只能接受 172.31.7.110 的单向同步，它的修改不会同步到集群中。

（3）在 172.31.7.110 上将 172.31.7.112 添加进来：

```sql
proxysql1> insert into proxysql_servers (hostname,port,weight,comment) values
('172.31.7.112',6032,100,'proxysql3');
proxysql1> load proxysql servers to runtime;
```

这样，172.31.7.112 就完全加⼊到集群中了。

在实际测试过程中，发现 2、3 步的顺序不能颠倒。

注意，如果先将 172.31.7.112 添加到集群，然后再在 172.31.7.112 中配置集群其它节点的信息，会导致整个集 群的 proxysql_servers 信息被 172.31.7.112 中的覆盖掉。

**验证**

```sql
# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL2> '
insert into mysql_users(username, password, default_hostgroup) values('test_user123','123456',10);
load mysql users to runtime;
save mysql users to disk;

ProxySQL1> select username,password,active,default_hostgroup from mysql_users;
+--------------+----------+--------+-------------------+
| username     | password | active | default_hostgroup |
+--------------+----------+--------+-------------------+
| test_user    | 123456   | 1      | 10                |
| test_user123 | 123456   | 1      | 10                |
+--------------+----------+--------+-------------------+
2 rows in set (0.02 sec)

ProxySQL3> select username,password,active,default_hostgroup from mysql_users;
+--------------+----------+--------+-------------------+
| username     | password | active | default_hostgroup |
+--------------+----------+--------+-------------------+
| test_user    | 123456   | 1      | 10                |
| test_user123 | 123456   | 1      | 10                |
+--------------+----------+--------+-------------------+
2 rows in set (0.01 sec)

ProxySQL2> select username,password,active,default_hostgroup from mysql_users;
+--------------+----------+--------+-------------------+
| username     | password | active | default_hostgroup |
+--------------+----------+--------+-------------------+
| test_user    | 123456   | 1      | 10                |
| test_user123 | 123456   | 1      | 10                |
+--------------+----------+--------+-------------------+
2 rows in set (0.00 sec)
```

# 8 ProxySQL 库表及参数

## 8.1 库表信息

```sql
ProxySQL> show databases;
+-----+------------------+-------------------------------------+
| seq | name             | file                                |
+-----+------------------+-------------------------------------+
| 0   | main             |                                     |
| 2   | disk             | /var/lib/proxysql/proxysql.db       |
| 3   | stats            |                                     |
| 4   | monitor          |                                     |
| 5   | stats_history    | /var/lib/proxysql/proxysql_stats.db |
| 6   | myhgm            |                                     |
| 7   | monitor_internal |                                     |
+-----+------------------+-------------------------------------+
7 rows in set (0.01 sec)
```

- main 库中的表

https://proxysql.com/documentation/main-runtime/

- stats 库中的表

https://proxysql.com/documentation/stats-statistics/

## 8.2 常⽤参数

### 8.2.1 如何修改参数

除了 mysql-interfaces, mysql-threads 和 mysql-stacksize，ProxySQL 中的其它参数均可动态修改。⽀持的修改 ⽅式包括：

1. 直接使⽤ SET 命令修改参数，如，

```sql
   set mysql-long_query_time=2000;
   ```

2. 使⽤ UPDATE 命令修改 GLOBAL_VARIABLES，如，

```sql
   update global_variables set variable_value=2000 where variable_name='mysqllong_query_time';
   ```

参数修改后需加载到 RUNTIME 中才会⽣效。对于管理参数，对应的加载命令是 LOAD ADMIN VARIABLES TO RUNTIME。 对于监控参数及 MySQL 参数，对应的记载命令是 LOAD MYSQL VARIABLES TO RUNTIME。

### 8.2.2 管理参数

管理参数以 “admin-” 开头，主要是⽤来控制管理接⼝的相关⾏为。相关参数如下：

- admin-admin_credentials：管理⽤户及密码，默认为 admin:admin。对于 admin ⽤户，只能本地登录。 如果需要远程登录，必须设置其它⽤户，如，

```sql
  admin-admin_credentials="admin:admin;proxy_admin:proxy_pass"
  ```

- admin-mysql_ifaces：管理端⼝，默认为 0.0.0.0:6032。如果要指定多个，中间需⽤分号（;）隔开。

- admin-hash_passwords：在执⾏ LOAD MYSQL USERS TO RUNTIME 时，是否将 mysql_users 中的明⽂密 码加密保存到runtime_mysql_users 中，默认为 true。如果 mysql_users 表中有使⽤ caching_sha2_password 的⽤户，需将该参数设置为 false。

### 8.2.3 监控参数

监控参数以 “mysql-monitor_” 开头，主要是控制 Monitor 模块的相关⾏为。

```sql
ProxySQL> show variables like 'mysql-monitor%';
```

Monitor 模块在 ProxySQL 中⽤来检查后端节点的状态。相关参数如下：

- mysql-monitor_enabled：是否开启 Monitor 模块。

- mysql-monitor_username，mysql-monitor_password：监控⽤户和密码。

- mysql-monitor_history：Monitor 模块的检查结果会保存在 monitor 库中。

mysql-monitor_history ⽤来指定检查结果的保留时⻓，默认 600000 ms，即 600s。

Monitor 模块主要会进⾏以下四⽅⾯的检查：

1. CONNECT
2. PING
3. READ_ONLY
4. 主从延迟

### 8.2.4 MySQL 参数

主要是⽤来调整 MySQL 相关的功能。

查询或事务的执⾏时⻓

- mysql-default_query_timeout：查询的超时时⻓，默认 36000000 ms，即 10 ⼩时。
- mysql-max_transaction_idle_time：事务的最⼤空闲时间，默认 14400000，即 4 ⼩时。
- mysql-max_transaction_time：事务的最⼤持续时间，等于事务执⾏时间 + 事务空闲时间，默认 14400000，即 4 ⼩时。

⽆论是查询，还是事务，超过指定时⻓，都会被 ProxySQL KILL 掉。注意，因超时被 KILL 的查询（事务）不会⾃ 动重试。

**其它重点参数**

- mysql-init_connect：ProxySQL 在创建或初始化后端连接时执⾏的 SQL。可指定多个 SQL，中间⽤分号隔 开。
- mysql-long_query_time：慢查询的阈值，默认 1s。慢查询的次数会记录在 stats_mysql_global 表的 Slow_queries 变量中。
- mysql-max_allowed_packet：限制单个数据包的最⼤⼤⼩。
- mysql-max_connections：ProxySQL 的最⼤连接数。
- mysql-wait_timeout：如果前端连接的空闲时间超过 mysql-wait_timeout，则会被 ProxySQL KILL 掉。
- mysql-default_schema：默认 schema。
- mysql-enable_load_data_local_infile：是否⽀持 LOAD DATA LOCAL INFILE 命令，默认是 false，不⽀持。 如果要⽀持，导⼊的⽂件只能放到 ProxySQL 本地，不能放到客户端本地。
- mysql-have_ssl：是否允许前端连接开启 SSL 通信。
- mysql-interfaces：指定 ProxySQL 的对外服务端⼝。修改后，需重启 ProxySQL ⽣效（PROXYSQL RESTART）。
- mysql-server_version：ProxySQL ⽤来响应客户端的服务端版本号。这个版本号与后端节点的实际版本没有 任何关系。
- mysql-threads：ProxySQL ⽤来处理客户端请求的线程数。修改后，需重启 ProxySQL ⽣效。

扩展：

- https://www.cnblogs.com/SQLServer2012/p/10972593.html

- https://blog.csdn.net/weixin_44231544/article/details/129155140