GBase8a将审计日志转储到express表

GBase8a将审计日志转储到express表

注：
1、测试数据库版本：9.5.3.27.20_patch.15e9b2e26
2、GBase8a的审计日志是存在在每个gcluster节点的，没办法均匀存储，会导致某个节点磁盘使用过高
3、sql_text查询方法：select * from gbaudit.audit_log where sql_text like ‘%insert%’;

#!/bin/bash
# 0 0 * * * source $HOME/.gbase_profile && /home/gbase/dump_audit/dump2gbase_audit.sh 定时任务执行
# Version : 1.1.1   update date: 2024-07-31

set -o errexit
# 操作系统用户
dbaUser=gbase
home_path=/home/gbase/dump_audit
# 数据库可操作用户
dbUser=gbase
# echo -n 'gbase20110531' | base64
dbapassword=`echo -n 'Z2Jhc2UyMDExMDUzMQ==' | base64 -d`
log_file=$home_path/dump2gbase_audit.log
# 管理节点ip
gc_list="192.168.122.21 192.168.122.22 192.168.122.23"

# dba用户判断
if [ `whoami` != "$dbaUser" ];then
	echo "Please use $dbaUser run me!" 
	exit 1
fi

# 创建存储的库表
gccli -u$dbUser -p$dbapassword -e '
create database if not exists gbaudit;
CREATE TABLE if not exists gbaudit."audit_log" (
  "ip" varchar(15) not null,
  "thread_id" int(10)  NOT NULL,
  "taskid" bigint(20) NOT NULL,
  "start_time" datetime NOT NULL ,
  "end_time" datetime NOT NULL ,
  "user_host" varchar(255) NOT NULL,
  "uid" bigint(20) NOT NULL,
  "user" varchar(255) NOT NULL,
  "host_ip" varchar(255) NOT NULL,
  "query_time" time NOT NULL,
  "rows" bigint(20) NOT NULL,
  "vc_id" varchar(64) NOT NULL,
  "db" varchar(512) NOT NULL,
  "table_list" varchar(255) NOT NULL,
  "sql_text" mediumtext NOT NULL,
  "sql_type" varchar(255) NOT NULL,
  "sql_command" varchar(255) NOT NULL,
  "operators" varchar(255) NOT NULL,
  "status" varchar(255) NOT NULL,
  "conn_type" varchar(255) NOT NULL
) ;
'

echo -e "\n\n\nTime now: `date +'%F %T'`" >> $log_file

for ip in $gc_list;
do
        echo "----------------- ${ip} -----------------" >> $log_file
        echo -e "The current ip is ${ip} ..." >> $log_file
        gccli -u$dbUser -p$dbapassword -Dgbaudit -vvv -e "
set _gbase_query_path = 1;
insert into gbaudit.audit_log select '${ip}',aud.* from gbase.audit_log aud;
FLUSH TABLES gbase.audit_log;
TRUNCATE SELF gbase.audit_log;
" >> $log_file
done