CNDBA社区
1 ProxySQL 概述
ProxySQL为MySQL的中间件，其有两个版本官方版和percona版，percona版是基于官方版基础上修改而来。
官方站点：
官方手册：
ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。具有以下特性：
- 连接池，而且是 multiplexing；
- 主机和用户的最大连接数限制；
- 自动下线后端DB；
- 延迟超过阀值
- ping 延迟超过阀值
- 网络不通或宕机
- 强大的规则路由引擎；
- 实现读写分离
- 查询重写
- sql流量镜像
- 支持prepared statement；
- 支持Query Cache；
- 支持负载均衡，与gelera结合自动failover；
- 将所有配置保存写入到SQLit表中。
- 支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效。
- 支持query cache。
- 支持对query的路由，可以针对某个语句进行分配去哪个实例执行。
- 不支持分表，可以分库，但是利用规则配置实现分表。
2 ProxySQL配置系统结构
ProxySQL有一个完备的配置系统，配置ProxySQL是基于sql命令的方式完成的。ProxySQL支持配置修改之后的在线保存、应用，不需要重启即可生效。
整个配置系统分三层设计：RUNTIME、MEMORY、DISK和CONFIG FILE。 这三个级别的配置文件互不干扰，在某个层级修改了配置文件，想要加载或保存到另一个层级，需要额外的LOAD或SAVE操作：”LOAD xx_config FROM xx_level | LOAD xx_config TO xx_level | SAVE xx_config TO xx_level | SAVE xx_config FROM xx_level”，达到加载配置或者持久化配置的目的。
2.1 RUNTIME层
代表的是ProxySQL当前生效的配置，包括 globalvariables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置，必须要从下一层load进来。该层级的配置时在proxysql管理库(sqlite)的main库中以runtime开头的表，这些表的数据库无法直接修改，只能从其他层级加载；该层代表的是ProxySQL当前生效的正在使用的配置，包括global_variables, mysql_servers, mysql_users, mysql_query_rules表。无法直接修改这里的配置，必须要从下一层load进来。也就是说RUNTIME这个顶级层，是proxysql运行过程中实际使用的那一份配置，这一份配置会直接影响到生产环境的，所以要将配置加载进RUNTIME层时需要三思而行。
2.2 MEMORY层
平时在mysql命令行修改的配置，是SQLite数据库在内存的镜像。该层级的配置在main库中以mysql_开头的表以及global_variables表，这些表的数据可以直接修改；用户可以通过MySQL客户端连接到此接口（admin接口），然后可以在mysql命令行查询不同的表和数据库，并修改各种配置，可以认为是SQLite数据库在内存的镜像。也就是说MEMORY这个中间层，上面接着生产环境层RUNTIME，下面接着持久化层DISK和CONFIG FILE。
MEMORY层是我们修改proxysql的唯一正常入口。一般来说在修改一个配置时，首先修改Memory层，确认无误后再接入RUNTIME层，最后持久化到DISK和CONFIG FILE层。也就是说memeory层里面的配置随便改，不影响生产，也不影响磁盘中保存的数据。通过admin接口可以修改mysql_servers、mysql_users、mysql_query_rules、global_variables等表的数据。
2.3 DISK|CONFIG FILR层
持久存储的那份配置，一般在$(DATADIR)/proxysql.db，在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到，完了后，如果要修改监听端口，还是需要在管理命令行里修改，再 save 到硬盘。该层级的配置在磁盘上的sqlite库或配置文件里。DISK/CONFIG FILE层表示持久存储的那份配置，持久层对应的磁盘文件是$(DATADIR)/proxysql.db，在重启ProxySQL的时候，会从proxysql.db文件中加载信息。而 /etc/proxysql.cnf文件只在第一次初始化的时候使用，之后如果要修改配置，就需要在管理端口的SQL命令行里进行修改，然后再save到硬盘。 也就是说DISK和CONFIG FILE这一层是持久化层，我们做的任何配置更改，如果不持久化下来，重启后，配置都将丢失。
3 修改ProxySQL 配置
如果存在”proxysql.db”文件，则ProxySQL服务只有在第一次启动时才会去读取proxysql.cnf文件并解析；后面启动会就不会读取proxysql.cnf文件了。
如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效，则需要先删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启proxysql服务。就相当于初始化启动proxysql服务了，会再次生产一个纯净的proxysql.db数据库文件(如果之前配置了proxysql相关路由规则等，则就会被抹掉)。
官方推荐用admin interface方式修改，即在proxysql本机使用mysql客户端连接管理端口。
3.1 修改ProxySQL 配置命令
一般，修改的配置都是在memory层。可以load到runtime，使配置在不用重启proxysql的情况下也可以生效，也可以save到disk，将对配置的修改持久化。
需要修改配置时，直接操作的是 MEMORAY，以下命令可用于加载或保存 users (mysql_users):
- LOAD MYSQL USERS TO RUNTIME / LOAD MYSQL USERS FROM MEMORY #常用。将修改后的配置(在memory层)用到实际生产
- SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME #将生产配置拉一份到memory中
- LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK #将磁盘中持久化的配置拉一份到memory中来
- SAVE MYSQL USERS TO DISK / SAVE MYSQL USERS FROM MEMORY #常用。将memoery中的配置保存到磁盘中去
- LOAD MYSQL USERS FROM CONFIG #将配置文件中的配置加载到memeory中
推荐用 TO，往上层是 LOAD，往下层是 SAVE。以下命令加载或保存servers (mysql_servers):
- LOAD MYSQL SERVERS TO RUNTIME #常用，让修改的配置生效
- SAVE MYSQL SERVERS TO MEMORY
- LOAD MYSQL SERVERS TO MEMORY
- SAVE MYSQL SERVERS TO DISK #常用，将修改的配置持久化
- LOAD MYSQL SERVERS FROM CONFIG
以下命令加载或保存query rules (mysql_query_rules):
- load mysql query rules to run #常用
- save mysql query rules to mem
- load mysql query rules to mem
- save mysql query rules to disk #常用
- load mysql query rules from config
以下命令加载或保存 mysql variables (global_variables):
- load mysql variables to runtime
- save mysql variables to memory
- load mysql variables to memory
- save mysql variables to disk
- load mysql variables from config
以下命令加载或保存admin variables (select * from global_variables where variable_name like ‘admin-%’):
- load admin variables to runtime
- save admin variables to memory
- load admin variables to memory
- save admin variables to disk
- load admin variables from config
3.2 修改配置操作示例
3.2.1 修改ProxySQL 登录账户和端口
proxysql有2个端口：
- 6032端口是管理入口，账号密码是admin(可以动态修改),允许客户端连接；
- 6033端口就是客户端入口，账号密码通过管理接口去设置。
在proxysql本机使用mysql客户端连接到ProxySQL的管理接口(admin interface), 该接口的默认管理员用户和密码都是admin。
[dave@www.cndba.cn:~]# cat /etc/proxysql.cnf
……
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
# debug=true
}
……
如果ip配置为0.0.0.0表示不限制ip，但是出于安全考虑，建议admin用户设置只能在本机登录。
这里的配置是admin_variables变量，所以我们直接sql 修改：
[dave@www.cndba.cn:~]# mysql -u admin -padmin -h 127.0.0.1 -P6032
mysql> update global_variables set variable_value = '0.0.0.0:16032' where variable_name ='admin-mysql_ifaces';
Query OK, 1 row affected (0.00 sec)
mysql> update global_variables set variable_value = 'root:root;radmin:radmin' where variable_name = 'admin-admin_credentials';
Query OK, 1 row affected (0.00 sec)
mysql> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save admin variables to disk;
Query OK, 49 rows affected (0.01 sec)
注意：
- save disk 是写入 sqllite 数据库：proxysql.db，proxysql.cnf 配置没有变化。
- 这里的变量名是通过globalvariables表查询的，不是配置文件中的名称，其实也就是加了一个admin的前缀：
mysql> select * from global_variables where variable_name like 'admin-%';
+---------------------------------------------------+-------------------+
| variable_name | variable_value |
+---------------------------------------------------+-------------------+
……
| admin-admin_credentials | admin:admin |
| admin-mysql_ifaces | 0.0.0.0:6032 |
+---------------------------------------------------+-------------------+
49 rows in set (0.00 sec)
验证：
[dave@www.cndba.cn:~]# mysql -u radmin -pradmin -h 127.0.0.1 -P16032
[dave@www.cndba.cn:~]# mysql -u root -proot -h 127.0.0.1 -P16032
3.2.2 添加配置
添加一个业务用户，在mysql_users表中执行一个插入操作：
mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values(‘root’,’root’,1,0,1);
Query OK, 1 row affected (0.00 sec)
将修改后的配置(MEMORY层)用到实际生产环境（RUNTIME层）：
mysql>load mysql users to runtime;
永久生效：
mysql>save mysql users to disk;
将磁盘中持久化的配置拉一份到memory中：
mysql>load mysql users to memory;
将配置文件中的配置加载到memeory中
mysql>load mysql users from config;
3.2.3 持久化配置
以上SQL命令是对mysql_users进行的操作，还可以对mysql_servers、mysql_query_rules、global_variables等执行类似的操作。
如对mysql_servers表插入完成数据后，要执行保存和加载操作：
mysql> load mysql servers to runtime;
mysql> save mysql servers to disk;
对mysql_query_rules表插入完成数据后，要执行保存和加载操作：
mysql> load mysql query rules to runtime;
mysql> save mysql query rules to disk;
以下命令加载或保存mysql variables（global_variables）:
load mysql variables to runtime;
save mysql variables to disk;
以下命令加载或保存admin variables（）:
select * from global_variables where variable_name like ‘admin-%’
load admin variables to runtime;
save admin variables to disk;
4 安装ProxySQL
我们这里选择percona 版本的，官网可以下载到源码包和rpm包：
4.1 安装
[dave@www.cndba.cn:/software]# rpm -ivh proxysql2-2.5.5-1.1.el7.x86_64.rpm
警告：proxysql2-2.5.5-1.1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 8507efa5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:proxysql2-2.5.5-1.1.el7 ################################# [100%]
[dave@www.cndba.cn:/software]#
4.2 启动
[dave@www.cndba.cn:/software]# systemctl start proxysql
[dave@www.cndba.cn:/software]# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/usr/lib/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2023-10-18 09:14:26 CST; 1s ago
Process: 15820 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 15823 (proxysql)
Tasks: 25
Memory: 14.9M
CGroup: /system.slice/proxysql.service
├─15823 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─15824 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
10月 18 09:14:26 gbase01 systemd[1]: Starting High Performance Advanced Proxy for MySQL...
10月 18 09:14:26 gbase01 proxysql[15820]: 2023-10-18 09:14:26 [INFO] Using config file /etc/proxysql.cnf
10月 18 09:14:26 gbase01 proxysql[15820]: 2023-10-18 09:14:26 [INFO] Current RLIMIT_NOFILE: 102400
10月 18 09:14:26 gbase01 proxysql[15820]: 2023-10-18 09:14:26 [INFO] Using OpenSSL version: OpenSSL 3.1.0 14 Mar 2023
10月 18 09:14:26 gbase01 proxysql[15820]: 2023-10-18 09:14:26 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generati...ficates.
10月 18 09:14:26 gbase01 systemd[1]: Started High Performance Advanced Proxy for MySQL.
Hint: Some lines were ellipsized, use -l to show in full.
[dave@www.cndba.cn:/software]#
4.3 相关文件和进程
这里有几个重要的文件：
[dave@www.cndba.cn:/software]# cd /var/lib/proxysql/
[dave@www.cndba.cn:/var/lib/proxysql]# ll
总用量 664
-rw-rw---- 1 proxysql proxysql 1082 10月 18 09:14 proxysql-ca.pem
-rw-rw---- 1 proxysql proxysql 1086 10月 18 09:14 proxysql-cert.pem
-rw-r----- 1 proxysql proxysql 217088 10月 18 09:14 proxysql.db
-rw-rw---- 1 proxysql proxysql 1675 10月 18 09:14 proxysql-key.pem
-rw-rw---- 1 proxysql proxysql 5341 10月 18 09:14 proxysql.log
-rw-r--r-- 1 proxysql proxysql 6 10月 18 09:14 proxysql.pid
-rw-r----- 1 proxysql proxysql 184320 10月 18 09:15 proxysql_stats.db
[dave@www.cndba.cn:/var/lib/proxysql]#
/var/lib/proxysql/proxysql.db： sqllite数据库
/var/lib/proxysql/proxysql.pid
/etc/proxysql.cnf： 配置文件
proxysql和MySQL的很相似，启动一个进程，然后fork出一个子进程，父进程负责监控子进程运行状况如果挂了则拉起来，子进程负责执行真正的任务。
[dave@www.cndba.cn:~]# ps -ef|grep proxysql
proxysql 15823 1 0 09:14 ? 00:00:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
proxysql 15824 15823 0 09:14 ? 00:00:01 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
root 18754 13662 0 09:17 pts/0 00:00:00 grep --color=auto proxysql
[dave@www.cndba.cn:~]#
4.4 ProxySQL启动过程
当proxysql启动时，首先读取配置文件CONFIG FILE(/etc/proxysql.cnf)，然后从该配置文件中获取datadir，datadir中配置的是sqlite的数据目录。
如果该目录存在，且sqlite数据文件存在，那么正常启动，将sqlite中的配置项读进内存，并且加载进RUNTIME，用于初始化proxysql的运行。
如果datadir目录下没有sqlite的数据文件，proxysql就会使用config file中的配置来初始化proxysql，并且将这些配置保存至数据库。sqlite数据文件可以不存在，但/etc/proxysql.cnf配置文件必须存在，否则，proxysql无法启动。
4.5 登录ProxySQL
默认的用户名和密码都是admin，启动后会监听两个端口，默认为6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL对外提供服务的端口 (即连接到转发后端的真正数据库的转发端口)。通过mysql 客户端登录。
[dave@www.cndba.cn:~]# mysql -u admin -padmin -h 127.0.0.1 -P6032
5 ProxySQL 库表说明
关于ProxySQL的库表，官网有详细说明：
https://proxysql.com/documentation/
5.1 数据库库
[dave@www.cndba.cn:~]# mysql -u root -proot -h 127.0.0.1 -P16032
mysql> 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
ProxySQL提供的数据库说明如下：
- main 内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
- disk 是持久化到硬盘的配置，sqlite数据文件。
- stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
- monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
5.2 MAIN和DISK库中的表
官网关于MAIN库表的说明：
查看MAIN 数据库中表信息：
mysql> show tables from main;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| coredump_filters |
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_hostgroup_attributes |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| restapi_routes |
| runtime_checksums_values |
| runtime_coredump_filters |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_firewall_whitelist_rules |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users |
| runtime_mysql_galera_hostgroups |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_hostgroup_attributes |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_restapi_routes |
| runtime_scheduler |
| scheduler |
+----------------------------------------------------+
36 rows in set (0.00 sec)
查看disk 库中的表，disk库的表字段和main一样：
mysql> show tables from disk;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_settings |
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_hostgroup_attributes |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| restapi_routes |
| scheduler |
+--------------------------------------------+
18 rows in set (0.00 sec)
表主要有两个前缀：mysql和runtime。
- mysql_开头的主要是memory层的配置，通过sql修改配置时，主要就是改变这一层的数据。
- runtime是运行时的配置，这些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime表，修改后必须执行”LOAD … TO RUNTIME”才能加载到RUNTIME生效，执行save … to disk才能将配置持久化保存到磁盘。
常用的几个表：
- global_variables ：设置变量，包括监听的端口、管理账号等。
- mysql_collations ： 相关字符集和校验规则。
- mysql_query_rules： 定义查询路由规则。
- mysql_replication_hostgroups ：监视指定主机组中所有服务器的read_only值，并且根据read_only的值将服务器分配给写入器或读取器主机组。ProxySQL monitor模块会监控hostgroups后端所有servers 的read_only 变量，如果发现从库的read_only变为0、主库变为1，则认为角色互换了，自动改写mysql_servers表里面 hostgroup关系，达到自动 Failover 效果。
- mysql_servers：设置后端MySQL的表
- mysql_users：配置后端数据库的程序账号和监控账号。
- scheduler：调度器是一个类似于cron的实现，集成在ProxySQL中，具有毫秒的粒度。通过脚本检测来设置ProxySQL。
5.3 stats库中的表
官网关于STATS库表的说明：
mysql> show tables from stats;
+---------------------------------------+
| tables |
+---------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_client_host_cache |
| stats_mysql_client_host_cache_reset |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_errors |
| stats_mysql_errors_reset |
| stats_mysql_free_connections |
| stats_mysql_global |
| stats_mysql_gtid_executed |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_message_metrics |
| stats_proxysql_message_metrics_reset |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_clients_status |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+---------------------------------------+
24 rows in set (0.00 sec)
常用的几个表：
- stats_mysql_commands_counters：统计各种SQL类型的执行次数和时间，通过参数mysql-commands_stats控制开关，默认是ture。
- stats_mysql_connection_pool：连接后端MySQL的连接信息。
- stats_mysql_processlist：类似MySQL的show processlist的命令，查看各线程的状态。
- stats_mysql_query_digest：表示SQL的执行次数、时间消耗等。通过变量mysql-query_digests控制开关，默认是开。
- stats_mysql_query_rules：路由命中次数统计。
5.4 monitor库中的表
mysql> show tables from monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
9 rows in set (0.01 sec)
常用的几个表：
- mysql_server_connect_log：连接到所有MySQL服务器以检查它们是否可用，该表用来存放检测连接的日志。
- mysql_server_ping_log：使用mysql_ping API ping后端MySQL服务器，检查它们是否可用，该表用来存放ping的日志。
- mysql_server_replication_lag_log：后端MySQL服务主从延迟的检测。
