[Doris集群迁移 -2] ProxySQL配置SQL审计

45人浏览 / 0人评论

ProxySQL核心功能

  • 镜像JDBC流量 允许SQL流量分发到另一个集群, 不影响正在使用的集群提供服务

  • sql审计, 不符合要求的SQL不执行

ProxySQL安装和初始化配置

  • 初始化proxysql日志配置
    	SET mysql-eventslog_filename='queries.log';
    	SET mysql-auditlog_filename='queries_audit.log';
    	SET mysql-eventslog_default_log=1;
    	SET mysql-eventslog_format=2;
    	SET mysql-multiplexing='false'
    	set mysql-forward_autocommit="true";
    
    	LOAD MYSQL VARIABLES TO RUNTIME;
    	SAVE MYSQL VARIABLES TO DISK;
    

添加Doris FE机器

  • 将doris中的用户添加到ProxySQL中
    		insert into mysql_servers(hostgroup_id, hostname, port) values(1, 'fe ip', 9030);  
    		load mysql servers to runtime;
    		save mysql servers to disk;
    

添加Doris FE用户

	insert into mysql_users(username, password, active, default_hostgroup, backend,frontend) values('doris用户', '*63246348CB0B4085797FB70952B0A05AB2126749', 1, 1, 1, 1);

添加审计规则

禁止设置doris的查询超时时间内
```
	insert into
mysql_query_rules(
    rule_id,
    active,
    match_digest,
    match_pattern,
    negate_match_pattern,
    destination_hostgroup,
    mirror_hostgroup,
    error_msg,
    ok_msg,
    apply) values(50, 1, NULL, ".*set query_timeout.*", 0, 1, 2,NULL, "doris禁止设置",1);
			
		load mysql query rules to runtime;
		save mysql query rules to disk;
```

全部评论