海山数据库（He3DB）代理ProxySQL使用详解：（一）架构说明与安装

一、ProxySQL介绍

 1.1 简介

业界比较知名的MySQL代理，由ProxySQL LLC公司开发并提供专业的服务支持，基于GPLv3开源协议进行发布,大部分配置项可动态变更。后端的MySQL实例可根据用途配置到不同的hostgroup中，由ProxySQL基于7层网络协议,将来源于APP的流量路由到对应的hostgroup内，从而实现负载均衡或者读写分离。目前ProxySQL 有官方版和 percona 版两种。percona版是在官方版的基础上修改的，添加了几个比较实用的工具。生产环境建议用官方版。

 ProxySQL相关的网站

https://www.proxysql.com/ https://github.com/sysown/proxysql/wiki

1.2 功能架构

上图中展示了流量从客户端发出 → ProxySQL进行处理转发 → 后端处理 → ProxySQL的前端连接 → 返回客户端的基本流程。简单梳理下ProxySQL的核心功能如下图:

• 读写分离：可查询走从库，写入走主库

• 简单Sharding：ProxySQL的sharding是通过正则匹配来实现的，可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。对于需要拆分SQL以及合并SQL执行结果的不能支持

• 连接池管理：常规功能，为了提高SQL执行效率。

• 多路复用：主要优化点在后端mysql连接的复用，可自行控制后端连接的复用逻辑。

• 流量管控：kill连接和kill query；whitelist配置。

• 高可用：底层mysql，如果从库挂了，自动摘除流量；主库挂了暂不处理。ProxySQL自身高可用，提供cluster的功能，cluster内部会自行同步元数据以及配置变更信息。

• 查询缓存：对username+schema+query的key进行缓存，设置ttl过期，不适合写完就查的场景，因为在数据在未过期之前可能是脏数据。

• 动态配置：大部分的配置可动态变更，先load到runtime，在save到disk，通过cluster的功能同步到其他的节点。

• 流量镜像：同一份流量可以多出写入，但是并不保证mirror的流量一定成功。

• SQL改写：在query rules中配置replace规则，可以对指定的SQL进行改写。

1.3 多层配置系统

ProxySQL的配置有三层，且绝大部分参数均可以通过动态配置并热加载到运行层，并通过save命令保存到持久层。

RUNTIME层

runtime层是即时生效的；代表的是ProxySQL当前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置，必须要从下一层load进来。

MEMORY层

memory层是保存在内存中，但不立即生效的，表示的是内存数据库，其实就是 main 库。通过管理接口修改的所有配置，都保存 在内存数据库(main)中。当 ProxySQL 重启或者崩溃时，这个内存数据库中的数据会丢失，所以需要 save 到 disk 库中（持久化）。 该层通常通过admin端口进来后，直接修改。

DISK层|CONFIG FILE层

disk层表示持久化层，持久存储的那份配置，一般在$(DATADIR)/proxysql.db，在重启的时候会从硬盘里加载。config file表示从配置文件中加载的。这里的 config file 就是传统的配置文件，默认为

/etc/proxysql.cnf 。 ProxySQL 启动时，主要是从 disk 库中读取配置加载到内存并最终加载到 runtime

生效，只有极少的几个特定配置内容是从 config file 中加载的，除非是第一次初始化 ProxySQL 运行环境

(或者disk库为空)。

在上面的多层配置系统图中，标注了[1]、[2]、[3]、[4]、[5]的序号。每个序号都有两个操作方向from/to，

其实只是所站角度不同而已。以下是各序号对应的操作：

