proxysql安装配置和读写分离入手测试

前言

　　小编折腾代码和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;

易超seo公司