需求分析RedHat8安装RsyslogLogAnalyzerMySQL自动水平分表1.更新清华镜像1.1 执行yum更新三连操作yum clean all yum makecache yum repolist1.2 编辑镜像源配置文件创建并编辑/etc/yum.repos.d/online.repocd/etc/yum.repos.d vim online.repo1.3 添加清华镜像源在online.repo文件中添加[BaseOS]nameBaseOSbaseurlhttps://mirrors.tuna.tsinghua.edu.cn/centos-vault/8.0.1905/BaseOS/$basearch/os/gpgcheck0enabled1gpgkeyhttps://mirrors.tuna.tsinghua.edu.cn/centos/RPM-GPG-KEY-CentOS-Official[AppStream]nameAppStreambaseurlhttps://mirrors.tuna.tsinghua.edu.cn/centos-vault/8.0.1905/AppStream/$basearch/os/gpgcheck0enabled1gpgkeyhttps://mirrors.tuna.tsinghua.edu.cn/centos/RPM-GPG-KEY-CentOS-Official[PowerTools]namePowerToolsbaseurlhttps://mirrors.tuna.tsinghua.edu.cn/centos-vault/8.0.1905/PowerTools/$basearch/os/gpgcheck0enabled1gpgkeyhttps://mirrors.tuna.tsinghua.edu.cn/centos/RPM-GPG-KEY-CentOS-Official[extras]nameextras baseurlhttps://mirrors.tuna.tsinghua.edu.cn/centos-vault/8.0.1905/extras/$basearch/os/gpgcheck0enabled1gpgkeyhttps://mirrors.tuna.tsinghua.edu.cn/centos/RPM-GPG-KEY-CentOS-Official[centosplus]namecentosplus baseurlhttps://mirrors.tuna.tsinghua.edu.cn/centos-vault/8.0.1905/centosplus/$basearch/os/gpgcheck0enabled1gpgkeyhttps://mirrors.tuna.tsinghua.edu.cn/centos/RPM-GPG-KEY-CentOS-Official1.4 保存并退出esc!wq清除缓存yum clean all查询镜像源yum repolist1.5 更新软件包yum makecache yum update2. RedHat 8 安装RsyslogLogAnalyzerMySQLrsyslog是RedHat自带的日志服务LogAnalyzer日志图形化分析工具MySQL接收rsyslog日志并存储到数据库#本机ip为192.168.40.1312.1 安装MySQL相关模块这里使用的是Mariadb是MySQL的同胞兄弟yum install rsyslog-mysql-y systemctl enable--now rsyslog.service yum-y install mariadb-server systemctl enable--now mariadb2.2 配置MySQL进入mysqlmysql创建Syslog数据库SystemEvents表CREATEDATABASESyslog;USESyslog;CREATETABLESystemEvents(IDintunsigned notnullauto_increment primary key,CustomerIDbigint,ReceivedAtdatetimeNULL,DeviceReportedTimedatetimeNULL,FacilitysmallintNULL,PrioritysmallintNULL,FromHostvarchar(60)NULL,Messagetext,NTSeverityintNULL,ImportanceintNULL,EventSourcevarchar(60),EventUservarchar(60)NULL,EventCategoryintNULL,EventIDintNULL,EventBinaryDatatextNULL,MaxAvailableintNULL,CurrUsageintNULL,MinUsageintNULL,MaxUsageintNULL,InfoUnitIDintNULL,SysLogTagvarchar(60),EventLogTypevarchar(60),GenericFileNameVarChar(60),SystemIDintNULL);创建mysql用户之后在rsyslog配置文件和图形化界面用的上。GRANTALLONSyslog.*TOrsyslog192.168.40.%IDENTIFIEDBYgm#Log8189;3 Rsyslog配置指定日志发送到数据库默认是发送SystemEvents表中。开启mysql模块vim/etc/rsyslog.conf#在MODULES语言下面,加下面行module(loadommysql)指定数据库和用户*.*接收所有info级别的日志发送到131主机上的数据库资源有限131即本机不能使用127和localhost代替因为mysql没有创建这个用户#在RULES语句块加下面行的格式 #facility.priority:ommysql:DBHOST,DBNAME,DBUSER,PASSWORD*.info:ommysql:192.168.40.131,Syslog,rsyslog,gm#Log8189打开远程端口514用于接收远程设备日志打开注释即可使用tcp或udp或者都使用保存退出并重启rsyslog服务systemctl restart rsyslog.service查看514端口是否打开ss-tuln|grep5143.1 测试mysql接收日志#在日志服务器上生成日志 loggerthis is a test log#进入mysql并查询日志 mysql useSyslogselectcount(*)fromSystemEvents;4. 安装图形化界面LogAnalyzer安装httpdphp等相关软件yum-y install httpd php-fpm php-mysqlnd php-gd systemctl enable--now httpd php-fpm wget https://download.adiscon.com/loganalyzer/loganalyzer-4.1.13.tar.gz tar xvf loganalyzer-4.1.13.tar.gz mv loganalyzer-4.1.13/src//var/www/html/log touch/var/www/html/log/config.php chmod666/var/www/html/log/config.php更改httpd端口vim/etc/httpd/conf/httpd.conf #找到Listen默认为80改为8080Listen8080#重启httpd systemctl restart httpd4.1 web界面初始化,资源访问不了浏览器访问IP192.168.40.131:8080/log如果显示没有权限访问资源是防火墙或者SELinux安全模块拦截了我这边是全关。#永久关闭防火墙 systemctl stop firewalld systemctl disable firewalld #临时关闭SELinuxsetenforce0#永久关闭SELinux将SELINUXenforcing改成 vim/etc/selinux/configSELINUXdisabled4.2 web初始化一路next来到step3next到step6成功读取到MYSQL中的记录5.水平分表单表存储日志过多的日志容易影响性能采取水平分表的方式切割存储到分表中。此脚本保留20天的日志文件每天将SystemEvents表中日志转存到SystemEvents_日期。创建脚本vim create_log_table_long.sh添加脚本#!/bin/bash #MySQL登录信息DB_HOST192.168.40.131DB_NAMESyslogDB_USERrsyslogDB_PASSgm#Log8189# 建议使用安全方式存储密码 # 获取当前日期CURRENT_DATE$(date%Y%m%d)OLD_DATE$(date-d20 days ago%Y%m%d)# 创建新表的SQLCREATE_TABLE_SQLCREATETABLEIFNOTEXISTSSystemEvents_$CURRENT_DATE(IDintunsigned notnullauto_increment primary key,CustomerIDbigint,ReceivedAtdatetimeNULL,DeviceReportedTimedatetimeNULL,FacilitysmallintNULL,PrioritysmallintNULL,FromHostvarchar(60)NULL,Messagetext,NTSeverityintNULL,ImportanceintNULL,EventSourcevarchar(60),EventUservarchar(60)NULL,EventCategoryintNULL,EventIDintNULL,EventBinaryDatatextNULL,MaxAvailableintNULL,CurrUsageintNULL,MinUsageintNULL,MaxUsageintNULL,InfoUnitIDintNULL,SysLogTagvarchar(60),EventLogTypevarchar(60),GenericFileNameVarChar(60),SystemIDintNULL); # 删除旧表的SQLDROP_TABLE_SQLDROP TABLE IF EXISTS SystemEvents_$OLD_DATE;# 将SystemEvents表的记录转存到新表的SQLINSERT_RECORDS_SQLINSERTINTOSystemEvents_$CURRENT_DATE(CustomerID,ReceivedAt,DeviceReportedTime,Facility,Priority,FromHost,Message,NTSeverity,Importance,EventSource,EventUser,EventCategory,EventID,EventBinaryData,MaxAvailable,CurrUsage,MinUsage,MaxUsage,InfoUnitID,SysLogTag,EventLogType,GenericFileName,SystemID)SELECTCustomerID,ReceivedAt,DeviceReportedTime,Facility,Priority,FromHost,Message,NTSeverity,Importance,EventSource,EventUser,EventCategory,EventID,EventBinaryData,MaxAvailable,CurrUsage,MinUsage,MaxUsage,InfoUnitID,SysLogTag,EventLogType,GenericFileName,SystemIDFROMSystemEvents; # 执行创建新表的SQLif!mysql-h $DB_HOST-u $DB_USER-p$DB_PASS-D$DB_NAME-e$CREATE_TABLE_SQL;then echoFailed to create table.exit1fi # 执行删除旧表的SQLif!mysql-h $DB_HOST-u $DB_USER-p$DB_PASS-D$DB_NAME-e$DROP_TABLE_SQL;then echoFailed to drop old table.exit1fi # 将记录转存到新表if!mysql-h $DB_HOST-u $DB_USER-p$DB_PASS-D$DB_NAME-e$INSERT_RECORDS_SQL;then echoFailed to insert records.exit1fi # 清空SystemEvents表if!mysql-h $DB_HOST-u $DB_USER-p$DB_PASS-D$DB_NAME-eTRUNCATE TABLE SystemEvents;;then echoFailed to truncate SystemEvents table.exit1fi每天1200定时执行脚本chmodx create_log_table_long.sh crontab-e #添加要执行的脚本00***/root/create_log_table_long.sh大功告成