ITPub博客

首页 > 数据库 > MySQL > MySQL中间件--ProxySQL

MySQL中间件--ProxySQL

原创 MySQL 作者:lhrbest 时间:2020-02-23 19:16:53 0 删除 编辑

MySQL中间件--ProxySQL


中文翻译: https://github.com/malongshuai/proxysql/wiki


ProxySQL MySQL 的一款中间件的产品,是灵活的 MySQL 代理层,可以实现读写分离,支持 QUERY 路由器的功能,支持动态指定 SQL 进行缓存,支持动态加载配置,故障切换和一些 SQL 的过滤功能。

其他产品:Dbproxy,MyCAT,OneProxy 等。

功能强大的MySQL 中间件;

官方站点:http://www.proxysql.com

官方的github https://github.com/sysown/proxysql/releases/tag/v1.4.9

 

Percona ProxySQL 的文档手册: https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html

ProxySQL 是使用 C++ 语言开发的,轻量级的产品,但性能非常好,功能很多,几乎能够满足中间件所需的绝大多数的功能,主要包括:

1. / 写分离,且支持多种方式;

2. 可以定义基于用户、基于 Schema 、基于 SQL 语句的规则对 SQL 语句进行路由转发;规则很灵活;

3. 基于 schema 和语句级别的规则,可以实现简单的数据库分片功能; (Sharding)

4. 可以通过查询缓存来缓存查询结果;

5. 监控后端服务器节点:后端服务器的心跳信息,后端服务器的 read-only/read-write 信息, Slave Master 的数据同步的延迟性 (replication lag)

获取安装程序包:

两个发行版本:

官方的ProxySQL

Percona ProxySQL

 

一.1   安装

https://www.percona.com/downloads/

https://www.percona.com/downloads/proxysql2/

https://www.percona.com/downloads/proxysql/

https://github.com/sysown/proxysql/releases

 

安装proxysql

[root@rhel6lhr soft]$ rpm -ivh proxysql2-2.0.7-1.2.el6.x86_64.rpm  

warning: proxysql2-2.0.7-1.2.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY

Preparing...                ########################################### [100%]

   1:proxysql2              ########################################### [100%]

[root@rhel6lhr soft]$

推荐rpm 安装。

[root@rhel6lhr proxysql]$ proxysql --help

High Performance Advanced Proxy for MySQL

 

USAGE: proxysql [OPTIONS]

 

OPTIONS:

 

-c, --config ARG             Configuration file

-D, --datadir ARG            Datadir

-e, --exit-on-error          Do not restart ProxySQL if crashes

-f, --foreground             Run in foreground

-h, -help, --help, --usage   Display usage instructions.

-M, --no-monitor             Do not start Monitor Module

-n, --no-start               Starts only the admin service

-r, --reuseport              Use SO_REUSEPORT

-S, --admin-socket ARG       Administration Unix Socket

-V, --version                Print version

--idle-threads               Create auxiliary threads to handle idle connections

--initial                    Rename/empty database file

--reload                     Merge config file into database file

--sqlite3-server             Enable SQLite3 Server

 

 

ProxySQL rev. 2.0.7-percona-1.2 -- Fri Oct 18 05:47:24 2019

Copyright (C) 2013-2019 ProxySQL LLC

This program is free and without warranty

[root@rhel6lhr proxysql]$

[root@rhel6lhr proxysql]$ proxysql -V    

ProxySQL version 2.0.7-percona-1.2, codename Truls

 

一.2   启动

配置文件路径为:/etc/proxysql.cnf

启动proxysql

service proxysql start

netstat -anlp |grep proxysql

6032 是管理端口, 6033 是对外服务的端口号 :

[root@rhel6lhr ~]$  netstat -anlp |grep proxysql

tcp        0      0 0.0.0.0:6032                0.0.0.0:*                   LISTEN      38917/proxysql

tcp        0      0 0.0.0.0:6033                0.0.0.0:*                   LISTEN      38917/proxysql

用户名和密码默认都是admin

查看proxysql 安装库情况:

mysql -uadmin -padmin -h127.0.0.1 -P6032

mysql -uadmin -padmin -h192.168.59.130 -P6033

一.3   Proxysql 库说明

[root@rhel6lhr ~]$ 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 3

Server version: 5.5.30 (ProxySQL Admin Module)

 

Copyright (c) 2000, 2014, 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.

 

(admin@127.0.0.1) [(none)]> show databases;

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

| seq | name          | file                                |

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

| 0   | main          |                                     |

| 2   | disk          | /var/lib/proxysql/proxysql.db       |

| 3   | stats         |                                     |

| 4   | monitor       |                                     |

| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |

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

5 rows in set (0.00 sec)

Main: 内存配置数据库,即 memory ,表里存放后端 db 实例,用户验证,路由规则等信息。 Main 库中有如下信息:

mysql_servers-- 后端可以连接 mysql 服务器的列表

mysql_users-- 配置后端数据库的账号和监控的账号

mysql_query_rules-- 指定 query 路由到后端不同服务器的规则列表

 

disk 库:持续化磁盘的配置。

Stats 库:统计信息的汇总。

Monitor 库:一些监控的收集信息,包括数据库的健康状态。


一、ProxySQL安装

Proxy官方地址:https://proxysql.com/

proxysql-2.0.8-1-centos7.x86_64.rpm下载地址:https://github.com/sysown/proxysql/releases/tag/v2.0.8

安装方法

1、上传安装包到服务器(CentOS7)

2、# yum install  proxysql-2.0.8-1-centos7.x86_64.rpm

 

 

二、ProxySQL架构示意图



通过官方站点或官方的github项目,或Percona官方及Percona资源的镜像站点;
安装ProxySQL:[root@proxysql ~]# yum install proxysql启动和关闭服务:[root@proxysql ~]# service proxysql start/stop验证服务启动的结果:
[root@proxysql ~]# ss -tnlp
    LISTEN     0      128               *:6032                          *:*                   users:(("proxysql",pid=35296,fd=23))
    LISTEN     0      128               *:6033                          *:*                   users:(("proxysql",pid=35296,fd=22))
    LISTEN     0      128               *:6033                          *:*                   users:(("proxysql",pid=35296,fd=21))
    LISTEN     0      128               *:6033                          *:*                   users:(("proxysql",pid=35296,fd=20))
    LISTEN     0      128               *:6033                          *:*                   users:(("proxysql",pid=35296,fd=19))
