CentOS 7.6-日志服务器

1. 配置MariaDB

查看MariaDB是否安装

[root@centos-7-tem ~]# cd /
[root@centos-7-tem /]# rpm -qa | grep mariadb    
mariadb-libs-5.5.60-1.el7_5.x86_64

安装MariaDB服务

[root@centos-7-tem /]# yum -y install mariadb mariadb-server    
[root@centos-7-tem /]# rpm -qa | grep mariadb
mariadb-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64
mariadb-libs-5.5.68-1.el7.x86_64

[root@centos-7-tem /]# systemctl start mariadb.service
[root@centos-7-tem /]# systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

[root@centos-7-tem /]# cd /root/
//设置MariaDB的密码
[root@centos-7-tem ~]# /bin/mysql_secure_installation    
Set root password? [Y/n] y //<– 是否设置root用户密码，输入y并回车或直接回车
New password://<– 设置root用户的密码123456
Re-enter new password: //<– 再输入一次设置的密码123456
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y //<– 是否删除匿名用户,生产环境建议删除，所以直接回车
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y //<– 是否禁止root远程登录,根据自己的需求选择y并回车,建议禁止
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y //<– 是否删除test数据库,直接回车

 - Dropping test database...
   ... Success!
 - Removing privileges on test database...
   ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y //<– 是否重新加载权限表，直接回车
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

//输入：mysql -uroot –p123456,进入MariaDB
[root@centos-7-tem ~]# mysql -uroot -p123456 
//创建rsyslog数据库（用来存放LogAnalyzer系统的配置信息），字符编码设置为utf-8
MariaDB [(none)]> create database rsyslog character set utf8 collate utf8_bin; 
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rsyslog            |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> grant all privileges on rsyslog.* to 'rsyslog'@'localhost' identified by 'rsyslog';flush privileges;    //为rsyslog数据库创建本地用户rsyslog，设置密码为rsyslog，权限为全部权限
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit;
Bye

//安装rsyslog的MySQL扩展程序包rsyslog-mysql
[root@centos-7-tem ~]# yum -y install rsyslog-mysql  
//将rsyslog的MySQL表导入创建的rsyslog数据库
[root@rsyslog-server ~]# mysql -uroot -p < /usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql    
Enter password:123456

[root@centos-7-tem ~]# mysql -uroot -p123456
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Syslog             |
| mysql              |
| performance_schema |
| rsyslog            |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use Syslog;
MariaDB [Syslog]> show tables;
MariaDB [Syslog]> grant all privileges on Syslog.* to 'rsyslog'@'%' identified by 'rsyslog';flush privileges;    //为Syslog数据库创建本地用户rsyslog，设置密码为rsyslog，权限为全部权限
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
MariaDB [Syslog]> ALTER TABLE SystemEvents ADD FromIP VARCHAR(60) DEFAULT NULL AFTER FromHost;
MariaDB [Syslog]> exit;
Bye

2. 配置rsyslog

[root@centos-7-tem ~]# vim /etc/rsyslog.conf

#### MODULES ####

# The imjournal module bellow is now used as a message source instead of imuxsock.

$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imjournal # provides access to the systemd journal
#$ModLoad imklog # reads kernel messages (the same are read from journald)
#$ModLoad immark  # provides --MARK-- message capability
$template insertpl,"insert into SystemEvents (Message, Facility, FromHost, FromIP, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', '%fromhost-ip%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL

$ModLoad ommysql
*.*:ommysql:localhost,Syslog,rsyslog,rsyslog;insertpl

# 根据客户端的IP单独存放主机日志在不同目录，rsyslog需要手动创建

$template RemoteLogs,"/var/log/rsyslog/%fromhost-ip%/%syslogtag%_%$YEAR%-%$MONTH%-%$DAY%-%$hour%:%$minute%.log"

# 排除本地主机IP日志记录，只记录远程主机日志

:fromhost-ip, !isequal, "127.0.0.1" ?RemoteLogs

# 忽略之前所有的日志，远程主机日志记录完之后不再继续往下记录

& stop

去除如下内容前面的#：

$ModLoad imudp

$UDPServerRun 514

$ModLoad imtcp

$InputTCPServerRun 514

[root@centos-7-tem ~]# systemctl restart rsyslog.service
[root@centos-7-tem ~]# systemctl enable rsyslog.service

3. 配置LAMP

//安装LAMP环境
[root@centos-7-tem ~]# yum -y install httpd php php-mysql php-gd    
//创建相应的目录用来存放loganalyzer的解压文件和运行后生成的日志
[root@centos-7-tem ~]# mkdir -p /var/www/html/log    
[root@centos-7-tem ~]# mkdir -p /var/log/httpd/log
[root@centos-7-tem ~]# cd /opt
[root@centos-7-tem opt]# wget https://download.adiscon.com/loganalyzer/loganalyzer-4.1.11.tar.gz --no-check-certificate
[root@centos-7-tem opt]# tar -zxvf loganalyzer-4.1.11.tar.gz
[root@centos-7-tem opt]# cd loganalyzer-4.1.11
[root@centos-7-tem loganalyzer-4.1.11]# cp -r src/* /var/www/html/log
[root@centos-7-tem loganalyzer-4.1.11]# cp -r contrib/* /var/www/html/log
[root@centos-7-tem loganalyzer-4.1.11]# cd /var/www/html/log/
[root@centos-7-tem log]# chmod +x configure.sh secure.sh
[root@centos-7-tem log]# ./configure.sh
[root@centos-7-tem log]# ./secure.sh
[root@centos-7-tem log]# touch config.php
[root@centos-7-tem log]# chmod 666 config.php
[root@centos-7-tem log]# chown -R apache.apache *
[root@centos-7-tem log]# systemctl start httpd && systemctl enable httpd && systemctl status httpd

