这篇文章的目的主要是稍微介绍一些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/
- 防火墙规则文件
- 将规则文件配置进 /etc/maxscale.cnf
实现
- 防火墙规则文件
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
- /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
- /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
- 查询结果
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/
评论