网站建设需要代码人力资源培训与开发
目录
一、环境信息
二、general log的用途
三、general log相关参数介绍
四、LInux环境模拟实验
1、查看参数配置
2、开启general log
3、输入测试SQL
4、查看文件级别general log
5、改为表级别general log
6、再次输入测试SQL
7、查看gbase.general_log
一、环境信息
| 名称 | 值 |
| CPU | Intel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz |
| 操作系统 | CentOS Linux release 7.9.2009 (Core) |
| 内存 | 3G |
| 逻辑核数 | 2 |
| Gbase8a版本 | 8.6.2-R43.34.27468a27 |
二、general log的用途
记录数据库曾经执行过的 SQL 语句,相较于审计日志记录的内容更加粗一些，并且是全部记录，没有针对性，例如需要记录哪些类型的SQL，耗时多少的SQL需要记录等都没有相关配置。
审计日志的相关内容介绍请参考之前的博客
《南大通用数据库-Gbase-8a-学习-05-通过审计日志抓取Sql、Trace日志查看执行计划》。
三、general log相关参数介绍
| 参数名 | 描述 |
| general_log | ON为开启，OFF为关闭。开启和关闭general log的开关。 |
| general_log_file | 当log_output为FILE时，general log输出的日志路径。 |
| log_output | log_output分为FILE和TABLE，表示日志的输出形式，是存在文件中，还是存在数据字典表中。此参数如果改动，会影响其他日志如审计日志的生成方式，请注意。 |
四、LInux环境模拟实验
1、查看参数配置
gbase> show variables like '%general_log%';
+------------------+------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------+
| general_log | OFF |
| general_log_file | /opt/gcluster/log/gcluster/gclusterd.log |
+------------------+------------------------------------------+
2 rows in set (Elapsed: 00:00:00.03)gbase> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (Elapsed: 00:00:00.01)2、开启general log
gbase> set global general_log=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.19)3、输入测试SQL
gbase> use czg;
Query OK, 0 rows affected (Elapsed: 00:00:00.06)gbase> show tables;
+--------------------------+
| Tables_in_czg |
+--------------------------+
| a |
| alldbvoidrate |
| b |
| czg |
| czg_test |
| d_admin_kpi_code |
| hash_tab |
| hash_tb_like |
| jointable |
| jointable1 |
| moon |
| moon_copy |
| nodedatamap |
| sg_t_loadconfig_incr |
| sg_t_loadconfig_incr_odm |
| strtab |
| sun |
| sun_gbk |
| t1 |
| t_policy_ext_info |
| test |
| test20230302 |
| test_12_05 |
| test_2023 |
| test_20230720 |
| test_table |
| test_table_1 |
| test_table_2 |
| test_table_3 |
| testtab |
| testtab_copy |
| tmp |
+--------------------------+
32 rows in set (Elapsed: 00:00:00.00)gbase> select * from test;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (Elapsed: 00:00:00.21)gbase> select * from testhahaha;
ERROR 1146 (42S02): Table 'czg.testhahaha' doesn't exist4、查看文件级别general log
[gbase@czg2 ~]$ tail -f /opt/gcluster/log/gcluster/gclusterd.log
Time Id Command Argument
230904 9:20:05 10 Connect root@192.168.142.12 on 10 Query set gcluster_lock_timeout=0,gbase_fast_update=FALSE10 Query set general_log=110 Query SET character_set_results = utf8mb410 Query set SELF global general_log=1
230904 9:20:27 9 Query use czg10 Init DB czg
230904 9:20:43 9 Query show tables
230904 9:20:51 9 Query select * from test
230904 9:21:10 9 Query select * from testhahaha5、改为表级别general log
gbase> set global log_output = 'table';
Query OK, 0 rows affected (Elapsed: 00:00:00.00)gbase> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (Elapsed: 00:00:00.00)6、再次输入测试SQL
同3、输入测试SQL。
7、查看gbase.general_log
gbase> select * from gbase.general_log where event_time >= current_date();
+---------------------+--------------------------------+-----+-----------+-----------+--------------+--------------------------------------------------------------------+
| event_time | user_host | uid | thread_id | server_id | command_type | argument |
+---------------------+--------------------------------+-----+-----------+-----------+--------------+--------------------------------------------------------------------+
| 2023-09-04 09:31:11 | root[root] @ [192.168.142.12] | 1 | 10 | 0 | Query | set SELF global general_log = 1 |
| 2023-09-04 09:31:27 | root[root] @ localhost [] | 1 | 9 | 0 | Query | use czg |
| 2023-09-04 09:31:27 | root[root] @ [192.168.142.12] | 1 | 10 | 0 | Init DB | czg |
| 2023-09-04 09:31:35 | root[root] @ localhost [] | 1 | 12 | 0 | Connect | root@localhost on |
| 2023-09-04 09:31:45 | root[root] @ localhost [] | 1 | 12 | 0 | Query | select * from gbase.general_log where event_time >= current_date() |
| 2023-09-04 09:32:01 | root[root] @ localhost [] | 1 | 9 | 0 | Query | select * from test |
| 2023-09-04 09:32:04 | root[root] @ localhost [] | 1 | 9 | 0 | Query | select * from testhahha |
| 2023-09-04 09:32:10 | root[root] @ localhost [] | 1 | 12 | 0 | Query | select * from gbase.general_log where event_time >= current_date() |
+---------------------+--------------------------------+-----+-----------+-----------+--------------+--------------------------------------------------------------------+
8 rows in set (Elapsed: 00:00:00.00)