Bash [1] ：将内存数据库中的配置加载到RUNTIME数据结构中 LOAD XXX FROM MEMORY LOAD XXX TO RUNTIME[2] ：将RUNTIME数据结构中的配置持久化到内存数据库中 SAVE XXX FROM RUNTIME SAVE XXX TO MEMORY [3] ：将磁盘数据库中的配置加载到内存数据库中 LOAD XXX FROM DISK LOAD XXX TO MEMORY [4] ：将内存数据库中的配置持久化到磁盘数据库中 SAVE XXX FROM MEMORY SAVE XXX TO DISK [5] ：从传统配置文件中读取配置加载到内存数据库中 LOAD XXX FROM CONFIG DISK/MEMORY/RUNTIME/CONFIG 可以缩写，只要能识别即可。例如MEMORY可以缩写为MEM， runtime可以缩写为run

 多层配置可参考https://proxysql.com/Documentation/configuring-proxySQL/

二、安装

支持多种安装方式：

• deb 或 rpm 安装包 - Releases · sysown/proxysql · GitHub

• repo包安装 - https://repo.proxysql.com/

• build from git repo any taged or dev version - GitHub - sysown/proxysql: High-performance MySQL proxy with a GPL license.

• build from source code archives - Releases · sysown/proxysql · GitHub

2.1 安装包

官网安装教程：https://www.proxysql.com/documentation/installing-proxysql/

以centos为例，配置ProxySQL源：

Bash [user@server1]$sudo cat <<EOF | tee /etc/yum.repos.d/proxysql.repo > [proxysql_repo] > name= ProxySQL YUM repository > baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/$releasever > gpgcheck=1 > gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key > EOF tee: /etc/yum.repos.d/proxysql.repo: Permission denied [proxysql_repo] name= ProxySQL YUM repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/ gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key [user@server1]$

安装ProxySQL：

Plain Text yum install proxysql OR yum install proxysql-version

如果没有权限更改 tee: /etc/yum.repos.d/proxysql.repo: Permission denied ，可以考虑源码编译安装，也

可以考虑去git官网下载最新的二进制文件安装。

官网下载：Releases · sysown/proxysql · GitHub

Bash #可以复制下载连接地址后使用wget下载 wget https://github.com/sysown/proxysql/releases/download/v2.5.5/proxysql-2.5.5-1-centos7.x86_64.rpm #安装依赖 yum install gnutls yum install perl-DBD-MySQL #rpm安装 rpm -ivh proxysql-2.5.5-1-centos7.x86_64.rpm #注：这种方法是将二进制文件安装到系统目录，可以通过systemctl start/stop proxysql 的方式启动proxysql #启动/关闭proxsql systemctl start/stop proxysql #rpm卸载proxsql #先使用rpm -qa | grep proxy查看名称 rpm -e --nodeps 文件包名称 #卸载

当 ProxySQL 启动后，将监听两个端口：

• admin管理接口，默认端口为6032。该端口用于查看、配置ProxySQL

• 接收SQL语句的接口，默认端口为6033，这个接口类似于MySQL的3306端口

ProxySQL 的 admin 管理接口是一个使用 MySQL 协议的接口，所以，可以直接使用 mysql 客户端、 navicat 等工具去连接这个管理接口，其默认的用户名和密码均为 admin

2.2 源码编译安装

安装操作系统安装依赖：

Bash # RHEL / CentOS < 7: yum install -y automake bzip2 cmake make g++ gcc git openssl openssl-devel gnutls libtool patch libuuid-devel # CentOS 7.4+ / Fedora 28+: yum install -y automake bzip2 cmake make gcc-c++ gcc git openssl openssl-devel gnutls gnutls-devel libtool patch libuuid-devel # Debian / Ubuntu Based: apt-get install -y automake bzip2 cmake make g++ gcc git openssl libssl-dev libgnutls28-dev libtool patch uuid-dev

下载源码编译

Bash git clone https://github.com/sysown/proxysql.git cd proxysql git checkout v2.5.5 make sudo make install ##启动 proxysql -c /etc/proxysql.cnf

三、ProxySQL Admin

ProxySQL Admin DB可通过标准MySQL客户端进行配置，是ProxySQL配置系统的核心，允许动态运行时重新配置ProxySQL管理和操作功能。

3.1 连接管理端口

