ProxySQL 安装配置详解及读写分离、负载均衡

前言

在MySQL的高可用集群环境中，中间件是不可缺少的一部分，它提供了读写分离、负载均衡等各种功能，满足集群的横向、纵向的可扩展。由于官方并没有在这方面推出好的产品，更多的是第三方的产品。如：

• ProxySQL #Percona
• MaxScale #MariaDB
• Atlas #360开源
• OneProxy #平民软件楼方鑫
• MyCat #社区推广
• KingShard #原Atlas作者离职后使用go开发
• TDDL #阿里巴巴开源
• Cobar #阿里巴巴开源
• DBProxy #美团在360Atlas上修改后开源
• Fabric #官方产品
• DRDS #阿里云分库分表产品

本次以测试ProxySQL为例，逐步了解ProxySQL的使用方式。

准备

环境：
ProxySQL: 1.4.1
Master: 118.190.67.67
Slave: 139.196.95.103(192.168.7.50)

安装配置详解

官网：http://www.proxysql.com/
Percona地址：https://www.percona.com/downloads/proxysql/
Github地址：https://github.com/sysown/proxysql/
本文通过作者编译好的rpm安装，也可通过编译安装的方式安装，本文省略

安装

下载proxysql可以有三种途径,分别为官网、Percona网站和Github网站
本文从github上下载最新稳定版本，这里选择centos67对应的rpm包
下载：wget -c -O proxysql-1.4.1-1-centos67.x86_64.rpm https://github.com/sysown/proxysql/releases/download/v1.4.1/proxysql-1.4.1-1-centos67.x86_64.rpm

[root@iZuf6c08fdv8duubho2b0rZ test]# yum localinstall -y proxysql-1.4.1-1-centos67.x86_64.rpm Loaded plugins: security docker-main-repo | 2.9 kB 00:00 Setting up Install Process Resolving Dependencies There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them. --> Running transaction check ---> Package proxysql.x86_64 0:1.4.1-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================================================================== Package Arch Version Repository Size ======================================================================================================================================================================== Installing: proxysql x86_64 1.4.1-1 /proxysql-1.4.1-1-centos67.x86_64 19 M Transaction Summary ======================================================================================================================================================================== Install 1 Package(s) Total size: 19 M Installed size: 19 M Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : proxysql-1.4.1-1.x86_64 1/1 Verifying : proxysql-1.4.1-1.x86_64 1/1 Installed: proxysql.x86_64 0:1.4.1-1 Complete!

ProxySQL默认配置文件为/etc/proxysql.cnf,只在第一次启动的时候有用,后续的所有配置都是通过对SQLite数据库的操作,并且不会更新到proxysql中,而是存储在/var/lib/proxysql/proxysql.db中

[root@jiessie test]# proxysql --version #查看版本 ProxySQL version 1.4.1-45-gab4e6ee, codename Truls [root@jiessie test]# rpm -ql proxysql #查看安装的具体内容 /etc/init.d/proxysql #启动脚本 /etc/proxysql.cnf #默认配置文件 /usr/bin/proxysql #执行文件 /usr/share/proxysql/tools/proxysql_galera_checker.sh #ProxySQL调度程序检查pxc_maint_mode参数状态,持续检测各个节点的状态 /usr/share/proxysql/tools/proxysql_galera_writer.pl #ProxySQL指定一个节点直接将流量写入galera [root@jiessie test]#

启动

启动之后才会生成存储目录/var/lib/proxysql

[root@jiessie test]# /etc/init.d/proxysql start Starting ProxySQL: DONE! [root@jiessie test]# ll /var/lib/proxysql/ 总用量 108 -rw------- 1 root root 98304 8月 29 15:37 proxysql.db -rw------- 1 root root 4306 8月 29 16:25 proxysql.log -rw-r--r-- 1 root root 5 8月 29 16:25 proxysql.pid [root@jiessie test]#

内置对象介绍

登录

启动了6032和6033两个端口,默认管理端口是6032,客户端服务端口是6033,默认的用户名密码都是 admin,通过mysql的客户端可以登录

[root@jiessie test]# netstat -tunlp|grep proxysql tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 5181/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 5181/proxysql [root@jiessie test]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2009-2017 Percona LLC and/or its affiliates Copyright (c) 2000, 2017, 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 [(none)] 16:28:32 > show databases; +-----+---------+-------------------------------+ | seq | name | file | +-----+---------+-------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | +-----+---------+-------------------------------+ 4 rows in set (0.00 sec) MySQL [(none)] 16:28:41 >

内置库

main：默认数据库名,用于存放后端db实例、用户认证、路由规则等信息。表名以runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改。只能修改对应的不以runtime_开头的（在内存）里的表，然后LOAD使其生效，SAVE使其存到硬盘以供下次重启加载。
disk：是持久化到硬盘的配置，sqlite数据文件。
stats：是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询各类汇总、执行时间等。
monitor：库存储monitor模块收集的信息，主要是对后端db的健康、延迟检查。

main库

runtime_表

MySQL [main] 17:19:10 > use main Database changed MySQL [main] 17:22:15 > show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ 17 rows in set (0.00 sec) MySQL [main] 17:22:16 >

其中，runtime_开关的表如下：

• runtime_global_variables：global_variables的运行时版本
• runtime_mysql_group_replication_hostgroups：mysql_group_replication_hostgroups的运行时版本
• runtime_mysql_query_rules：mysql_query_rules的运行时版本
• runtime_mysql_replication_hostgroups：mysql_replication_hostsgroups的运行时版本
• runtime_mysql_servers：mysql_servers的运行时版本
• runtime_mysql_users：mysql_users的运行时版本

• runtime_scheduler：scheduler调度程序的运行时版本

  global_variables表

  内置参数表，参考下文

  mysql_servers表

  
  

