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)
