ProxySQL技术实践分享

昵称真不好起

一、ProxySQL基本介绍1.1 前言ProxySQL是 MySQL 的高性能、高可用性、协议感知代理。以下为结合主从复制对ProxySQL读写分离、黑白名单、路由规则等做些基本测试。1.2 基本介绍先简单介绍下ProxySQL及其功能和配置，主要包括：最基本的读/写分离，且方式有多种；可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由，规则很灵活；动态加载配置，即绝大部分的配置可以在线修改，但有少部分参数还是需要重启来生效；可缓存查询结果。虽然缓存策略比较简陋，但实现了基本的缓存功能；过滤危险的SQL，增加防火墙等功能；提供连接池、日志记录、审计日志等功能；1.2.1 请求流程流量从客户端发出 → ProxySQL进行处理转发 → 后端处理 → ProxySQL的前端连接 → 返回客户端的基本流程1.2.2 核心功能读写分离：可查询走从库，写入走主库简单Sharding：ProxySQL的sharding是通过正则匹配来实现的，对于需要拆分SQL以及合并SQL执行结果的不能支持，所以写了简单sharding连接池管理：常规功能，为了提高SQL执行效率。多路复用：主要优化点在后端mysql连接的复用，对比smart client，中间层不仅对前端建连也会对后端建连，可自行控制后端连接的复用逻辑。流量管控：kill连接和kill query；whitelist配置。高可用：底层mysql，如果从库挂了，自动摘除流量；主库挂了暂不处理。proxysql自身高可用，提供cluster的功能，cluster内部会自行同步元数据以及配置变更信息。查询缓存：对username+schema+query的key进行缓存，设置ttl过期，不适合写完就查的场景，因为在数据在未过期之前可能是脏数据。动态配置：大部分的配置可动态变更，先load到runtime，在save到disk，通过cluster的功能同步到其他的节点。流量镜像：同一份流量可以多出写入，但是并不保证mirror的流量一定成功。SQL改写：在query rules中配置replace规则，可以对指定的SQL进行改写。1.2.3 多层的配置系统ProxySQL的配置有三层，且绝大部分参数均可以通过动态配置并热加载到运行层，并通过save命令保存到持久层。RUNTIME层runtime层是即时生效的；表示当前生效的配置，该层的配置无法直接修改，必须要从下一层即MEMORY层load进来；MEMORY层memory层是保存在内存中，但不立即生效的；该层通常通过admin端口进来后，直接修改。DISK层|CONFIG FILE层disk层表示持久化层，config file表示从配置文件中加载的；不同层的加载和保存# 以修改mysql servers为例,以下为不同层之间的同步关系## 将修改层的配置加载到运行层load mysql servers to runtime;## 将运行层的配置同步到修改层save mysql servers to memroy;## 将持久层的配置加载到memory层load mysql servers to memory; ## 将修改层的配置保存到持久层save mysql servers to disk; ## 从配置文件中将配置信息加载到修改层load mysql servers from config;1.2.4 第一次启动配置文件第一次启动时，会从proxysql.cnf配置文件中加载相关配置项来初始化memory和runtime层，并最终持久化到sqlite数据库中；如果想重新初始化proxysql，可以在启动时使用--initial选项来强制初始化proxysql，这会将sqlite数据库重置为最原始的状态，并在必须要时重命名sqlite的数据文件持久层disk的配置再次启动时，如果配置文件指定的datadir目录下sqlite数据文件存在，则会将sqlite中的配置项读取到内存，最终会加载到runtime层。此时不再读取proxysql.cnf配置文件的相关配置。1.2.5 ProxySQL相关表配置ProxySQL后端采用sqlite对配置进行存储，有以下几个库：下列库中：main表示runtime运行层，disk表示持久化库，stats表示统计数据库，monitor存储监控相关表，stats_history表示统计历史库。3306-Admin > show databases;+-----+---------------+--------------------------------------------------+| seq | name | file |+-----+---------------+--------------------------------------------------+| 0 | main | || 2 | disk | /var/lib/data/3306/proxysql.db || 3 | stats | || 4 | monitor | || 5 | stats_history | /var/lib/data/3306/proxysql_stats.db |+-----+---------------+--------------------------------------------------+5 rows in set (0.00 sec)global_variables表全局配置表，用于存储admin接口、mysql接口、监控等相关参数的配置，其相关配置可以通过以下两种方式进行修改：## 直接通过SQL修改表3306-Admin > update global_variables set variable_value='xxx' where variable_name='xxx';## 通过set方式3306-Admin > set variable_name='xxx';mysql_servers表定义后端backends，如MySQL Server或者使用MySQL协议的其他实例，同一个hostgroup组中的backends具有相同的逻辑功能；主要字段hostgroup_id：指定后端MySQL所在的hostgroup_id组，同一个后端MySQL可以分属不同的组；hostname,port: 指定后端MySQL的主机和端口；status: 后端MySQL的状态，online表示在线提供服务，即正常状态；offline_soft表示非强制下线，即不再接受新的请求，但可以处理当前已建立的会话请求；offline_hard表示强制下线，即强制关闭当前的所有会话且不再接受新请求；shunned表示后端MySQL临时不可用，如因为连接数过多或者主从延迟超过设置的阈值等；weight：backend的权重，权重越大，则同hostgroup中被选择的概率越大；max_connections：设置ProxySQL能连接后端MySQL的最大连接；max_replication_lag：设置后端MySQL允许的最大延迟时间；mysql_users表用于存储访问ProxySQL的相关用户，并最终连接到后端的MySQL上；主要字段username, password：连接ProxySQL来访问后端MySQL的用户名和密码。需注意：用户需要在后端MySQL实例中创建且能确保ProxySQL所在的机器IP已加白；在mysql_users表中配置的用户不能再被用到mysql-monitor_username监控配置用户上；active：指定用户是否是激活有效的，如果设置为0，则该用户不会被加载到runtime层；default_hostgroup：指定用户默认访问的主机组，如果没有配置查询规则，则SQL统一会分流到默认主机组；推荐默认组设置为写组；transaction_persistent：表示是否持久化事务，设置为1表示开启一个事务后，该事务的所有操作均在同一个hostgroup中操作完成，其会忽略设置的任何查询规则；frontend,backend：指定用户是属于前端和后端；前端表示用户通过ProxySQL进行连接，后端表示ProxySQL连接后端MySQL；通常添加一个用户后，在runtime层，ProxySQL默认会将该用户同时设定为前端和后端；max_connections：设定该用户的最大连接；mysql_replication_hostgroups表定义使用异步同步 /半同步的传统主从复制中各个hostgroup的对应关系。主要字段writer_hostgroup：指定写组id，如设置10表示所有主机组为10的后端MySQL将会处理写请求；reader_hostgroup: 指定读组id，如设置20表示所有主机组为20的后端MySQL将会处理读请求；check_type: 判断MySQL只读的类型，ProxySQL也是通过该只读属性来自动判断添加的后端MySQL是属于读组还是写组；检查类型支持单一参数值的检查，也支持一些参数值逻辑运算后检查，支持的类型如下：read_only 默认值，通过read_only参数判断innodb_read_onlysuper_read_onlyread_only|innodb_read_only 通过read_only&innodb_read_onlymysql_query_rules表定义路由策略和属性;主要字段rule_id：全局唯一的规则id，规则是按照rule_id的顺序进行处理的；active：表示该规则是否生效。只有该字段值为1的规则才会加载到runtime数据结构；username：用户名筛选，当设置为非NULL值时，只有匹配的用户建立的连接发出的查询才会被匹配；schemaname：schema筛选，当设置为非NULL值时，只有当连接使用schemaname作为默认schema时，该连接发出的查询才会被匹配；flagIN, flagOUT：这两个字段允许我们创建"链式规则"(chains of rules)，即一个规则接一个规则的链式处理；apply：表示是否apply该规则；client_addr：通过源地址进行匹配；从1.4.7开始，支持按网段进行匹配，如192.168.56.% 匹配C网段；match_digest：通过正则表达式匹配digest；match_pattern：通过正则表达式匹配查询语句的文本内容；destination_hostgroup：将匹配到的查询路由到该主机组。当然设置transaction_persistent=1的事务除外；cache_ttl：设置查询结果缓存的ttl失效时间；error_msg：当查询被阻塞时，则该列设置的值将会返回给客户端。通常用于黑名单功能，即如果匹配该规则后，如果其error_msg不为空，则客户端的操作将会接受到error_msg设置的错误信息；二、ProxySQL相关功能介绍2.1 读写分离ProxySQL官方demo演示了三种读写分离的方式：使用不同的端口进行读写分离、使用正则表达式进行通用的读写分离、使用正则和摘要进行更智能的读写分离。最后一种是针对特定业务进行的优化调整，也可将其归结为第二种方式，下边分别进行测试。2.1.1 使用不同端口的读写分离基础环境准备写端口通过6401端口，读端口通过6402端口，从而通过不同端口来实现读写分离功能。## 在mysql servers表中插入一主一从两个server的信息## 其中master的hostgroup为10,其余两个slave的hostgroup为20INSERT INTO mysql_servers(hostgroup id,hostname, port)VALUES(10,'db.test.master',3306);INSERT INTO mysql_servers(hostgroup id,hostname, port)VALUES(20,'db.test.slave1',3306);## 插入连接proxysq1的用户INSERT INTO mysql_users(username,password,default_hostgroup)VALUES('proxysql_test','proxysql',10);## 基于入口端添加路由INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)VALUES (1,1,6401,10,1), (2,1,6402,20,1);## 设置读写分离的端口3306-Admin > SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';## 持久化配置3306-Admin > SAVE MYSQL SERVERS TO DISK;3306-Admin > SAVE MYSQL QUERY RULES TO DISK;3306-Admin > SAVE MYSQL VARIABLES TO DISK;## 因端口的修改不能动态加载,需重启ProxySQL实例$ sudo sysetmctl restart proxysql测试验证所有来自于6401端口连接的查询都将被发送到hostgroup为10的组中；所有来自于6402端口连接的查询都将被发送到hostgroup为20的组中;配置缺点该配置需要应用有内置的读写分离功能，以便区分读和写；通常应用只配置一个单独的入口来连接ProxySQL，这对很多业务来说是不可接受的。2.1.2 基于通用正则规则的读写分离开启eventlogeventslog可以记录用户在通过ProxySQL访问时的所有SQL语句，可以用其观察具体的路由规则，以下为开启方法：## 设置eventslog的文件名称3306-Admin > SET mysql-eventslog_filename='queries.log';## 设置eventslog的格式为json3306-Admin > SET mysql-eventslog_format=2;## 加载到runtime层并保存到持久层load mysql variables to runtime;save mysql variables to disk;## 清空查询规则表delete from mysql_query_rules;## 延续上面配置,添加查询规则记录eventslog,如下表示对所有的操作都记录INSERT INTO mysql_query_rules(active, match_digest, log,apply)VALUES(1,'.',1,0);## 添加基于正则的路由规则INSERT INTO mysql_query_rules(active,match_digest,destination_hostgroup,apply)VALUES(1,'^SELECT.*FOR UPDATE ,10,1),(1,'^SELECT',20,1);## 加载配置到运行层并保存配置到持久层LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;测试预测所有的SELECT FOR UPDATE语句将被路由到hostgroup为10的组中；其他的SELECT语句将被路由到hostgroup为 20的组中；除以上外的其他语句，将被路由到连接用户默认的hostgroup组中；测试验证## 小写查询（select请求忽略大小写，查看eventslog，该SQL路由到读组20）mysql> select * from sbtest1 limit 1; ## 大写查询（select请求忽略大小写，查看eventslog，该SQL路由到读组20）mysql> SELECT * from sbtest1 limit 1; ## 大小写混合查询（select请求忽略大小写，查看eventslog，该SQL路由到读组20）mysql> SelECT * from sbtest1 limit 1; ## 有多余字符查询（select请求需以select作为开头，查看eventslog，该SQL路由到默认组10）mysql> /* hints*/SelECT * from sbtest1 limit 1; ## 包含空格的查询（ProxySQL会对包含空格的SQL进行处理，查看eventslog，该SQL路由到读组20）mysql> SelECT * from sbtest1 limit 1;2.2 hostgroup组相关2.2.1 组故障场景测试当ProxySQL中某个hostgroup组中所有的servers均故障后，ProxySQL的表现；测试步骤## 1. 查看runtime下的servers的各个状态（各个hostgroup组的server均为ONLINE）3306-Admin > select hostgroup_id,hostname,port,status,max_replication_lag from runtime_mysql_servers;+--------------+-----------------+-------+--------+---------------------+| hostgroup_id | hostname | port | status | max_replication_lag |+--------------+-----------------+-------+--------+---------------------+| 10 | db.test.master | 3306 | ONLINE | 100 || 20 | db.test.slave1 | 3306 | ONLINE | 100 |+--------------+-----------------+-------+--------+---------------------+## 2. 手动将hostgroup_id=20的读组所有servers进行关闭## 3. 查看runtime下的servers的各个状态(hostgroup_id的server状态已变为SHUNNED)3306-Admin > select hostgroup_id,hostname,port,status,max_replication_lag from runtime_mysql_servers;+--------------+-----------------+-------+---------+---------------------+| hostgroup_id | hostname | port | status | max_replication_lag |+--------------+-----------------+-------+---------+---------------------+| 10 | db.test.master | 3306 | ONLINE | 100 || 20 | db.test.slave1 | 3306 | SHUNNED | 100 |+--------------+-----------------+-------+---------+---------------------+## 4. 发起查询语句(已配置了读写分离规则)## 因为根据读写分离路由规则，select查询会路由到hostgroup_id为20的读组中，但因该组的所有servers均处于SHUNNED，故查询报错mysql> select * from sbtest1 where id=1; ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 20 after 10001ms测试结论如果ProxySQL根据路由规则路由到的hostgroup组中所有的servers都不可用，则操作将会失败；对于读写分离一主一从架构来说，如果从库有故障，则仍然会影响线上故障，这是不可接受的；解决方式之一：对于一主一从架构来说，可以将主从server均配置到读组中，并调整主库的优先级为最低，当从库不可用时，依然能保证读的请求不受影响；2.2.2 同组server不同权重值影响测试目的测试在同hostgroup组下的servers的weight权重有差异情况下的路由情况；测试步骤## 1.更新mysql_servers的信息(读组中包含一主一从两个servers)3306-Admin > select hostgroup_id,hostname,port,status,weight,max_replication_lag from mysql_servers;+--------------+-----------------+-------+--------+--------+---------------------+| hostgroup_id | hostname | port | status | weight | max_replication_lag |+--------------+-----------------+-------+--------+--------+---------------------+| 10 | db.test.master | 3306 | ONLINE | 1 | 100 || 20 | db.test.slave1 | 3306 | ONLINE | 2 | 100 || 20 | db.test.master | 3306 | ONLINE | 1 | 100 |+--------------+-----------------+-------+--------+--------+---------------------+## 2. 使用sysbench发起oltp_read_only的只读请求，使读的请求全部落到hostgoup_id=20的读组中## 3. 分别对读组中db.test.slave1 的Server进行权重的更新## 4. 对查询日志进行统计分析测试结果不断修改20组下的db.test.slave1读库权重weight值：权重为2时，该读库的请求占比为66.828%；权重为100时，该读库的请求占比为98.997%；权重为10000时，该读库的请求占比为99.989%；权重为10000000最大值时，该读库的请求占比为99.99991%；测试结论同组内servers的询选择与servers的weight值正向相关：同组内servers的weight权重值越大，则其被轮询访问的概率越大对于一主一从架构，从读写分离角度来看，如果想要实现绝大多数的读路由到从库上，则需要增大从库的weight权重，使其跟主库的权重差值越大越好；生产建议直接配置最大值10000000；ProxySQL默认的负载轮询算法为random-weighted，通过该轮询算法可知，轮询与权重值的设置正向相关，但也并不能完全避免一些低权重的servers不被路由到；2.2.3 组server延迟场景测试目的测试在hostgroup组下的servers的延迟配置情况；影响因素mysql_servers.max_replication_lagserver的延迟最大允许值，当判定延迟超过设置的该值后，则从库会处于SHUNNED状态。monitor_replication_lag_use_percona_heartbeat动态配置，可以使用pt-heartbeat作为延迟的判断，默认为空表示使用show slave status来判断。如配置成percona.heartbeat表示在percona库下的heartbeat表monitor_slave_lag_when_null当复制延迟检查发现Seconds_Behind_Master=NULL时，使用该参数设置一个值用来假设当前延迟的时间，默认为60秒；测试步骤monitor_replication_lag_use_percona_heartbeat为空为空表示使用show slave status来判断主从延迟## 1.设置max_replication_lag为100秒,monitor_slave_lag_when_null默认值60## 2. 停掉主从相关线程或者子线程mysql> stop slave;## 3. 观察服务状态(从库server始终处于ONLINE状态)## 4. 设置max_replication_lag为59秒,monitor_slave_lag_when_null默认值60## 5. 停掉主从相关线程或者子线程mysql> stop slave;## 6. 观察服务状态(从库server立刻变为SHUNNED状态)## 结论:因主从复制被中断，当通过show slave status获取Seconds_Behind_Master时为空,则设置延迟值为60,如果小于max_replication_lag设置的值则不延迟，反之则延迟。monitor_replication_lag_use_percona_heartbeat不为空## 1.设置max_replication_lag为100秒,monitor_slave_lag_when_null默认值60,配置参数值为percona.heartbeat## 2. 使用pt-heartbeat做心跳pt-heartbeat -D percona --user dba_user --password xxx -h master_ip -P 3306 --update --daemonize --create-table --check-read-only## 3. 停掉主从相关线程或者子线程mysql> stop slave;## 4. 观察服务状态(从库server超100秒后处于SHUNNED状态)## 5. 将主从恢复,且等延迟追上,设置配置参数为一个不存在的心跳表3306-Admin > set mysql-monitor_replication_lag_use_percona_heartbeat='percona.heartbeat1';3306-Admin > load mysql variables to runtime;## 6. 停掉主从相关线程或者子线程mysql> stop slave;## 7. 等待100秒后，发现从库仍处于ONLINE状态(查询mysql_server_replication_lag_log表提示如下错误)Table 'percona.heartbeat1' doesn't exist## 8. 将主从恢复,且等延迟追上,并配置正确的心跳表,但取消monitor_username用户对心跳表的查询权限mysql> revoke select on percona.heartbeat from monitor@'192.168.56.101';## 9. 停掉主从相关线程或者子线程mysql> stop slave;## 10. 等待100秒后发现从库仍处于ONLINE状态,延迟日志表中出现如下错误:SELECT command denied to user 'monitor'@'192.168.56.101' for table 'heartbeat'## 结论:主从复制被中断，当ProxySQL通过pt-heartbeat表作为延迟判断时,因心跳值持续增长，当达到设置的最大延迟时则从库处于SHUNNED状态不可用;如果参数设置为一个不存在的心跳表,则无法判断延迟情况(repl_lag为NULL),此时即使主从复制中断从库依然可用;如果监控后端MySQL的用户没有心跳表的查询权限,则仍然无法判断延迟情况,表现同上;测试总结ProxySQL可以通过show slave status、也提供通过pt-heartbeat工具来判断主从的延迟情况；可以根据不同的故障场景进行不同的配置：在MySQL无高可用、主库宕机业务仍然能追求读时，可以设置mysql-monitor_slave_lag_when_null的值小于max_replication_lag，且不依赖pt-heartbeat心跳表；2.3 黑白名单介绍ProxySQL 可以通过路由规则来支持IP或网段的黑名单和白名单功能，从ProxySQL2.0.9开始还启用了防火墙来简化类似功能。2.3.1 黑名单实现对黑名单的支持很简单，如果想禁用某个IP或网段的访问，则可以通过如下方式进行禁用测试步骤## 1. 延续之前的配置，对mysql_query_rules进行清空delete from mysql_query_rules;## 2. 插入一条黑名单insert into mysql_query_rules (rule_id,active,client_addr,error_msg,apply)values(3,1,'192.168.216.146','this ip is banned!',1);## 3. 加载配置load mysql query rules to run;测试结果通过192.168.216.146机器连接到ProxySQL，在执行SQL时，返回this ip is banned! 错误。测试结论ProxySQL的黑名单功能是通过mysql_query_rules.error_msg来设置的，如果mysql_query_rules.error_msg不为空，则当匹配该规则后，操作就会出现error_msg，从而实现黑名单功能。2.3.2 白名单实现链式规则的介绍在mysql_query_rules表中，有两个特殊字段"flagIN"和"flagOUT"，它们分别用来定义规则的入口和出口，从而实现链式规则(chains of rules)。链式规则的路由当入口值flagIN设置为0时，表示开始进入链式规则；当语句匹配完当前规则后，则记下当前规则的flagOUT值，如果flagOUT值非空(NOT NULL)，则为该语句打上flagOUT标记。如果该规则的apply字段值不是1，则继续向下匹配；如果apply字段值为1，则进行apply并结束匹配；如果语句的flagOUT标记和下一条规则的flagIN值不同，则跳过该规则，继续向下匹配。直到匹配到flagOUT=flagIN的规则，则匹配该规则，该规则是链式规则中的另一条规则；直到某规则的apply字段设置为1，或者已经匹配完所有规则，则最后一次被评估的规则将直接生效；测试步骤# 1. 清理mysql_query_rules表delete from mysql_query_rules; ## 插入白名单及相关链式规则## 2. 插入eventslog记录的所有日志路由规则(rule_id为1表示记录所有操作语句)insert into mysql_query_rules (rule_id,active, match_digest, log,apply) VALUES (1,1,'.',1,0); ## 3. 插入由client_addr指定ip的白名单路由规则(设置apply为0)insert into mysql_query_rules(rule_id,active,client_addr,flagOUT,apply) values(2,1,'192.168.56.%',10,0);insert into mysql_query_rules(rule_id,active,client_addr,flagOUT,apply) values(3,1,'192.168.58.100',10,0); ## 4. 插入链式规则中的SQL查询路由规则(其flagIN要与白名单规则的flagOUT匹配,并设置apply为1)insert into mysql_query_rules (rule_id,active,flagIN,match_digest,destination_hostgroup,apply)values(4,1,10,'^SELECT.*FOR UPDATE ,10,1);insert into mysql_query_rules (rule_id,active,flagIN,match_digest,destination_hostgroup,apply)values(5,1,10,'^SELECT',20,1); ## 5. 插入非白名单拒绝的路由规则(对于非白的IP走到该规则时,返回拒绝连接)INSERT INTO mysql_query_rules (rule_id,active, match_digest, error_msg, apply) VALUES (6,1,'.','not allow to connect db',1); ## 6. 加载新的路由规则到runtimeload mysql query rules to runtime;## 7. 查询规则+---------+--------+----------+----------------+-------------------------+--------+---------+--------+--------------+------------------------+-----------------------+-----+-------+| rule_id | active | username | client_addr | error_msg | flagIN | flagOUT | digest | match_digest | match_pattern | destination_hostgroup | log | apply |+---------+--------+----------+----------------+-------------------------+--------+---------+--------+--------------+------------------------+-----------------------+-----+-------+| 1 | 1 | NULL | NULL | NULL | 0 | NULL | NULL | . | NULL | NULL | 1 | 0 || 2 | 1 | NULL | 192.168.56.% | NULL | 0 | 10 | NULL | NULL | NULL | NULL | NULL | 0 || 3 | 1 | NULL | db01.test.net | NULL | 0 | 10 | NULL | NULL | NULL | NULL | NULL | 0 || 4 | 1 | NULL | NULL | NULL | 10 | NULL | NULL | NULL | ^SELECT .* FOR UPDATE$ | 10 | NULL | 1 || 5 | 1 | NULL | NULL | NULL | 10 | NULL | NULL | NULL | ^SELECT | 20 | NULL | 1 || 6 | 1 | NULL | NULL | not allow to connect db | 0 | NULL | NULL | . | NULL | NULL | NULL | 1 |+---------+--------+----------+----------------+-------------------------+--------+---------+--------+--------------+------------------------+-----------------------+-----+-------+6 rows in set (0.00 sec)测试验证## 在192.168.56.1加白的机器上执行select查询语句(执行成功并返回查询结果)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"# 查看命中规则(命中rule_id为1、2、5的规则,符合预期)3306-Admin > select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1 | 1 || 2 | 1 || 3 | 0 || 4 | 0 || 5 | 1 || 6 | 0 |+---------+------+6 rows in set (0.00 sec)## 在192.168.100.100非加白的机器上执行select查询语句(执行失败,提示错误)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"ERROR 1148 (42000) at line 1: not allow to connect db# 查看命中规则(命中rule_id为1、6的规则,符合预期)3306-Admin > select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 1 | 2 || 2 | 1 || 3 | 0 || 4 | 0 || 5 | 1 || 6 | 1 |+---------+------+6 rows in set (0.00 sec)## 在域名为db01.test.net的机器上执行select查询语句(执行失败,提示错误)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"ERROR 1148 (42000) at line 1: not allow to connect db测试结论通过mysql_query_rules可以通过client_addr结合读写分离规则做相关黑白名单的路由；client_addr支持IP和标准网段的授权，但不支持域名；说明：查询规则是根据rule_id由小到大进行匹配，如果想继续添加白名单，则其rule_id需要小于读写分离的规则rule_id；建议将本测试用例中rule_id为4、5、6的三条更新为更大的值，如同时加100，则新添加的白名单rule_id可以在[4,103]之间选择。2.3.3 防火墙功能如果查询限制ip、定向的根据指纹进行路由，则通过mysql_query_rules可能需要定义很多复杂的规则，从ProxySQL2.0.9开始，启用了防火墙功能。其简化了这类的需求配置。防火墙的两张表mysql_firewall_whitelist_users设置防火墙用户的表，一个用户由username和client_address作为唯一主键；主要字段active：表示是否是否生效；username：指定ProxySQL中mysql_users表定义的用户名；client_address：表示连接ProxySQL的客户端IP，为空表示匹配任意ip；需注意其只支持ip，暂不支持子网段；mode：OFF模式表示允许定义的username@client_address用户执行任意的查询，完全忽略mysql_firewall_whitelist_rules中定义的规则；DETECTING模式下，允许用户查询，但当执行的SQL指纹不在mysql_firewall_whitelist_rules中时，则会在错误日志中输出一条告警；PROTECTING模式下，只允许用户执行在mysql_firewall_whitelist_rules中定义的SQL指纹语句，如果不在该表里，则将会报错；mysql_firewall_whitelist_rules定义防火墙SQL指纹规则，一条规则由username、client_address、schemaname、flagIN、digest共同组成；如果mysql_firewall_whitelist_users中有用户匹配执行的SQL，且其mode为DETECTING或者PROTECTING模式，则将会在该表中查找SQL指纹规则，以决定执行的SQL是否会被执行；说明：通常建议对已经运行一段时间的业务SQL进行收集整理，挑选出受信SQL后存放到该表中。INSERT INTO mysql_firewall_whitelist_rules(active, username, client_address, schemaname, flagIN, digest, comment)SELECT DISTINCT 1, username, client_address, schemaname, 0, digest, '' FROM stats_history.history_mysql_query_digest;如果该表为空，则在PROTECTING模式下，将拒绝任何SQL的执行。防火墙相关参数mysql-firewall_whitelist_enabled是否开启防火墙。mysql-firewall_whitelist_errormsg用来返回给执行被阻止SQL的客户端信息。测试防火墙白名单功能测试DETECTING模式## 1. 开启防火墙功能3306-Admin > set mysql-firewall_whitelist_enabled=1;3306-Admin > load mysql variables to runtime;## 2. 添加用户和ip白名单到mysql_firewall_whitelist_users表中(使用DETECTING模式),同时不设置mysql_firewall_whitelist_rules3306-Admin > INSERT INTO mysql_firewall_whitelist_users(active, username, client_address, mode,comment) values(1,'user_test', '192.168.56.100', 'DETECTING', '');## 添加一条包含网段的白名单3306-Admin > INSERT INTO mysql_firewall_whitelist_users(active, username, client_address, mode,comment) values(1,'user_test', '192.168.58.%', 'DETECTING', '');## 3. 加载防火墙配置到运行层3306-Admin > load mysql firewall to runtime;## 4. 在192.168.56.100上执行查询语句(能正常查询，但在proxysql的日志上出现告警)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"## 告警logQuery_Processor.cpp:1814:process_mysql_query(): [WARNING] Firewall detected unknown query with digest 0x02033E45904D3DF0 from user user_test@192.168.56.100## 5. 在192.168.56.101上执行查询语句(无法正常查询，未加白出现如下错误)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"ERROR 1148 (42000) at line 1: Firewall blocked this query## 6. 在192.168.58.100上执行查询语句(无法正常查询，虽然按网段加白但不生效)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"ERROR 1148 (42000) at line 1: Firewall blocked this query测试PROTECTING模式## 1. 将模式更改为PROTECTING模式3306-Admin > update mysql_firewall_whitelist_users set mode='PROTECTING';## 2. 加载防火墙配置到运行层3306-Admin > load mysql firewall to runtime;## 3. 在192.168.56.100上执行查询语句(无法正常查询，出现错误。因为只有匹配mysql_firewall_whitelist_rules表中设置的SQL指纹SQL才能被执行)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"ERROR 1148 (42000) at line 1: Firewall blocked this query## 4. 在192.168.56.101上执行查询语句(无法正常查询，因未加白)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"ERROR 1148 (42000) at line 1: Firewall blocked this query## 5. 在192.168.58.100上执行查询语句(无法正常查询，虽然按网段加白但不生效)$ /usr/bin/mysql -uuser_test -pxxxx -hxxx.xxx.xxx.xx -P6032 -e "select * from sbtest.sbtest1 limit 1"ERROR 1148 (42000) at line 1: Firewall blocked this query测试结论防火墙功能比直接在mysql_query_rules中设置查询规则要简单一些，其能很方便的实现白名单功能，但因为暂不支持网段的设置，目前还有些缺陷。三 总结本文通过ProxySQL主要功能介绍以及应用案例演示，包括：读写分离、黑白名单、路由规则、主从延迟等，使读者更了解ProxySQL。总体来说，ProxySQL是一款功能强大、易于配置和管理的MySQL代理，可以帮助我们提高MySQL服务的性能和可用性。四 参考连接https://proxysql.com/documentation/https://www.cnblogs.com/hahaha111122222/p/16305988.htmlhttps://www.cnblogs.com/Alpes/p/15217774.htmlhttps://www.cnblogs.com/gered/p/15868767.html