海山数据库（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设置的错误信息；