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服务，可以建立数据库日志的集中存储及审计。