MySQL [main] 17:22:16 > show create table mysql_servers\G *************************** 1. row *************************** table: mysql_servers Create Table: CREATE TABLE mysql_servers ( hostgroup_id INT NOT NULL DEFAULT 0, hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE', weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1, compression INT CHECK (compression >=0 AND compression <= 102400) 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) MySQL [main] 17:34:05 >

hostgroup_id：ProxySQL通过hostgroup的形式组织后端db实例,一个hostgroup代表同属于一个角色。
表的主键是(hostgroup_id, hostname, port),以hostname:port在多个hostgroup中存在。
一个hostgroup可以有多个实例，即是多个从库，可能通过weight分配权重。
hostgroup_id 0是一个特殊的hostgroup，路由查询的时候，没有匹配到规则则默认选择hostgroup 0。

status：
ONLINE：当前后端实例状态正常。
SHUNNED：临时被剔除，可能因为后端too many connection error，或者超过了max_replication_lag。
OFFLINE_SOFT：软离线状态，不再接受新的连接，但已建立的连接会等待活跃事务完成。
OFFLINE_HARD：硬离线状态，不再接受新的连接，已建立的连接或被强制中断，当后端实例宕机或网络不可达，会出现。

max_connections：允许连接到该后端实例的最大连接数，不要大于MySQL的max_connections。
如果后端实例hostname:port在多个hostgroup里，以较大者为准，而不是各自独立允许的最大连接数。

max_replication_lag：允许的最大延迟，主库不受影响，默认为0，如果>0，monitor模块监控主从延迟大于阈值时，会临时把它的状态变更为SHUNNED。

max_latency_ms：mysql_ping响应时间，大于这个阈值会把它从连接池剔除，即使是ONLINE。

comment：备注，不建设为空。

其他的字段，可通过字面意思理解。

mysql_users表

MySQL [main] 09:13:02 > 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, PRIMARY KEY (username, backend), UNIQUE (username, frontend)) 1 row in set (0.00 sec) MySQL [main] 09:13:08 >

username,password：连接到后端MySQL或ProxySQL实例的凭证,参考密码管理。
密码可插入明文，也可通过PASSWORD()插入密文，proxysql以*开头判断插入是否是密文。
但是runtime_mysql_users里统一是密文，所以明文插入，再SAVE MYSQL USERS TO MEM，此时看到的也是HASH密文。

active：是否生效该用户，active=0的用户将在数据库中被跟踪，但不会加载到内存中的数据结构中。

default_hostgroup：这个用户的请求没有匹配到规则时，默认发到hostgroup，默认0。

default_schema：这个用户连接时没有指定schema时，默认使用的schema。
默认为NULL，实际上受变量mysql-default_schema的影响，默认为information_schema。

transaction_persistent： 如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不论是否会匹配上其它路由规则，直到事务结束。

frontend：如果设置为1，则用户名、密码对ProxySQL进行身份验证。

backend：如果设置为1，则用户名、密码根据任何主机组向mysqld服务器进行身份验证。
注意，目前所有用户都需要将“前端”和“后端“都设置为1，未来版本的ProxySQL将分离前端和后端之间的crendentials。以这种方式，前端将永远不会知道直接连接到后端的凭据，强制所有通过ProxySQL的连接并增加系统的安全性。

mysql_replication_hostgroups表

MySQL [main] 10:18:15 > 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), comment VARCHAR, UNIQUE (reader_hostgroup)) 1 row in set (0.00 sec) MySQL [main] 10:18:22 >

定义hostgroup的主从关系。ProxySQL monitor模块会监控hostgroup后端所有servers的read_only变量，如果发现从库的read_only变为0、主库变为1，则认为角色互换了，自动改写mysql_servers表里面hostgroup关系，达到failover效果。

mysql_query_rules查询规则表

MySQL [main] 10:25:22 > 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 NOT NULL DEFAULT 0, client_addr VARCHAR, proxy_addr VARCHAR, proxy_port INT, 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, replace_pattern VARCHAR, destination_hostgroup INT DEFAULT NULL, cache_ttl INT CHECK(cache_ttl > 0), reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL, timeout INT UNSIGNED, retries INT CHECK (retries>=0 AND retries <=1000), delay INT UNSIGNED, 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)), log INT CHECK (log IN (0,1)), apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0, comment VARCHAR) 1 row in set (0.00 sec) MySQL [main] 10:25:34 >

rule_id：表主键，自增，规则处理是以rule_id为顺序进行。

active：只有active=1时的规则才会参与匹配。

username：过滤匹配用户名的条件，如果是非空值，则仅当连接使用正确的用户名时，查询才匹配。

schemaname：匹配schemaname的过滤条件，如果是非空值，则仅当连接schemaname用作默认模式时，查询才匹配。

flagIN,flagOUT,apply：用来定义路由链chains of rules
首先会检查flagIN=0的规则，以rule_id的顺序；如果没有匹配上，则走这个用户的default_hostgroup。
当匹配一条规则后，会检查flagOUT。
如果不为NULL，并且flagIN!=flagOUT，则进入以flagIN为上一个flagOUT值的新规则链。
如果不为NULL，并且flagIN=flagOUT，则应用这条规则。
如果为NULL，或者apply=1，则结束，应用这条规则。
如果最终没有匹配到，则找到这个用户的default_hostgroup。

client_addr：匹配客户端来源IP。

proxy_addr,proxy_port：匹配本地proxysql的ip、端口。

digest：精确匹配的查询。

match_digest：正则匹配查询。query,digest是指对查询去掉具体值后进行”模糊化“后的查询，类似pt-query-digest的效果。

match_pattern：正则匹配查询。
以上都是匹配查询的规则，1.4版本可以通过变量mysql-query_processor_regex设置，支持RE2和PCRE，1.4版本开始默认为PCRE。

negate_match_pattern：反向匹配，相当于对match_digest/match_pattern的匹配取反。

re_modifiers：修改正则匹配的参数，比如默认的：忽略大小写CASELESS、禁用GLOBAL。

下面是匹配后的行为：

replace_pattern：查询重写，默认为空。

destination_hostgroup：路由查询到这个hostgroup，当然如果用户显式start transaction且transaction_persistent=1,那么即使匹配到了，也依然按照事务里第一条sql的路由规则去走的。

cache_ttl：查询结果缓存的毫秒数。

timeout：这一类查询执行的的最大时间(毫秒),超时则自动kill。
这是对后端DB的保护机制，相当于阿里云RDS的loose_max_statement_time变量的功能，但不同的是，阿里云这个变量的时间时不包括DML操作出现InnoDB行锁等待的时间，而ProxySQL的这个timeout是计算从发送sql到等待响应的时间。默认mysql-default_query_timeout是10h。

retries：语句在执行失败时，重试次数。默认由mysql-query_retries_on_failure变量指定，为1。建议不要重试，有风险。

delay：查询延迟执行，这是ProxySQL提供的限流机制，会让其它的查询优先执行。
默认值mysql-default_query_delay为0。

mirror_flagOUT,mirror_hostgroup：与镜像相关的设置。

error_msg：默认为NULL，如果指定了则这个查询直接被block掉，将error_msg返回给客户端。

multiplex：连接是否利用，请参考文章。

log：是否记录查询日志，可以看到log是否记录的对象是根据规则。
要开启日志记录，需要设置变量mysql-eventslog_filename来指定文件名，然后这个log标记为1。但是目前proxysql记录的日志是二进制格式，需要特定的工具才能读取：eventslog_reader_sample。这个工具在源码目录 tools下面。