[root@rsyslog-server log]# vim /etc/httpd/conf/httpd.conf
// #ServerName www.example.com:80下面添加 ServerName 127.0.0.1:80
[root@rsyslog-server log]# systemctl restart httpd
[root@rsyslog-server log]# systemctl status httpd

4. 配置Loganalyzer

http://192.168.20.123/log

5. 解决IP不显示

注： DBMappings注意大小写，对应后面全部小写，不能有错，对应如下
uID => id,
Date => devicereportedtime,
Host => fromhost,
Messagetype => infounitid,
Message => message,
Facility => facility,
IP => fromip,
Severity => priority,
Syslogtag => syslogtag,
ProcessID => processid,
Event ID => eventid,
Eventlog Type => eventlogtype,
Event Source => eventsource,
Event Category => eventcategory,
Event User => eventuser,
SystemID => systemid,
Checksum => checksum

facllity：设施，从功能或程序上对日志进行分类，并由专门的工具附则记录其日志

auth：认证相关信息

authpriv：认证授权相关信息

cron：周期性计划任务相关信息

daemon：守护进程相关信息

kern：内核相关信息

lpr：打印相关信息

mail：收发邮件相关信息

mark：防火墙标记

news：新闻相关信息

security：安全相关信息

syslog：自身记录

user：用户相关信息

uucp：早起系统文件共享服务

local0…local7：8个自定义

priority：级别

debug：调试信息

info：基本说明信息

notice：需要注意的信息

warn，warning：警告信息

err，error：错误信息

crit：蓝色警报

alert：橙色警报

emerg，panic：红色警报

6. 查看数据库容量

[root@rsyslog-server ~]# mysql -uroot -p123456
MariaDB [(none)]> use information_schema;
MariaDB [information_schema]> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_mb from TABLES;    #数据库总大小
+---------+
| data_mb |
+---------+
| 0.59MB  |
+---------+
1 row in set (0.00 sec)
    
MariaDB [information_schema]> SELECT table_schema,CONCAT(ROUND(SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024,2),'MB') AS total_mb FROM TABLES GROUP BY table_schema;    #每个数据库大小
+--------------------+----------+
| table_schema       | total_mb |
+--------------------+----------+
| information_schema | NULL     |
| mysql              | 0.63MB   |
| performance_schema | 0.00MB   |
| rsyslog            | 0.02MB   |
| Syslog             | 0.05MB   |
+--------------------+----------+
5 rows in set (0.01 sec)

MariaDB [(none)]> select
    -> table_schema as '数据库',
    -> sum(table_rows) as '记录数',
    -> sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
    -> sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
    -> from information_schema.tables
    -> group by table_schema;
+--------------------+-----------+------------------+------------------+
| 数据库             | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
+--------------------+-----------+------------------+------------------+
| information_schema |      NULL |             0.00 |             0.00 |
| mysql              |      2052 |             0.50 |             0.03 |
| performance_schema |     23014 |             0.00 |             0.00 |
| rsyslog            |        53 |             0.00 |             0.00 |
| Syslog             |       167 |             0.05 |             0.00 |
+--------------------+-----------+------------------+------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> select
    -> table_schema as '数据库',
    -> table_name as '表名',
    -> table_rows as '记录数',
    -> truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    -> truncate(index_length/1024/1024, 2) as '索引容量(MB)'
    -> from information_schema.tables
    -> order by data_length desc, index_length desc;
+--------------------+----------------------------------------------+-----------+------------------+------------------+
| 数据库             | 表名                                         | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
+--------------------+----------------------------------------------+-----------+------------------+------------------+
| mysql              | help_topic                                   |       508 |             0.42 |             0.01 |
| mysql              | help_keyword                                 |       464 |             0.08 |             0.01 |
| Syslog             | SystemEvents                                 |       167 |             0.04 |             0.00 |
| Syslog             | SystemEventsProperties                       |         0 |             0.01 |             0.00 |
...略...
+--------------------+----------------------------------------------+-----------+------------------+------------------+
116 rows in set (0.00 sec)

1、SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

2、TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

3、COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

4、STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

5、USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

6、SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

7、TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

8、COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

9、CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

10、COLLATIONS表：提供了关于各字符集的对照信息。

11、COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

12、TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

13、KEY_COLUMN_USAGE表：描述了具有约束的键列。

14、ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

15、VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

16、TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表