ProxySQL v2.5.2 版本升级及新特性速览

ProxySQL 最新 GA 版本为 v2.5.2, 本文将介绍如何升级 ProxySQL 到最新版本，以及 v2.5.x 都有哪些值得一看的新特性。

ProxySQL 升级最新版本

本地环境安装的版本为 2.4.7-5-gd467cc0，通过 RPM 进行安装，那么升级也将变得简单，可以直接通过 YUM 进行升级。

具体操作如下，

1. 下载新包到指定目录

wget https://github.com/sysown/proxysql/releases/download/v2.5.2/proxysql-2.5.2-1-centos7.x86_64.rpm

$ ls proxysql-2.5.2-1-centos7.x86_64.rpm; sha256sum proxysql-2.5.2-1-centos7.x86_64.rpm
proxysql-2.5.2-1-centos7.x86_64.rpm
6f7ecc879864708ac7b62cf05edf31f6785697e1501e389e9a8f0d8fc92da489  proxysql-2.5.2-1-centos7.x86_64.rpm

2. 停止当前 ProxySQL

停止proxysql，并查看日志，确认服务已停止。

sudo systemctl stop proxysql

日志输出：

2023-06-13 17:17:27 ProxySQL_GloVars.cpp:16:term_handler(): [WARNING] Received TERM signal: shutdown in progress...
2023-06-13 17:17:27 [INFO] Starting shutdown...
2023-06-13 17:17:27 [INFO] Shutdown completed!
2023-06-13 17:17:27 [INFO] Exiting...
2023-06-13 17:17:27 [INFO] Shutdown angel process

3. 安装新版 ProxySQL

通过yum将下载好的安装包进行安装升级。

$ sudo yum install proxysql-2.5.2-1-centos7.x86_64.rpm
Loaded plugins: changelog, fastestmirror, product-id, search-disabled-repos, subscription-manager, verify

This system is not registered with an entitlement server. You can use subscription-manager to register.

Examining proxysql-2.5.2-1-centos7.x86_64.rpm: proxysql-2.5.2-1.x86_64
Marking proxysql-2.5.2-1-centos7.x86_64.rpm as an update to proxysql-2.4.7-1.x86_64
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:2.4.7-1 will be updated                                                                               
---> Package proxysql.x86_64 0:2.5.2-1 will be an update                                                                             
--> Finished Dependency Resolution                                                                               
                                                                                                                 
Dependencies Resolved                                                                                            
                                                                                                                 
=========================================================================================================================================
Package                                Arch           Version              Repository                                              Size
=========================================================================================================================================
Updating:                                                                                                        
proxysql                               x86_64         2.5.2-1              /proxysql-2.5.2-1-centos7.x86_64                        71 M
                                                                                                                 
Transaction Summary                                                                                              
=========================================================================================================================================
Upgrade  1 Package                                                                                               
                                                                                                                 
Total size: 71 M                                                                                                 
Is this ok [y/d/N]: y                                                                                            
Downloading packages:                                                                                            
Running transaction check                                                                                        
Running transaction test                                                                                         
Transaction test succeeded                                                                                       
Running transaction                                                                                              
  Updating   : proxysql-2.5.2-1.x86_64                                                                                               1/2
  Cleanup    : proxysql-2.4.7-1.x86_64                                                                                               2/2
  Verifying  : proxysql-2.5.2-1.x86_64                                                                                               1/2
  Verifying  : proxysql-2.4.7-1.x86_64                                                                                               2/2
                                                                                                                 
Updated:                                                                                                         
  proxysql.x86_64 0:2.5.2-1           
                                      
Complete!

4. 启动 ProxySQL

安装成功后，proxysql自动启动，查看服务状态。