scheduler调度表

MySQL [main] 10:27:52 > show create table scheduler\G *************************** 1. row *************************** table: scheduler Create Table: CREATE TABLE scheduler ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL, filename VARCHAR NOT NULL, arg1 VARCHAR, arg2 VARCHAR, arg3 VARCHAR, arg4 VARCHAR, arg5 VARCHAR, comment VARCHAR NOT NULL DEFAULT '') 1 row in set (0.00 sec) MySQL [main] 11:09:59 >

id：调度程序作业的唯一标识符。

active：如果设置为1，则作业处于活动状态。

interval_ms：工作的开始频率(以毫秒为单位)，最小interval_ms为100毫秒。

filename：可执行文件的完整路径。

arg1-arg5：传递作业的参数。最多5个。

comment：注释。

参考文档

disk库

• MySQL [main] 15:12:14 > show tables from disk; +------------------------------------+ | tables | +------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | scheduler | +------------------------------------+ 9 rows in set (0.00 sec) MySQL [main] 15:12:26 >

具体的表介绍和main库一致。

stats库

MySQL [main] 15:12:26 > show tables from stats; +-----------------------------------+ | tables | +-----------------------------------+ | global_variables | | stats_memory_metrics | | stats_mysql_commands_counters | | stats_mysql_connection_pool | | stats_mysql_connection_pool_reset | | stats_mysql_global | | stats_mysql_processlist | | stats_mysql_query_digest | | stats_mysql_query_digest_reset | | stats_mysql_query_rules | | stats_mysql_users | | stats_proxysql_servers_metrics | | stats_proxysql_servers_status | +-----------------------------------+ 13 rows in set (0.00 sec) MySQL [main] 15:13:53 >

stats_mysql_commands_counters表

MySQL [stats] 15:15:36 > show create table stats.stats_mysql_commands_counters\G *************************** 1. row *************************** table: stats_mysql_commands_counters Create Table: CREATE TABLE stats_mysql_commands_counters ( Command VARCHAR NOT NULL PRIMARY KEY, Total_Time_us INT NOT NULL, Total_cnt INT NOT NULL, cnt_100us INT NOT NULL, cnt_500us INT NOT NULL, cnt_1ms INT NOT NULL, cnt_5ms INT NOT NULL, cnt_10ms INT NOT NULL, cnt_50ms INT NOT NULL, cnt_100ms INT NOT NULL, cnt_500ms INT NOT NULL, cnt_1s INT NOT NULL, cnt_5s INT NOT NULL, cnt_10s INT NOT NULL, cnt_INFs) 1 row in set (0.00 sec) MySQL [stats] 15:15:58 >

• command：已执行的SQL命令的类型，如FLUSH、INSERT、KILL、SELECT FOR UPDATE等。
• Total_Time_us：执行该类型命令的总时间(以毫秒为单位)。
• Total_cnt：执行该类型的命令的总数。

• cnt_100us-cnt_INFs：在指定的时间限制内执行的给定类型的命令总数和前一个命令的总数。
  #####　stats_mysql_connection_pool表

MySQL [stats] 15:15:58 > show create table stats.stats_mysql_connection_pool \G *************************** 1. row *************************** table: stats_mysql_connection_pool Create Table: CREATE TABLE stats_mysql_connection_pool ( hostgroup INT, srv_host VARCHAR, srv_port INT, status VARCHAR, ConnUsed INT, ConnFree INT, ConnOK INT, ConnERR INT, Queries INT, Bytes_data_sent INT, Bytes_data_recv INT, Latency_us INT) 1 row in set (0.00 sec) MySQL [stats] 15:20:08 >

hostgroup：后端服务器所属的主机组，单个后端服务器可以属于多个主机组。

srv_host,srv_port：mysqld后端服务器正在侦听连接的TCP端点的IP和Port。

status：后端服务器的状态。可以有ONLINE，SHUNNED，OFFLINE_SOFT，OFFLINE_HARD。

ConnUsed：ProxySQL当前使用多少个连接来向后端服务器发送查询。

ConnFree：目前有多少个连接是空闲。

ConnOK：成功建立了多少个连接。

ConnERR：没有成功建立多少个连接。

Queries：路由到此特定后端服务器的查询数。

Bytes_data_sent：发送到后端的数据量。

Bytes_data_recv：从后端接收的数据量。

Latency_ms：从Monitor报告的当前ping以毫秒为单位的延迟时间。

stats_mysql_global表

MySQL [stats] 15:20:08 > show create table stats.stats_mysql_global\G *************************** 1. row *************************** table: stats_mysql_global Create Table: CREATE TABLE stats_mysql_global ( Variable_Name VARCHAR NOT NULL PRIMARY KEY, Variable_Value VARCHAR NOT NULL) 1 row in set (0.00 sec) MySQL [stats] 15:22:35 >

Variable_Name：代表与MySQL相关的代理级别的全局统计
如Client_Connections_aborted：由于无效凭据或max_connections而导致的前端连接数已达到；
如Client_Connections_connected：当前连接的前端连接数。
如Client_Connections_created：到目前为止创建的前端连接数。等等。

Variable_Value：统计所对应的值。

stats_mysql_processlist表

MySQL [stats] 15:24:11 > show create table stats.stats_mysql_processlist\G *************************** 1. row *************************** table: stats_mysql_processlist Create Table: CREATE TABLE stats_mysql_processlist ( ThreadID INT NOT NULL, SessionID INTEGER PRIMARY KEY, user VARCHAR, db VARCHAR, cli_host VARCHAR, cli_port INT, hostgroup INT, l_srv_host VARCHAR, l_srv_port INT, srv_host VARCHAR, srv_port INT, command VARCHAR, time_ms INT NOT NULL, info VARCHAR) 1 row in set (0.00 sec) MySQL [stats] 15:26:43 >

ThreadID：ProxySQL线程的内部ID。

SessionID：ProxySQL会话ID，通过这个ID可以进行kill操作。

user：与MySQL客户端连接到ProxySQL的用户。

db：当前选择的数据库。

cli_host,cli_port：连接ProxySQL的IP和TCP端口。

hostgroup：当前主机组。如果正在处理查询，则是查询已被路由或将要路由的主机组，或默认主机组。可以通过这个查看该SQL到底是到哪个HG里。

l_srv_host,l_srv_port：ProxySQL的IP和TCP端口。

srv_host,srv_port：后端MySQL服务器的IP和端口。

command：正在执行的MySQL查询的类型。

time_ms：命令执行的时间(以毫秒为单位)。

info：正在执行的SQL。

stats_mysql_query_digest表

