前言
小编折腾代码和MySQL的相关工作，最近线上需要一款性能上佳的MySQL中间件产品，本人在了解一些如ProxySQL、MariaDB MaxScale、MySQL Router、Atlas、DBProxy,Oneproxy等相关产品后，经过反复对比和相关测试，初步选用了proxysql。本文是proxysql系列的第一篇，笔者计划proxysql系列的博文将为您介绍proxysql的安装配置、读写分离、可用性测试、深入了解proxysql各组件、proxysql+keepalived实现高可用、proxysql和直连mysql以及其它中间件产品的性能测试、bug测试与线上环境测试报告与总结。
环境
| mysql版本 | 5.7.17 |
| proxysql版本 | 1.3.6 |
| proxysql的地址和端口 |
10.198.9.114的6032和6033端口 |
| mysql master的地址和端口 |
10.198.9.101:3306 |
| mysql slave01的地址和端口 |
10.198.9.101:3307 |
| mysql slave02的地址和端口 |
10.198.9.101:3308 |
proxysql安装
首先从proxysql的github上获取安装包，选择下载对应版本的rpm包，笔者这里选择下载proxysql-1.4.3-1-centos67.x86_64.rpm，github地址：
https://github.com/sysown/proxysql/releases/latest
使用yum安装你刚才下载的rmp包
yum -y install proxysql-1.4.3-1-centos67.x86_64.rpm
查看yum安装过程中生成了哪些文件
# rpm -ql proxysql /etc/init.d/proxysql #proxysql的启动控制文件 /etc/proxysql-admin.cnf /etc/proxysql.cnf #proxysql配置文件 /usr/bin/proxysql /usr/bin/proxysql-admin /usr/bin/proxysql_galera_checker /usr/bin/proxysql_node_monitor /usr/share/doc/proxysql-1.3.6 /usr/share/doc/proxysql-1.3.6/LICENSE /var/lib/proxysql #proxysql的数据文件路径 /var/run/proxysql
查看proxysql的数据文件路径下有哪些文件
# ll /var/lib/proxysql/ total 17548 -rw------- 1 nscd nscd 34816 Jul 13 21:40 proxysql.db -rw------- 1 nscd nscd 17905611 Jul 14 14:59 proxysql.log -rw------- 1 nscd nscd 0 Jul 8 15:06 queries.log.00000001 -rw------- 1 nscd nscd 0 Jul 11 21:21 queries.log.00000002
对以上重要文件的说明
/etc/proxysql.cnf文件是proxysql的配置文件，proxysql服务初次启动时会读取这个文件，并将文件内容加载到runtime中使配置生效。 /var/lib/proxysql/proxysql.db文件是proxysql管理数据库（默认6032端口下）中的数据写入磁盘中生成的一个数据库文件，proxysql每次启动时（除了初次启动）会读取这个文件，并将文件内容加载到runtime中使配置生效。 /var/lib/proxysql/proxysql.log记录了proxysql的相关日志
查看分析/etc/proxysql.cnf文件
# cat /etc/proxysql.cnf datadir="/var/lib/proxysql" admin_variables= { admin_credentials="admin:admin" #定义连接管理端口的用户名和密码 mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" #定义管理端口 # refresh_interval=2000 # debug=true } mysql_variables= { threads=4 #定义每个转发端口开启多少个线程 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033;/tmp/proxysql.sock" #定义转发端口 default_schema="information_schema" stacksize=1048576 server_version="5.7.17" #设置后端mysql实例的版本号，仅起到comment的作用 connect_timeout_server=3000 monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 } #管理端口6032是用来连接proxysql的管理数据库，修改proxysql服务的设置以及路由策略 #转发端口6033是用来连接后端的mysql实例，起到代理转发的作用
启动proxysql服务，proxysql安装成功
# /etc/init.d/proxysql start # ss -lntup |grep proxysql tcp 0 128 127.0.0.1:6032 *:* users:(("proxysql",21104,36)) tcp 0 1024 *:6033 *:* users:(("proxysql",21104,31)) tcp 0 1024 *:6033 *:* users:(("proxysql",21104,30)) tcp 0 1024 *:6033 *:* users:(("proxysql",21104,27)) tcp 0 1024 *:6033 *:* users:(("proxysql",21104,25)) #可以看到转发端口的6033开启了4个线程，线程数由全局变量"threads"控制，受cpu物理核心数的影响（每个端口下的线程数<=cpu物理核心数）
proxysql配置信息层次
proxysql的配置信息有4个层次：
| runtime | 决定着当前生效的配置信息 |
| memory | 保存在内存中的配置信息，进入配置端口6032后，生效的配置修改就保存在这里 |
| disk | 保存在磁盘上的配置信息，对应着文件/var/lib/proxysql/proxysql.db |
| configure file | 保存在配置文件的配置信息，对应着文件/etc/proxysql.cnf |
这4个层次的转换关系图（来自于互联网）：
要想使得配置生效，需要将配置加载到runtime；要想使得配置永久化存储，需要将配置保存在disk；要想快速修改配置，需要进入管理端口修改memory。这就决定了这几个层次之间需要命令进行配置转换
以mysql_users表为例，序号对应上图： [1]: LOAD MYSQL USERS TO RUNTIME / LOAD MYSQL USERS FROM MEMORY --将修改后的配置(在memory层)用到实际生产 [2]: SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME --将生产配置拉一份到memory中 [3]: LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK --将磁盘中持久化的配置拉一份到memory中来 [4]: SAVE MYSQL USERS TO DISK / SAVE MYSQL USERS FROM MEMORY --将memoery中的配置保存到磁盘中去 [5]: LOAD MYSQL USERS FROM CONFIG --将配置文件中的配置加载到memeory中
proxysql核心组件
mysql_servers：将可连接的后端mysql实例进行分组并设置属性
登录proxysql的6032端口，并插入一些记录：
[root@10-198-9-101 ~]# mysql -uroot -predhat -P6032 -h127.0.0.1 insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(100,‘10.198.9.101‘,3306,1,‘ReadWrite‘),(1000,‘10.198.9.101‘,3307,1,‘ReadOnly‘),(1001,‘10.198.9.101‘,3308,1,‘ReadOnly‘);
查看mysql_servers的记录与字段分析
mysql> select * from mysql_servers; +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+ | 100 | 10.198.9.101 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | ReadWrite | | 1000 | 10.198.9.101 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | ReadOnly | | 1001 | 10.198.9.101 | 3308 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | ReadOnly | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+ #hostgroup_id是组号，后续的属性设置和路由策略都是基于hostgroup来做。其中hostgroup_id 0是一个特殊的组号，如果没有匹配到路由规则，则默认选择hostgroup 0 #hostname和port决定了加入该组的mysql实例 #status: Online：当前后端实例状态正常 SHUNNED：临时被剔除，可能因后端 too many connections error，或者超过了可容忍延迟阀值 max_replication_lag OFFINE_SOFT： “软离线”状态，不再接受新的连接，但已建立的连接会等待活跃事务完成 OFFLINE_HARD： “硬离线”状态，不再接受新的连接，已建立的连接或被强制中断。当后端实例宕机或网络不可达，会出现。 #weight是权重，同一个hostgroup_id内的成员默认是基于权重做轮询 #max_connections是允许连接到该后端实例的最大连接数，建议不要设置超过后端mysql实例的max_connections #max_replication_lag： 允许的最大延迟，主库不受这个影响，默认0。如果 > 0， monitor 模块监控主从延迟大于阀值时，会临时把它变为 SHUNNED #max_latency_ms： mysql_ping 响应时间，大于这个阀值会把它从连接池剔除（即使是ONLINE），默认0 #comment：备注
mysql_users：配置后端数据库的账号和监控的账号
在mysql_users里插入一条记录：
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values(‘user0‘,‘password0‘,1,100,1);
查看mysql_users的记录与字段分析：
mysql> select * from mysql_users\G *************************** 1. row *************************** username: user0 #后端mysql实例的用户名 password: password0 #后端mysql实例的密码 active: 1 #active=1表示用户生效，0表示不生效 use_ssl: 0 default_hostgroup: 100 #用户默认登录到哪个hostgroup_id下的实例 default_schema: NULL #用户默认登录后端mysql实例时连接的数据库，这个地方为NULL的话，则由全局变量mysql-default_schema决定，默认是information_schema schema_locked: 0 transaction_persistent: 1 #如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不伦是否会匹配上其它路由规则，直到事务结束。虽然默认是0 fast_forward: 0 #忽略查询重写/缓存层，直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能，一般不用，路由规则 .* 就行了 backend: 1 frontend: 1 max_connections: 10000 #该用户允许的最大连接数 #这里配置的用户名和密码要有在后端mysql实例远程登录以及相关操作的权限
mysql_query_rules：定义查询路由规则
在mysql_query_rules里插入一条记录
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,‘^SELECT‘,1000,1);
查看mysql_query_rules的记录与字段分析：
mysql> select * from mysql_query_rules\G *************************** 1. row *************************** rule_id: 1 #表主键，自增。规则处理是以 rule_id 的顺序进行 active: 1 #只有 active=1 的规则才会参与匹配 username: NULL #如果非 NULL，只有连接用户是 username 的值才会匹配 schemaname: NULL #如果非 NULL，只有查询连接使用的db是 schemaname 的值才会匹配。注意如果是 NULL，不代表连接没有使用schema，而是不伦任何schema都进一步匹配 flagIN: 0 client_addr: NULL #匹配客户端来源IP proxy_addr: NULL #匹配本地proxysql的IP proxy_port: NULL #匹配本地proxysql的Port digest: NULL #精确的匹配一类查询 match_digest: NULL #正则匹配一类查询。query digest 是指对查询去掉具体值后进行“模糊化”后的查询 match_pattern: ^SELECT #正则匹配查询 negate_match_pattern: 0 #反向匹配，相当于对 match_digest/ flagOUT: NULL replace_pattern: NULL #查询重写，默认为空，不rewrite destination_hostgroup: 1000 #路由查询到这个 hostgroup。当然如果用户显式start transaction 且transaction_persistent=1，那么即使匹配到了，也依然按照事务里第一条sql的路由规则去走 cache_ttl: NULL #查询结果缓存的毫秒数。proxysql这个 Query Cache 与 MySQL 自带的query cache不是同一个。proxysql query cache也不会关心后端数据是否被修改，它所做的就是针对某些特定种类的查询结果进行缓存，比如一些历史数据的count结果。一般不设 reconnect: NULL timeout: NULL #这一类查询执行的最大时间（毫秒），超时则自动kill。这是对后端DB的保护机制，默认mysql-default_query_timeout给的是10h retries: NULL #语句在执行时失败时，重试次数。默认由 mysql-query_retries_on_failure变量指定，默认为1 delay: NULL #查询延迟执行，这是ProxySQL提供的限流机制，会让其它的查询优先执行 mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL #默认为NULL，如果指定了则这个查询直接被 block 掉，马上返回这个错误信息。这个功能也很实用，比如线上突然冒出一个 “坏查询”，应用端不方便马上发版解决，我们就可以在这配置一个规则，把查询屏蔽掉，想正常的mysql报错那样抛异常 log: NULL #是否记录查询日志 apply: 1 comment: NULL
#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
global_variables：存储全局变量
使用set命令修改变量：
set mysql-default_charset=‘utf8mb4‘;
set mysql-query_retries_on_failure=0;
set mysql-eventslog_filename=‘queries.log‘;
set mysql-ping_timeout_server=1500;
set mysql-monitor_connect_timeout=1000;
set mysql-default_max_latency_ms=2000;
set mysql-monitor_username=‘monitor‘;
set mysql-monitor_password=‘monitor‘;
set mysql-server_version=‘5.7.17‘;
注意：在使用set修改mysql-interfaces变量时有一个bug
修改技巧：不要用set，而是用update直接修改表。 mysql> update global_variables set variable_value=‘10.198.9.101:6033‘ where variable_name=‘mysql-interfaces‘;
读写分离测试
将上述配置写到disk做永久化保存：
save mysql users to disk;
save mysql servers to disk;
save mysql variables to disk;
SAVE MYSQL QUERY RULES TO DISK;
将上述配置写到runtime使之立即生效：
load mysql users to runtime;
load mysql servers to runtime;
load mysql variables to runtime;
LOAD MYSQL QUERY RULES TO RUN;
在客户端连接proxysql的转发端口，查看读写分离的转发规则是否生效：
mysql -uuser0 -ppassword0 -h10.198.9.101 -P6033 #查看全局变量的port，发现现在是在3306端口下 MySQL [(none)]> show variables like ‘port‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.00 sec) #用select语句查看port的时候，发现已经跳转到3307端口下 MySQL [(none)]> select @@port; +--------+ | @@port | +--------+ | 3307 | +--------+
上述proxysql转发过程解析：
首先proxysql在其监听的转发端口6033接收到了来自用户的请求"mysql -uuser0 -ppassword0 -h10.198.9.101 -P6033" 检查请求的用户名和密码和proxysql的mysql_user中的用户名密码是否匹配 在mysql_users表中对该用户设置了default_hostgroup为100，因此该用户被应用到hostgroup_id为100的组里 proxysql检查mysql_servers表中hostgroup_id为100的相关配置，发现对应的mysql实例是10.198.9.101的3306端口，因此该用户请求最终被送往3306端口 用户的query语句"show variables like ‘port‘;"不匹配mysql_query_rules中的任何规则，因此由默认的hostgroup对应的3306端口所处理，因此"port"的值为3306 用户的query语句"select @@port"被mysql_query_rules表中的rule_id为1的转发规则所匹配，目标hostgroup_id是1000，对应着10.198.9.101的3307端口，因此此时显示的"port"值为3307 关键点: 每次修改必须连接6032控制端 mysql -u admin -padmin -h127.0.0.1 -P6032 2.修改里面的数据库表 3.
save mysql variables to disk;
4.load mysql variables to runtime; 5. LOAD MYSQL QUERY RULES TO RUN;