ProxySQL自身共有5个 库，分别为3个保存在内存中的库，和三个保存在磁盘的SQLite库。通过6032管理端口登入后，默认就是main库，所有的配置更改都必须在这个库中进行，disk存档库不会直接受到影响。接下使用 mysql 客户端去连接 ProxySQL 的管理接口：

Bash [user@server1]$ ./mysql -uadmin -p -h127.0.0.1 -P6032 #连接 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 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)

• main：内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。

• disk：是持久化到硬盘的配置，sqlite数据文件。SQLite3 数据库，默认位置为 $（DATADIR）/proxysql.db,在重新启动时，未保留的内存中配置将丢失。因此，将配置保留在 DISK 中非常重要。(SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎)

• stats：proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。

• monitor：库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。

• stats_history:统计信息历史库

注意：

由于 ProxySQL 的配置全部保存在几个自带的库中，所以通过管理接口，可以非常方便地通过发送一 些SQL命令去修改 ProxySQL 的配置。 ProxySQL 会解析通过该接口发送的某些对ProxySQL 有效的 特定命令，并将其合理转换后发送给内嵌的 SQLite3 数据库引擎去运行 ProxySQL 的配置几乎都是通过管理接口来操作的，通过 Admin 管理接口，可以在线修改几乎所有的 配置并使其生效。只有两个变量的配置是必须重启 ProxySQL 才能生效的，它们是： mysql-threads 和 mysql-stacksize

3.2 管理端口相关变量

admin-admin_credentials

admin-admin_credentials 变量控制的是admin管理接口的管理员账户。默认的管理员账户和密码为admin:admin，但是这个默认的用户只能在本地使用。

如果想要远程连接到ProxySQL，例如用windows上的navicat连接Linux上的ProxySQL管理接口，必须自定义一个管理员账户。

Bash #默认的管理账户 mysql> select @@admin-admin_credentials; +---------------------------+ | @@admin-admin_credentials | +---------------------------+ | admin:admin | +---------------------------+ 1 row in set (0.00 sec) #设置管理员帐号xxxx,密码xxxx mysql> set admin-admin_credentials='admin:xxxx;myadmin:xxxxx'; #账号密码通过“:”冒号分开，多个使用";"分号 分开。 Query OK, 1 row affected (0.00 sec) mysql> select @@admin-admin_credentials; +-----------------------------+ | @@admin-admin_credentials | +-----------------------------+ | 用户名1:xxxxx;用户名2:xxxxxx! | +-----------------------------+ 1 row in set (0.00 sec) mysql> load admin variables to runtime; #装载到runtime层使之生效 Query OK, 0 rows affected (0.00 sec) mysql> save admin variables to disk; Query OK, 35 rows affected (0.00 sec) #保存到disk

admin-stats_credentials

admin-stats_credentials 变量控制admin管理接口的普通用户，这个变量中的用户没有超级管理员权限，

只能查看monitor库和main库中关于统计的数据，其它库都是不可见的，且没有任何写权限。

默认的普通用户名和密码均为 stats ，与admin一样，它默认也只能用于本地登录，若想让人远程查看则要

添加查看的专有用户。

Bash #默认的普通用户 mysql> select @@admin-stats_credentials; +---------------------------+ | @@admin-stats_credentials | +---------------------------+ | stats:stats | +---------------------------+ 1 row in set (0.00 sec) #添加专有的查看用户 mysql> set admin-stats_credentials='stats:xxxxx;mystats:xxxx'; Query OK, 1 row affected (0.00 sec) mysql> select @@admin-stats_credentials; +-----------------------------+ | @@admin-stats_credentials | +-----------------------------+ | stats:xxxxxx;mystats:xxxxxx! | +-----------------------------+ 1 row in set (0.00 sec) mysql> load admin variables to runtime; #装载到runtime层使之生效 Query OK, 0 rows affected (0.00 sec) mysql> save admin variables to disk; Query OK, 35 rows affected (0.00 sec) #保存到disk #同样，这个变量中的用户必须不能存在于mysql_users表中 #可以使用mystats用户远程连接查看状态 只能查看monitor库和main库中关于统计的数据