MySQL [stats] 15:26:43 > show create table stats.stats_mysql_query_digest\G *************************** 1. row *************************** table: stats_mysql_query_digest Create Table: CREATE TABLE stats_mysql_query_digest ( hostgroup INT, schemaname VARCHAR NOT NULL, username VARCHAR NOT NULL, digest VARCHAR NOT NULL, digest_text VARCHAR NOT NULL, count_star INTEGER NOT NULL, first_seen INTEGER NOT NULL, last_seen INTEGER NOT NULL, sum_time INTEGER NOT NULL, min_time INTEGER NOT NULL, max_time INTEGER NOT NULL, PRIMARY KEY(hostgroup, schemaname, username, digest)) 1 row in set (0.00 sec) MySQL [stats] 15:29:27 >

hostgroup：发送查询的主机组。值-1表示查询查询缓存。

schemaname：查询的数据库。

user：连接ProxySQL的用户名。

digest：一个十六进制散列，表示其参数剥离的SQL。

digest_text：参数剥离的实际SQL文本

count_star：执行查询的总次数（参数的值不同）。

first_seen：unix时间戳，是通过代理路由查询的第一时刻。

last_seen：unix时间戳，当查询通过代理路由时的最后一刻（到目前为止）。

sum_time：执行此类查询的总时间（以微秒为单位）。
这对于确定应用程序工作负载中花费的最多时间在哪里是非常有用的，并为改进的地方提供了一个良好的起点。

min_time,max_time - 执行此类查询时期望的持续时间范围。
min_time是到目前为止所看到的最小执行时间，而max_time表示最大执行时间，以微秒为单位。

stats_mysql_query_rules表

MySQL [stats] 15:29:27 > show create table stats.stats_mysql_query_rules\G *************************** 1. row *************************** table: stats_mysql_query_rules Create Table: CREATE TABLE stats_mysql_query_rules ( rule_id INTEGER PRIMARY KEY, hits INT NOT NULL) 1 row in set (0.00 sec) MySQL [stats] 15:31:57 >

rule_id：路由规则的ID与main.mysql_query_rules的id对应。

hits：此路由规则的匹配总数。 如果当前传入的查询符合规则，则会记录一次命中。

monitor库

对后端MySQL的健康检查，由变量mysql-monitor_enabled来确定是否开启Monitor模块。

• MySQL [stats] 15:35:32 > show tables from monitor; +------------------------------------+ | tables | +------------------------------------+ | mysql_server_connect | | mysql_server_connect_log | | mysql_server_group_replication_log | | mysql_server_ping | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +------------------------------------+ 7 rows in set (0.00 sec) MySQL [stats] 15:35:52 >

mysql_server_connect/mysql_server_connect_log表

MySQL [stats] 15:37:39 > show create table monitor.mysql_server_connect\G *************************** 1. row *************************** table: mysql_server_connect Create Table: CREATE TABLE mysql_server_connect ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_since INT NOT NULL DEFAULT 0, time_until INT NOT NULL DEFAULT 0, connect_success_count INT NOT NULL DEFAULT 0, connect_success_first INT NOT NULL DEFAULT 0, connect_success_last INT NOT NULL DEFAULT 0, connect_success_time_min INT NOT NULL DEFAULT 0, connect_success_time_max INT NOT NULL DEFAULT 0, connect_success_time_total INT NOT NULL DEFAULT 0, connect_failure_count INT NOT NULL DEFAULT 0, connect_failure_first INT NOT NULL DEFAULT 0, connect_failure_last INT NOT NULL DEFAULT 0, PRIMARY KEY (hostname, port)) 1 row in set (0.00 sec) MySQL [stats] 15:37:46 > show create table monitor.mysql_server_connect_log\G *************************** 1. row *************************** table: mysql_server_connect_log Create Table: CREATE TABLE mysql_server_connect_log ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_start_us INT NOT NULL DEFAULT 0, connect_success_time_us INT DEFAULT 0, connect_error VARCHAR, PRIMARY KEY (hostname, port, time_start_us)) 1 row in set (0.00 sec) MySQL [stats] 15:37:51 >

• 连接到所有MySQL服务器以检查它们是否可用，该表用来存放检测连接的日志。由变量mysql-monitor_connect_interval来控制其检测的时间间隔，由参数mysql-monitor_connect_timeout控制连接是否超时（默认200毫秒）。

  mysql_server_ping/mysql_server_ping_log表

MySQL [stats] 15:39:23 > show create table monitor.mysql_server_ping\G *************************** 1. row *************************** table: mysql_server_ping Create Table: CREATE TABLE mysql_server_ping ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_since INT NOT NULL DEFAULT 0, time_until INT NOT NULL DEFAULT 0, ping_success_count INT NOT NULL DEFAULT 0, ping_success_first INT NOT NULL DEFAULT 0, ping_success_last INT NOT NULL DEFAULT 0, ping_success_time_min INT NOT NULL DEFAULT 0, ping_success_time_max INT NOT NULL DEFAULT 0, ping_success_time_total INT NOT NULL DEFAULT 0, ping_failure_count INT NOT NULL DEFAULT 0, ping_failure_first INT NOT NULL DEFAULT 0, ping_failure_last INT NOT NULL DEFAULT 0, PRIMARY KEY (hostname, port)) 1 row in set (0.00 sec) MySQL [stats] 15:40:12 > show create table monitor.mysql_server_ping_log\G *************************** 1. row *************************** table: mysql_server_ping_log Create Table: CREATE TABLE mysql_server_ping_log ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_start_us INT NOT NULL DEFAULT 0, ping_success_time_us INT DEFAULT 0, ping_error VARCHAR, PRIMARY KEY (hostname, port, time_start_us)) 1 row in set (0.00 sec) MySQL [stats] 15:40:15 >

使用mysql_ping API ping后端MySQL服务器检查它们是否可用，该表用来存放ping的日志。由变量mysql-monitor_ping_interval控制ping的时间间隔，默认值：10000（毫秒，相当于10秒）。

mysql_server_replication_lag_log表

• MySQL [stats] 15:40:53 > show create table monitor.mysql_server_replication_lag_log\G *************************** 1. row *************************** table: mysql_server_replication_lag_log Create Table: CREATE TABLE mysql_server_replication_lag_log ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_start_us INT NOT NULL DEFAULT 0, success_time_us INT DEFAULT 0, repl_lag INT DEFAULT 0, error VARCHAR, PRIMARY KEY (hostname, port, time_start_us)) 1 row in set (0.00 sec) MySQL [stats] 15:41:12 >

