GBase 8a
运维管理
文章
GBase8a审计日志主要SQL记录
发表于2025-01-02 10:51:4233次浏览0个评论
with audit_log as (select taskid, date(start_time) sql_date, time_to_sec(query_time) query_time, rows, db, sql_text
from gbase.audit_log
where cast(user as varchar) not in ('root','gbase','dbperf','dbmon')
and cast(sql_type as varchar) in ('DQL','DML')
and cast(status as varchar) = 'SUCCESS'
and date(start_time)>= to_date('2025-01-01','YYYY-MM-DD')
and date(start_time)<= to_date('2025-02-01','YYYY-MM-DD'))
select sql_date, sum(query_time) sum_query_time, max(rows)
from audit_log
group by sql_date
order by sql_date desc;
with audit_log as (select taskid, date(start_time) sql_date, time_to_sec(query_time) query_time, rows, db, sql_text
from vc00001.gclusterdb.audit_log_express
where user not in ('root','gbase','dbperf','dbmon')
and sql_type in ('DQL','DML')
and status = 'SUCCESS'
and date(start_time)>= to_date('2025-01-01','YYYY-MM-DD')
and date(start_time)<= to_date('2025-02-01','YYYY-MM-DD'))
select sql_date, sum(query_time) sum_query_time, max(rows)
from audit_log
group by sql_date
order by sql_date desc;
1.根据审计日志获取时间范围内的SQL；
2.根据SQL获取相关表的建表语句；
3.根据SQL获取select执行计划；
4.获取时间范围内的always、gcluster、gnode
gbase> desc gbase.audit_log;
+-------------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------------------+-----------------------------+
| thread_id | int(10) unsigned | NO | | NULL | |
| taskid | bigint(20) | NO | | NULL | |
| start_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| end_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| user_host | mediumtext | NO | | NULL | |
| uid | bigint(20) | NO | | NULL | |
| user | mediumtext | NO | | NULL | |
| host_ip | mediumtext | NO | | NULL | |
| query_time | time | NO | | NULL | |
| rows | bigint(20) | NO | | NULL | |
| vc_id | varchar(64) | NO | | NULL | |
| db | varchar(512) | NO | | NULL | |
| table_list | mediumtext | NO | | NULL | |
| sql_text | mediumtext | NO | | NULL | |
| sql_type | mediumtext | NO | | NULL | |
| sql_command | mediumtext | NO | | NULL | |
| operators | mediumtext | NO | | NULL | |
| status | mediumtext | NO | | NULL | |
| conn_type | mediumtext | NO | | NULL | |
+-------------+------------------+------+-----+---------------------+-----------------------------+
19 rows in set (Elapsed: 00:00:00.01)
gbase> desc gclusterdb.audit_log_express;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| hostname | varchar(64) | YES | | NULL | |
| thread_id | int(11) | YES | | NULL | |
| taskid | bigint(20) | YES | | NULL | |
| start_time | datetime | YES | | NULL | |
| uid | bigint(20) | YES | | NULL | |
| user | varchar(16) | YES | | NULL | |
| host_ip | varchar(32) | YES | | NULL | |
| query_time | time | YES | | NULL | |
| rows | bigint(20) | YES | | NULL | |
| vc_id | varchar(64) | YES | | NULL | |
| db | varchar(512) | YES | | NULL | |
| table_list | varchar(4096) | YES | | NULL | |
| sql_text | varchar(8191) | YES | | NULL | |
| sql_type | varchar(16) | YES | | NULL | |
| sql_command | varchar(32) | YES | | NULL | |
| operators | varchar(256) | YES | | NULL | |
| status | varchar(16) | YES | | NULL | |
| conn_type | varchar(16) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
18 rows in set (Elapsed: 00:00:00.01)
#### 登录数据库（成功）
[gbase@GBASEDATA01 gcluster]$ gccli -ututu -ptu
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase>
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 91
taskid: 0
start_time: 2024-11-07 20:10:25
end_time: 2024-11-07 20:10:25
user_host: tutu[tutu] @ localhost []
uid: 321
user: tutu
host_ip:
query_time: 00:00:00.003443
rows: 0
vc_id:
db:
table_list:
sql_text: Connect
sql_type: OTHERS
sql_command: Connect
operators:
status: SUCCESS
conn_type: ODBC
1 row in set (Elapsed: 00:00:00.00)
#### 登录数据库（失败）
[gbase@GBASEDATA01 gcluster]$ gccli -ututu -ptua
ERROR 1045 (28000): Access denied for user 'tutu'@'localhost' (using password: YES)
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 127
taskid: 0
start_time: 2024-11-07 20:28:43
end_time: 2024-11-07 20:28:43
user_host: [tutu] @ localhost []
uid: 0
user: tutu
host_ip:
query_time: 00:00:00.002152
rows: 0
vc_id:
db:
table_list:
sql_text: Connect
sql_type: OTHERS
sql_command: Connect
operators:
status: FAILED
conn_type: ODBC
1 row in set (Elapsed: 00:00:00.00)
#### use数据库
gbase> use tdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 91
taskid: 786544
start_time: 2024-11-07 20:12:59
end_time: 2024-11-07 20:12:59
user_host: tutu[tutu] @ localhost []
uid: 321
user: tutu
host_ip:
query_time: 00:00:00.001180
rows: 0
vc_id: vc00001
db: tdb
table_list: WRITE: ; READ: ; OTHER: ;
sql_text: use tdb
sql_type: OTHERS
sql_command: OTHERS
operators:
status: SUCCESS
conn_type: ODBC
1 row in set (Elapsed: 00:00:00.00)
#### 查询表数据
gbase> select * from t1;
+------+------+------------+------+------+---------------------+------+
| a | b | c | d | e | f | g |
+------+------+------------+------+------+---------------------+------+
| 0 | 1 | DMD | kds | dmd | 2013-04-01 10:23:01 | 1.10 |
| 0 | 3 | DMD | cj | dmd | 2013-04-01 10:23:01 | 2.10 |
| 1 | 3 | DMD | lm | dmd1 | 2013-04-01 10:23:01 | 2.20 |
| 1 | 4 | DMD | zx | dmd2 | 2013-04-01 10:23:01 | 2.30 |
+------+------+------------+------+------+---------------------+------+
4 rows in set (Elapsed: 00:00:00.05)
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 91
taskid: 786546
start_time: 2024-11-07 20:13:43
end_time: 2024-11-07 20:13:43
user_host: tutu[tutu] @ localhost []
uid: 321
user: tutu
host_ip:
query_time: 00:00:00.049917
rows: 4
vc_id: vc00001
db: tdb
table_list: WRITE: ; READ: `vc00001`.`tdb`.`t1`; OTHER: ;
sql_text: select * from t1
sql_type: DQL
sql_command: SELECT
operators:
status: SUCCESS
conn_type: ODBC
1 row in set (Elapsed: 00:00:00.01)
gbase> select sleep(15) from tdb.t1;
+-----------+
| sleep(15) |
+-----------+
| 0 |
| 0 |
| 0 |
| 0 |
+-----------+
4 rows in set (Elapsed: 00:00:15.03)
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 91
taskid: 786575
start_time: 2024-11-07 20:23:43
end_time: 2024-11-07 20:23:58
user_host: tutu[tutu] @ localhost []
uid: 321
user: tutu
host_ip:
query_time: 00:00:15.029281
rows: 4
vc_id: vc00001
db: tdb
table_list: WRITE: ; READ: `vc00001`.`tdb`.`t1`; OTHER: ;
sql_text: select sleep(15) from tdb.t1
sql_type: DQL
sql_command: SELECT
operators:
status: SUCCESS
conn_type: ODBC
1 row in set (Elapsed: 00:00:00.00)
### 写入数据（失败）
gbase> insert into tdb.t1 values (2,3,'','','','','');
ERROR 1292 (22007): Incorrect datetime value: '' for column 'f' at row 1
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 91
taskid: 786553
start_time: 2024-11-07 20:15:10
end_time: 2024-11-07 20:15:10
user_host: tutu[tutu] @ localhost []
uid: 321
user: tutu
host_ip:
query_time: 00:00:00.016874
rows: 0
vc_id: vc00001
db: tdb
table_list: WRITE: `vc00001`.`tdb`.`t1`; READ: ; OTHER: ;
sql_text: insert into tdb.t1 values (2,3,'','','','','')
sql_type: DML
sql_command: INSERT
operators:
status: FAILED
conn_type: ODBC
1 row in set (Elapsed: 00:00:00.00)
### 写入数据（成功）
gbase> insert into tdb.t1 values (2,3,'','','',null,null);
Query OK, 1 row affected (Elapsed: 00:00:00.31)
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 91
taskid: 786556
start_time: 2024-11-07 20:16:26
end_time: 2024-11-07 20:16:26
user_host: tutu[tutu] @ localhost []
uid: 321
user: tutu
host_ip:
query_time: 00:00:00.310332
rows: 1
vc_id: vc00001
db: tdb
table_list: WRITE: `vc00001`.`tdb`.`t1`; READ: ; OTHER: ;
sql_text: insert into tdb.t1 values (2,3,'','','',null,null)
sql_type: DML
sql_command: INSERT
operators:
status: SUCCESS
conn_type: ODBC
1 row in set (Elapsed: 00:00:00.01)
### 删除数据（成功）
gbase> delete from t1 where a='2';
Query OK, 1 row affected (Elapsed: 00:00:00.37)
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 91
taskid: 786561
start_time: 2024-11-07 20:19:32
end_time: 2024-11-07 20:19:32
user_host: tutu[tutu] @ localhost []
uid: 321
user: tutu
host_ip:
query_time: 00:00:00.368704
rows: 1
vc_id: vc00001
db: tdb
table_list: WRITE: `vc00001`.`tdb`.`t1`; READ: ; OTHER: ;
sql_text: delete from t1 where a='2'
sql_type: DML
sql_command: DELETE
operators: WHERE
status: SUCCESS
conn_type: ODBC
1 row in set (Elapsed: 00:00:00.01)
### 删除表（成功）
gbase> drop table tdb.t2;
Query OK, 0 rows affected (Elapsed: 00:00:00.23)
gbase> select * from gbase.audit_log where cast(user as varchar) not in ('root','gbase','dbperf') order by start_time desc limit 1\G;
*************************** 1. row ***************************
thread_id: 113
taskid: 0
start_time: 2024-11-07 20:21:23
end_time: 2024-11-07 20:21:23
user_host: tutu[tutu] @ [10.168.195.101]
uid: 321
user: tutu
host_ip: 10.168.195.101
query_time: 00:00:00.019904
rows: 0
vc_id:
db:
table_list:
sql_text: Connect
sql_type: OTHERS
sql_command: Connect
operators:
status: SUCCESS
conn_type: CAPI
1 row in set (Elapsed: 00:00:00.00)评论
登录后才可以发表评论
热门帖子
- 12025-12-01浏览数：182759
- 22023-05-09浏览数：25044
- 42023-09-25浏览数：18519
- 52020-05-11浏览数：17526