看到上述结果,说明proxysql服务启动成功;
登录方式:
    可以使用mysql客户端工具连接到proxysql的管理接口,该管理接口有默认的管理员账户和密码,都是admin;
    [root@localhost ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.5.30 (ProxySQL Admin Module)
    Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    MySQL [(none)]>

实验中所涉及到的库表的含义:
默认情况下,ProxySQL提供了几个库,每个库都有各自的意义;查看所有库的方法:
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+

            主要介绍main库和monitor库相关的表的功能;
                MySQL [main]> show tables from main;
                +--------------------------------------------+                | tables                                     |
                +--------------------------------------------+                | global_variables                           |
                | mysql_collations                           |
                | mysql_group_replication_hostgroups         |
                | mysql_query_rules                          |
                | mysql_query_rules_fast_routing             |
                | mysql_replication_hostgroups               |
                | mysql_servers                              |
                | mysql_users                                |
                | proxysql_servers                           |
                | runtime_checksums_values                   |
                | runtime_global_variables                   |
                | runtime_mysql_group_replication_hostgroups |
                | runtime_mysql_query_rules                  |
                | runtime_mysql_query_rules_fast_routing     |
                | runtime_mysql_replication_hostgroups       |
                | runtime_mysql_servers                      |
                | runtime_mysql_users                        |
                | runtime_proxysql_servers                   |
                | runtime_scheduler                          |
                | scheduler                                  |
                +--------------------------------------------+
                MySQL [main]> show tables from monitor;
                +------------------------------------+                | tables                             |
                +------------------------------------+                | mysql_server_connect_log           |
                | mysql_server_group_replication_log |
                | mysql_server_ping_log              |
                | mysql_server_read_only_log         |
                | mysql_server_replication_lag_log   |
                +------------------------------------+
            注意:                1.所有的以"runtime_"开头的表都是运行时的配置,是不能修改的。要完成ProxySQL的配置,仅能修改那些不是以"runtime_"开头的表;                2.在执行表的修改之后,必须手动的将修改结果同步至运行时环境和持久化保存到磁盘;
                    LOAD ... TO RUNTIME;
                        将指定的表中的修改结果同步至运行时环境;
                    SAVE ... TO DISK;
                        将指定的表中的修改结果同步至磁盘以实现持久化存储;
                        注意:“...”表示表名,去掉真实表名中的"_"符号用空白字符代替;
                        示例:修改了msyql_servers表,
                            load mysql servers to runtime;
                            save mysql servers to disk;
        mysql_servers表中有很多个字段,每个字段都有十分重要的意义;
            MySQL [main]> show create table mysql_servers;
            字段                              数据类型        是否为空        字段默认值
            hostgroup_id                INT                 NOT NULL        DEFAULT 0,
            hostname                        VARCHAR         NOT NULL        
            port                                INT                 NOT NULL        DEFAULT 3306,
            status                          VARCHAR         NOT NULL        DEFAULT 'ONLINE',
            weight                          INT                 NOT NULL        DEFAULT 1,
            compression                 INT                 NOT NULL        DEFAULT 0,
            max_connections         INT                 NOT NULL        DEFAULT 1000,
            max_replication_lag INT                 NOT NULL        DEFAULT 0,
            use_ssl                         INT                 NOT NULL        DEFAULT 0,
            max_latency_ms          INT                 NOT NULL        DEFAULT 0,
            comment                         VARCHAR         NOT NULL        DEFAULT '',
        各字段的含义:
            hostgroup_id:
                后端MySQL实例所在的服务器所在的主机组的编号;注意:一台主机可能有多种角色,可能存在于多个主机组中;
            hostname:
                后端的MySQL实例所在的服务器监听的IP地址;该字段没有默认值,意味着在添加一个新的后端服务器节点时,必须指定该字段的值;
            port:
                后端的MySQL实例所在的服务器监听的端口号;默认值为3306,通常使用默认值即可;
            status:
                后端MySQL服务器的运行时状态,有四种状态值:'ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD'
                    ONLINE:此状态表示后端MySQL服务器节点完全正常;
                    SHUNNED:此状态表示后端MySQL服务器节点暂时被ProxySQL自动忽略,可能的原因是在一个较短时间内发生了大量的连接错误,也可能是Slave端与Master端之间的数据延迟过大;
                    OFFLINE_SOFT:此状态表示ProxySQL不会再向此服务器转发任何请求,但此服务器上尚未完成的事务会继续执行,直到所有事务执行完毕之后,会进入"非活跃状态";graceful stop;                    'OFFLINE_HARD:此状态表示ProxySQL不会再向此服务器转发任何请求,同时此服务器上正在执行的事务立即中断结束,ProxySQL也会临时将其移除出主机组,常用于维护操作;
            weight:对应后端服务器在主机组中的权重,权重值越高,ProxySQL就会向其转发越多的请求;默认值为1;
            compression:标记ProxySQL和后端MySQL服务器之间建立的连接中,是否会先压缩数据,再传输;默认值为0;
                如果该字段值为0,则不压缩传输;
                如果该字段值大于0,则压缩后传输;
            max_connections:表示ProxySQL与后端MySQL服务器之间允许建立的最大连接数;一旦达到最大值,即使后端服务器的权重值再大,也不会再建立新的连接;默认值为1000,表示每个后端MySQL服务器最多同时接受1000个来自于ProxySQL的连接;
                通过合理的定义该字段的值,可以避免后端MySQL服务器超负荷运转;
            max_replication_lag:用于表示后端的SLave端在复制时延后于Master多长时间,如果超出该字段定义的上限值,proxySQL会暂时忽略此服务器,直到Slave的复制内容赶上Master为止(数据一致);
            use_ssl:表示是否允许ProxySQL和后端MySQL服务器之间基于SSL协议建立连接;
            max_latency_ms:监控模块定期向后端MySQL服务器发起ping(SELECT)检查操作的延迟时间;
            comment:注释信息,说明信息,可以随意定义的内容,主要起辅助提示作业;

向ProxySQL插入监控节点:
MySQL [main]> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'172.16.75.3',3306),(10,'172.16.75.4',3306),(10,'172.16.75.5',3306);

监控后端的MySQL节点:
添加节点完成之后,更重要的是监控各后端节点;后端必须是主从复制的环境;而ProxySQL会读取后端MySQL服务器的read_only服务器参数的值,以区分该服务器节点是属于"读组"还是"写组";

在后端的Master服务器上创建一个用于监控功能的用户,该用户仅需要有"USAGE"权限即可;如果想要监控在复制结构中Slave端是否与Master端存在验证的延迟或滞后的状态,即replication lag状态,还需要让该监控用户具备"REPLICARION CLIENT"权限;
**创建监控用户的方法:**
    在Master端执行下列SQL语句:
        MariaDB [(none)]> grant replication client,replication slave on *.* to 'mmonitor'@'172.16.74.%' identified by '123';
    在ProxySQL端执行下列SQL语句:
        MySQL [main]> set mysql-monitor_username='mmonitor';
        MySQL [main]> set mysql-monitor_password='qhdlink';
        实际上是在修改global_variables表中对应的变量的值,所以可以直接使用UPDATE语句来修改对应的字段的值:
            UPDATE global_variables SET variable_value='mmonitor' WHERE variable_name='mysql-monitor_username';
            UPDATE global_variables SET variable_value='qhdlink' WHERE variable_name='mysql-monitor_password';
让此前添加的后端MySQL服务器节点及用于监控各节点的用户生效:
    MySQL [main]> load mysql servers to runtime;
    MySQL [main]> save mysql servers to disk;
    MySQL [main]> load mysql variables to runtime;
    MySQL [main]> save mysql variables to disk;

