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

数据分析

2021-01-21

273

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;
```

欢迎添加微信,互相学习↑↑↑ -_-

发表评论

全部评论:0条

白老虎

programming is not only to solve problems, ways to think