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

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

时间:2024-03-29 18:48:18浏览次数:28  
标签:10 15 19 数据库 kingbase 34 --- syslog 2023

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

标签:10,15,19,数据库,kingbase,34,---,syslog,2023
From: https://www.cnblogs.com/kingbase/p/17923268.html

相关文章

  • 数据库对象大小统计脚本
    获取数据库排名前20的表selectt.table_catalogasdb,n.nspnameasschemaname,c.relname,c.reltuples::numericasrowcount,sys_size_pretty(sys_table_size('"'||nspname||'"."'||relname||......
  • KingbaseES V8R6集群运维案例之---备库register故障
    案例说明:据现场实施人员说,备库执行了clone,启动数据库服务,执行'repmgrstandbyregister'后,无法将备库register到集群。适用版本:KingbaseESV8R6一、问题现象如下图所示,执行'repmgrstandbyregister',register失败:二、问题分析1、repmgrstandbyregister分析如下图所示:......
  • KingbaseES V8R3集群运维案例之---集群启动“DATA_SIZE_DIFF 16 (MB)”故障
    案例说明:为保证集群数据的一致性安全,在主备库的数据相差“DATA_SIZE_DIFF>=16M"以上时,该备库不能参与主备切换,并且通过kingbase_monitor.sh启动集群时,集群将无法启动;本案例对此种故障做了复现,并测试了解决方法。适用版本:KingbaseESV8R3适用版本:KingbaseESV8R3一、案例......
  • KingbaseES V8R3备份恢复案例之---backup_label does not exist in KINGBASE_DATA
    案例说明:在KingbaseESV8R3集群主库执行sys_rman的全备时,出现‘backup_labeldoesnotexistinKINGBASE_DATA’的故障,如下图所示:适用版本:KingbaseESV8R3一、问题现象如下所示,数据库执行sys_rman物理备份:[kingbase@node201bin]$./sys_rman-Usystem-W123456-dtes......
  • KingbaseES集群运维案例之-- V8R3与V8R6集群wal函数应用
    案例说明:KingbaseESV8R3和V8R6集群在通过函数获取wal日志的相关信息时,两个版本的函数名称不同,本案例做了函数应用的对比和总结。适用版本:KingbaseESV8R3/R6一、KingbaseESV8R3相关函数Tips:在V8R3的版本,事务日志名称为xlog。1、查询数据库支持的函数test=#selectpron......
  • kingbaseES V8R6集群运维案例之---配置priority防止failover切换案例
    案例说明:在一主多备的架构中,需要配置一台备库在主备切换时,不能选举为主库。对于repmgr主备切换主库的选择算法如下:Tips:Repmgr选举候选备节点会以以下顺序选举:LSN---->Priority---->Node_ID。系统会先选举一个LSN比较大者作为候选备节点;如LSN一样,会根据Priority优先级进行比......
  • KingbaseES V8R6集群运维案例之---级联备库upstream节点故障
    KingbaseESV8R6集群运维案例之---级联备库upstream节点故障案例说明:在KingbaseESV8R6集群,构建级联备库后,在其upstream的节点故障后,级联备库如何处理?适用版本:KingbaseESV8R6集群架构:案例一:一、配置集群的recovery参数(allnodes)Tips:关闭备库的aut-recovery机制......
  • 鸿蒙开发-web
    鸿蒙开发-UI-图形-页面内动画鸿蒙开发-UI-图形-组件内转场动画鸿蒙开发-UI-图形-弹簧曲线动画鸿蒙开发-UI-交互事件-通用事件鸿蒙开发-UI-交互事件-键鼠事件鸿蒙开发-UI-交互事件-焦点事件鸿蒙开发-UI-交互事件-手势事件文章目录前言一、web组件概述二、web组件......
  • 春秋云镜 CVE-2023-26469
    靶标介绍:Jorani是一款开源的员工考勤和休假管理系统,适用于中小型企业和全球化组织,它简化了员工工时记录、休假请求和审批流程,并提供了多语言支持以满足不同地区的需求。在Jorani1.0.0中,攻击者可以利用路径遍历来访问文件并在服务器上执行代码。访问靶场链接查询CVE-2......
  • 春秋云镜 CVE-2023-27179
    靶标介绍:GDideesCMSv3.9.1及更低版本被发现存在本地文件泄露漏洞,漏洞通过位于/_admin/imgdownload.php的filename参数进行利用。访问实验地址通过靶场介绍得知,漏洞通过位于/_admin/imgdownload.php的filename参数进行利用。构造访问目录。http://eci-2zeh4jwpx8......