验证监控的结果:
ProxySQL监控模块的指标都保存在monitor库的各log表中;
如果想查看连接是否正常的监控信息,在mysql_server_connect_log表中
MySQL [(none)]> select * from mysql_server_connect_log limit 3; 
+-------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-------------+------+------------------+-------------------------+---------------+
| 172.16.74.2 | 3306 | 1533700332922373 | 1945 | NULL |
| 172.16.75.1 | 3306 | 1533700332933127 | 1689 | NULL |
| 172.16.75.2 | 3306 | 1533700332944270 | 50012 | NULL |
+-------------+------+------------------+-------------------------+---------------+
3 rows in set (0.00 sec)

    可能会看到一些connect_error信息,这是因为此前并没有设置正确的用于监控的用户账户信息;在成功的配置监控用户之后,connect_error自动的值应该是"NULL",表示连接正常;
    如果想查看后端MySQL服务器的心跳信息的监控,在mysql_server_ping_log表中;
    MySQL [(none)]> select * from mysql_server_ping_log limit 3;
    +-------------+------+------------------+----------------------+------------+    | hostname    | port | time_start_us    | ping_success_time_us | ping_error |
    +-------------+------+------------------+----------------------+------------+    | 172.16.74.2 | 3306 | 1533700268376052 | 649                  | NULL       |
    | 172.16.75.1 | 3306 | 1533700268377872 | 602                  | NULL       |
    | 172.16.75.2 | 3306 | 1533700268379770 | 824                  | NULL       |
    +-------------+------+------------------+----------------------+------------+    3 rows in set (0.00 sec)
    由于尚未对后端MySQL服务器进行明确的节点分组,所以mysql_server_read_only_log和mysql_server_replication_lag_log都是空的;
    要想定义后端MySQL服务器的分组,在mysql_replication_hostgroups表中添加对应组的ID即可;
         writer_hostgroup
         reader_hostgroup
    默认mysql_replication_hostgroups表是空表,想要定义读组和写组的ID,需要向该表中插入数据:
        MySQL [main]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,2);
    **  注意:应该保证插入的组的ID应该与此前插入的主机所定义的组ID相同;**
    此时并不能立即生效,查看mysql_servers表中各主机所属组的时候,看到的是旧的数据;
            MySQL [main]> select hostgroup_id,hostname,port from mysql_servers;
            +--------------+-------------+------+            | hostgroup_id | hostname    | port |
            +--------------+-------------+------+            | 10           | 172.16.75.1 | 3306 |
            | 10           | 172.16.75.2 | 3306 |
            | 10           | 172.16.74.2 | 3306 |
            +--------------+-------------+------+
    将此前修改的内容加载到runtime之后,才能使其生效;
        MySQL [main]> load mysql servers to runtime;
        MySQL [main]> save mysql servers to disk;
    再查看主机分组的信息:
        MySQL [main]> select hostgroup_id,hostname,port from mysql_servers;
        +--------------+-------------+------+        | hostgroup_id | hostname    | port |
        +--------------+-------------+------+        | 10           | 172.16.75.1 | 3306 |
        | 2             | 172.16.75.2 | 3306 |
        | 2             | 172.16.74.2 | 3306 |
        +--------------+-------------+------+
    Monitor模块会根据后端MySQL服务器上的read_only服务器变量的值判断并将对应的节点自动移动到读组或写组中;
    在monitor库的mysql_server_read_only_log表中就会有后端MySQL服务器的read_only属性;
    MySQL [main]> select * from mysql_server_read_only_log limit 3;
    +-------------+------+------------------+-----------------+-----------+-------+    | hostname    | port | time_start_us    | success_time_us | read_only | error |
    +-------------+------+------------------+-----------------+-----------+-------+
    | 172.16.75.1 | 3306 | 1533627317448205 | 2403            | 0         | NULL  |
    | 172.16.75.2 | 3306 | 1533627317449233 | 2670            | 1         | NULL  |
    | 172.16.74.2 | 3306 | 1533627317450825 | 2213            | 1         | NULL  |
    +-------------+------+------------------+-----------------+-----------+-------+

管理mysql_users
上述所有配置都是与后端MySQL服务器节点相关的,除此之外,ProxySQL还可以配置与SQL语句相关的内容,包括:发送SQL请求的用户,SQL语句路由规则,SQL的查询缓存,SQL语句的重写等;

发送SQL请求的用户的相关配置:
    root
    sqlsender
在后端的Master服务器上创建对应用户账户:
    MariaDB [(none)]> grant all on *.* to 'root'@'172.16.74.%' identified by '123';
    MariaDB [(none)]> grant all on *.* to 'sqlsender'@'172.16.74.%' identified by '123';
在ProxySQL上,需要向mysql_users表中添加用户账户;
    MySQL [main]> insert into mysql_users (username,password,default_hostgroup) values ('root','123',10),('sqlsender','123',2); 
    添加用户之后,需要将修改后的数据加载至runtime及持久化存储至磁盘:
        MySQL [main]> load mysql users to runtime;
        MySQL [main]> save mysql users to disk;
    注意:在向mysql_users表中添加用户时,至少要指定username,password和default_hostgroup三个字段;
        username:前端连接到ProxySQL以及ProxySQL将SQL语句路由至MySQL时所使用的用户名;
        password:用户对应的密码,可以是明文保存,也可以使用hash加密的密码存放,如果想要使用hash密码,需要借助于PASSWORD('password_string')函数;
        default_hostgroup:用于为用户名设置默认的路由目标;
        其他的字段的含义:
        active:对应的用户是否为有效用户;
        transaction_persistent:是否启用事务持久化,如果值为1,意味着当某连接使用该用户开启了一个事务之后,此事务在提交或回滚之前,所有的语句都被路由到同一组中,以避免同一事务的SQL语句分散到不同组,从而引发数据混乱或事务无法正常提交执行;默认值为1;此字段在较早的proxySQL版本中有默认值为0的情况,如果出现此类情况,在添加用户时必须手动设置其值为1,而不使用默认值;
    查看mysql_users表的内容:

MySQL [(none)]> select * from mysql_users;
+-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123 | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
| sqlsender | 123 | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)

使用主机172.16.74段的任意主机(这里使用172.16.74.1),测试基于用户账户的读写分离机制,读请求访问由结果可知默认的访问后台读服务器是随机访问而不是轮询方式访问:
[root@mysqlrouter ~]# mysql -uroot -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+

[root@mysqlrouter ~]# mysql -usqlsender -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 201 |
+-------------+

[root@mysqlrouter ~]# mysql -usqlsender -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 301 |
+-------------+

基于SQL语句实现读写分离
需要添加路由规则:
与查询规则有关的表有两个:
mysql_query_rules
mysql_query_rules_fast_routing

    mysql_query_rules_fast_routing是mysql_query_rules的扩展表,在proxysql 1.4.7以后才出现,支持快速路由表
    mysql_query_rules常用的配置字段:
        rule_id:规则编号,自动增长的整数,可以不指定;
        active:规则是否有效,默认值为0,表示无效,需要在定义规则时,将其设置为1;
        match_digest:定义规则的具体匹配内容;由正则表达式元字符组成,用来匹配SQL语句;
        destination_hostgroup:对于符合规则的请求,设置目标主机组,从而实现路由转发;
        apply:是否有效提交;默认值为0,表示无效,需要在定义规则时,将其值设置为1;
添加查询路由规则:
    MySQL [none]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',2,1);
