Maxscale-SQL防火墙(3)

HH
MySQLMaxscale-SQL防火墙(3)已关闭评论10,0854字数 4236阅读14分7秒阅读模式

这篇文章的目的主要是稍微介绍一些Maxscale有SQL防火墙这个功能，具体更多关于Maxscale如何使用SQL防火墙，或配置SQL匹配过滤等功能还得去浏览一下它的手册

https://github.com/mariadb-corporation/MaxScale/tree/2.0.1/Documentation文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

我们经常说禁止线上写没有WHERE的QDL、MDL，SQL防火墙就能很好的做这样的事情。文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

Maxscale的SQL防火墙需要有：文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

  1. 防火墙规则文件
  2. 将规则文件配置进 /etc/maxscale.cnf

实现

  1. 防火墙规则文件
vim /usr/local/maxscale/etc/blacklists.rule
# limit Query times in Table t1 限制 20 秒内 某语句只能查询 5 次 超过则冻结 10 秒
# rule limit_rate_of_queries deny limit_queries 5 20 10
# rule query_regex deny regex '.*select.*from.*t1.*'
#
# users %@% match all rules limit_rate_of_queries query_regex
 
# Deny delete table t1 no WHERE clause 在某一时间段不允许删除语句没有WHERE
rule safe_delete deny no_where_clause at_times 14:40:00-14:47:00 on_queries delete
rule managers_table deny regex '.*[fF][rR][oO][mM].*[tT]1.*'
 
users %@% match all rules safe_delete managers_table
  1. /etc/maxscale.cnf 配置文件主要配置

这边使用Maxscale只读服务来说明配置现象文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/ 

# 配置使用 规则的服务
[Read-Only Service]
...
filters=dbfw-blacklist
 
# 配置规则
[dbfw-blacklist]
type=filter
module=dbfwfilter
action=block
rules=/usr/local/maxscale/etc/blacklists.rule
  1.  /etc/maxscale.cnf 完整配置
[root@normal_11 tmp]# cat /etc/maxscale.cnf 
###################################################
# CREATE USER maxscale@'%' IDENTIFIED BY "123456";
# GRANT replication slave, replication client ON *.* TO maxscale@'%';
# GRANT SELECT ON mysql.* TO maxscale@'%';
# GRANT ALL ON maxscale_schema.* TO maxscale@'%';
# GRANT SHOW DATABASES ON *.* TO maxscale@'%';
# groupadd maxscale
# useradd -g maxscale maxscale
# cd /opt
# tar -zxf maxscale-2.0.1.rhel.7.tar.gz
# ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale
# chown -R maxscale:maxscale /usr/local/maxscale
# mkdir -p /u01/maxscale/{data,cache,logs,tmp}
# mkdir -p /u01/maxscale/logs/{binlog,trace}
# chown -R maxscale:maxscale /u01/maxscale
# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/
# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456
###################################################
 
[maxscale]
# 开启线程个数，默认为1.设置为auto会同cpu核数相同
threads=auto
# timestamp精度
ms_timestamp=1
# 将日志写入到syslog中
syslog=1
# 将日志写入到maxscale的日志文件中
maxlog=1
# 不将日志写入到共享缓存中，开启debug模式时可打开加快速度
log_to_shm=0
# 记录告警信息
log_warning=1
# 记录notice
log_notice=1
# 记录info
log_info=1
# 不打开debug模式
log_debug=0
# 日志递增
log_augmentation=1
 
# 相关目录设置
basedir=/usr/local/maxscale/
logdir=/u01/maxscale/logs/trace/
datadir=/u01/maxscale/data/
cachedir=/u01/maxscale/cache/
piddir=/u01/maxscale/tmp/
 
[server1]
type=server
address=192.168.137.21
port=3306
protocol=MySQLBackend
serv_weight=1
 
[server2]
type=server
address=192.168.137.22
port=3306
protocol=MySQLBackend
serv_weight=3
 
[server3]
type=server
address=192.168.137.23
port=3306
protocol=MySQLBackend
serv_weight=3
 
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
# 监控心态为 10s
monitor_interval=10000
# 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点
detect_stale_master=true
# 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master)
# detect_replication_lag=true
 
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
router_options=slave
# 允许root用户登录执行
enable_root_user=1
# 查询权重
weightby=serv_weight
filters=dbfw-blacklist
 
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
max_slave_connections=100%
# sql语句中的存在变量只指向master中执行
use_sql_variables_in=master
# 允许root用户登录执行
enable_root_user=1
# 允许主从最大间隔(s)
max_slave_replication_lag=3600
filters=Hint
 
[MaxAdmin Service]
type=service
router=cli
 
[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008
 
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
 
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=/u01/maxscale/tmp/maxadmin.sock
port=6603
 
[dbfw-blacklist]
type=filter
module=dbfwfilter
action=block
rules=/usr/local/maxscale/etc/blacklists.rule
  1. 查询结果
HH@192.168.137.11 03:08:26 [(none)]>SELECT * FROM test.t1;
ERROR 1141 (HY000): Access denied for user 'HH'@'192.168.137.11': Permission denied, query matched regular expression.
 
# 日志输出
2016-11-05 15:09:46.045   [5]  info   : (rule_matches): dbfwfilter: rule 'managers_table': regex matched on query

提示：虽然上面的设置以及达到了无WHERE不能执行的目的，当时其实这样跟现实的程序的编写还是有出入了。往往程序员基本都知道为了让查询不报错都会写成 WHERE 1=1，这是一个小技巧啦。当是这样的语句就没办法通过Maxscale来过滤了。文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

期待

还是很之后Maxscale能将WHERE 1=1 也过滤去了，可以通过一个Hint来解决能执行没有WHERE的SQL。文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

昵称: HH文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

QQ: 275258836文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207（新） 微信公众号: ttlsacom)文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

感觉本文内容不错，读后有收获？文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

逛逛衣服店，鼓励作者写出更好文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-sql-firewall/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
HH
  • 本文由 发表于 07/12/2016 00:52:28
  • 转载请务必保留本文链接：https://www.ttlsa.com/mysql/maxscale-sql-firewall/
  • maxscale
  • mysql