ProxySQL--灵活强大的MySQL代理层

安装配置
1 wget https://github.com/sysown/proxysql/releases/download/v1.3.6/proxysql-1.3.6-1-centos67.x86_64.rpm

2 yum install perl-DBD-MySQL

3 rpm -ivh proxysql-1.3.6-1-centos67.x86_64.rpm

4 service proxysql start

5 proxysql --version

ProxySQL version 1.3.6-0-g434b376, codename Truls

本地配置文件

vim /etc/proxysql.cnf

 proxysql有个配置文件 /etc/proxysql.cnf只在第一次启动的时候有用后续所有的配置修改都是对SQLite数据库操作并且不会更新到proxysql.cnf文件中。ProxySQL绝大部分配置都可以在线修改配置存储在 /var/lib/proxysql/proxysql.db 后面会介绍它的在线配置的设计方式。

6 登陆管理界面(配置信息从启动进程的配置文件查看)

 

mysql -u admin -padmin -h127.0.0.1 -P6032  --prompt='proxysql>'

mysql -u admin -padmin -S/tmp/proxysql_admin.sock--prompt='proxysql>'

看到管理接口的端口是6032账号密码是admin(可以动态修改)只能通过本地连接客户端接口的端口是6033账号密码通过管理接口去设置。

库、表说明

通过管理接口登录需要先装好mysql客户端并且设置prompt

[client]

prompt      = \\u@\\h:\\d\\r:\\m:\\s>

通过管理接口进入ProxySQL

 

 

配置读写分离和路由规则

环境三个后台服务器,一主两从的架构)

Master 10.9.160.2483306

Slave1  10.9.117.107:3306  设置read_only

Slave2   10.9.150.86:3306    设置read_only

 
在后端MySQL服务器db的主节点上创建proxy的监控账户和业务账户

grant  USAGE,replication client on *.* to 'monitor'@'%'identified by 'monitor'

#grant all privileges on *.* to 'monitor'@'%'identified by 'monitor'

 grant allprivileges on *.* to 'msandbox'@'%' identified by 'msandbox';

 

proxy管理命令行添加后端服务器列表

proxysql>INSERT INTOmysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.160.248',3306);

proxysql>INSERT INTOmysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.150.86',3306);

proxysql>INSERT INTOmysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.117.107',3306);

注意到这时的hostgroup都为1这没什么问题

即使生效

LOAD MYSQL SERVERS TO RUNTIME;

 
配置和注册监控账户

proxysql>update global_variables setvariable_value='monitor' where variable_name='mysql-monitor_password';

proxysql>update global_variables setvariable_value='monitor' where variable_name='mysql-monitor_username';

这里填的账户和密码均为monitor的信息就是步骤1中在后端服务器创建的监控账户

即使生效并持久化到磁盘

LOAD MYSQL VARIABLES TO RUNTIME;

SAVE MYSQL VARIABLES TO DISK;

查看监控是否生效

proxysql>SELECT * FROMmonitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

+--------------+------+------------------+-------------------------+---------------+

| hostname     | port |time_start_us    | connect_success_time_us | connect_error |

+--------------+------+------------------+-------------------------+---------------+

| 10.9.160.248 | 3306 | 1497001266520778 |688                    | NULL          |

| 10.9.150.86  | 3306 | 1497001266510692 |541                    | NULL          |

| 10.9.117.107 | 3306 | 1497001266500632 | 718                    |NULL          |

| 10.9.160.248 | 3306 | 1497001206520719 |652                    | NULL          |

| 10.9.150.86  | 3306 | 1497001206510580 |618                    | NULL          |

| 10.9.117.107 | 3306 | 1497001206500519 |744                    |NULL          |

| 10.9.160.248 | 3306 | 1497001146520616 |699                    | NULL          |

| 10.9.150.86  | 3306 | 1497001146510561 | 557                    | NULL          |

| 10.9.117.107 | 3306 | 1497001146500487 |759                    |NULL          |