admin-mysql_ifaces

admin-mysql_ifaces 变量指定admin接口的监听地址，格式为冒号分隔的hostname:port列表。默认监听

在 0.0.0.0:6032

注意，允许使用UNIX的domain socket进行监听，这样本主机内的应用程序就可以直接被处理。

例如：

SQL mysql> SET admin-mysql_ifaces='0.0.0.0:6032;/tmp/proxysql_admin.sock';#监听端口可以有多个，用分号分割允许使 用UNIX的domain socket进行监听 Query OK, 1 row affected (0.00 sec) #是配置生效 mysql> load admin variables to runtime; Query OK, 0 rows affected (0.00 sec) mysql> load admin variables to runtime; Query OK, 0 rows affected (0.00 sec)

3.3 关键配置表

点击图片可查看完整电子表格

mysql_servers表

定义后端backends，如MySQL Server或者使用MySQL协议的其他实例，同一个hostgroup组中的backends具有相同的逻辑功能；

Bash mysql>  SHOW CREATE TABLE mysql_servers\G *************************** 1. row ***************************        table: mysql_servers Create Table: CREATE TABLE mysql_servers (     hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,     hostname VARCHAR NOT NULL,     port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,     gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,     status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',     weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,     compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,     max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,     max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,     use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,     max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,     comment VARCHAR NOT NULL DEFAULT '',     PRIMARY KEY (hostgroup_id, hostname, port) ) 1 row in set (0.00 sec) 其中主要字段 hostgroup_id：指定后端MySQL所在的hostgroup_id组，同一个后端MySQL可以分属不同的组； hostname,port: 指定后端MySQL的主机和端口； status: 后端MySQL的状态，online表示在线提供服务，即正常状态；offline_soft表示非强制下线，即不再接受新的请求，但可以处理当前已建立的会话请求；offline_hard表示强制下线，即强制关闭当前的所有会话且不再接受新请求；shunned表示后端MySQL临时不可用，如因为连接数过多或者主从延迟超过设置的阈值等； weight：backend的权重，权重越大，则同hostgroup中被选择的概率越大； max_connections：设置ProxySQL能连接后端MySQL的最大连接； max_replication_lag：设置后端MySQL允许的最大延迟时间；

mysql_users表

用于存储访问ProxySQL的相关用户，并最终连接到后端的MySQL上；

Bash mysql> show create table mysql_users\G *************************** 1. row ***************************        table: mysql_users Create Table: CREATE TABLE mysql_users (     username VARCHAR NOT NULL,     password VARCHAR,     active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,     use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,     default_hostgroup INT NOT NULL DEFAULT 0,     default_schema VARCHAR,     schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,     transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,     fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,     backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,     frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,     max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,     attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',     comment VARCHAR NOT NULL DEFAULT '',     PRIMARY KEY (username, backend),     UNIQUE (username, frontend)) 1 row in set (0.01 sec) 其中 username, password：连接ProxySQL来访问后端MySQL的用户名和密码。 active：指定用户是否是激活有效的，如果设置为0，则该用户不会被加载到runtime层； default_hostgroup：指定用户默认访问的主机组，如果没有配置查询规则，则SQL统一会分流到默认主机组； transaction_persistent：表示是否持久化事务，设置为1表示开启一个事务后，该事务的所有操作均在同一个hostgroup中操作完成，其会忽略设置的任何查询规则； frontend,backend：指定用户是属于前端和后端；前端表示用户通过ProxySQL进行连接，后端表示ProxySQL连接后端MySQL；通常添加一个用户后，在runtime层，ProxySQL默认会将该用户同时设定为前端和后端； max_connections：设定该用户的最大连接；

需注意：

用户需要在后端MySQL实例中创建且能确保ProxySQL所在的机器IP已加白；

在mysql_users表中配置的用户不能再被用到mysql-monitor_username监控配置用户上；

mysql_replication_hostgroups表

定义使用异步同步 /半同步的传统主从复制中各个hostgroup的对应关系。

Bash mysql> show create table mysql_replication_hostgroups\G *************************** 1. row ***************************        table: mysql_replication_hostgroups Create Table: CREATE TABLE mysql_replication_hostgroups (     writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,     reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),     check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',     comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup)) 1 row in set (0.01 sec) 其中 writer_hostgroup：指定写组id，如设置10表示所有主机组为10的后端MySQL将会处理写请求； reader_hostgroup: 指定读组id，如设置20表示所有主机组为20的后端MySQL将会处理读请求； check_type: 判断MySQL只读的类型，ProxySQL也是通过该只读属性来自动判断添加的后端MySQL是属于读组还是写组； 注意： 检查类型支持单一参数值的检查，也支持一些参数值逻辑运算后检查，支持的类型如下： read_only  默认值，通过read_only参数判断 innodb_read_only super_read_only read_only|innodb_read_only  通过 read_only&innodb_read_only

mysql_query_rules表

定义路由策略和属性;

Bash mysql> show create table mysql_query_rules\G *************************** 1. row ***************************        table: mysql_query_rules Create Table: CREATE TABLE mysql_query_rules (     rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,     active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,     username VARCHAR,     schemaname VARCHAR,     flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,     client_addr VARCHAR,     proxy_addr VARCHAR,     proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535),     digest VARCHAR,     match_digest VARCHAR,     match_pattern VARCHAR,     negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,     re_modifiers VARCHAR DEFAULT 'CASELESS',     flagOUT INT CHECK (flagOUT >= 0),     replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),     destination_hostgroup INT DEFAULT NULL,     cache_ttl INT CHECK(cache_ttl > 0),     cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,     cache_timeout INT CHECK(cache_timeout >= 0),     reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,     timeout INT UNSIGNED CHECK (timeout >= 0),     retries INT CHECK (retries>=0 AND retries <=1000),     delay INT UNSIGNED CHECK (delay >=0),     next_query_flagIN INT UNSIGNED,     mirror_flagOUT INT UNSIGNED,     mirror_hostgroup INT UNSIGNED,     error_msg VARCHAR,     OK_msg VARCHAR,     sticky_conn INT CHECK (sticky_conn IN (0,1)),     multiplex INT CHECK (multiplex IN (0,1,2)),     gtid_from_hostgroup INT UNSIGNED,     log INT CHECK (log IN (0,1)),     apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,     attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',     comment VARCHAR) 1 row in set (0.00 sec) 其中 rule_id：全局唯一的规则id，规则是按照rule_id的顺序进行处理的； active：表示该规则是否生效。只有该字段值为1的规则才会加载到runtime数据结构； username：用户名筛选，当设置为非NULL值时，只有匹配的用户建立的连接发出的查询才会被匹配； schemaname：schema筛选，当设置为非NULL值时，只有当连接使用schemaname作为默认schema时，该连接发出的查询才会被匹配； flagIN, flagOUT：这两个字段允许我们创建"链式规则"(chains of rules)，即一个规则接一个规则的链式处理； apply：表示是否apply该规则； client_addr：通过源地址进行匹配；从1.4.7开始，支持按网段进行匹配，如192.168.56.% 匹配C网段； match_digest：通过正则表达式匹配digest； match_pattern：通过正则表达式匹配查询语句的文本内容； destination_hostgroup：将匹配到的查询路由到该主机组。当然设置transaction_persistent=1的事务除外；cache_ttl：设置查询结果缓存的ttl失效时间； error_msg：当查询被阻塞时，则该列设置的值将会返回给客户端。通常用于黑名单功能，即如果匹配该规则后，如果其error_msg不为空，则客户端的操作将会接受到error_msg设置的错误信息；

其他配置表具体说明参考https://proxysql.com/documentation/main-runtime/#mysql_servers

作者简介

丁鹏，中国移动云能力中心数据库产品部分布式数据库高级开发工程师，负责云原生数据库海山（He3DB）分布式版架构设计，拥有丰富的数据库内核开发经验和实践知识。