• 后端MySQL服务主从延迟的检测。由参数mysql-monitor_replication_lag_interval控制检测间隔时间， 如果复制滞后太大，可以暂时关闭从。由mysql_servers.max_replication_lag列控制。默认值：10000（毫秒，相当于10秒）。

内置参数

global_variables 1.4版本中有95个参数，参数较多，解释请参考文档。

MySQL [main] 11:16:22 > show variables; +-----------------------------------------------------+--------------------+ | Variable_name | Value | +-----------------------------------------------------+--------------------+ | admin-admin_credentials | admin:admin | | admin-cluster_check_interval_ms | 1000 | | admin-cluster_password | | | admin-cluster_username | | | admin-hash_passwords | true | | admin-mysql_ifaces | 0.0.0.0:6032 | | admin-read_only | false | | admin-refresh_interval | 2000 | | admin-stats_credentials | stats:stats | | admin-telnet_admin_ifaces | (null) | | admin-telnet_stats_ifaces | (null) | | admin-version | 1.4.1-45-gab4e6ee | | mysql-client_found_rows | true | | mysql-commands_stats | true | | mysql-connect_retries_delay | 1 | | mysql-connect_retries_on_failure | 10 | | mysql-connect_timeout_server | 3000 | | mysql-connect_timeout_server_max | 10000 | | mysql-connection_delay_multiplex_ms | 0 | | mysql-connection_max_age_ms | 0 | | mysql-default_charset | utf8 | | mysql-default_max_latency_ms | 1000 | | mysql-default_query_delay | 0 | | mysql-default_query_timeout | 36000000 | | mysql-default_reconnect | true | | mysql-default_schema | information_schema | | mysql-default_sql_mode | | | mysql-default_time_zone | SYSTEM | | mysql-enforce_autocommit_on_reads | false | | mysql-eventslog_filename | | | mysql-eventslog_filesize | 104857600 | | mysql-forward_autocommit | false | | mysql-free_connections_pct | 10 | | mysql-have_compress | true | | mysql-hostgroup_manager_verbose | 1 | | mysql-init_connect | (null) | | mysql-interfaces | 0.0.0.0:6033 | | mysql-long_query_time | 1000 | | mysql-max_allowed_packet | 4194304 | | mysql-max_connections | 2048 | | mysql-max_stmts_cache | 10000 | | mysql-max_stmts_per_connection | 20 | | mysql-max_transaction_time | 14400000 | | mysql-mirror_max_concurrency | 16 | | mysql-mirror_max_queue_length | 32000 | | mysql-monitor_connect_interval | 60000 | | mysql-monitor_connect_timeout | 600 | | mysql-monitor_enabled | true | | mysql-monitor_groupreplication_healthcheck_interval | 5000 | | mysql-monitor_groupreplication_healthcheck_timeout | 800 | | mysql-monitor_history | 600000 | | mysql-monitor_password | monitor | | mysql-monitor_ping_interval | 10000 | | mysql-monitor_ping_max_failures | 3 | | mysql-monitor_ping_timeout | 1000 | | mysql-monitor_query_interval | 60000 | | mysql-monitor_query_timeout | 100 | | mysql-monitor_read_only_interval | 1500 | | mysql-monitor_read_only_timeout | 500 | | mysql-monitor_replication_lag_interval | 10000 | | mysql-monitor_replication_lag_timeout | 1000 | | mysql-monitor_slave_lag_when_null | 60 | | mysql-monitor_username | monitor | | mysql-monitor_wait_timeout | true | | mysql-monitor_writer_is_also_reader | true | | mysql-multiplexing | true | | mysql-ping_interval_server_msec | 120000 | | mysql-ping_timeout_server | 500 | | mysql-poll_timeout | 2000 | | mysql-poll_timeout_on_failure | 100 | | mysql-query_cache_size_MB | 256 | | mysql-query_digests | true | | mysql-query_digests_lowercase | false | | mysql-query_digests_max_digest_length | 2048 | | mysql-query_digests_max_query_length | 65000 | | mysql-query_processor_iterations | 0 | | mysql-query_processor_regex | 1 | | mysql-query_retries_on_failure | 1 | | mysql-server_capabilities | 45578 | | mysql-server_version | 5.5.30 | | mysql-servers_stats | true | | mysql-session_idle_ms | 1000 | | mysql-session_idle_show_processlist | true | | mysql-sessions_sort | true | | mysql-shun_on_failures | 5 | | mysql-shun_recovery_time_sec | 10 | | mysql-ssl_p2s_ca | (null) | | mysql-ssl_p2s_cert | (null) | | mysql-ssl_p2s_cipher | (null) | | mysql-ssl_p2s_key | (null) | | mysql-stacksize | 1048576 | | mysql-threads | 4 | | mysql-threshold_query_length | 524288 | | mysql-threshold_resultset_size | 4194304 | | mysql-wait_timeout | 28800000 | +-----------------------------------------------------+--------------------+ 95 rows in set (0.00 sec) MySQL [main] 11:16:33 >

ProxySQL多层配置设计

ProxySQL设计模型介绍

ProxySQL使用多层配置系统，适合满足以下需求：

• 允许自动更新配置，与MySQL兼容管理界面；
• 允许在线修改配置，不用重启ProxySQL；

• 允许回滚配置；
  多层配置系统的实现，如下图：

+-------------------------+ | RUNTIME | +-------------------------+ /|\ | | | [1] | [2] | | \|/ +-------------------------+ | MEMORY | +-------------------------+ _ /|\ | |\ | | \ [3] | [4] | \ [5] | \|/ \ +-------------------------+ +-------------------------+ | DISK | | CONFIG FILE | +-------------------------+ +-------------------------+

RUNTIME代表ProxySQL当前生效的配置，包括global_variables、mysql_servers、mysql_users、mysql_query_rules。无法直接修改这里的配置，必须要从下一层load过来。

MEMORY(main)代表平时在mysql命令行修改的main里的配置，可以认为是SQLite数据库在内存的镜像。可修改以下：

mysql_server 后端服务器列表 mysql_users 连接到ProxySQL的用户列表及其凭据 mysql_query_rules 将流量路由到不同的后端服务器的规则列表 global_variables 全局变量列表 mysql_collat MySQL排序规则列表

DISK和CONFIG FILE表示磁盘上SQLite数据库，默认位置在$datadir/proxysql.db，在重新启动过程中，内存中未被保存的配置将丢失。/etc/proxysql.cnf文件只在第一次初始化的时候用到。如要修改端口，还是需要在管理命令行里修改，再save到磁盘。

ProxySQL多层配置修改示例

mysql users

LOAD MYSQL USERS TO RUNTIME / LOAD MYSQL USERS FROM MEMORY SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK SAVE MYSQL USERS TO DISK / SAVE MYSQL USERS FROM MEMORY LOAD MYSQL USERS FROM CONFIG

