KingbaseES数据库案例之---输出数据库日志到syslog服务器

案例说明：
生产中心需对数据库日志建立审计，需要将数据库服务器的日志发送到日志服务器集中存储并建立审计。适用版本： KingbaseES V8R3/R6

案例主机架构：

node201  192.168.1.201   # 数据库主机、syslog客户端
 node202  192.168.1.202   # syslog服务器

一、构建syslog服务器
Syslog服务器可以用作一个网络中的日志监控中心，所有能够通过网络来发送日志的设施(包含了Linux或Windows服务器，路由器，交换机以及其他主机)都可以把日志发送给它。 通过设置一个syslog服务器，可以将不同设施/主机发送的日志，过滤和合并到一个独立的位置，这样使得你更容易地查看和获取重要的日志消息。
Rsyslog 作为标准的syslog守护进程，预装在了大多数的Linux发行版中。在客户端/服务器架构的配置下，rsyslog同时扮演了两种角色：
1)作为一个syslog服务器，rsyslog可以收集来自其他设施的日志信息；
2)作为一个syslog客户端，rsyslog可以将其内部的日志信息传输到远程的syslog服务器。

1、配置syslog服务器

1)syslog配置文件配置

# 配置rsyslogd进程监听的端口，默认使用udp:514
[root@node202 data]# cat /etc/rsyslog.conf
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514

# 配置日志文件存储路径及模版
###KingbaseES####
$template RemoteLogs,"/var/log/%HOSTNAME%/%PROGRAMNAME%.log" *
*.*  ?RemoteLogs
& ~

2)配置服务端接收日志
(SYSLOGD_OPTIONS=“-c 5” 添加“ -r选项”即可，目的可以让服务器能够接受客户端传来的数据。)

[root@node202 data]# cat /etc/sysconfig/rsyslog
# Options for rsyslogd
# Syslogd options are deprecated since rsyslog v3.
# If you want to use them, switch to compatibility mode 2 by "-c 2"
# See rsyslogd(8) for more details
SYSLOGD_OPTIONS="-r -c 5"

3)重启rsyslogd服务

[root@node202 data]# systemctl restart rsyslog
[root@node202 data]# systemctl status rsyslog
● rsyslog.service - System Logging Service
   Loaded: loaded (/usr/lib/systemd/system/rsyslog.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2023-10-19 10:55:20 CST; 4h 13min ago
 Main PID: 649 (rsyslogd)
   CGroup: /system.slice/rsyslog.service
           └─649 /usr/sbin/rsyslogd -n

Oct 19 10:55:19 node202 systemd[1]: Starting System Logging Service...
Oct 19 10:55:20 node202 systemd[1]: Started System Logging Service.
Oct 19 15:09:00 node202 systemd[1]: Started System Logging Service.

4)查看rsyslogd服务监听状态

[root@node202 data]# netstat -antulp|grep 514
tcp        0      0 0.0.0.0:514             0.0.0.0:*               LISTEN      26152/rsyslogd
tcp6       0      0 :::514                  :::*                    LISTEN      26152/rsyslogd
udp        0      0 0.0.0.0:514             0.0.0.0:*                           26152/rsyslogd
udp6       0      0 :::514                  :::*                                26152/rsyslogd

2、rsyslog客户端配置(数据库服务器)

[root@node201 data]# cat /etc/sysconfig/rsyslog
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514

# 配置数据库日志存储路径及日志服务器ip
###KingbaseES####
local0.* /home/kingbase/db/r6_c8/data/sys_log
local0.* @192.168.1.202

---local0 - local7: 用户自定义的消息 (local7 通常被Cisco和Windows 服务器使用)

----配置后，注意重启rsyslogd服务。

二、数据库log输出配置

1、配置数据库服务器log输出
如下所示，指定数据库日志输出到'syslog‘：

# local0 - local7: 用户自定义的消息 (local7 通常被Cisco和Windows 服务器使用)
[kingbase@node201 data]$ cat kingbase.conf|grep syslog
log_destination = 'syslog'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
# These are relevant when logging to syslog:
syslog_facility = 'LOCAL0'
syslog_ident = 'kingbase'
#syslog_sequence_numbers = on
#syslog_split_messages = on

2、重启数据库服务3、查看数据库服务日志信息
如下所示，在数据库sys_log下会有日志文件生成，但是日志的内容会输出到syslog服务器。

[kingbase@node201 sys_log]$ ls -lh
total 20K
-rw------- 1 kingbase kingbase 166 Oct 19 15:18 kingbase-2023-10-19_151836.log
-rw------- 1 kingbase kingbase 166 Oct 19 15:21 kingbase-2023-10-19_152158.log
-rw------- 1 kingbase kingbase 166 Oct 19 15:23 kingbase-2023-10-19_152314.log
-rw------- 1 kingbase kingbase 166 Oct 19 15:24 kingbase-2023-10-19_152449.log
-rw------- 1 kingbase kingbase 166 Oct 19 15:34 kingbase-2023-10-19_153437.log

