Mysql数据库请求语句审核,日志,阻断,结果替换和SQL请求语句修改中间件maxscale部署测试

shw3588 2019-08-12 PM 103℃ 0条
#MariaDB MaxScale can be built on any system that meets the requirements. The main requirements are as follows:
#CMake version 2.8 or later (Packaging requires version 2.8.12 or later)
#GCC version 4.4.7 or later
#SQLite3 version 3.3 or later
#OpenSSL
#Bison 2.7 or later
#Flex 2.5.35 or later
#libuuid
#GNUTLS

yum install cmake gcc bison openssl flex 
yum install sqlite-devel

#SQLite3 version 3.3 or later
sqlite3 -version

yum install libuuid gnutls


#wget http://www.isres.com/file/maxscale-2.0.3-1.centos.6.x86_64.rpm
wget https://downloads.mariadb.com/MaxScale/2.3.9/centos/6/x86_64/maxscale-2.3.9-1.centos.6.x86_64.rpm

rpm -ivh maxscale-2.0.3-1.centos.6.x86_64.rpm
#备份配置文件
cp /etc/maxscale.cnf  /etc/maxscale.cnf.bak

大多数人用来做读写分离,本人用来做 Mysql数据库请求语句审核,日志,阻断,结果替换和SQL请求语句修改

配置参考

# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md

# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md

[maxscale]
threads=4
log_debug=1 #


# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#

[server1]
type=server
address=192.168.10.3
port=3306
protocol=MySQLBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Monitors/MySQL-Monitor.md

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1
user=user
passwd=password
monitor_interval=10000

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md

[Read-Only Service]
type=service
router=readconnroute
servers=server1
user=user
passwd=password
#router_options=slave
#filters=DatabaseFirewall
#filters=ShwFilter
filters=SelectLog|ShwConfigRows|ShwMaskResult|ShwFilter

[MaxAdmin Service]
type=service
router=cli



[DatabaseFirewall]
type=filter
module=dbfwfilter
rules=/home/dbfw_block.conf

[ShwFilter]
type=filter
module=regexfilter
match=^[\S\s]*select[\S\s]*$
replace=select * from ($0) as c limit 1
#replace=tee /public_html/mysqllog/mysql.log 

#限定返回结果集的行数或排序大小
[ShwConfigRows]
type=filter
module=maxrows
#强制limit 超过限定值将返回空
max_resultset_rows=100
#256K
max_resultset_size=256000

#对输出结果的列做安全过滤替换,处理敏感信息比如手机号
[ShwMaskResult]
type=filter
module=masking
#以下四项设置是包含要过滤的列不终止,只替换继续显示
prevent_function_usage=false
check_user_variables = FALSE
check_unions = FALSE
check_subqueries = FALSE

rules=/var/lib/maxscale/shwmaskresult.conf

[SelectLog]
type=filter
module=qlafilter
#match=.*
log_type=unified,session
#session 写入特定于会话的文件 unified |为所有会话使用一个文件
append=true 
#追加 重启不会覆盖旧日志文件
flush=true 
#实时写入日志 不缓存
options=ignorecase,extended
exclude=INFORMATION_SCHEMA|show status|show tables|SET PROFILING
filebase=/var/lib/maxscale/maxscale_query.log


# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

[Read-Only Listener]
type=listener
address=0.0.0.0
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=default

/home/dbfw_block.conf

#早前单独配置、没有使用
rule burangcha deny regex '^.*from(((?!limit).))*$'
users %@% match all rules burangcha

/var/lib/maxscale/shwmaskresult.conf

{
    "rules": [
        {
            "replace": {
                "column": "mail",
        "match":".{4}(?=.{4}$)"
            },
            "with": {
        "fill":"*"
            }
        }
    ]
}

使用行数限定的情况下,如果没加limit,如果数据量大直接查询还是会卡,因为要全部查出来后规则才能计算行数是否超过
会出现navicat展开没有看到表,原因是限定行设置太小,表的个数大于限定行

官方文档:https://github.com/mariadb-corporation/MaxScale/tree/2.3/Documentation

标签: 中间件

评论啦~


    captcha
*为减少垃圾评论,评论提交后会审核,审核后才显示