Startrocks审计日志插件

一、审计日志插件简介

StartRocks中的所有SQL的审计日志都保存在本地的fe/log/fe.audit.log，没有入库保存，后续为了方便统计SQL任务的执行情况，该插件基于Stream Load的方式将审计日志解析导入至对应库表中。

二、安装及使用

1.创建对应数据库及表

mysql> create database starrocks_audit_db__;
mysql> create table starrocks_audit_db__.starrocks_audit_tbl__
(
    query_id varchar(48) comment "查询唯一ID",
    time datetime not null comment "查询开始时间",
    client_ip varchar(32) comment "客户端IP",
    user varchar(64) comment "查询用户名",
    db varchar(96) comment "查询所在数据库",
    state varchar(8) comment "查询状态：EOF，ERR，OK",
    query_time bigint comment "查询执行时间（毫秒）",
    scan_bytes bigint comment "查询扫描的字节数",
    scan_rows bigint comment "查询扫描的记录行数",
    return_rows bigint comment "查询返回的结果行数",
    cpu_cost_ns bigint comment "查询CPU耗时（纳秒）",
    mem_cost_bytes bigint comment "查询消耗内存（字节）",
    stmt_id int comment "SQL语句的增量ID",
    is_query tinyint comment "SQL是否为查询（1或0）",
    frontend_ip varchar(32) comment "执行该语句的FE IP",
    stmt string comment "SQL原始语句",
    digest varchar(32) comment "SQL指纹"
) engine=OLAP
duplicate key(query_id, time, client_ip)
partition by range(time) ()
distributed by hash(query_id) buckets 1
properties(
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-30",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "1",
    "dynamic_partition.enable" = "true",
    "replication_num" = "3"
);

2.修改配置文件

解压对应zip包

unzip auditloader.zip

解压后auditloader目录中含有三个文件：

auditloader.jar：插件代码打包的核心jar包

plugin.conf：插件配置文件，需根据集群信息修改

plugin.properties：插件属性文件，无需修改

修改plugin.conf文件

### plugin configuration

# The max size of a batch, default is 50MB
#大小为50MB
max_batch_size=52428800

# The max interval of batch loaded, default is 60 seconds
#最大间隔60s
max_batch_interval_sec=60

# the max stmt length to be loaded in audit table, default is 4096
max_stmt_length=4096

# StarRocks FE host for loading the audit, default is 127.0.0.1:8030.
# this should be the host port for stream load
#对应http port
frontend_host_port=127.0.0.1:8030

# Database of the audit table
database=starrocks_audit_db__

# Audit table name, to save the audit data.
table=starrocks_audit_tbl__

# StarRocks user. This user must have LOAD_PRIV to the audit table.
user=root

# StarRocks user's password
password=

重新打包，并分发至集群FE节点上

zip -q -m -r auditloader.zip auditloader.jar plugin.conf plugin.properties

3.安装插件

mysql> INSTALL PLUGIN FROM "/opt/StarRocks/auditloader.zip";

查看是否安装成功 SHOW PLUGINS\G，每隔60s或者积攒50MB就会向对应审计日志表中Stream Load一次，时间大小可以在plugin.conf中修改。

查看是否有语句记录，以用户root为例，进行sql类操作，如果查询后有信息，说明审计插件生效。

SELECT * FROM starrocks_audit_db__.starrocks_audit_tbl__ where user='root'\G