ProxySQL 2.4.7 安装及新特性速览

原创严少安 2023-06-13

481

导言

ProxySQL是一个开源的高性能、高可用性、数据库协议感知的MySQL代理。

proxysql v2.4.x 系列版本已经终结，最新版本为v2.5.x系列。

安装 proxysql

本文使用的 proxysql 版本为 2.4.7，系统为centos7，下载对应的rpm包然后进行安装。

wget https://github.com/sysown/proxysql/releases/download/v2.4.7/proxysql-2.4.7-1-centos7.x86_64.rpm
yum install proxysql-2.4.7-1-centos7.x86_64.rpm
复制

启动 proxysql

$ systemctl status proxysql
* proxysql.service - High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
   Active: inactive (dead)

$ sudo systemctl start 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 10:31:21 JST; 3s ago
  Process: 1894515 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 1894517 (proxysql)
    Tasks: 26
   Memory: 13.2M
   CGroup: /system.slice/proxysql.service
           |-1894517 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
           `-1894518 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
复制

启动后，可以看到有log输出，

shawnyan@centos7:/var/lib/proxysql$ ll
total 664
-rw------- 1 proxysql proxysql      0 Jun 13 10:31 auditlog.log.00000001
-rw-rw---- 1 proxysql proxysql   1082 Jun 13 10:31 proxysql-ca.pem
-rw-rw---- 1 proxysql proxysql   1086 Jun 13 10:31 proxysql-cert.pem
-rw-rw---- 1 proxysql proxysql   1679 Jun 13 10:31 proxysql-key.pem
-rw------- 1 proxysql proxysql 208896 Jun 13 10:31 proxysql.db
-rw------- 1 proxysql proxysql   5841 Jun 13 10:31 proxysql.log
-rw-r--r-- 1 proxysql proxysql      8 Jun 13 10:31 proxysql.pid
-rw------- 1 proxysql proxysql 184320 Jun 13 10:31 proxysql_stats.db
-rw------- 1 proxysql proxysql      0 Jun 13 10:31 queries.log.00000001
复制

启动日志在文件proxysql.log中，输出查看如下：

shawnyan@centos7:/var/lib/proxysql$ sudo cat proxysql.log
2023-06-13 10:31:21 [INFO] ProxySQL version 2.4.7-5-gd467cc0
2023-06-13 10:31:21 [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 10:31:21 [INFO] ProxySQL SHA1 checksum: 59b0a2dce1059598a526a8fbac0a807912746a90
2023-06-13 10:31:21 [INFO] Starting ProxySQL
2023-06-13 10:31:21 [INFO] Successfully started
2023-06-13 10:31:21 [INFO] Angel process started ProxySQL process 1894518
2023-06-13 10:31:21 [INFO] Loaded built-in SQLite3
Standard ProxySQL MySQL Logger rev. 2.0.0714 -- MySQL_Logger.cpp -- Wed Jan 18 11:36:24 2023
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Wed Jan 18 11:36:24 2023
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Wed Jan 18 11:36:24 2023
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Wed Jan 18 11:36:24 2023
2023-06-13 10:31:21 [INFO] Using UUID: 28a7d839-4b4d-403d-b1e1-1f35f8207714 , randomly generated. Writing it to database
2023-06-13 10:31:21 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0xA4565F5D560FB2E0', with epoch '1686619881'
2023-06-13 10:31:21 [INFO] Switching query logging format from 1 to 2
2023-06-13 10:31:21 ProxySQL_Admin.cpp:6921:flush_mysql_variables___database_to_runtime(): [ERROR] Found incompatible values for mysql-default_charset (utf8mb4) and mysql-default_collation_connection (utf8_general_ci)
2023-06-13 10:31:21 [INFO] Changing mysql-default_collation_connection to utf8mb4_general_ci using configured mysql-default_charset: utf8mb4
2023-06-13 10:31:21 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0xED346CC5B472E7DB', with epoch '1686619881'
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Wed Jan 18 11:36:24 2023
2023-06-13 10:31:21 [INFO] ProxySQL SHA1 checksum: 59b0a2dce1059598a526a8fbac0a807912746a90
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Jan 18 11:36:24 2023
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Jan 18 11:36:24 2023
2023-06-13 10:31:21 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x0000000000000000', with epoch '1686619881'
2023-06-13 10:31:21 [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 |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2023-06-13 10:31:21 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2023-06-13 10:31:21 [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 |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2023-06-13 10:31:21 [INFO] New mysql_group_replication_hostgroups table
2023-06-13 10:31:21 [INFO] New mysql_galera_hostgroups table
2023-06-13 10:31:21 [INFO] New mysql_aws_aurora_hostgroups table
2023-06-13 10:31:21 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2023-06-13 10:31:21 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1686619881'
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Wed Jan 18 11:36:24 2023
2023-06-13 10:31:21 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0x0000000000000000', with epoch '1686619881'
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Jan 18 11:36:24 2023
2023-06-13 10:31:21 [INFO] Starting new mysql event log file /var/lib/proxysql/queries.log.00000001
2023-06-13 10:31:21 [INFO] Starting new audit log file /var/lib/proxysql/auditlog.log.00000001
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Wed Jan 18 11:36:24 2023
2023-06-13 10:31:21 [INFO] For information about products and services visit: https://proxysql.com/
2023-06-13 10:31:21 [INFO] For online documentation visit: https://proxysql.com/documentation/
2023-06-13 10:31:21 [INFO] For support visit: https://proxysql.com/services/support/
2023-06-13 10:31:21 [INFO] For consultancy visit: https://proxysql.com/services/consulting/
复制

连接proxysql

proxysql的管理端口为6032，可以像正常访问mariadb一样访问proxysql，并查询版本号。

shawnyan@centos7:/var/lib/proxysql$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
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.4.7-5-gd467cc0 |
+------------------+
1 row in set (0.000 sec)

Admin>
复制

从日志中可以看到，这是第一次登陆管理端口，版本号为2.4.7-5-gd467cc0。

新特性

下面介绍从proxysql 2.4引入的新特性。

1. 新参数 mysql-unshun_algorithm

从 proxysql 2.4.0 开始，引入了新的变量mysql-unshun_algorithm。它控制是否只在发送流量的主机组上执行自动取消主机回避，还是在主机存在的所有主机组上执行自动取消主机回避。

该参数有两个值，0和1. 默认值为0.

0：默认行为，只有来自主机组的服务器处理流量是不回避的。
1：当一个服务器在一个特定的主机组中不被回避时，它在所有其他主机组中也不被回避。

设定方式：

select * from global_variables where variable_name = 'mysql-unshun_algorithm';
SET mysql-unshun_algorithm='1';
LOAD MYSQL VARIABLES TO RUNTIME;
复制

更改设定后，日志输出：

2023-06-13 11:48:06 [INFO] Received command SET mysql-unshun_algorithm='1'
2023-06-13 11:48:08 [INFO] Received LOAD MYSQL VARIABLES TO RUNTIME command
2023-06-13 11:48:08 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0x1047DB032DEBE347', with epoch '1686624488'
复制

2. 支持命令 COM_RESET_CONNECTION

从 proxysql 2.4.0 开始，支持命令 COM_RESET_CONNECTION。该命令对应 mysql 客户端 api 的 mysql_reset_connection()。

Mysql_reset_connection()的作用类似于mysql_change_user()或自动重新连接，除了连接不关闭和重新打开，并且不进行重新认证。

执行该命令的影响如下：

回滚所有活动事务并重置自动提交模式。
释放所有表锁。
关闭(和删除)所有TEMPORARY表。
将会话系统变量重新初始化为相应的全局系统变量的值，包括由诸如set NAMES之类的语句隐式设置的系统变量。
丢失用户定义的变量设置。
释放准备好的语句。
关闭HANDLER变量。
将LAST_INSERT_ID()的值重置为0。
释放通过GET_LOCK()获取的锁。

3. 新参数 mysql-query_digests_keep_comment

添加了新的配置变量mysql-query_digests_keep_comment:启用后，不会从查询摘要中删除/* */类型的注释。

设定方式：

SET mysql-query_digests_keep_comment='true';
复制

设定后，演示如下：

Admin> select * from stats_mysql_query_digest;
+-----------+--------------------+----------+----------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| hostgroup | schemaname         | username | client_address | digest             | digest_text                                                                                                                 | count_star | first_seen | last_seen  | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+--------------------+----------+----------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| 0         | information_schema | sbtest   | 1    | 0x5E0FFD01059F91D0 | SELECT /* ;create_new_connection=1 */ lower(variable_name),variable_value FROM information_schema.SESSION_VARIABLES limit ? | 1          | 1686632838 | 1686632838 | 13536    | 13536    | 13536    | 0                 | 1             |
| 0         | information_schema | sbtest   | 1    | 0x226CD90D52A2BA0B | select @@version_comment limit ?                                                                                            | 1          | 1686632835 | 1686632835 | 0        | 0        | 0        | 0                 | 0             |
+-----------+--------------------+----------+----------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
2 rows in set (0.008 sec)
复制

4. 支持内置方法 current_user() 和 user()

在proxysql的管理界面，新增两种内置方法，支持查询用户信息。

Admin> select current_user();
+-----------------+
| current_user()  |
+-----------------+
| admin@127.0.0.1 |
+-----------------+
1 row in set (0.001 sec)

Admin> select user();
+-----------------+
| user()          |
+-----------------+
| admin@127.0.0.1 |
+-----------------+
1 row in set (0.000 sec)

复制

注：在MySQL中使用current_user函数可以在连接到MySQL时获得实际验证过的用户名和主机名。另外，当你连接到MySQL时，使用USER函数可以获得你指定的用户名和主机名。

MySQL [(none)]> select user();
+----------------+
| user()         |
+----------------+
| sbtest@centos7 |
+----------------+
1 row in set (0.001 sec)

MySQL [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| sbtest@%       |
+----------------+
1 row in set (0.001 sec)
复制