前言 在MySQL的高可用集群环境中，中间件是不可缺少的一部分，它提供了读写分离、负载均衡等各种功能，满足集群的横向、纵向的可扩展。由于官方并没有在这方面推出好的产品，更多的是第三方的产品。如：
ProxySQL #Percona MaxScale #MariaDB Atlas #360开源 OneProxy #平民软件楼方鑫 MyCat #社区推广 KingShard #原Atlas作者离职后使用go开发 TDDL #阿里巴巴开源 Cobar #阿里巴巴开源 DBProxy #美团在360Atlas上修改后开源 Fabric #官方产品 DRDS #阿里云分库分表产品 本次以测试ProxySQL为例，逐步了解ProxySQL的使用方式。
准备 环境：
安装配置详解 官网：http://www.proxysql.com/ https://www.percona.com/downloads/proxysql/ https://github.com/sysown/proxysql/
安装 下载proxysql可以有三种途径,分别为官网、Percona网站和Github网站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] Starting ProxySQL: DONE! [root@jiessie test] 总用量 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] 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] 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使其存到硬盘以供下次重启加载。
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_开关的表如下：
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代表同属于一个角色。
status：
max_connections：允许连接到该后端实例的最大连接数，不要大于MySQL的max_connections。
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实例的凭证,参考密码管理 。
active：是否生效该用户，active=0的用户将在数据库中被跟踪，但不会加载到内存中的数据结构中。
default_hostgroup：这个用户的请求没有匹配到规则时，默认发到hostgroup，默认0。
default_schema：这个用户连接时没有指定schema时，默认使用的schema。
transaction_persistent： 如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不论是否会匹配上其它路由规则，直到事务结束。
frontend：如果设置为1，则用户名、密码对ProxySQL进行身份验证。
backend：如果设置为1，则用户名、密码根据任何主机组向mysqld服务器进行身份验证。
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
client_addr：匹配客户端来源IP。
proxy_addr,proxy_port：匹配本地proxysql的ip、端口。
digest：精确匹配的查询。
match_digest：正则匹配查询。query,digest是指对查询去掉具体值后进行”模糊化“后的查询，类似pt-query-digest的效果。
match_pattern：正则匹配查询。
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。
retries：语句在执行失败时，重试次数。默认由mysql-query_retries_on_failure变量指定，为1。建议不要重试，有风险。
delay：查询延迟执行，这是ProxySQL提供的限流机制，会让其它的查询优先执行。
mirror_flagOUT,mirror_hostgroup：与镜像相关的设置。
error_msg：默认为NULL，如果指定了则这个查询直接被block掉，将error_msg返回给客户端。
multiplex：连接是否利用，请参考文章 。
log：是否记录查询日志，可以看到log是否记录的对象是根据规则。
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 >
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相关的代理级别的全局统计
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 - 执行此类查询时期望的持续时间范围。
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 [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表 内置参数 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 runsave mysql query rules to memload mysql query rules to memsave mysql query rules to diskload mysql query rules from config
mysql variables
load mysql variables to runtimesave mysql variables to memory load mysql variables to memory save mysql variables to diskload mysql variables from config
admin variables
ProxySQL读写分离示例 准备 Master:118.190.67.67:3306
安装配置 主从安装配置省略。
示例目标 客户端通过访问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管理端设置：
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)
全局变量生效并保存到磁盘：
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)
查询路由规则：
ProxySQL监控 PMM是Percona推出的一款很好的监控MySQL和MongoDB的开源工具，安装方便，功能丰富，图表美观，同时也支持ProxySQL的监控，故选择PMM作为ProxySQL的监控软件。
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 installedResolving 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
评论