| 10.9.160.248 | 3306 | 1497001086520458 |595                    | NULL          |

+--------------+------+------------------+-------------------------+---------------+

10 rows in set (0.00 sec)

 

 

配置主从信息

注意点:slave节点需要设置read_only=1。如果后端是PXC/MGR/MariaDB Galera则无需手动设置因为会自动设置。

提供2MySQL节点的配置文件。

# 以下是Master的配置文件

[mysqld]

datadir=/data

socket=/data/mysql.sock

server-id=110           

log-bin=/data/master-bin

sync-binlog=1           

log-error=/data/error.log

pid-file=/data/mysqld.pid

 

# 以下是slave的配置文件

[mysqld]

datadir=/data

socket=/data/mysql.sock 

server-id=120           

relay_log=/data/relay-log

log-error=/data/error.log

pid-file=/data/mysqld.pid

read_only=1

 

表示该复制组,1为写入组的编号,2为读取组的编号

INSERT INTO mysql_replication_hostgroups VALUES (1,2,’one-master-1-slave’);

即时生效

LOAD MYSQL SERVERS TO RUNTIME;   

当生效后proxysql会去检测后端的数据节点

假设该节点read_only1则会将该server移入group 2

假设该节点read_only0则会将该server移入group 1

这时再查mysql_servers就会发现节点的hostgroup_id根据read_only的情况自动变化了

proxysql>select hostgroup_id,hostname frommysql_servers;

+--------------+--------------+

| hostgroup_id | hostname     |

+--------------+--------------+

|1            |10.9.160.248 |

|2            |10.9.117.107 |

|2            |10.9.150.86  |

+--------------+--------------+

3 rows in set (0.00 sec)

确认无误后  持久化到磁盘

SAVE MYSQL SERVERS TO DISK;

 

 

配置和注册业务账户

插入步骤1中生成的业务账户

INSERT INTOmysql_users(username,password,default_hostgroup) VALUES ('msandbox','msandbox',1);

proxysql>select * from mysql_users\G

*************************** 1. row***************************

             username: msandbox

             password: msandbox

               active: 1

              use_ssl: 0

     default_hostgroup: 1

       default_schema: NULL

        schema_locked: 0

transaction_persistent: 0

         fast_forward: 0

              backend: 1

             frontend: 1

       max_connections:10000

1 row in set (0.00 sec)

proxysql>update mysql_users settransaction_persistent=1 where username='msandbox';

Query OK, 1 row affected (0.00 sec)

即时生效持久化

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK;

 

说明几个比较重要的列名含义

Active表示该用户是否生效

default_hostgroup 表示如果没有配置其他路由规则那么默认走的就是该值指定的hostgroup

max_connections该用户能创建的最大连接数

