海山数据库(He3DB)MySQL代理ProxySQL使用详解：（一）架构说明与安装一、 ProxySQL介绍 1.1

一、

**ProxySQL

介绍

1.1

简介

业界比较知名的

MySQL

代理，由

ProxySQL LLC

公司开发并提供专业的服务支持，基于

GPLv3

开源协议进行发布

大部分配置项可动态变更。后端的

MySQL

实例可根据用途配置到不同的

hostgroup

中，由

ProxySQL

基于

层网络协议

将来源于

APP

的流量路由到对应的

hostgroup

内，从而实现负载均衡或者读写分离。目前

ProxySQL

有官方版和

percona

版两种。

percona

版是在官方版的基础上修改的，添加了几个比较实用的工具。生产环境建议用官方版。

ProxySQL

相关的网站

www.proxysql.com/

github.com/sysown/prox…

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

多层配置可参考

proxysql.com/Documentati…

二、安装

支持多种安装方式：

• deb

或

rpm

安装包

github.com/sysown/prox…

• repo

包安装

repo.proxysql.com/

• build from git repo any taged or dev version - github.com/sysown/prox…

• build from source code archives - github.com/sysown/prox…

2.1

安装包

官网安装教程：

www.proxysql.com/documentati…

以

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=repo.proxysql.com/ProxySQL/pr…
> EOF
tee: /etc/yum.repos.d/proxysql.repo: Permission denied
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=repo.proxysql.com/ProxySQL/pr…
gpgcheck=1
gpgkey=repo.proxysql.com/ProxySQL/pr…
[user@server1]$

安装

ProxySQL

：

Plain Text
yum install proxysql OR yum install proxysql-version

如果没有权限更改

tee: /etc/yum.repos.d/proxysql.repo: Permission denied

，可以考虑源码编译安装，也

可以考虑去

git

官网下载最新的二进制文件安装。

官网下载：

github.com/sysown/prox…

Bash
#可以复制下载连接地址后使用wget下载
wget github.com/sysown/prox…
#安装依赖
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 github.com/sysown/prox…
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

自身共有

个库，分别为

个保存在内存中的库，和三个保存在磁盘的

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.

• main

：内存配置数据库，表里存放后端

实例、用户验证、路由规则等信息。表名以

runtime_

开头的表示

proxysql

当前运行的配置内容，不能通过

dml

语句修改，只能修改对应的不以

runtime_

开头的（在内存）里的表，然后

LOAD

使其生效，

SAVE

使其存到硬盘以供下次重启加载。

• disk

：是持久化到硬盘的配置，

sqlite

数据文件。

SQLite3

数据库，默认位置为

（

DATADIR

）

/proxysql.db,

在重新启动时，未保留的内存中配置将丢失。因此，将配置保留在

DISK

中非常重要。

(SQLite

是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的

SQL

数据库引擎

)

• stats

：

proxysql

运行抓取的统计信息，包括到后端各命令的执行次数、流量、

processlist

、查询种类汇总

执行时间等等。

• monitor

：库存储

monitor

模块收集的信息，主要是对后端

的健康

延迟检查。

• 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

所在的机器

已加白；

在

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设置的错误信息；

其他配置表具体说明参考

proxysql.com/documentati…

作者

简介

丁鹏

，中国移动云能力中心数据库产品部

分布式

数据库

高级

开发工程师

，

负责云原生数据库海山

（

He3DB

）

分布式

版架构设计

，

拥有

丰富的

数据库内核开发经验

和

实践知识。