mysql servers

LOAD MYSQL SERVERS TO RUNTIME 让修改的配置生效 SAVE MYSQL SERVERS TO MEMORY LOAD MYSQL SERVERS TO MEMORY SAVE MYSQL SERVERS TO DISK 将修改的配置持久化 LOAD MYSQL SERVERS FROM CONFIG

mysql query rules

load mysql query rules to run save mysql query rules to mem load mysql query rules to mem save mysql query rules to disk load mysql query rules from config

mysql variables

load mysql variables to runtime save mysql variables to memory load mysql variables to memory save mysql variables to disk load mysql variables from config

admin variables

• load admin variables to runtime save admin variables to memory load admin variables to memory save admin variables to disk load admin variables from config

• 参考

• https://github.com/sysown/proxysql/wiki/Multi-layer-configuration-system
• https://severalnines.com/blog/mysql-load-balancing-proxysql-overview
• http://seanlook.com/2017/04/10/mysql-proxysql-install-config/

ProxySQL读写分离示例

准备

Master:118.190.67.67:3306
Slave :139.196.95.103:3306(192.168.7.50)
ProxySQL：139.196.95.103:3306(192.168.7.50)
版本：percona-server 5.7.18

安装配置

主从安装配置省略。

示例目标

客户端通过访问ProxySQL的ip，实际访问Master和Slave的效果。

添加后端DB服务

100是主库，101是从库，同时主库也处理1/10的读请求，登录ProxySQL管理端设置：

MySQL [(none)] 14:22:06 > insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(100, '118.190.67.67', 3306, 1, 'db0,ReadWrite'),(101, '192.168.7.50', 3306, 9, 'db0,ReadOnly'); Query OK, 2 rows affected (0.00 sec)

添加访问用户

登录Master主库设置监控用户和程序用户(由于是测试使用，权限较大，主机允许所有)：

MySQL [(none)] 15:51:32 > create user 'monitor'@'%' identified by 'monitor'; Query OK, 0 rows affected (0.00 sec) MySQL [(none)] 15:51:37 > grant select,super,process,show databases,replication client,replication slave on *.* to 'monitor'@'%'; Query OK, 0 rows affected (0.00 sec) MySQL [(none)] 15:45:23 > create user 'user0'@'%' identified by 'password0'; Query OK, 0 rows affected (0.00 sec) MySQL [(none)] 15:49:42 > GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES, EXECUTE, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'read0'@'%'; Query OK, 0 rows affected (0.00 sec)

登录ProxySQL管理端设置：
这里default_hostgroup指定了hostgroup为100的主库，下文会设置SELECT …FOR UPDATE规则到100，SELECT到101，其他所有的SQL到default_hostgroup，也就是主库。

MySQL [(none)] 14:22:15 > INSERT INTO mysql_users (username, password, active, default_hostgroup, max_connections) VALUES ('user0', 'password0', 1, 100, 1000); Query OK, 2 rows affected (0.00 sec)

添加复制关系

登录ProxySQL管理端设置：

MySQL [main] 17:32:46 > INSERT INTO mysql_replication_hostgroups VALUES(100,101,'db0'); Query OK, 1 row affected (0.00 sec) MySQL [main] 17:33:30 > load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main] 17:33:54 > save mysql variables to disk; Query OK, 83 rows affected (0.01 sec) MySQL [main] 17:35:53 > SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10; +---------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +---------------+------+------------------+-----------------+-----------+-------+ | 192.168.7.50 | 3306 | 1504085770195146 | 630 | 1 | NULL | | 118.190.67.67 | 3306 | 1504085770194710 | 23722 | 0 | NULL | | 192.168.7.50 | 3306 | 1504085768695087 | 650 | 1 | NULL | | 118.190.67.67 | 3306 | 1504085768694620 | 23706 | 0 | NULL | | 192.168.7.50 | 3306 | 1504085767194957 | 628 | 1 | NULL | | 118.190.67.67 | 3306 | 1504085767194507 | 23686 | 0 | NULL | | 192.168.7.50 | 3306 | 1504085765694834 | 634 | 1 | NULL | | 118.190.67.67 | 3306 | 1504085765694387 | 23669 | 0 | NULL | | 192.168.7.50 | 3306 | 1504085764194744 | 641 | 1 | NULL | | 118.190.67.67 | 3306 | 1504085764194301 | 23729 | 0 | NULL | +---------------+------+------------------+-----------------+-----------+-------+ 10 rows in set (0.00 sec)

修改全局变量

登录ProxySQL管理端设置：

MySQL [(none)] 14:25:07 > set mysql-query_retries_on_failure=0; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-max_stmts_per_connection=1000; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-eventslog_filename='queries.log'; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-monitor_slave_lag_when_null=7200; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-ping_timeout_server=1500; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-monitor_connect_timeout=1000; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-default_max_latency_ms=2000; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-monitor_password='monitor'; Query OK, 1 row affected (0.00 sec) MySQL [(none)] 14:25:07 > set mysql-server_version='5.7.18'; Query OK, 1 row affected (0.00 sec)

全局变量生效并保存到磁盘：
登录ProxySQL管理端设置：

MySQL [(none)] 14:25:07 > load mysql users to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [(none)] 14:25:27 > load mysql servers to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [(none)] 14:25:27 > load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [(none)] 14:25:28 > save mysql users to disk; Query OK, 0 rows affected (0.02 sec) MySQL [(none)] 14:25:36 > save mysql servers to disk; save mysql variables to disk;Query OK, 0 rows affected (0.03 sec) MySQL [(none)] 14:25:37 > save mysql variables to disk; Query OK, 83 rows affected (0.01 sec) MySQL [(none)] 14:25:37 > save mysql users to mem; -- 可以屏蔽看到的明文密码 Query OK, 0 rows affected (0.00 sec)

路由规则

• ProxySQL使用查询规则来确定路由，如果没有规则用于查询，默认会访问hostgroup 0主机组，会报以下错误：

• [root@jiessie ~]# mysql -uuser0 -ppassword0 -h 127.0.0.1 -P6033 -e "SELECT 1" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 1 after 2000ms

设置路由规则：

MySQL [(none)] 09:29:14 > use main; Database changed MySQL [main] 09:29:15 > INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, cache_ttl) VALUES (1, '^SELECT .* FOR UPDATE', 100, NULL); Query OK, 1 row affected (0.00 sec) MySQL [main] 09:29:17 > INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, cache_ttl) VALUES (1, '^SELECT .*', 101, NULL); Query OK, 1 row affected (0.00 sec) MySQL [main] 09:29:18 > load mysql query rules to run; Query OK, 0 rows affected (0.00 sec) MySQL [main] 09:29:56 > save mysql query rules to disk; Query OK, 0 rows affected (0.03 sec)