$ systemctl status proxysql
* proxysql.service - High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2023-06-13 17:18:34 JST; 2min 16s ago
  Process: 1920276 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 1920278 (proxysql)
    Tasks: 25
   Memory: 12.3M
   CGroup: /system.slice/proxysql.service
           |-1920278 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
           `-1920279 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf

查看日志，确认启动正常。

2023-06-13 17:18:34 [INFO] ProxySQL version 2.5.2-217-g7f727b3
2023-06-13 17:18:34 [INFO] Detected OS: Linux centos7.shawnyan.com 3.10.0-1127.19.1.el7.x86_64 #1 SMP Tue Aug 25 17:23:54 UTC 2020 x86_64
2023-06-13 17:18:34 [INFO] ProxySQL SHA1 checksum: 28c2de2d21c48288b340f5c07e0753d95eca521d
2023-06-13 17:18:34 [INFO] Starting ProxySQL
2023-06-13 17:18:34 [INFO] Successfully started
2023-06-13 17:18:34 [INFO] Angel process started ProxySQL process 1920279
2023-06-13 17:18:34 [INFO] Loaded built-in SQLite3
Standard ProxySQL MySQL Logger rev. 2.5.0421 -- MySQL_Logger.cpp -- Thu May 11 11:05:36 2023
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Thu May 11 11:05:36 2023
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Thu May 11 11:05:36 2023
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Thu May 11 11:05:36 2023
2023-06-13 17:18:34 [INFO] Using UUID from database: 28a7d839-4b4d-403d-b1e1-1f35f8207714
2023-06-13 17:18:34 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0x355C22D42ECC56EC', with epoch '1686644314'
2023-06-13 17:18:34 [INFO] Switching query logging format from 1 to 2
2023-06-13 17:18:34 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0xA8C47DB669B380E4', with epoch '1686644314'
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Thu May 11 11:05:36 2023
2023-06-13 17:18:34 [INFO] ProxySQL SHA1 checksum: 28c2de2d21c48288b340f5c07e0753d95eca521d
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Thu May 11 11:05:36 2023
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Thu May 11 11:05:36 2023
2023-06-13 17:18:34 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x7917CD487C11478F', with epoch '1686644314'
2023-06-13 17:18:34 [INFO] Dumping mysql_servers_incoming
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname    | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | 10000000000 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2023-06-13 17:18:34 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2023-06-13 17:18:34 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname    | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | 10000000000 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2023-06-13 17:18:34 [INFO] Creating new server in HG 0 : 10000000000:3306 , gtid_port=0, weight=1, status=0
2023-06-13 17:18:34 [INFO] New mysql_group_replication_hostgroups table
2023-06-13 17:18:34 [INFO] New mysql_galera_hostgroups table
2023-06-13 17:18:34 [INFO] New mysql_aws_aurora_hostgroups table
2023-06-13 17:18:34 [INFO] New mysql_hostgroup_attributes table
2023-06-13 17:18:34 [INFO] Checksum for table mysql_servers is 0x3125ECE8F745478E
2023-06-13 17:18:34 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { old: 0x0, new: 0x3125ECE8F745478E }, mysql_replication_hostgroups { old:0x0, new:0x0 }
2023-06-13 17:18:34 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2023-06-13 17:18:34 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1686644314'
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Thu May 11 11:05:36 2023
2023-06-13 17:18:34 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0x63709E548329ECD5', with epoch '1686644314'
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Thu May 11 11:05:36 2023
2023-06-13 17:18:34 [INFO] Starting new mysql event log file /var/lib/proxysql/queries.log.00000004
2023-06-13 17:18:34 [INFO] Starting new audit log file /var/lib/proxysql/auditlog.log.00000004
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Thu May 11 11:05:36 2023
2023-06-13 17:18:34 [INFO] For information about products and services visit: https://proxysql.com/
2023-06-13 17:18:34 [INFO] For online documentation visit: https://proxysql.com/documentation/
2023-06-13 17:18:34 [INFO] For support visit: https://proxysql.com/services/support/
2023-06-13 17:18:34 [INFO] For consultancy visit: https://proxysql.com/services/consulting/

5. 验证版本

从上述日志中，可以看到版本已经升到 2.5.2-217-g7f727b3，再从管理端口连接proxysql确认。

$ prxsql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Admin> select version();
+--------------------+
| version()          |
+--------------------+
| 2.5.2-217-g7f727b3 |
+--------------------+
1 row in set (0.000 sec)

到此，proxysql已升级到最新版本 v2.5.2。

ProxySQL 2.5 新特性

1. mysql_hostgroup_attributes

proxysql 增加了一张新系统表 mysql_hostgroup_attributes，
对于不同主机组中的后端服务器版本不同或不同主机组的预期行为不同的场景，该表特别有用。
具体表定义如下：

Admin> show create table mysql_hostgroup_attributes\G
*************************** 1. row ***************************
       table: mysql_hostgroup_attributes
Create Table: CREATE TABLE mysql_hostgroup_attributes (
    hostgroup_id INT NOT NULL PRIMARY KEY,
    max_num_online_servers INT CHECK (max_num_online_servers>=0 AND max_num_online_servers <= 1000000) NOT NULL DEFAULT 1000000,
    autocommit INT CHECK (autocommit IN (-1, 0, 1)) NOT NULL DEFAULT -1,
    free_connections_pct INT CHECK (free_connections_pct >= 0 AND free_connections_pct <= 100) NOT NULL DEFAULT 10,
    init_connect VARCHAR NOT NULL DEFAULT '',
    multiplex INT CHECK (multiplex IN (0, 1)) NOT NULL DEFAULT 1,
    connection_warming INT CHECK (connection_warming IN (0, 1)) NOT NULL DEFAULT 0,
    throttle_connections_per_sec INT CHECK (throttle_connections_per_sec >= 1 AND throttle_connections_per_sec <= 1000000) NOT NULL DEFAULT 1000000,
    ignore_session_variables VARCHAR CHECK (JSON_VALID(ignore_session_variables) OR ignore_session_variables = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '')
1 row in set (0.001 sec)

依据 Release Notes, 目前该表如下几个字段有意义：

multiplex: 每个主机组值 HG，该值为特定主机组启用或禁用多路复用。有关相关全局变量的更多信息，请参见 mysql-multiplexing。
free_connections_pct: 打开的空闲连接占主机组中特定服务器的最大连接总数的百分比。有关相关全局变量的更多信息，请参见 mysql-free_connections_pct。
throttle_connections_per_sec: 确定每秒可以为特定主机组打开的最大新连接数。有关相关全局变量的更多信息，请参见 mysql-throttle_connections_per_sec_to_hostgroup。
init_connect: 字符串包含一个或多个SQL语句，以分号分隔，这些语句将由ProxySQL在创建或初始化时对特定主机组中的每个后端连接执行。有关相关全局变量的更多信息，请参见 mysql-init_connect。

其余几个字段是预定义字段，目前尚未实现功能。

2. 依赖包升级

本次共计13个依赖包的版本得到了升级，分别如下：

cityhash

CityHash，用于字符串的哈希函数族。CityHash为字符串提供哈希函数。这些函数完全混合了输入位，但不适合加密。

clickhouse-cpp

ClickHouse 的 C++ 客户端。

curl

一个命令行工具和库，用于传输数据与URL语法，支持DICT, FILE, FTP, FTPS, GOPHER, gopers, HTTP, HTTPS, IMAP, IMAPS, LDAP, LDAPS, MQTT, POP3, POP3S, RTMP, RTMPS, RTSP, SCP, SFTP, SMB, SMTP, SMTPS, TELNET, TFTP, WS和WSS。Libcurl提供了无数强大的特性

json

JSON (JavaScript Object Notation)是一种轻量级的数据交换格式。人类很容易读和写。它很容易被机器解析和生成。它基于JavaScript编程语言标准ECMA-262第三版(1999年12月)的一个子集。 JSON是一种完全独立于语言的文本格式，但使用C系列语言程序员所熟悉的约定，包括C、c++、c#、Java、JavaScript、Perl、Python和许多其他语言。这些属性使JSON成为理想的数据交换语言。

libconfig

用于处理配置文件的C/ c++库

libev

事件通知库

libhttpserver

用于创建嵌入式Rest HTTP服务器(以及更多)的c++库

libmicrohttpd

libmicrohttpd是一个用C编写的GNU库(GNU项目的一部分)，它提供了一个紧凑的API和HTTP 1.1 web服务器的实现(也支持HTTP 1.0)。libmicrohttpd只实现HTTP 1.1协议。主应用程序必须仍然提供内容。

LZ4是一种无损压缩算法，每核压缩速度> 500 MB/s，可在多核CPU下扩展。它具有极快的解码器，每核速度可达数GB/s，通常达到多核系统的RAM速度限制。

pcre

提供类型安全的preg_*替换的PCRE包装库。
这个库为您提供了一种方法来确保preg_*函数不会无声地失败，返回可能无法处理的意外空值。

prometheus-cpp

Prometheus 客户端库的 C++ 实现。该库旨在为c++服务启用度量驱动开发。它实现了Prometheus数据模型，这是一个强大的抽象，可以在其上收集和公开指标。我们提供了由Prometheus收集指标的可能性，但其他推/拉收集可以作为插件添加。

RE2是一种快速、安全、线程友好的替代方案，可以回溯PCRE、Perl和Python中使用的正则表达式引擎。它是一个c++库。

sqlite3

SQLite是一个c语言库，它实现了一个小型、快速、自包含、高可靠性、全功能的SQL数据库引擎。SQLite是世界上使用最多的数据库引擎。

3. core dump 命令

从 ProxySQL 2.5 开始，增加了按需生成核心转储的功能(Google coredumper)，
新增命令 PROXYSQL COREDUMP 和 PROXYSQL COMPRESSEDCOREDUMP。

命令调用：

Admin> PROXYSQL COREDUMP;
Query OK, 0 rows affected (1.041 sec)
Coredump: core

Admin> PROXYSQL COMPRESSEDCOREDUMP;
Query OK, 0 rows affected (5.016 sec)
Coredump: core

效果如下：

shawnyan@centos7:/var/lib/proxysql$ ll -h core*
-rw------- 1 proxysql proxysql 212M Jun 19 10:27 core
-rw------- 1 proxysql proxysql 1.3M Jun 19 10:29 core.bz2

该特性只支持 x86-64 Linux 系统。

4. Query Log 增强

在新版本的 Query Log 中，支持了 last_insert_id 字段。

示例如下：

{
   "client":"192.168.0.101:22960",
   "digest":"0x47B278EE303E9CCE",
   "duration_us":2130,
   "endtime":"2023-06-19 10:47:55.620990",
   "endtime_timestamp_us":1687139275620990,
   "event":"COM_QUERY",
   "hostgroup_id":0,
   "last_insert_id":1, --> New!
   "query":"INSERT INTO test.test_insert_id VALUES (NULL)",
   "rows_affected":1,
   "rows_sent":0,
   "schemaname":"test",
   "server":"192.168.0.101:3306",
   "starttime":"2023-06-19 10:47:55.618860",
   "starttime_timestamp_us":1687139275618860,
   "thread_id":24,
   "username":"sbtest"
}

总结

ProxySQL 是值得信赖的高可用中间件，适用于 MySQL, MariaDB, TiDB, ClickHouse 数据库。
本文介绍了如何升级 ProxySQL 到最新版本，以及 ProxySQL v2.5 版本中几个重要的新特性。

如果您看到这里，觉得有些许收获，那么请为本文点个赞吧~