查看新创建的路由规则:
MySQL [(none)]> select * from mysql_query_rules\G;

1. row 
rule_id: 1
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: ^SELECT.*FOR UPDATE$
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 10
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
log: NULL
apply: 1
comment: NULL
2. row 
rule_id: 2
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: ^SELECT
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 2
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
log: NULL
apply: 1
comment: NULL
2 rows in set (0.00 sec)

ERROR: No query specified

将规则的修改加载值runtime并持久化存储至磁盘:
MySQL [main]> load mysql query rules to runtime;
MySQL [main]> save mysql query rules to disk;

测试事务持久化:
在Master主服务器的MySQL交互模式中:
先创建数据库mytest:
MariaDB [mysql]> use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mytest]> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)

[root@mysqlrouter ~]# mysql -uroot -h172.16.74.1 -P6033 -p123 -e 'set @@autocommit=0;\
start transaction;\
use mytest;\
insert into t1 values (1000),(2000);\
select @@server_id;\
commit;\
'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+

测试proxysql基于SQL请求实现读写分离:
-P指定6032是管理端口,指定6033是查询端口

[root@proxysql ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id for update;'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+

[root@proxysql ~]# mysql -uroot -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+

[root@mysqlrouter ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 201 |
+-------------+

[root@mysqlrouter ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 301 |
+-------------+

修改proxysql默认端口的办法:
proxysql一旦启动起来,其数据库文件已经形成的情况下,配置文件的内容将会被跳过,要是想修改内容的话,必须在运行时环境设置,并且保存到文件才可以。

步骤:
先在运行时环境(proxysql的交互模式中)设置如下:
MySQL [(none)]> set mysql-interfaces='0.0.0.0:3306';
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> save mysql variables to disk;
Query OK, 95 rows affected (0.03 sec)
MySQL [(none)]> select @@mysql-interfaces;
+--------------------+| @@mysql-interfaces |+--------------------+| 0.0.0.0:3306       |+--------------------+1 row in set (0.00 sec)
然后重启proxysql服务就可以了
[root@mysqlrouter ~]# service proxysql stopShutting down ProxySQL: DONE!
[root@mysqlrouter ~]# service proxysql startStarting ProxySQL: DONE!
查看监听端口状态:
[root@mysqlrouter ~]# ss -tnlpState      Recv-Q Send-Q                                         Local Address:Port                                                        Peer Address:Port              
LISTEN     0      128                                                        *:3306                                                                   *:*                   users:(("proxysql",pid=36322,fd=22))
LISTEN     0      128                                                        *:3306                                                                   *:*                   users:(("proxysql",pid=36322,fd=21))
LISTEN     0      128                                                        *:3306                                                                   *:*                   users:(("proxysql",pid=36322,fd=20))
LISTEN     0      128                                                        *:3306                                                                   *:*                   users:(("proxysql",pid=36322,fd=19))
LISTEN     0      128                                                        *:111                                                                    *:*                   users:(("rpcbind",pid=758,fd=8))
LISTEN     0      128                                                        *:6032                                                                   *:*                   users:(("proxysql",pid=36322,fd=23))

至此,MySQLRouter和Proxysql实现MySQL/MariaDB读写分离部署完成。






MHA+ProxySQL 读写分离高可用

1、ProxySQL说明

ProxySQL是mysql的一款中间件的产品,是灵活的mysql代理层,可以实现读写分离,支持query路由器的功能,支持动态指定sql进行缓存,支持动态加载配置,故障切换和一些sql的过滤功能。

其他产品:Dbproxy,MyCAT,OneProxy等。

 

2、MHA+Proxysql 读写分离实验

2.1. 安装步骤

IP

角色

操作系统

版本

172.16.10.21

Proxysql

Redhat6.7

1.4.9

172.16.10.32

Master

Redhat6.7

5.7.20

172.16.10.34

Slave1

Redhat6.7

5.7.20

172.16.10.36

Salve2

Redhat6.7

5.7.20

172.16.10.30

VIP

 

从库开启read_only=1,主库read_only=0

ProxySQL安装源码包:

yum -y install perl-DBD-MYSQL perl-DBI perl-Time-Hires perl-IO-Socket-ssl

或者简单粗暴的 :yum -y install perl*

proxySQL软件包下载地址:

https://www.percona.com/downloads/proxysql/

安装proxysql

rpm -ivh proxysql-1.4.9-1.1.el6.x86_64.rpm

配置文件路径为:/etc/proxysql.cnf

启动proxysql

service proxysql start

 

 

netstat -anlp |grep proxysql

 

 

6032是管理端口,6033是对外服务的端口号

用户名和密码默认都是admin

使用帮助如下:

 

 

 

查看proxysql 安装库情况:

mysql -uadmin -padmin -h127.0.0.1 -P6032

 

 

 

 

2.2. Proxysql库说明

Proxysql 版本1.4.9-percona-1.1实例:

Main:内存配置数据库,即memory,表里存放后端db实例,用户验证,路由规则等信息。Main库中有如下信息:

 

 

mysql_servers          --后端可以连接mysql服务器的列表                   

mysql_users            --配置后端数据库的账号和监控的账号

mysql_query_rules    --指定query路由到后端不同服务器的规则列表

 

disk库:持续化磁盘的配置。

Stats库:统计信息的汇总。

Monitor库:一些监控的收集信息,包括数据库的健康状态。

 

2.3. 配置proxysql监控

 

https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL

顶层为runtime,中间层为memory,底层也就是持久层disk和config file。

 

Runtime:代表Proxysql当前生效的正在使用的配置,无法直接修改这里的配置,必须要从下一层load进来。

Memory:memory层上面连接runtime层,下面连接持久化层。在这层可以正常操作Proxysql配置,随便修改,不会影响生产环境。修改一个配置一般都是现在memory层完成,确认正常后在加载到runtime和持久化到磁盘。

Disk和config file:持久化配置信息,重启后内存的配置信息会丢失,所以需要将配置信息保留在磁盘中。重启时,可以从磁盘快速加载回来。

 

1为写组,2为读组。

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.10.32',3307);

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.10.34',3307);

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.10.36',3307);

 

select * from mysql_servers;

 

 

 

配置监控账户:

create user 'mon'@'172.16.10.%' IDENTIFIED BY 'mon';

GRANT all privileges ON *.* TO 'mon'@'172.16.10.%' with grant option;

 

对外访问账户:

create user 'wr'@'172.16.10.%' IDENTIFIED BY 'wr';

GRANT all privileges ON *.* TO ON *.* TO 'wr'@'172.16.10.%' with grant option;  

 

配置Proxysql监控:

set mysql-monitor_username='mon';

set mysql-monitor_password='mon';

load mysql servers to runtime;

save mysql servers to disk;

 

 

 

之后验证监控信息:

select * from monitor.mysql_server_connect_log limit 6;

 

 

select * from monitor.mysql_server_ping_log order by time_start_us limit 6;

 

 

监控信息提示正常。

 

2.4. 配置Proxysql主从分区信息

配置主从分区需要用到mysql_replication_hostgroups

show create table mysql_replication_hostgroups\G;

 

writer_hostgroup 写入组的编号

reader_hostgroup 读取组的编号

实验使用10作为写入组,20作为读取组。

insert into mysql_replication_hostgroups values(10,20,'proxy');

load mysql servers to runtime;

save mysql servers to disk;

select * from mysql_replication_hostgroups;

 

 

Proxysql 会根据server的read_only的取值将服务进行分组,read_only=0的server,master被分到编号为10的组,read_only=1的server,slave则被分到编号为20的读组。

select * from mysql_servers;

 

 

Mysql_users表中的 transaction_persistent字段默认为0,建议在创建完用户之后设置为1,避免发生脏读幻读等现象:

insert into mysql_users(username,password,default_hostgroup) values('wr','wr',10);

update mysql_users set transaction_persistent=1 where username='wr';

load mysql users to runtime;

save mysql users to disk;

测试登陆(端口6033):

mysql -uwr -pwr -h 172.16.10.34 -P3307 -e "show slave status\G"

 

 

2.5. 配置读写分离策略

 

配置读写分离使用的表mysql_query_rules:

match_pattern:字段就是代表设置的规则。

destination_hostgroup:字段代表默认指定的分组。

apply代表真正执行应用规则。

insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',10,1);

insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

 

2.6. 测试读写分离

通过wr所创建的账户连接Proxysql登陆数据库。

mysql -uwr -pwr -h172.16.10.21 -P6033

 

 

 

通过管理端口登陆查看:

mysql -uadmin -padmin -h127.0.0.1 -P6032

select * from stats_mysql_query_digest;

 

 

可以得知,select count(*) from t; 这条语句自动编号到20的读组上,即slave上。

 

测试update。

 

测试update语句在10的写组上。

 

2.7. 读写分离权重调整

读写分离设置成功后,可以调节权重,如slave2(172.16.10.36)多进行读操作。

update mysql_servers set weight=10 where hostname='172.16.10.36';

 

load mysql servers to runtime;

load mysql variables to runtime;

load mysql users to runtime;

 

save mysql servers to disk;

save mysql variables to disk;

save mysql users to disk;

 

 

select * from mysql_servers;

 

 

2.8. MHA failover测试

测试前:

 

Master 172.16.10.32为master,组数为10,写组。

Failover后:

 

 

新的master为172.16.10.34(原slave1)

select * from runtime_mysql_servers;

 

 

新的master为写组(10),原为20读组。

 

进行读写分离测试:

 

 

发现读写分离仍然成功(回切后也成功)。

 

 


MySQL中间件之ProxySQL(7):详述ProxySQL的路由规则

返回 ProxySQL系列文章:http://www.cnblogs.com/f-ck-need-u/p/7586194.html

 

1.关于ProxySQL路由的简述

当ProxySQL收到前端app发送的SQL语句后,它需要将这个SQL语句(或者重写后的SQL语句)发送给后端的MySQL Server,然后收到SQL语句的MySQL Server执行查询,并将查询结果返回给ProxySQL,再由ProxySQL将结果返回给客户端(如果设置了查询缓存,则先缓存查询结果)。

ProxySQL可以实现多种方式的路由:基于ip/port、username、schema、SQL语句。其中基于SQL语句的路由是按照规则进行匹配的,匹配 方式有hash高效匹配、正则匹配,还支持更复杂的链式规则匹配。

本文将简单演示基于端口、用户和schema的路由,然后再详细介绍基于SQL语句的路由规则。不过需要说明的是,本文只是入门,为后面ProxySQL的高级路由方法做铺垫。

在阅读本文之前,请确保:

  1. 已经理解ProxySQL的多层配置系统,可参考: ProxySQL的多层配置系统
  2. 会操作ProxySQL的Admin管理接口,可参考: ProxySQL的Admin管理接口
  3. 已经配置好了后端节点、mysql_users等。可参考: ProxySQL管理后端节点

如果想速成,可参考; ProxySQL初试读写分离

本文涉及到的实验环境如下:

角色 主机IP server_id 数据状态
Proxysql 192.168.100.21 null
Master 192.168.100.22 110 刚安装的全新MySQL实例
Slave1 192.168.100.23 120 刚安装的全新MySQL实例
Slave2 192.168.100.24 130 刚安装的全新MySQL实例

该实验环境已经在前面的文章中搭建好,本文不再赘述一大堆的内容。环境的搭建请参考前面给出的1、2、3。

2.ProxySQL基于端口的路由

我前面写了一篇 通过MySQL Router实现MySQL读写分离的文章,MySQL Router实现读写分离的方式就是通过监听不同端口实现的:一个端口负责读操作,一个端口负责写操作。这样的路由逻辑非常简单,配置起来也很方便。

虽然基于端口实现读写分离配置起来非常简单,但是缺点也很明显:必须在前端app的代码中指定端口号码。这意味着MySQL的一部分流量权限被开发人员掌控了,换句话说,DBA无法全局控制MySQL的流量。此外,修改端口号时,app的代码也必须做出相应的修改。

虽说有缺点,但为了我这个ProxySQL系列文章的完整性,本文还是要简单演示ProxySQL如何基于端口实现读写分离。

首先修改ProxySQL监听SQL流量的端口号,让其监听在不同端口上。

admin> set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';
admin> save mysql variables to disk;

然后重启ProxySQL。

[root@xuexi ~]# service proxysql stop
[root@xuexi ~]# service proxysql start
[root@xuexi ~]# netstat -tnlp | grep proxysql
tcp  0  0 0.0.0.0:6032  0.0.0.0:*   LISTEN  27572/proxysql
tcp  0  0 0.0.0.0:6033  0.0.0.0:*   LISTEN  27572/proxysql
tcp  0  0 0.0.0.0:6034  0.0.0.0:*   LISTEN  27572/proxysql

监听到不同端口,再去修改 mysql_query_rules表。这个表是ProxySQL的路由规则定制表,后文会非常详细地解释该表。

例如,插入两条规则,分别监听在6033端口和6034端口,6033端口对应的 hostgroup_id=10是负责写的组,6034对应的 hostgroup_id=20是负责读的组。

insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) 
values(1,1,6033,10,1), (2,1,6034,20,1);
load mysql query rules to runtime;
save mysql query rules to disk;

这样就配置结束了,是否很简单?

其实除了基于端口进行分离,还可以基于监听地址(修改字段proxy_addr即可),甚至可以基于客户端地址(修改字段client_addr字段即可,该用法可用于采集数据、数据分析等)。

无论哪种路由方式,其实都是在修改mysql_query_rules表,所以下面先解释下这个表。

3.mysql_query_rules表

可以通过 show create table mysql_query_rules语句查看定义该表的语句。

下面是我整理出来的字段属性。

|       COLUMN          |  TYPE   |  NULL?   | DEFAULT    ||-----------------------|---------|----------|------------|| rule_id   (pk)        | INTEGER | NOT NULL |            || active                | INT     | NOT NULL | 0          || username              | VARCHAR |          |            || schemaname            | VARCHAR |          |            || flagIN                | INT     | NOT NULL | 0          || client_addr           | VARCHAR |          |            || proxy_addr            | VARCHAR |          |            || proxy_port            | INT     |          |            || digest                | VARCHAR |          |            || match_digest          | VARCHAR |          |            || match_pattern         | VARCHAR |          |            || negate_match_pattern  | INT     | NOT NULL | 0          || re_modifiers          | VARCHAR |          | 'CASELESS' || flagOUT               | INT     |          |            || replace_pattern       | VARCHAR |          |            || destination_hostgroup | INT     |          | NULL       || cache_ttl             | INT     |          |            || reconnect             | INT     |          | NULL       || timeout               | INT     |          |            || retries               | INT     |          |            || delay                 | INT     |          |            || mirror_flagOU         | INT     |          |            || mirror_hostgroup      | INT     |          |            || error_msg             | VARCHAR |          |            || sticky_conn           | INT     |          |            || multiplex             | INT     |          |            || log                   | INT     |          |            || apply                 | INT     | NOT NULL | 0          || comment               | VARCHAR |          |            |

各个字段的意义如下:有些字段不理解也无所谓,后面会分析一部分比较重要的。

  • rule_id:规则的id。 规则是按照rule_id的顺序进行处理的
  • active:只有该字段值为1的规则才会加载到runtime数据结构,所以只有这些规则才会被查询处理模块处理。
  • username:用户名筛选,当设置为非NULL值时,只有匹配的用户建立的连接发出的查询才会被匹配。
  • schemaname:schema筛选,当设置为非NULL值时,只有当连接使用 schemaname作为默认schema时,该连接发出的查询才会被匹配。(在MariaDB/MySQL中,schemaname等价于databasename)。
  • flagIN,flagOUT:这些字段允许我们创建"链式规则"(chains of rules),一个规则接一个规则。
  • apply:当匹配到该规则时,立即应用该规则。
  • client_addr:通过源地址进行匹配。
  • proxy_addr:当流入的查询是在本地某地址上时,将匹配。
  • proxy_port:当流入的查询是在本地某端口上时,将匹配。
  • digest:通过digest进行匹配,digest的值在 stats_mysql_query_digest.digest中。
  • match_digest:通过正则表达式匹配digest。
  • match_pattern:通过正则表达式匹配查询语句的文本内容。
  • negate_match_pattern:设置为1时,表示未被 match_digestmatch_pattern匹配的才算被成功匹配。也就是说,相当于在这两个匹配动作前加了NOT操作符进行取反。
  • re_modifiers:RE正则引擎的修饰符列表,多个修饰符使用逗号分隔。指定了 CASELESS后,将忽略大小写。指定了 GLOBAL后,将替换全局(而不是第一个被匹配到的内容)。为了向后兼容,默认只启用了 CASELESS修饰符。
  • replace_pattern:将匹配到的内容替换为此字段值。它使用的是RE2正则引擎的Replace。注意,这是可选的,当未设置该字段,查询处理器将不会重写语句,只会缓存、路由以及设置其它参数。
  • destination_hostgroup:将匹配到的查询路由到该主机组。但注意,如果用户的 transaction_persistent=1(见 mysql_users表),且该用户建立的连接开启了一个事务,则这个事务内的所有语句都将路由到同一主机组,无视匹配规则。
  • cache_ttl:查询结果缓存的时间长度(单位毫秒)。注意,在ProxySQL 1.1中,cache_ttl的单位是秒。
  • reconnect:目前不使用该功能。
  • timeout:被匹配或被重写的查询执行的最大超时时长(单位毫秒)。如果一个查询执行的时间太久(超过了这个值),该查询将自动被杀掉。如果未设置该值,将使用全局变量 mysql-default_query_timeout的值。
  • retries:当在执行查询时探测到故障后,重新执行查询的最大次数。如果未指定,则使用全局变量 mysql-query_retries_on_failure的值。
  • delay:延迟执行该查询的毫秒数。本质上是一个限流机制和QoS,使得可以将优先级让位于其它查询。这个值会写入到 mysql-default_query_delay全局变量中,所以它会应用于所有的查询。将来的版本中将会提供一个更高级的限流机制。
  • mirror_flagOUT和mirror_hostgroup mirroring相关的设置,目前mirroring正处于实验阶段,所以不解释。
  • error_msg:查询将被阻塞,然后向客户端返回 error_msg指定的信息。
  • sticky_conn:当前还未实现该功能。
  • multiplex:如果设置为0,将禁用multiplexing。如果设置为1,则启用或重新启用multiplexing,除非有其它条件(如用户变量或事务)阻止启用。如果设置为2,则只对当前查询不禁用multiplexing。默认值为 NULL,表示不会修改multiplexing的策略。
  • log:查询将记录日志。
  • apply:当设置为1后,当匹配到该规则后,将立即应用该规则,不会再评估其它的规则(注意:应用之后,将不会评估 mysql_query_rules_fast_routing中的规则)。
  • comment:注释说明字段,例如描述规则的意义。

4.基于mysql username进行路由

基于mysql user的配置方式和基于端口的配置是类似的。

需要注意,在插入mysql user到 mysql_users表中时,就已经指定了默认的路由目标组,这已经算是一个路由规则了(只不过是默认路由目标)。当成功匹配到 mysql_query_rules中的规则时,这个默认目标就不再生效。所以,通过默认路由目标,也能简单地实现读写分离。

例如,在后端MySQL Server上先创建好用于读、写分离的用户。例如,root用户用于写操作,reader用户用于读操作。

# 在master节点上执行:grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';grant select,show databases,show view on *.* to reader@'192.168.100.%' identified by 'P@ssword1!';

然后将这两个用户添加到ProxySQL的 mysql_users表中,并创建两条规则分别就有这两个用户进行匹配。

insert into mysql_users(username,password,default_hostgroup) values('root','P@ssword1!',10),('reader','P@ssword1!',20);load mysql users to runtime;save mysql users to disk;delete from mysql_query_rules;      # 为了测试,先清空已有规则insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply) values(1,1,'root',10,1),(2,1,'reader',20,1);load mysql query rules to runtime;save mysql query rules to disk;

当然,在上面演示的示例中,mysql_query_rules中基于username的规则和mysql_users中这两个用户的默认规则是重复了的。

5.基于数据库名称进行路由

ProxySQL支持基于schemaname进行路由。这在一定程度上实现了简单的sharding功能。例如,将后端MySQL集群中的节点A和节点B定义在不同主机组中,ProxySQL将所有对于DB1库的查询路由到节点A所在的主机组,将所有对DB2库的查询路由到节点B所在的主机组。

只需配置一个schemaname字段就够了,好简单,是不是感觉很爽。但想太多了,ProxySQL的schemaname字段只是个鸡肋,要实现分库sharding,只能通过正则匹配、查询重写的方式来实现。

例如,原语句如下,用于找出浙江省的211大学。

select * from zhongguo.university where prov='Zhejiang' and high=211;

按省份分库后,通过ProxySQL的正则替换,将语句改写为如下SQL语句:

select * from Zhejiang.university where 1=1 high=211;

然后还可以将改写后的SQL语句路由到指定的主机组中,实现真正的分库。

这些内容比较复杂、也比较高级,在后面的文章中我会详细解释。

6.基于SQL语句路由

从这里开始,开始介绍ProxySQL路由规则的核心:基于SQL语句的路由。

ProxySQL接收到前端发送的SQL语句后,首先分析语句,然后从 mysql_query_rules表中寻找是否有匹配该语句的规则。如果先被username或ip/port类的规则匹配并应用,则按这些规则路由给后端,如果是被基于SQL语句的规则匹配,则启动正则引擎进行正则匹配,然后路由给对应的后端组,如果规则中指定了正则替换字段,则还会重写SQL语句,然后再发送给后端。

ProxySQL支持两种类型的SQL语句匹配 方式:match_digest和match_pattern。在解释这两种匹配 方 式之前,有必要先解释下SQL语句的参数化。

6.1 SQL语句分类:参数化

什么是参数化?

select * from tbl where id=?

这里将where条件语句中字段id的值进行了参数化,也就是上面的问号 ?

我们在客户端发起的SQL语句都是完整格式的语句,但是SQL优化引擎出于优化的目的需要考虑很多事情。例如,如何缓存查询结果、如何匹配查询缓存中的数据并取出,等等。将SQL语句参数化是优化引擎其中的一个行为,对于那些参数相同但参数值不同的查询语句,SQL语句认为这些是同类查询,同类查询的SQL语句不会重复去编译而增加额外的开销。

例如,下面的两个语句,就是同类SQL语句:

select * from tbl where id=10;select * from tbl where id=20;

将它们参数化后,结果如下:

select * from tbl where id=?;

通俗地讲,这里的"?"就是一个变量,任何满足这个语句类型的值都可以传递到这个变量中。

所以, 对参数化进行一个通俗的定义:对于那些参数相同、参数值不同的SQL语句,使用问号"?"去替换参数值,替换后返回的语句就是参数化的结果。

无论是MySQL、SQL Server还是Oracle(这个不确定),优化引擎内部都会将语句进行参数化。例如,下面是SQL Server的执行计划,其中"@1"就是所谓的问号"?"。

ProxySQL也支持参数化。当前端发送SQL语句到达ProxySQL后,ProxySQL会将其参数化并分类。例如,下面是sysbench测试过程中,ProxySQL统计的参数化语句。

+----+----------+------------+-------------------------------------------------------------+| hg | sum_time | count_star | digest_text                                                 |+----+----------+------------+-------------------------------------------------------------+| 2  | 14520738 | 50041      | SELECT c FROM sbtest1 WHERE id=?                            || 1  | 3142041  | 5001       | COMMIT                                                      || 1  | 2270931  | 5001       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c || 1  | 2021320  | 5003       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?            || 1  | 1768748  | 5001       | UPDATE sbtest1 SET k=k+? WHERE id=?                         || 1  | 1697175  | 5003       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?       || 1  | 1346791  | 5001       | UPDATE sbtest1 SET c=? WHERE id=?                           || 1  | 1263259  | 5001       | DELETE FROM sbtest1 WHERE id=?                              || 1  | 1191760  | 5001       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)     || 1  | 875343   | 5005       | BEGIN                                                       |+----+----------+------------+-------------------------------------------------------------+

ProxySQL的 mysql_query_rules表中有三个字段,能基于 参数化后的SQL语句进行三种不同方式的匹配:

如果要进行SQL语句的重写(即正则替换),或者对参数值匹配,则必须采用match_pattern。如果可以,尽量采用digest匹配 方式,因为它的效率更高。

6.2 路由相关的几个统计表

在ProxySQL的stats库中,包含了几个统计表。

admin> 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_prepared_statements_info || stats_mysql_processlist              |     <--模拟show processlist的结果| stats_mysql_query_digest             | <--本文解释| stats_mysql_query_digest_reset       | <--本文解释| stats_mysql_query_rules              | <--本文解释| stats_mysql_users                    |     <--各mysql user前端和ProxySQL的连接数| stats_proxysql_servers_checksums     |     <--ProxySQL集群相关| stats_proxysql_servers_metrics       |     <--ProxySQL集群相关| stats_proxysql_servers_status        |     <--ProxySQL集群相关+--------------------------------------+

这些表的内容、解释我已经翻译,参见: ProxySQL的stats库。本文介绍其中3个和路由、规则相关的表。

6.2.1 stats_mysql_query_digest

这个表对于分析SQL语句至关重要,是分析语句性能、定制路由规则指标的最主要来源。

刚才已经解释过什么是SQL语句的参数化,还说明了ProxySQL会将参数化后的语句进行hash计算得到它的digest,这个统计表中记录的就是每个参数化分类后的语句对应的统计数据,包括该类语句的执行次数、所花总时间、所花最短、最长时间,还包括语句的文本以及它的digest。

如下图:

以下是各个字段的意义:

注意,该表中的查询所花时长是指ProxySQL从接收到客户端查询开始,到ProxySQL准备向客户端发送查询结果的时长。因此,这些时间更像是客户端看到的发起、接收的时间间隔(尽管客户端到服务端数据传输也需要时间)。更精确一点,在执行查询之前,ProxySQL可能需要更改字符集或模式,可能当前后端不可用(当前后端执行语句失败)而找一个新的后端,可能因为所有连接都繁忙而需要等待空闲连接,这些都不应该计算到查询执行所花时间内。

其中hostgroup、digest、digest_text、count_start、{sum,min,max}_time这几列最常用。

例如:

admin> select hostgroup hg,count_star,sum_time,digest,digest_text from stats_mysql_query_digest;+----+------------+----------+--------------------+------------------------+| hg | count_star | sum_time | digest             | digest_text            |+----+------------+----------+--------------------+------------------------+| 10 | 4          | 2412     | 0xADB885E1F3A7A5C2 | select * from test2.t1 || 10 | 6          | 4715     | 0x57497F236587B138 | select * from test1.t1 |+----+------------+----------+--------------------+------------------------+

从中分析,两个语句都路由到了hostgroup=10的组中,第一个语句执行了4次,这4次总共花费了2412微秒(即2.4毫秒),第二个语句执行了6次,总花费4.7毫秒。还给出了这两个语句参数化后的digest值,以及参数化后的SQL文本。

6.2.2 stats_mysql_query_digest_reset

这个表的表结构和 stats_mysql_query_digest是完全一样的,只不过每次从这个表中检索数据(随便检索什么,哪怕 where 1=0),都会重置 stats_mysql_query_digest表中已统计的数据。

6.2.3 stats_mysql_query_rules

这个表只有两个字段:

6.3 基于SQL语句路由:digest

digest匹配规则是对digest进行精确匹配。

例如,从 stats_mysql_query_digest中获取两个对应的digest值。注意,现在它们的hostgroup_id=10。

admin> select hostgroup hg,count_star,sum_time,digest,digest_text from stats_mysql_query_digest;+----+------------+----------+--------------------+------------------------+| hg | count_star | sum_time | digest             | digest_text            |+----+------------+----------+--------------------+------------------------+| 10 | 4          | 2412     | 0xADB885E1F3A7A5C2 | select * from test2.t1 || 10 | 6          | 4715     | 0x57497F236587B138 | select * from test1.t1 |+----+------------+----------+--------------------+------------------------+

插入两条匹配这两个digest的规则:

insert into mysql_query_rules(rule_id,active,digest,destination_hostgroup,apply) values(1,1,"0xADB885E1F3A7A5C2",20,1),(2,1,"0x57497F236587B138",10,1);

然后测试

mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"

再去查看规则的路由命中情况:

admin> select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1       | 1    || 2       | 1    |+---------+------+

查看路由的目标:

admin> select hostgroup hg,count_star cs,digest,digest_text from stats_mysql_query_digest;+----+----+--------------------+------------------------+| hg | cs | digest             | digest_text            |+----+----+--------------------+------------------------+| 20 | 1  | 0xADB885E1F3A7A5C2 | select * from test2.t1 || 10 | 1  | 0x57497F236587B138 | select * from test1.t1 |+----+----+--------------------+------------------------+

可见,基于digest的精确匹配规则已经生效。

6.4 基于SQL语句路由:match_digest

match_digest是对digest做正则匹配,但注意match_pattern字段中给的规则不是hash值,而是SQL语句的文本匹配规则。

ProxySQL支持两种正则引擎:

老版本中默认的正则引擎是RE2,现在默认的正则引擎是PCRE。可从变量 mysql-query_processor_regex获知当前的正则引擎是RE2还是PCRE:

Admin> select @@mysql-query_processor_regex;+-------------------------------+| @@mysql-query_processor_regex |+-------------------------------+| 1                             |+-------------------------------+

其中1代表PCRE,2代表RE2。

mysql_query_rules表中有一个字段 re_modifiers,它用于定义正则引擎的修饰符,默认已经设置 caseless,表示正则匹配时忽略大小写,所以select和SELECT都能匹配。此外,还可以设置global修饰符,表示匹配全局,而非匹配第一个,这个在重写SQL语句时有用。

(RE2引擎无法同时设置caseless和global,即使它们都设置了也不会生效。所以,将默认的正则引擎改为了PCRE)

在进行下面的实验之前,先把 mysql_query_rules表清空,并将规则的统计数据也清空。

delete from mysql_query_rules;select * from stats_mysql_query_digest_reset;insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,"^select .* test2.*",20,1),(2,1,"^select .* test1.*",10,1);load mysql query rules to runtime;save mysql query rules to disk;

