三.实现使用 mysql 集中存储日志数据

实验环境：

: centos7.7(172.20.1.193) --- rsyslog 日志服务器 : centos8(172.20.1.191)--- mysql 数据服务器

• centos7.7(172.20.1.193) --- 日志服务器

[root@localhost ~]# yum install rsyslog-mysql
[root@localhost ~]# rpm -ql rsyslog-mysql
/usr/lib64/rsyslog/ommysql.so
/usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql
[root@localhost ~]# scp /usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql 172.20.1.191:/data/
[root@localhost ~]# vim /etc/rsyslog.conf
#### MODULES ####
...
##########custom##########
$ModLoad ommysql
...
#### RULES ####

...
#########custom rules##########
*.info         :ommysql:172.20.1.191,Syslog,rsyslog,stevenux

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

• centos8(172.20.1.191)--- mysql 服务器

[root@localhost ~]# cat /data/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,
        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
);

MariaDB [Syslog]> source /data/mysql-createDB.sql
MariaDB [Syslog]> GRANT ALL ON Syslog.* TO 'rsyslog'@'172.20.1.193' IDENTIFIED BY 'stevenux';
MariaDB [Syslog]> FLUSH privileges;
MariaDB [Syslog]> SHOW tables;
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+

• centos7.7(172.20.1.193) --- 日志服务器

[root@localhost ~]# logger "hello this is a test message"

centos8(172.20.1.191)--- mysql 服务器
MariaDB [Syslog]> select * from SystemEvents\G
*************************** 4. row ***************************
                ID: 4
        CustomerID: NULL
        ReceivedAt: 2019-12-16 21:22:49
DeviceReportedTime: 2019-12-16 21:22:49
          Facility: 1
          Priority: 5
          FromHost: localhost
           Message: hello this is a test message
        NTSeverity: NULL
        Importance: NULL
       EventSource: NULL
         EventUser: NULL
     EventCategory: NULL
           EventID: NULL
   EventBinaryData: NULL
      MaxAvailable: NULL
         CurrUsage: NULL
          MinUsage: NULL
          MaxUsage: NULL
        InfoUnitID: 1
         SysLogTag: root:
      EventLogType: NULL
   GenericFileName: NULL
          SystemID: NULL
4 rows in set (0.00 sec)

四.实现使用 mysql 集中存储日志数据，loganalyzer 展示数据

• loganalyzer 是用 php 语言实现的日志管理系统，可将 MySQL 数据库的日志用丰富的 WEB 界面方式进行展示 官网

使用环境： : 一台日志服务器，利用上一个案例实现，IP: 172.20.1.193 : 一台数据库服务器，利用上一个案例实现，IP: 172.20.1.191 : 一台当 httpd+php 服务器，并安装 loganalyzer 展示 web 图形，IP: 172.20.1.207

1. 172.20.1.207 安装 php 和依赖包

yum install httpd php-fpm php-mysqlnd  php-gd
# php-gd模块是提供gd图形库的共享模块
systemctl restart httpd php-fpm

2. 172.20.1.207 安装 LogAnalyzer

#从http://loganalyzer.adiscon.com/downloads/ 下载loganalyzer-4.1.8.tar.gz
yum install httpd php php-mysqlnd php-gd
tar xvf loganalyzer-4.1.8.tar.gz
mv loganalyzer-4.1.8/src/ /var/www/html/log
touch /var/www/html/log/config.php
chmod 666 /var/www/html/log/config.php

3. 访问http://172.20.1.207/log初始化