导言
ProxySQL 是一个开源的高性能、高可用性、数据库协议感知的MySQL代理。
ProxySQL v2.4.x 系列版本已经终结，最新版本为 v2.5.x 系列。
安装 proxysql
本文使用的 proxysql 版本为 2.4.7，系统为centos7，下载对应的rpm包然后进行安装。
1 2 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 $ 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输出：
1 2 3 4 5 6 7 8 9 10 11 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中，输出查看如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 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，并查询版本号。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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 2 3 select * from global_variables where variable_name = 'mysql-unshun_algorithm' ;SET mysql- unshun_algorithm= '1' ;LOAD MYSQL VARIABLES TO RUNTIME;
更改设定后，日志输出：
1 2 3 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'
从 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()获取的锁。
添加了新的配置变量 mysql-query_digests_keep_comment:启用后，不会从查询摘要中删除 /* */ 类型的注释。
设定方式：
1 SET mysql- query_digests_keep_comment= 'true' ;
设定后，演示如下：
1 2 3 4 5 6 7 8 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 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 管理界面，新增两种内置方法，支持查询用户信息。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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函数可以获得你指定的用户名和主机名。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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)
总结
本文综述了 ProxySQL v2.4.x 新版本的安装及使用，并重点强调了其中的4个新特性。