然后分别执行:

mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"

查看规则匹配结果:

admin> select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1       | 1    || 2       | 1    |+---------+------+admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;+----+----+--------------------+------------------------+| hg | cs | digest             | dt                     |+----+----+--------------------+------------------------+| 10 | 1  | 0x57497F236587B138 | select * from test1.t1 || 20 | 1  | 0xADB885E1F3A7A5C2 | select * from test2.t1 |+----+----+--------------------+------------------------+

显然,命中规则,且按照期望进行路由。

如果想对match_digest取反,即不被正则匹配的SQL语句才命中规则,则设置 mysql_query_rules表中的字段 negate_match_pattern=1。同样适用于下面的match_pattern匹配 方式。

6.5 基于SQL语句路由:match_pattern

和match_digest的匹配 方式类似,但match_pattern是基于原始SQL语句进行匹配的,包括参数值。有两种情况必须使用match_pattern:

如果想对match_pattern取反,即不被正则匹配的SQL语句才命中规则,则设置 mysql_query_rules表中的字段 negate_match_pattern=1

例如:

## 清空规则以及规则的统计数据delete from mysql_query_rules;select * from stats_mysql_query_digest_reset where 1=0;insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,"^select .* test2.*",20,1),(2,1,"^select .* test1.*",10,1);load mysql query rules to runtime;save mysql query rules to disk;