常用查询

• 查询连接日志：

MySQL [(none)] 13:45:08 > SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10; +---------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +---------------+------+------------------+-------------------------+---------------+ | 192.168.7.50 | 3306 | 1504590343795072 | 410 | NULL | | 118.190.67.67 | 3306 | 1504590343780010 | 69662 | NULL | | 192.168.7.50 | 3306 | 1504590283795083 | 521 | NULL | | 118.190.67.67 | 3306 | 1504590283779977 | 68310 | NULL | | 192.168.7.50 | 3306 | 1504590223794987 | 533 | NULL | | 118.190.67.67 | 3306 | 1504590223779913 | 53220 | NULL | | 192.168.7.50 | 3306 | 1504590163794887 | 497 | NULL | | 118.190.67.67 | 3306 | 1504590163779772 | 71389 | NULL | | 192.168.7.50 | 3306 | 1504590103794788 | 487 | NULL | | 118.190.67.67 | 3306 | 1504590103779728 | 68372 | NULL | +---------------+------+------------------+-------------------------+---------------+ 10 rows in set (0.00 sec)

查询ping日志：

MySQL [(none)] 13:46:22 > SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10; +---------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +---------------+------+------------------+----------------------+------------+ | 192.168.7.50 | 3306 | 1504590413773092 | 100 | NULL | | 118.190.67.67 | 3306 | 1504590413770521 | 23105 | NULL | | 192.168.7.50 | 3306 | 1504590403773088 | 168 | NULL | | 118.190.67.67 | 3306 | 1504590403770479 | 23080 | NULL | | 192.168.7.50 | 3306 | 1504590393772977 | 135 | NULL | | 118.190.67.67 | 3306 | 1504590393770364 | 23078 | NULL | | 192.168.7.50 | 3306 | 1504590383772899 | 138 | NULL | | 118.190.67.67 | 3306 | 1504590383770309 | 23205 | NULL | | 192.168.7.50 | 3306 | 1504590373772885 | 102 | NULL | | 118.190.67.67 | 3306 | 1504590373770291 | 23099 | NULL | +---------------+------+------------------+----------------------+------------+ 10 rows in set (0.00 sec)

查询后端DB状态

MySQL [(none)] 13:46:55 > SELECT * FROM mysql_servers; +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+ | 100 | 118.190.67.67 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | db0,ReadWrite | | 101 | 192.168.7.50 | 3306 | ONLINE | 9 | 0 | 1000 | 0 | 0 | 0 | db0,ReadOnly | +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+ 2 rows in set (0.00 sec)

查询监控状态：

MySQL [(none)] 13:47:56 > SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10; +---------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +---------------+------+------------------+-----------------+-----------+-------+ | 192.168.7.50 | 3306 | 1504590524103828 | 577 | 1 | NULL | | 118.190.67.67 | 3306 | 1504590524103406 | 23499 | 0 | NULL | | 192.168.7.50 | 3306 | 1504590522603717 | 646 | 1 | NULL | | 118.190.67.67 | 3306 | 1504590522603268 | 23487 | 0 | NULL | | 192.168.7.50 | 3306 | 1504590521103641 | 629 | 1 | NULL | | 118.190.67.67 | 3306 | 1504590521103182 | 23497 | 0 | NULL | | 192.168.7.50 | 3306 | 1504590519603662 | 639 | 1 | NULL | | 118.190.67.67 | 3306 | 1504590519603201 | 23525 | 0 | NULL | | 192.168.7.50 | 3306 | 1504590518103492 | 618 | 1 | NULL | | 118.190.67.67 | 3306 | 1504590518103062 | 23508 | 0 | NULL | +---------------+------+------------------+-----------------+-----------+-------+ 10 rows in set (0.00 sec)

查询用户信息：

MySQL [(none)] 13:49:28 > SELECT * FROM mysql_users; +----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | user0 | password0 | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 1000 | +----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 1 row in set (0.00 sec)

查询连接池：

MySQL [(none)] 13:51:19 > SELECT * FROM stats.stats_mysql_connection_pool; +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+ | 100 | 118.190.67.67 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 3 | 58 | 233 | 23059 | | 101 | 192.168.7.50 | 3306 | ONLINE | 0 | 1 | 3 | 88 | 5 | 106 | 360 | 102 | +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+ 2 rows in set (0.00 sec)

查询执行命令统计信息：

MySQL [(none)] 13:51:47 > SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt; +---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+ | Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs | +---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+ | SELECT | 167664 | 27 | 15 | 4 | 0 | 6 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | | SET | 0 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | SHOW | 13818 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | +---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+ 3 rows in set (0.00 sec)

查询路由规则的详情：

MySQL [(none)] 13:53:05 > SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC; +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ | 100 | information_schema | user0 | 0xA322907BCBC120DD | select * from tables limit ? | 1 | 1504589288 | 1504589288 | 133826 | 133826 | 133826 | | 100 | information_schema | user0 | 0x02033E45904D3DF0 | show databases | 1 | 1504589886 | 1504589886 | 13818 | 13818 | 13818 | | 100 | information_schema | user0 | 0x3765930C7143F468 | select * from t1 | 1 | 1504589311 | 1504589311 | 13466 | 13466 | 13466 | | 101 | information_schema | user0 | 0xA322907BCBC120DD | select * from tables limit ? | 2 | 1504582304 | 1504583327 | 7325 | 3564 | 3761 | | 101 | test | user0 | 0x814EDBB68FBACD5D | select * from neworders limit ? | 2 | 1504583184 | 1504583242 | 5959 | 2964 | 2995 | | 101 | test | user0 | 0x3765930C7143F468 | select * from t1 | 3 | 1504583288 | 1504589859 | 3386 | 64 | 3056 | | 101 | test | user0 | 0xA322907BCBC120DD | select * from tables limit ? | 2 | 1504583168 | 1504583299 | 3095 | 117 | 2978 | | 101 | information_schema | user0 | 0x620B328FE9D6D71A | SELECT DATABASE() | 2 | 1504589729 | 1504589859 | 607 | 193 | 414 | | 100 | information_schema | user0 | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 8 | 1504582304 | 1504589886 | 0 | 0 | 0 | | 100 | test | user0 | 0x52B8B04283B3A18D | set names utf8 | 1 | 1504583162 | 1504583162 | 0 | 0 | 0 | | 100 | information_schema | user0 | 0x52B8B04283B3A18D | set names utf8 | 1 | 1504582582 | 1504582582 | 0 | 0 | 0 | | 100 | test | user0 | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 6 | 1504583162 | 1504583299 | 0 | 0 | 0 | +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ 12 rows in set (0.00 sec) MySQL [(none)] 13:55:46 > SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; +-----+----------+------------+----------------------------------+ | hg | sum_time | count_star | digest_text | +-----+----------+------------+----------------------------------+ | 100 | 133826 | 1 | select * from tables limit ? | | 100 | 13818 | 1 | show databases | | 100 | 13466 | 1 | select * from t1 | | 101 | 7325 | 2 | select * from tables limit ? | | 101 | 5959 | 2 | select * from neworders limit ? | | 101 | 3386 | 3 | select * from t1 | | 101 | 3095 | 2 | select * from tables limit ? | | 101 | 607 | 2 | SELECT DATABASE() | | 100 | 0 | 8 | select @@version_comment limit ? | | 100 | 0 | 1 | set names utf8 | | 100 | 0 | 1 | set names utf8 | | 100 | 0 | 6 | select @@version_comment limit ? | +-----+----------+------------+----------------------------------+ 12 rows in set (0.00 sec)

