Integration Of rsyslog with MySQL

Posted: August 7, 2013 in Uncategorized

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 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
[root@afzalkhan ~]#  

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


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.



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


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


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


Restart rsyslog service :

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


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)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s