执行查询:

mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"

然后查看匹配结果:

admin> select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1       | 1    || 2       | 1    |+---------+------+admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;+----+----+--------------------+------------------------+| hg | cs | digest             | dt                     |+----+----+--------------------+------------------------+| 20 | 1  | 0xADB885E1F3A7A5C2 | select * from test2.t1 || 10 | 1  | 0x57497F236587B138 | select * from test1.t1 |+----+----+--------------------+------------------------+

再来看看匹配参数值(虽然几乎不会这样做)。这里要测试的语句如下:

mysql -uroot -p123456 -h127.0.0.1 -P6033 -e "select * from test1.t1 where name like 'malong%';"mysql -uroot -p123456 -h127.0.0.1 -P6033 -e "select * from test2.t1 where name like 'xiaofang%';"

现在插入两条规则,对参数"malong%"和"xiaofang"进行匹配。

## 清空规则以及规则的统计数据delete from mysql_query_rules;select * from stats_mysql_query_digest_reset where 1=0;insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,"malong",20,1),(2,1,"xiaofang",10,1);load mysql query rules to runtime;save mysql query rules to disk;

执行上面的两个查询语句,然后查看匹配结果:

admin> select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1       | 1    || 2       | 1    |+---------+------+admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;+----+----+--------------------+------------------------------------------+| hg | cs | digest             | dt                                       |+----+----+--------------------+------------------------------------------+| 20 | 1  | 0x0C624EDC186F0217 | select * from test1.t1 where name like ? || 10 | 1  | 0xA38442E236D915A7 | select * from test2.t1 where name like ? |+----+----+--------------------+------------------------------------------+

