rsyslog实现将日志存储到mysql中
前提：准备好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 -ADatabase changedmysql> SELECT * FROM SystemEvents\G
*************************** 1. row ***************************ID: 1CustomerID: NULLReceivedAt: 2016-10-16 13:35:46
DeviceReportedTime: 2016-10-16 13:35:46Facility: 0Priority: 6FromHost: centos6Message: imklog 5.8.10, log source = /proc/kmsg started.NTSeverity: NULLImportance: NULLEventSource: NULLEventUser: NULLEventCategory: NULLEventID: NULLEventBinaryData: NULLMaxAvailable: NULLCurrUsage: NULLMinUsage: NULLMaxUsage: NULLInfoUnitID: 1SysLogTag: kernel:EventLogType: NULLGenericFileName: NULLSystemID: NULL
*************************** 2. row ***************************ID: 2CustomerID: NULLReceivedAt: 2016-10-16 13:35:46
DeviceReportedTime: 2016-10-16 13:35:46Facility: 5Priority: 6FromHost: centos6Message: [origin software="rsyslogd" swVersion="5.8.10" x-pid="3081" x-info="http://www.rsyslog.com"] startNTSeverity: NULLImportance: NULLEventSource: NULLEventUser: NULLEventCategory: NULLEventID: NULLEventBinaryData: NULLMaxAvailable: NULLCurrUsage: NULLMinUsage: NULLMaxUsage: NULLInfoUnitID: 1SysLogTag: rsyslogd:EventLogType: NULLGenericFileName: NULLSystemID: NULL
*************************** 3. row ***************************ID: 3CustomerID: NULLReceivedAt: 2016-10-16 13:35:50
DeviceReportedTime: 2016-10-18 10:48:37Facility: 18Priority: 6FromHost: localhostMessage: nidhaiNTSeverity: NULLImportance: NULLEventSource: NULLEventUser: NULLEventCategory: NULLEventID: NULLEventBinaryData: NULLMaxAvailable: NULLCurrUsage: NULLMinUsage: NULLMaxUsage: NULLInfoUnitID: 1SysLogTag: root:EventLogType: NULLGenericFileName: NULLSystemID: NULL
*************************** 4. row ***************************ID: 4CustomerID: NULLReceivedAt: 2016-10-16 13:35:52
DeviceReportedTime: 2016-10-18 10:48:39Facility: 18Priority: 6FromHost: localhostMessage: DNTSeverity: NULLImportance: NULLEventSource: NULLEventUser: NULLEventCategory: NULLEventID: NULLEventBinaryData: NULLMaxAvailable: NULLCurrUsage: NULLMinUsage: NULLMaxUsage: NULLInfoUnitID: 1SysLogTag: root:EventLogType: NULLGenericFileName: NULLSystemID: NULL
4 rows in set (0.00 sec)