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读写分离部署完成。
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读组。
进行读写分离测试：
发现读写分离仍然成功（回切后也成功）。
ProxySQL介绍
ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。具有以下特性：http://www.proxysql.com/
1、连接池，而且是multiplexing
2、主机和用户的最大连接数限制
3、自动下线后端DB
-
延迟超过阀值
-
ping 延迟超过阀值
-
网络不通或宕机
4、强大的规则路由引擎
5、支持prepared statement
6、支持Query Cache
7、支持负载均衡，与gelera结合自动failover
1、系统环境
三台服务器系统环境一致如下
[root@db1 ~]
# cat /etc/redhat-release
CentOS Linux release
7.4
.1708 (Core)
[root@db1 ~]
# uname -r
3.10
.0
-693.el7.x86_64
2、IP地址与软件版本
-
proxy 192.168.22.171
-
db1 192.168.22.173
-
db2 192.168.22.174
-
mysql 5.7.17
-
proxy sql 1.4.8
3、关闭防火墙、selinux
systemctl
stop firewalld
#停止防火墙服务
systemctl
disable firewalld
#禁止开机自启动
sed -i
's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/conf && reboot
#用sed命令替换的试修改selinux的配置文件
4、mysql安装与主从同步
安装请参考以下文章
LAMP架构应用实战——MySQL服务
主从同步请参以下文章
Linux系统MySQL数据库主从同步实战过程
安装布署过程
1、数据库主从同步
查看主从同步状态
mysql>
show
slave
status\G
***************************
1.
row ***************************
Slave_IO_State: Waiting
for
master
to send
event
Master_Host:
192.168
.22
.173
Master_User: rep
Master_Port:
3306
Connect_Retry:
60
Master_Log_File:
master-
log
.000001
Read_Master_Log_Pos:
154
Relay_Log_File: db2-relay-
bin
.000002
Relay_Log_Pos:
321
Relay_Master_Log_File:
master-
log
.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
0
Last_Error:
Skip_Counter:
0
Exec_Master_Log_Pos:
154
Relay_Log_Space:
526
Until_Condition:
None
Until_Log_File:
Until_Log_Pos:
0
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
0
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno:
0
Last_IO_Error:
Last_SQL_Errno:
0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
1
Master_UUID:
70a61633
-63ae
-11e8-ab86
-000c29fe99ea
Master_Info_File: /mysqldata/master.info
SQL_Delay:
0
SQL_Remaining_Delay:
NULL
Slave_SQL_Running_State:
Slave has
read all relay
log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in
set (
0.00 sec)
检测主从同步
[root@db1 ~]
# mysql -uroot -p -e "create database testdb;"
Enter
password:
[root@db1 ~]
# mysql -uroot -p -e "show databases;" |grep testdb
Enter
password:
testdb
#db2上查看是否同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows
in set (
0.
01 sec)
2、准备proxySQL软件
[root@proxy ~]
# wget https:
//github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm
[root@proxy ~]
# ll proxysql-1.4.8-1-centos7.x86_64.rpm
-rw-r--r--
1 root root
5977168 Apr
10
11:
38 proxysql
-1.4
.8
-1-centos7.x86_64.rpm
3、安装配置
[root@proxy ~]
# yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm
[root@proxy ~]
# rpm -ql proxysql
/etc/init.d/proxysql #启动脚本
/etc/proxysql.cnf #配置文件，仅在第一次(/var/lib/proxysql/proxysql.db文件不存在)启动时有效。启#动后可以在proxysql管理端中通过修改数据库的方式修改配置并生效(官方推荐方式。)
/usr/bin/proxysql #主程序文件
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
4、配置文件详解
[root@proxy ~]
# egrep -v
"^#|^$" /etc/proxysql.cnf
datadir=
"/var/lib/proxysql" #数据目录
admin_variables=
{
admin_credentials=
"admin:admin" #连接管理端的用户名与密码
mysql_ifaces=
"0.0.0.0:6032" #管理端口，用来连接proxysql的管理数据库
}
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" #指定转发端口，用于连接后端mysql数据库的，相当于代理作用
default_schema=
"information_schema"
stacksize=
1048576
server_version=
"5.5.30" #指定后端mysql的版本
connect_timeout_server=
3000
monitor_username=
"monitor"
monitor_password=
"monitor"
monitor_history=
600000
monitor_connect_interval=
60000
monitor_ping_interval=
10000
monitor_read_only_interval=
1500
monitor_read_only_timeout=
500
ping_interval_server_msec=
120000
ping_timeout_server=
500
commands_stats=
true
sessions_sort=
true
connect_retries_on_failure=
10
}
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)
#因此我们使用官方推荐的方式来配置proxy sql
5、启动服务并查看
[root@proxy ~]# /etc/init.d/proxysql start
Starting ProxySQL: DONE!
[root@proxy ~]
# ss -lntup|grep proxy
tcp LISTEN
0
128 *:
6032 *:* users:((
"proxysql",pid=
1199,fd=
23))
tcp LISTEN
0
128 *:
6033 *:* users:((
"proxysql",pid=
1199,fd=
22))
tcp LISTEN
0
128 *:
6033 *:* users:((
"proxysql",pid=
1199,fd=
21))
tcp LISTEN
0
128 *:
6033 *:* users:((
"proxysql",pid=
1199,fd=
20))
tcp LISTEN
0
128 *:
6033 *:* users:((
"proxysql",pid=
1199,fd=
19))
#可以看出转发端口
6033是启动了四个线程
6、在mysql上配置账号并授权
mysql>
GRANT ALL
ON *.*
TO
'proxysql'@
'192.168.22.%'
IDENTIFIED
BY
'123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql>
flush
privileges;
Query OK, 0 rows affected (0.02 sec)
7、proxysql默认数据库说明
[root@proxy ~]
# yum install mysql -y
[root@proxy ~]
# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands
end
with ; or \g.
Your MySQL connection id is 1
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)]>
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：内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
disk：是持久化到硬盘的配置，sqlite数据文件。
stats：是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
monitor：库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
8、proxysql的配置系统
ProxySQL具有一个复杂但易于使用的配置系统，可以满足以下需求：
1、允许轻松动态更新配置（这是为了让ProxySQL用户可以在需要零宕机时间配置的大型基础架构中使用它）。与MySQL兼容的管理界面可用于此目的。
2、允许尽可能多的配置项目动态修改，而不需要重新启动ProxySQL进程
3、可以毫不费力地回滚无效配置
4、这是通过多级配置系统实现的，其中设置从运行时移到内存，并根据需要持久保存到磁盘。
3级配置由以下几层组成：
+-------------------------+
| RUNTIME |
+-------------------------+
/|\ |
| |
[1] | [2] |
| \|/
+-------------------------+
| MEMORY |
+-------------------------+ _
/|\ | |\
| | \
[3] | [4] | \ [5]
| \|/ \
+-------------------------+ +-------------------------+
| DISK | | CONFIG FILE |
+-------------------------+ +-------------------------+
参考文章：https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL
9、配置proxysql管理用户
proxysql默认的表信息如下
MySQL [main]>
show
tables;
+
--------------------------------------------+
| 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 |
+
--------------------------------------------+
20 rows in
set (
0.00 sec)
#这里是使用insert into语句来动态配置，而可以不需要重启
MySQL [(
none)]>
insert
into mysql_servers(hostgroup_id,hostname,port,weight,
comment)
values(
1,
'db1',
'3306',
1,
'Write Group');
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]>
insert
into mysql_servers(hostgroup_id,hostname,port,weight,
comment)
values(
2,
'db2',
'3307',
1,
'Read Group');
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]>
select *
from mysql_servers;
+
--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
comment |
+
--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
|
1 | db1 |
3306 |
ONLINE |
1 |
0 |
1000 |
0 |
0 |
0 | Write
Group |
|
2 | db2 |
3307 |
ONLINE |
1 |
0 |
1000 |
0 |
0 |
0 |
Read
Group |
+
--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
2
rows
in
set (
0.00 sec)
#接下来将刚刚在mysql客户端创建的用户写入到proxy sql主机的mysql_users表中，它也是用于proxysql客户端访问数据库，默认组是写组，当读写分离规则出现问题时，它会直接访问默认组的数据库。
MySQL [
main]>
INSERT
INTO mysql_users(username,
password,default_hostgroup)
VALUES (
'proxysql',
'123456',
1);
Query OK, 1 row affected (0.00 sec)
MySQL [main]>
select *
from mysql_users;
+
----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+
----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+
----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in
set (
0.00 sec)
在mysql上添加监控的用户
mysql>
GRANT
SELECT
ON *.*
TO
'monitor'@
'192.168.22.%'
IDENTIFIED
BY
'monitor';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
flush
privileges;
Query OK, 0 rows affected (0.00 sec)
#在proxysql主机端配置监控用户
MySQL [main]>
set mysql-monitor_username=
'monitor';
Query OK, 1 row affected (0.00 sec)
MySQL [main]>
set mysql-monitor_password=
'monitor';
Query OK, 1 row affected (0.00 sec)
#参考文章：https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
10、配置proxysql的转发规则
MySQL [main]>
insert
into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,
apply)
values(
1,
1,
'^SELECT.*FOR UPDATE$',
1,
1);
Query OK, 1 row affected (0.01 sec)
MySQL [main]>
insert
into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,
apply)
values(
2,
1,
'^SELECT',
2,
1);
Query OK, 1 row affected (0.00 sec)
MySQL [main]>
select rule_id,active,match_digest,destination_hostgroup,
apply
from mysql_query_rules;
+
---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+
---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*
FOR
UPDATE$ |
1 |
1 |
|
2 |
1 | ^
SELECT |
2 |
1 |
+
---------+--------+----------------------+-----------------------+-------+
2
rows
in
set (
0.00 sec)
#配置查询select的请求转发到hostgroup_id=2组上（读组）
#征对select * from table_name for update这样的修改语句，我们是需要将请求转到写组，也就是hostgroup_id=1
#对于其它没有被规则匹配的请求全部转发到默认的组（mysql_users表中default_hostgroup）
11、更新配置到RUNTIME中
由上面的配置系统层级关系可以得知所有进来的请求首先是经过RUNTIME层
MySQL [main]>
load mysql
users
to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]>
load mysql servers
to runtime;
Query OK, 0 rows affected (0.02 sec)
MySQL [main]>
load mysql
query
rules
to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]>
load mysql
variables
to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]>
load
admin
variables
to runtime;
Query OK, 0 rows affected (0.00 sec)
12、将所有配置保存至磁盘上
所有配置数据保存到磁盘上，也就是永久写入/var/lib/proxysql/proxysql.db这个文件中
MySQL [main]> save mysql users to disk;
Query OK,
0
rows
affected
(
0.03 sec)
MySQL [main]> save mysql servers to disk;
Query OK,
0
rows
affected
(
0.04 sec)
MySQL [main]> save mysql query rules to disk;
Query OK,
0
rows
affected
(
0.03 sec)
MySQL [main]> save mysql variables to disk;
Query OK,
94
rows
affected
(
0.02 sec)
MySQL [main]> save admin variables to disk;
Query OK,
31
rows
affected
(
0.02 sec)
MySQL [main]> load mysql users to runtime;
Query OK,
0
rows
affected
(
0.00 sec)
13、测试读写分离
[root@proxy ~]
# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033
Welcome to the MariaDB monitor. Commands
end
with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL)
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)]>
show
databases;
+
--------------------+
| Database |
+
--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+
--------------------+
5 rows in
set (
0.02 sec)
#这才是我们真正的数据库啊
创建数据与表，测试读写分离情况
MySQL [(none)]>
create
database test_proxysql;
Query OK, 1 row affected (0.02 sec)
MySQL [(none)]>
use test_proxysql;
Database changed
MySQL [test_proxysql]>
create
table test_tables(
name
varchar(
20),age
int(
4));
Query OK, 0 rows affected (0.07 sec)
MySQL [test_proxysql]>
insert
into test_tables
values(
'zhao',
'30');
Query OK, 1 row affected (0.09 sec)
MySQL [test_proxysql]>
select *
from test_tables;
+
------+------+
| name | age |
+
------+------+
| zhao | 30 |
+
------+------+
1 row in
set (
0.02 sec)
在proxysql管理端查看读写分离
MySQL [main]>
select *
from stats_mysql_query_digest;
+
-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+
-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 2 | test_proxysql | proxysql | 0x57CF7EC26C91DF9A |
select *
from test_tables |
1 |
1527667635 |
1527667635 |
14253 |
14253 |
14253 |
|
1 | information_schema | proxysql |
0x226CD90D52A2BA0B |
select @@version_comment
limit ? |
1 |
1527667214 |
1527667214 |
0 |
0 |
0 |
|
1 | test_proxysql | proxysql |
0xFF9877421CFBDA6F |
insert
into test_tables
values(?,?) |
1 |
1527667623 |
1527667623 |
89033 |
89033 |
89033 |
|
1 | information_schema | proxysql |
0xE662AE2DEE853B44 |
create
database
test-proxysql |
1 |
1527667316 |
1527667316 |
8470 |
8470 |
8470 |
|
1 | information_schema | proxysql |
0x02033E45904D3DF0 |
show
databases |
1 |
1527667222 |
1527667222 |
19414 |
19414 |
19414 |
|
1 | information_schema | proxysql |
0xB9EF28C84E4207EC |
create
database test_proxysql |
1 |
1527667332 |
1527667332 |
15814 |
15814 |
15814 |
|
2 | information_schema | proxysql |
0x620B328FE9D6D71A |
SELECT
DATABASE() |
1 |
1527667342 |
1527667342 |
23386 |
23386 |
23386 |
|
1 | test_proxysql | proxysql |
0x02033E45904D3DF0 |
show
databases |
1 |
1527667342 |
1527667342 |
2451 |
2451 |
2451 |
|
1 | test_proxysql | proxysql |
0x59F02DA280268525 |
create
table test_tables |
1 |
1527667360 |
1527667360 |
9187 |
9187 |
9187 |
|
1 | test_proxysql | proxysql |
0x99531AEFF718C501 |
show
tables |
1 |
1527667342 |
1527667342 |
1001 |
1001 |
1001 |
|
1 | test_proxysql | proxysql |
0xC745E37AAF6095AF |
create
table test_tables(
name
varchar(?),age
int(?)) |
1 |
1527667558 |
1527667558 |
68935 |
68935 |
68935 |
+
-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+
11
rows
in
set (
0.01 sec)
#从上述结果就可以看出读写分离配置是成功的，读请求是转发到2组，写请求转发到1组
整个读写分离的架构配置到此就完成了，但是此架构存在需要优化的地方，那就是
此架构存在单点问题。实际生产环境中可采用
MHA+ProxySQL+Mysql这类架构解决此问题
https://hub.docker.com/r/proxysql/proxysql
Overview
ProxySQL is a high performance, high availability, protocol aware proxy for MySQL and forks (like Percona Server and MariaDB). All the while getting the unlimited freedom that comes with a GPL license.
Its development is driven by the lack of open source proxies that provide high performance.
Details at
ProxySQL website.
Image Description
To pull the
latest image simply run:
$ docker pull proxysql/proxysql
Otherwise for a specific version specify the associated tag e.g. for version 2.0.9:
$ docker pull proxysql/proxysql:2.0.9
The image is based on Debian and runs ProxySQL as a foreground process.
- NOTE: The package doesn't contain the MySQL client
Run
To run a ProxySQL container with a custom ProxySQL configuration file:
$ docker run -p 16032:6032 -p 16033:6033 -d -v /path/to/proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql
NOTE: You will need to define a second pair of admin credentials to connect outside of your container.
Sample config file (i.e.
/path/to/proxysql.cnf listed above)
The following basic configuration file should be sufficient for development purposes, this configuration will allow you to connect to your ProxySQL Docker container remotely using the second pair of
admin_credentials e.g.:
mysql -h127.0.0.1 -P16032 -uradmin -pradmin --prompt "ProxySQL Admin>"
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin;radmin:radmin"
mysql_ifaces="0.0.0.0:6032"
}
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"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
MySQL中间件之ProxySQL(7)：详述ProxySQL的路由规则
返回
ProxySQL系列文章：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
当ProxySQL收到前端app发送的SQL语句后，它需要将这个SQL语句(或者重写后的SQL语句)发送给后端的MySQL Server，然后收到SQL语句的MySQL Server执行查询，并将查询结果返回给ProxySQL，再由ProxySQL将结果返回给客户端(如果设置了查询缓存，则先缓存查询结果)。
ProxySQL可以实现多种方式的路由：基于ip/port、username、schema、SQL语句。其中基于SQL语句的路由是按照规则进行匹配的，匹配 方式有hash高效匹配、正则匹配，还支持更复杂的链式规则匹配。
本文将简单演示基于端口、用户和schema的路由，然后再详细介绍基于SQL语句的路由规则。不过需要说明的是，本文只是入门，为后面ProxySQL的高级路由方法做铺垫。
在阅读本文之前，请确保：
-
已经理解ProxySQL的多层配置系统，可参考：
ProxySQL的多层配置系统
- 会操作ProxySQL的Admin管理接口，可参考：
ProxySQL的Admin管理接口
- 已经配置好了后端节点、mysql_users等。可参考：
ProxySQL管理后端节点
如果想速成，可参考；
ProxySQL初试读写分离
本文涉及到的实验环境如下：
| 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。
我前面写了一篇
通过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表，所以下面先解释下这个表。
可以通过
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_digest或
match_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：注释说明字段，例如描述规则的意义。
基于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中这两个用户的默认规则是重复了的。
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语句路由到指定的主机组中，实现真正的分库。
这些内容比较复杂、也比较高级，在后面的文章中我会详细解释。
从这里开始，开始介绍ProxySQL路由规则的核心：基于SQL语句的路由。
ProxySQL接收到前端发送的SQL语句后，首先分析语句，然后从
mysql_query_rules表中寻找是否有匹配该语句的规则。如果先被username或ip/port类的规则匹配并应用，则按这些规则路由给后端，如果是被基于SQL语句的规则匹配，则启动正则引擎进行正则匹配，然后路由给对应的后端组，如果规则中指定了正则替换字段，则还会重写SQL语句，然后再发送给后端。
ProxySQL支持两种类型的SQL语句匹配 方式：match_digest和match_pattern。在解释这两种匹配 方 式之前，有必要先解释下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匹配 方式，因为它的效率更高。
在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个和路由、规则相关的表。
这个表对于分析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文本。
这个表的表结构和
stats_mysql_query_digest是完全一样的，只不过每次从这个表中检索数据(随便检索什么，哪怕
where 1=0)，都会重置
stats_mysql_query_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的精确匹配规则已经生效。
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匹配 方式。
和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 ? |+----+----+--------------------+------------------------------------------+
已按预期进行路由。
一个极简单却大有用处的读、写分离功能：将默认路由组设置为写组，然后再插入下面两个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);
但需要注意的是，这样的规则只适用于小环境下的读写分离，对于稍复杂的环境，需要对不同语句进行开销分析，对于开销大的语句需要制定专门的路由规则。在之后的文章中我会稍作分析。
ProxySQL能通过ip、port、client_ip、username、schemaname、digest、match_digest、match_pattern实现不同方式的路由，方式可谓繁多。特别是基于正则匹配的灵活性，使得ProxySQL能满足一些比较复杂的环境。
总的来说，ProxySQL主要是通过digest、match_digest和match_pattern进行规则匹配的。在本文中，只是介绍了匹配规则的基础以及简单的用法，为进军后面的文章做好铺垫。
About Me
 |
 |