# 日志文件内容数据到syslog服务器
[kingbase@node201 data]$ cat sys_log/kingbase-2023-10-19_153437.log
2023-10-19 15:34:37.932 CST [4597] LOG:  ending log output to stderr
2023-10-19 15:34:37.932 CST [4597] HINT:  Future log output will go to log destination "syslog".

三、查看syslog服务器数据库日志输出1、数据库日志远程存储路径

[root@node202 node201]# pwd
/var/log/node201
[root@node202 node201]# ls -lh
total 8.0K
-rw------- 1 root root 4.2K Oct 19 15:34 kingbase.log

2、syslog记录的数据库日志
如下所示，在syslog服务器的日志完整记录了数据库日志的输出内容。

[root@node202 node201]# cat kingbase.log
Oct 19 15:34:37 node201 kingbase[3151]: [9-1] 2023-10-19 15:34:37.786 CST [3151] LOG:  received fast shutdown request
Oct 19 15:34:37 node201 kingbase[3151]: [10-1] 2023-10-19 15:34:37.788 CST [3151] LOG:  aborting any active transactions
Oct 19 15:34:37 node201 kingbase[3151]: [11-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  [instance]background writer process (pid 3155) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3151]: [12-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  [instance]walwriter process (pid 3156) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3151]: [13-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  [instance]autovacuum launcher process (pid 3157) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3151]: [14-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  background worker "kwr collector" (PID 3159) exited with exit code 1
Oct 19 15:34:37 node201 kingbase[3151]: [15-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  [instance]background worker "kwr collector" process (pid 3159) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3151]: [16-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  [instance]background worker "ksh writer" process (pid 3160) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3151]: [17-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  [instance]background worker "ksh collector" process (pid 3161) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3151]: [18-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  background worker "logical replication launcher" (PID 3162) exited with exit code 1
Oct 19 15:34:37 node201 kingbase[3151]: [19-1] 2023-10-19 15:34:37.793 CST [3151] LOG:  [instance]background worker "logical replication launcher" process (pid 3162) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3154]: [8-1] 2023-10-19 15:34:37.793 CST [3154] LOG:  shutting down
Oct 19 15:34:37 node201 kingbase[3151]: [20-1] 2023-10-19 15:34:37.799 CST [3151] LOG:  [instance]checkpointer process (pid 3154) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3151]: [21-1] 2023-10-19 15:34:37.800 CST [3151] LOG:  [instance]statistics collector process (pid 3158) detected no errors, freed resources and shutted down.
Oct 19 15:34:37 node201 kingbase[3151]: [22-1] 2023-10-19 15:34:37.800 CST [3151] LOG:  [instance]all child processes have shutted down, kingbase instance will shut down.
Oct 19 15:34:37 node201 kingbase[3151]: [23-1] 2023-10-19 15:34:37.806 CST [3151] LOG:  database system is shut down
Oct 19 15:34:37 node201 kingbase[4597]: [1-1] 2023-10-19 15:34:37.896 CST [4597] LOG:  sepapower extension initialized
Oct 19 15:34:37 node201 kingbase[4597]: [2-1] 2023-10-19 15:34:37.899 CST [4597] LOG:  starting KingbaseES V008R006C008B0014 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
Oct 19 15:34:37 node201 kingbase[4597]: [3-1] 2023-10-19 15:34:37.900 CST [4597] LOG:  listening on IPv4 address "0.0.0.0", port 54323
Oct 19 15:34:37 node201 kingbase[4597]: [4-1] 2023-10-19 15:34:37.900 CST [4597] LOG:  listening on IPv6 address "::", port 54323
Oct 19 15:34:37 node201 kingbase[4597]: [5-1] 2023-10-19 15:34:37.902 CST [4597] LOG:  listening on Unix socket "/tmp/.s.KINGBASE.54323"
Oct 19 15:34:37 node201 kingbase[4597]: [6-1] 2023-10-19 15:34:37.932 CST [4597] LOG:  redirecting log output to logging collector process
Oct 19 15:34:37 node201 kingbase[4597]: [6-2] 2023-10-19 15:34:37.932 CST [4597] HINT:  Future log output will appear in directory "sys_log".
Oct 19 15:34:37 node201 kingbase[4597]: [7-1] 2023-10-19 15:34:37.932 CST [4597] LOG:  ending log output to stderr
Oct 19 15:34:37 node201 kingbase[4597]: [7-2] 2023-10-19 15:34:37.932 CST [4597] HINT:  Future log output will go to log destination "syslog".
Oct 19 15:34:37 node201 kingbase[4599]: [8-1] 2023-10-19 15:34:37.937 CST [4599] LOG:  database system was shut down at 2023-10-19 15:34:37 CST
Oct 19 15:34:37 node201 kingbase[4597]: [8-1] 2023-10-19 15:34:37.944 CST [4597] LOG:  database system is ready to accept connections

四、总结
通过Linux下的syslog服务，可以建立数据库日志的集中存储及审计。