案例说明:
生产中心需对数据库日志建立审计,需要将数据库服务器的日志发送到日志服务器集中存储并建立审计。
适用版本:
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服务,可以建立数据库日志的集中存储及审计。