transaction_persistent 如果为1则一个完整的SQL只可能路由到一个节点这点非常重要主要解决这种情况一个事务有混合的读操作和写操作组成事务未提交前如果事务中的读操作和写操作路由到不同节点那么读取到的结果必然是脏数据。所以一般情况下,该值应该设置为1,尤其是业务中使用到事务机制的情况(默认为0

(注意:该值设为1后,sysbench不好测试读写分离的情况,因为sysbench中的oltp脚本中的SQL都是包含在一个个事务中的,所以测试阶段可以将其值改为0

frontend backend列目前的版本必须为1,因为目前的proxy和底层的节点共用一套账户认证体系,后续可能会单独分离出来,所以增加了这两个字段。)

 

这时验证下账户登陆,确定登陆上去的就是预想中的从库

[root@10-9-192-94 ~]# mysql -u msandbox -pmsandbox -h127.0.0.1 -P6033 -e "show slave hosts"

+-----------+------+------+-----------+--------------------------------------+

| Server_id | Host | Port | Master_id |Slave_UUID                          |

+-----------+------+------+-----------+--------------------------------------+

| 168392043 |      | 3306 |168403192 | baaf5e8b-4cbe-11e7-8ca2-e8611f12fd90 |

| 168400470 |      | 3306 |168403192 | aed780a0-4cbe-11e7-8ca2-6c92bf15bed0 |

 

配置读写分离策略

1 除了select ..for update以外的select都路由到从库

2 其他所有操作都路由到主库

proxysql>show create table mysql_query_rules\G

***************************1. row***************************

       table:mysql_query_rules

Create Table:CREATE TABLE mysql_query_rules (

    rule_id INTEGER PRIMARY KEYAUTOINCREMENTNOT NULL,

    active INT CHECK (active IN (0,1))NOT NULLDEFAULT 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 INTCHECK(negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,

    flagOUT INT,

    replace_pattern VARCHAR,

    destination_hostgroup INT DEFAULTNULL,

    cache_ttl INT CHECK(cache_ttl >0),

    reconnect INT CHECK (reconnect IN(0,1))DEFAULT NULL,

    timeout INT UNSIGNED,

    retries INT CHECK (retries>=0ANDretries <=1000),

    delay INT UNSIGNED,

    mirror_flagOUT INT UNSIGNED,

    mirror_hostgroup INT UNSIGNED,

    error_msg VARCHAR,

    log INT CHECK (log IN (0,1)),

    apply INT CHECK(apply IN (0,1)) NOTNULLDEFAULT 0,

    comment VARCHAR)

1 row in set(0.00 sec)

Active:是否启用这个路由规则

Username:如果不为空,该规则匹配该用户

schemaname 如果不为空该规则只匹配该库名称

destination_hostgroup该路由规则发往哪个组

apply:为1表示该正则匹配后,将不再接受其他匹配,直接转发

match_digest描述规则的正则表达式其中1.4版本以后正则表达式支持PCRERE2两种默认使用pcreperl语言支持的正则表达式

cache_ttl:用户查询缓存的时间阈值,单位为毫秒

更多字段说明,参考官方wiki文档

https://github.com/sysown/proxysql/wiki/MySQL-Query-Rules

通过查看该表的表结构,不难看出所有的路由规则是用户完全自定义的,也就是说不仅仅可以实现读写分离,任何SQL类型都可以自定义发送到指定的数据节点执行,比如在实现大部分读写分离的同时,一些对实时性要求很高的SQL,可以定义成发送到master节点运行;
配置insert,update,delete

proxysql>Insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply) values(9,1,'msandbox','^SELECT .* FORUPDATE$',1,1);

proxysql>insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply)values(8,1,'msandbox','^INSERT',1,1);

proxysql>insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply)values(7,1,'msandbox','^UPDATE',1,1);

proxysql>insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply)values(6,1,'msandbox','^DELETE',1,1);

配置select

proxysql>Insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply) values(10,1,'msandbox','^SELECT',2,1);

Query OK, 1 row affected (0.00 sec)

刚才通过mysql_users表的配置和解释可知路由规则以外的SQL都发往mysql_users表中的default_hostgroup字段即数值1该组为master节点

即时生效并持久化

load mysql query rules to runtime;

save mysql query rules to disk;

 

读写分离测试

清空历史查询记录

proxysql>select* fromstats.stats_mysql_query_digest_reset limit 1;

通过查询stats_mysql_query_digest_reset的效果是这样的返回stats_mysql_query_digest表的查询结果并且执行truncate table stats_mysql_query_digest清空stats_mysql_query_digest

查看SQL历史记录

proxysql>select* from stats.stats_mysql_query_digest;

Empty set (0.00sec)

修改transaction_persistent0使得sysbench适用该场景

update mysql_users set transaction_persistent=0 whereusername='msandbox';

LOAD MYSQL USERS TO RUNTIME;

 

sysbench压测proxy

sysbench--report-interval=1 --num-threads=4 --max-time=2000--test=sysbench/tests/db/oltp.lua--mysql-user='msandbox'--mysql-password='msandbox' --oltp-table-size=10000--mysql-host=127.0.0.1--mysql-port=6033 --mysql-db=proxytest  --max-requests=100000000 prepare

