rsyslog实现将日志存储到mysql中-CSDN博客
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
前提准备好msql server或mariadb server
1、安装rsyslog连接至mysql server的驱动模块
[13:24 root@centos6.8~]# yum install -y rsyslog-mysql
[13:24 root@centos6.8~]# rpm -ql rsyslog-mysql
/lib64/rsyslog/ommysql.so
/usr/share/doc/rsyslog-mysql-5.8.10
/usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql
可以看到其实rsyslog模块提供的启动mysql的模块还有createDB.sql它的作用就是定义了日志的记录表格式可以直接导入该sql语句到mysql即可
新建rsyslog的mysql用户
CREATE USER 'rsyslog'@'localhost' IDENTIFIED BY 'password';
授权数据库
GRANT ALL PRIVILEGES ON Syslog.* TO 'rsyslog'@'localhost';
刷新权限运行以下命令以使权限更改立即生效
FLUSH PRIVILEGES;
2、在mysql server准备rsyslog专用的用户账号
mysql>GRANT ALL ON Syslog.* TO 'rsyslog'@'127.0.0.1' IDENTIFIED BY 'rsyslogpass';
mysql>GRANT ALL ON Syslog.* TO 'rsyslog'@'local' IDENTIFIED BY 'rsyslogpass';
mysql> FLUSH PRIVILEGES;
3、导入该sql语句到mysql即可生成所需要的数据库和表
[13:24 root@centos6.8~]# mysql -ursyslog -h127.0.0.1 -p123456 < /usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql
mysql> USE Syslog
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+------------------------+
| Tables_in_Syslog |
+------------------------+
| SystemEvents |
| SystemEventsProperties |
+------------------------+
2 rows in set (0.00 sec)
mysql> DESC SystemEvents
-> ;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| CustomerID | bigint(20) | YES | | NULL | |
| ReceivedAt | datetime | YES | | NULL | |
| DeviceReportedTime | datetime | YES | | NULL | |
| Facility | smallint(6) | YES | | NULL | |
| Priority | smallint(6) | YES | | NULL | |
| FromHost | varchar(60) | YES | | NULL | |
| Message | text | YES | | NULL | |
| NTSeverity | int(11) | YES | | NULL | |
| Importance | int(11) | YES | | NULL | |
| EventSource | varchar(60) | YES | | NULL | |
| EventUser | varchar(60) | YES | | NULL | |
| EventCategory | int(11) | YES | | NULL | |
| EventID | int(11) | YES | | NULL | |
| EventBinaryData | text | YES | | NULL | |
| MaxAvailable | int(11) | YES | | NULL | |
| CurrUsage | int(11) | YES | | NULL | |
| MinUsage | int(11) | YES | | NULL | |
| MaxUsage | int(11) | YES | | NULL | |
| InfoUnitID | int(11) | YES | | NULL | |
| SysLogTag | varchar(60) | YES | | NULL | |
| EventLogType | varchar(60) | YES | | NULL | |
| GenericFileName | varchar(60) | YES | | NULL | |
| SystemID | int(11) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)
4、配置rsyslog加载ommysql模块
#### MODULES ####
......
$ModLoad ommysql
5、配置RULES将所期望的日志信息记录于mysql中
其格式为
facility.priority :ommysql:DBHOST,DB,DBUSER,DBUSERPASS
检查数据库连接信息确保数据库连接信息正确。在你的配置中你正在将日志发送到MySQL服务器的127.0.0.1数据库名为Syslog用户名为rsyslog密码为123456。请确保这些信息与你的实际MySQL配置匹配。
配置规则在*.*:ommysql这部分*.*表示将所有日志消息都发送到MySQL。你可以根据需要更改这部分以匹配特定的日志规则。
所以添加以下信息
*.*
:ommysql:127.0.0.1,Syslog,rsyslog,123456
6、启动mysql服务并重启rsyslog服务mysql已经记录日志到此为止日志信息已经成功的存储于数据库中
systemctl restart mariadb && systemctl restart rsyslog
mysql>USE Syslog
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM SystemEvents\G
*************************** 1. row ***************************
ID: 1
CustomerID: NULL
ReceivedAt: 2016-10-16 13:35:46
DeviceReportedTime: 2016-10-16 13:35:46
Facility: 0
Priority: 6
FromHost: centos6
Message: imklog 5.8.10, log source = /proc/kmsg started.
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: kernel:
EventLogType: NULL
GenericFileName: NULL
SystemID: NULL
*************************** 2. row ***************************
ID: 2
CustomerID: NULL
ReceivedAt: 2016-10-16 13:35:46
DeviceReportedTime: 2016-10-16 13:35:46
Facility: 5
Priority: 6
FromHost: centos6
Message: [origin software="rsyslogd" swVersion="5.8.10" x-pid="3081" x-info="http://www.rsyslog.com"] start
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: rsyslogd:
EventLogType: NULL
GenericFileName: NULL
SystemID: NULL
*************************** 3. row ***************************
ID: 3
CustomerID: NULL
ReceivedAt: 2016-10-16 13:35:50
DeviceReportedTime: 2016-10-18 10:48:37
Facility: 18
Priority: 6
FromHost: localhost
Message: nidhai
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. row ***************************
ID: 4
CustomerID: NULL
ReceivedAt: 2016-10-16 13:35:52
DeviceReportedTime: 2016-10-18 10:48:39
Facility: 18
Priority: 6
FromHost: localhost
Message: D
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)