查询路由规则：

• MySQL [(none)] 13:57:12 > SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id; +---------+--------------+-----------------------+-----------------+-----------+-------+ | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | +---------+--------------+-----------------------+-----------------+-----------+-------+ | 10 | NULL | ^SELECT .* FOR UPDATE | NULL | NULL | 0 | | 11 | NULL | ^SELECT .* | NULL | NULL | 0 | +---------+--------------+-----------------------+-----------------+-----------+-------+ 2 rows in set (0.00 sec)

• 参考：

• https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration#p6033
• http://seanlook.com/2017/04/17/mysql-proxysql-route-rw_split/
• http://severalnines.com/blog/how-proxysql-adds-failover-and-query-control-your-mysql-replication-setup

ProxySQL监控

PMM是Percona推出的一款很好的监控MySQL和MongoDB的开源工具，安装方便，功能丰富，图表美观，同时也支持ProxySQL的监控，故选择PMM作为ProxySQL的监控软件。
这里以ProxySQL服务端(192.168.7.50)为例，作为PMM的客户端，PMM服务器为139.196.99.230,仅演示ProxySQL安装PMM客户端，服务器安装配置省略。

PMM Client安装

[root@jiessie ~]# yum localinstall -y /hwdata/duanwenjie/test/proxysql-1.4.1-1-centos67.x86_64.rpm Loaded plugins: security Setting up Local Package Process Examining /hwdata/duanwenjie/test/proxysql-1.4.1-1-centos67.x86_64.rpm: proxysql-1.4.1-1.x86_64 Marking /hwdata/duanwenjie/test/proxysql-1.4.1-1-centos67.x86_64.rpm to be installed Resolving Dependencies There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them. --> Running transaction check ---> Package proxysql.x86_64 0:1.4.1-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================================================================== Package Arch Version Repository Size ======================================================================================================================================================================== Installing: proxysql x86_64 1.4.1-1 /proxysql-1.4.1-1-centos67.x86_64 19 M Transaction Summary ======================================================================================================================================================================== Install 1 Package(s) Total size: 19 M Installed size: 19 M Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Warning: RPMDB altered outside of yum. ** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows: mysql-community-libs-5.7.16-1.el6.x86_64 has missing requires of mysql-community-common(x86-64) >= ('0', '5.7.9', None) Installing : proxysql-1.4.1-1.x86_64 1/1 Verifying : proxysql-1.4.1-1.x86_64 1/1 Installed: proxysql.x86_64 0:1.4.1-1 Complete! [root@jiessie ~]#

PMM Client配置

[root@jiessie ~]# ifconfig #查看IP地址 eth0 Link encap:Ethernet HWaddr 00:16:3E:00:DC:49 inet addr:192.168.7.50 Bcast:192.168.15.255 Mask:255.255.240.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:1017660 errors:0 dropped:0 overruns:0 frame:0 TX packets:1380639 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:273791594 (261.1 MiB) TX bytes:116287303 (110.9 MiB) lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:1297762 errors:0 dropped:0 overruns:0 frame:0 TX packets:1297762 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:141606631 (135.0 MiB) TX bytes:141606631 (135.0 MiB) [root@jiessie ~]# pmm-admin config --bind-address 192.168.7.50 --client-address 139.196.95.103 --server 139.196.99.230:8080 --client-name ProxySQL_Test #添加config OK, PMM server is alive. PMM Server | 139.196.99.230:8080 Client Name | ProxySQL_Test Client Address | 139.196.95.103 (192.168.7.50) [root@jiessie ~]# pmm-admin add linux:metrics --force ProxySQL_Test #添加Linux监控 OK, now monitoring this system. [root@jiessie ~]# [root@jiessie ~]# pmm-admin add proxysql:metrics --dsn "admin:admin@tcp(127.0.0.1:6032)/" proxysql6032 #添加ProxySQL监控 OK, now monitoring ProxySQL metrics using DSN admin:***@tcp(localhost:6032) [root@jiessie ~]# [root@jiessie ~]# pmm-admin list #查看状态 pmm-admin 1.3.0 PMM Server | 139.196.99.230:8080 Client Name | ProxySQL_Test Client Address | 139.196.95.103 (192.168.7.50) Service Manager | unix-systemv ----------------- -------------- ----------- -------- ------------------------------ -------- SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS ----------------- -------------- ----------- -------- ------------------------------ -------- linux:metrics ProxySQL_Test 42000 YES - mysql:metrics ProxySQL_Test 42002 YES root:***@tcp(127.0.0.1:3306) proxysql:metrics proxysql6032 42004 YES admin:***@tcp(127.0.0.1:6032) [root@jiessie ~]#

PMM Server监控展示

PMM Server监控项包括：

• 客户端连接数
• 客户端总查询
• ProxySQL连接池状态
• 活动连接
• 失败连接
• 客户端查询路由详情
• 客户端延迟状态
• 网络接口
  

总结

ProxySQL是一款很出色的MySQL中间件，在稳定性上、易用性、高性能等方面表现很不错。由于发布的时间较短，功能可能还不太完善，需要多做测试，特别是查询路由和规则方面需要详情的了解，测试。可重点关注。

来源：https://dwj999.github.io/ProxySQL-%E5%AE%89%E8%A3%85%E9%85%8D%E7%BD%AE%E8%AF%A6%E8%A7%A3%E5%8F%8A%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB%E3%80%81%E8%B4%9F%E8%BD%BD%E5%9D%87%E8%A1%A1.html