Integration Of rsyslog with MySQL

Rsyslogd has one coolest feature to directly point sys log to MySQL database. Using this information we can easily generate web page or report. For this rsyslog-mysql package need to be present on the local system to call ommysql.so module.
The rsyslog-mysql package contains the SQL file that can create the database from scratch. Using the SQL script it will create a database called Syslog with the appropriate tables in place.

So finally we need to have below two rpm to be present on the system :-

[root@afzalkhan ~]# rpm -qa |grep rsyslog
rsyslog-mysql-5.8.10-2.el6.x86_64
rsyslog-5.8.10-2.el6.x86_64
[root@afzalkhan ~]#  

[Note: Take backup of your /etc/rsyslog.conf file before making any changes to it ]



Step:1

MySQL support in rsyslog is integrated via a loadable plug-in module. To use the database functionality, MySQL must be enabled in the config file BEFORE the first database table action is used. Add below line under MODULES section of rsyslog.conf file.

$ModLoad ommysql.so


Step:2

Create an database schema by dumping “/usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql” file

[root@afzalkhan ~]# mysql –user root < /usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql

Step:3

Now We have database “Syslog” and table “SystemEvents”
Create one user to allow “INSERT” functionality transactions related to the Syslog database.

mysql> GRANT INSERT ON Syslog.* to ‘logger’@’localhost’ identified by ‘loggerpass’;

In above query we have created mysql user logger to allow insert transaction on Syslog database from localhost with “loggerpass” as a password

Step:4

Add below line in rsyslog.conf which will redirect all sys log to MySQL. This can be also use for daemon wise redirection for e.g cron.*, mail.*
 

*.* :ommysql:localhost,Syslog,logger,loggerpass



So how to read above rule ???

*.*                  =  All Logs
ommsyql       =  Module Name
localhost       =  Local Host Name
Syslog           =  Database Name
logger           =  MySQL User
loggerpass   =  MySQL User logger Password

Step:5

Restart rsyslog service :

[root@afzalkhan ~]# /etc/init.d/rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]
[root@afzalkhan ~]#  

Step:6

Testing :-

[root@afzalkhan ~]# logger “Hello Test 123”

mysql> select ID,CustomerID,ReceivedAt,DeviceReportedTime,FromHost,Message from SystemEvents where Message like “%Hello Test%”;
+——+————+———————+———————+———-+—————–+
| ID | CustomerID | ReceivedAt | DeviceReportedTime | FromHost | Message |
+——+————+———————+———————+———-+—————–+
| 1285 | NULL | 2013-02-08 18:23:29 | 2013-02-08 18:23:29 | afzalkhan | Hello Test 123 |
+——+————+———————+———————+———-+—————–+
1 row in set (0.00 sec)

Advertisement