已按预期进行路由。

7.实用的读写分离

一个极简单却大有用处的读、写分离功能:将默认路由组设置为写组,然后再插入下面两个select语句的规则。

# 10为写组,20为读组insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),       (2,1,'^SELECT',20,1);

但需要注意的是,这样的规则只适用于小环境下的读写分离,对于稍复杂的环境,需要对不同语句进行开销分析,对于开销大的语句需要制定专门的路由规则。在之后的文章中我会稍作分析。

8.总结

ProxySQL能通过ip、port、client_ip、username、schemaname、digest、match_digest、match_pattern实现不同方式的路由,方式可谓繁多。特别是基于正则匹配的灵活性,使得ProxySQL能满足一些比较复杂的环境。

总的来说,ProxySQL主要是通过digest、match_digest和match_pattern进行规则匹配的。在本文中,只是介绍了匹配规则的基础以及简单的用法,为进军后面的文章做好铺垫。





About Me

........................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在itpub、博客园、CSDN和个人微 信公众号( xiaomaimiaolhr)上有同步更新

● 本文itpub地址: http://blog.itpub.net/26736162

● 本文博客园地址: http://www.cnblogs.com/lhrbest

● 本文CSDN地址: https://blog.csdn.net/lihuarongaini

● 本文pdf版、个人简介及小麦苗云盘地址: http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号: 230161599 、618766405

● 微 信群:可加我微 信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友 646634621 ,注明添加缘由

● 于 2020-02-01 06:00 ~ 2020-02-31 24:00 在西安完成

● 最新修改时间:2020-02-01 06:00 ~ 2020-02-31 24:00

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

........................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

小麦苗OCP、OCM、高可用网络班http://blog.itpub.net/26736162/viewspace-2148098/

小麦苗腾讯课堂主页https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号( xiaomaimiaolhr)及QQ群(DBA宝典)、添加小麦苗微 信, 学习最实用的数据库技术。

........................................................................................................................

欢迎与我联系

 

 



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2676876/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1431
  • 访问量
    8509671