1. 通过loganalyzer展示数据库中的日志

1. 通过loganalyzer展示数据库中的日志

 

 

1.在rsyslog服务器上安装连接mysql模块相关的程序包

 

[root@CentOS8 ~]# yum install rsyslog-mysql -y

 

[root@CentOS8 ~]# rpm -ql rsyslog-mysql

/usr/lib/.build-id

/usr/lib/.build-id/e6

/usr/lib/.build-id/e6/aa0e40c19a2e0524d72780eee3b1698684cbe7

/usr/lib64/rsyslog/ommysql.so

/usr/share/doc/rsyslog/mysql-createDB.sql

 

#查看sql脚本文件内容

[root@CentOS8 ~]# cat /usr/share/doc/rsyslog/mysql-createDB.sql

CREATE DATABASE Syslog;

USE Syslog;

CREATE TABLE SystemEvents

(

        ID int unsigned not null auto_increment primary key,

        CustomerID bigint,

        ReceivedAt datetime NULL,

        DeviceReportedTime datetime NULL,

        Facility smallint NULL,

        Priority smallint NULL,

        FromHost varchar(60) NULL,

        Message text,

        NTSeverity int NULL,

        Importance int NULL,

        Importance int NULL,

        EventSource varchar(60),

        EventUser varchar(60) NULL,

        EventCategory int NULL,

        EventID int NULL,

        EventBinaryData text NULL,

        MaxAvailable int NULL,

        CurrUsage int NULL,

        MinUsage int NULL,

        MaxUsage int NULL,

        InfoUnitID int NULL ,

        SysLogTag varchar(60),

        EventLogType varchar(60),

        GenericFileName VarChar(60),

        SystemID int NULL

);

 

CREATE TABLE SystemEventsProperties

(

        ID int unsigned not null auto_increment primary key,

        SystemEventID int NULL ,

        ParamName varchar(255) NULL ,

        ParamValue text NULL

);

 

 

#将sql脚本复制到数据库服库上

[root@CentOS8 ~]# rsync /usr/share/doc/rsyslog/mysql-createDB.sql 10.0.0.18:/data

The authenticity of host '10.0.0.18 (10.0.0.18)' can't be established.

ECDSA key fingerprint is SHA256:4UtU2z0fFNEI5Fjdhf+NufMSk5DMBuFlYbWfPSbpYTM.

Are you sure you want to continue connecting (yes/no/[fingerprint])? yes

Warning: Permanently added '10.0.0.18' (ECDSA) to the list of known hosts.

root@10.0.0.18's password:

 

2. MySQL Server 配置

[root@centos8 ~]#yum install mariadb-server

[root@CentOS8 ~]# systemctl enable --now mariadb.service

 

#在mariadb数据库服务器上创建相关数据库和表，并授权rsyslog能连接至当前服务器

[root@CentOS8 ~]# mysql -uroot

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.3.28-MariaDB MariaDB Server

 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

MariaDB [(none)]> quit

Bye

 

导入数据库

[root@CentOS8 data]# ls

mysql-createDB.sql

[root@CentOS8 data]# mysql<mysql-createDB.sql

[root@CentOS8 data]# mysql -e "show databases;"

+--------------------+

| Database           |

+--------------------+

| Syslog             |

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

 

创建授权用户

[root@CentOS8 data]# mysql

 

MariaDB [(none)]> create user rsyslog@'10.0.0.%' identified by 'Mmagedu0!';

Query OK, 0 rows affected (0.001 sec)

 

MariaDB [(none)]> grant all on Syslog.* to rsyslog@'10.0.0.%';

Query OK, 0 rows affected (0.001 sec)

 

MariaDB [(none)]> select user,host from mysql.user;

+---------+-----------+

| user    | host      |

+---------+-----------+

| rsyslog | 10.0.0.%  |

| root    | 127.0.0.1 |

| root    | ::1       |

| root    | centos8   |

| root    | localhost |

+---------+-----------+

5 rows in set (0.001 sec)

 

 

3 配置日志服务器将日志发送至指定数据库

#配置rsyslog将日志保存到mysql中

[root@centos8 ~]#vim /etc/rsyslog.conf

#

####MODULES####

#在 MODULES 语言下面，如果是 CentOS 8 加下面行

module(load="ommysql")

#在 MODULES 语言下面，如果是 CentOS 7，6 加下面行

$ModLoad ommysql

#在RULES语句块加下面行的格式

#facility.priority   :ommysql:DBHOST,DBNAME,DBUSER, PASSWORD

*.info :ommysql:10.0.0.18,Syslog,rsyslog,magedu

[root@centos8 ~]#systemctl restart rsyslog.service

 

 

4 测试

#在日志服务器上生成日志

[root@centos8 ~]#logger "this is a test log"

#在数据库上查询到上面的测试日志

MariaDB [none>use Syslog;

MariaDB [Syslog]> show tables;

+------------------------+

| Tables_in_Syslog       |

+------------------------+

| SystemEvents           |

| SystemEventsProperties |

+------------------------+

2 rows in set (0.000 sec)

 

MariaDB [Syslog]> select count(*) from SystemEvents;

+----------+

| count(*) |

+----------+

|       15 |

+----------+

1 row in set (0.001 sec)

 

 

 

5.LogAnalyzer 服务器配置

5.1安装 php和相关软件包

在10.0.0.28主机上安装httpd, php和相关软件包

[root@centos8 ~]#yum -y install httpd php-fpm php-mysqlnd php-gd

[root@centos8 ~]#systemctl enable --now httpd php-fpm

 

5.1安装 LogAnalyzer

在10.0.0.28主机上安装LogAnalyzer

#从http://loganalyzer.adiscon.com/downloads/ 下载loganalyzer-4.1.10.tar.gz

[root@centos8 ~]#tar xvf loganalyzer-4.1.10.tar.gz

[root@centos8 ~]#mv loganalyzer-4.1.10/src/ /var/www/html/log

[root@centos8 ~]#touch /var/www/html/log/config.php

[root@centos8 ~]#chmod 666 /var/www/html/log/config.php

 

5.3基于 web 页面初始化

访问http://10.0.0.28/log 实现初始化

选择：MySQL Native, Syslog Fields, Monitorware

密码用上面数据库设置的密码

 

 

 

 

 

 

 

 

 

 

 

5.4 安全加强

[root@centos8 ~]#chmod 644 /var/www/html/log/config.php