sysbench--report-interval=1 --num-threads=4 --max-time=2000--test=sysbench/tests/db/oltp.lua--mysql-user='msandbox'--mysql-password='msandbox' --oltp-table-size=10000--mysql-host=127.0.0.1--mysql-port=6033 --mysql-db=proxytest  --max-requests=100000000 run

压测过程中观察读写分离情况
proxysql>select * from stats.stats_mysql_query_digest;

 

可以看到查询都是发往group 2,写操作都是发往group 1,即实现了读写分离

另外可以分别登陆三个数据节点show  processlist确定proxysql真实实现了读写分离

关于select ..for update和事务的路由测试可以手工写几条特定的SQL测试这里忽略

 

通过mysql客户端连接:
[root@node6683 ~]# mysql -umsandbox -pmsandbox -h192.168.66.83 -P6033

 

测试proxysql的查询缓存

Proxysql的查询缓存和mysql的查询缓存有点类似,但不是一回事;proxysql的查询缓存指的是:如果在指定时间大小范围内发送的SQL一摸一样,那么直接返回结果集,而返回的结果集可能并不是准确的查询结果,所以需要设置合适的时间范围,既能提升性能,又得满足业务需求,即查询结果足够的。这个特性我想可以用于这个方便:针对一些查询频率很高但结果并不需要太精确的业务,可以单独给这些SQL配置查询缓存

proxysql>update mysql_query_rules setcache_ttl=2000 where rule_id=11\G

Query OK, 1 row affected (0.00 sec)

proxysql>LOAD MYSQL QUERY RULES TO RUNTIME;  #使配置生效

Query OK, 0 rows affected (0.00sec)         

sysbench --report-interval=1--num-threads=4 --max-time=2000--test=sysbench/tests/db/oltp.lua--mysql-user='msandbox'--mysql-password='msandbox' --oltp-table-size=10000--mysql-host=127.0.0.1--mysql-port=6033--mysql-db=proxytest --max-requests=100000000 run

proxysql>select hostgroup,digest_text,sum_time fromstats.stats_mysql_query_digest;

可以看到hostgroup部分为-1,表示这些查询是使用了proxy查询缓存的,耗时为0,这里的0不可能是真的没有耗时,只是不好统计,显示上直接显示成0

测试查询重写

范例   将带distinctSQL去掉排序      

9.1 写入下面这条改写规则,注意规则中大小写是敏感的

proxysql>select * from mysql_query_rules\G

*************************** 1.row***************************

             rule_id: 31

              active: 1 

            username:msandbox

          schemaname:NULL

              flagIN: 0

         client_addr:NULL

          proxy_addr:NULL

          proxy_port:NULL

              digest: NULL

        match_digest:NULL

       match_pattern: DISTINCT (.*) ORDER BY  c

 negate_match_pattern: 0

             flagOUT: NULL

     replace_pattern:DISTINCT\1

destination_hostgroup: NULL

           cache_ttl:NULL

           reconnect:NULL

             timeout: NULL

             retries: NULL

               delay: NULL

      mirror_flagOUT:NULL

    mirror_hostgroup: NULL

           error_msg:NULL

                 log: NULL

               apply: 1

             comment: NULL

1 row in set (0.00 sec)

9.2 执行下面这个SQL

 

可以看到解析计划中没有using filesortSQL已经被改写了

9.3 执行一把SQL内容查看规则命中情况和执行列表确认

proxysql>select * from stats_mysql_query_rules ;

+---------+------+

| rule_id | hits |

+---------+------+

| 31     |1    |

+---------+------+

1 row in set (0.00 sec)

proxysql>select hostgroup,digest_textfromstats.stats_mysql_query_digest;

+-----------+--------------------------------------------------------------------+

| hostgroup |digest_text                                                       |

+-----------+--------------------------------------------------------------------+

| 1        |SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? and ? order by c |

+-----------+--------------------------------------------------------------------+

1 row in set (0.00 sec)

YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS