Traditionally, rsyslog(syslog) on linux data is text file, it's not handy especially you want to do some analysis or data mining, or setup a web based front viewing interface. rsyslog ommysql module provides a handy msql based database logging function. Here is how to setup it on Linux.

Install rsyslog on Linux

The package is ready on most of linux distro, so

# yum install rsyslog

Start rsyslog

By default rsyslog will start on boot:

# chkconfig --list rsyslog
rsyslog         0:off   1:off   2:on    3:on    4:on    5:on    6:off

Let's start rsyslog:

# service  rsyslog start
Starting system logger:                                    [  OK  ]

Install and config MySQL

It's out of this article scope, you can follow the instruction in

http://dev.mysql.com/doc/refman/5.1/en/linux-installation.html

Create a user syslog(you can name another name)

Configure rsyslog MySQL logging

If you installed rsyslog package from your linux distro, then you can find a createdbDB.sql comes with the package.

If not, then try to get a rsyslog tar ball, find the createDB.sql there.

For example:

wget http://www.rsyslog.com/files/download/rsyslog/rsyslog-8.8.0.tar.gz

You will see a ./plugins/ommysql/createDB.sql . Review it to check that the database name is acceptable for you.

It looks like this:

CREATE DATABASE Syslog;
USE Syslog;
CREATE TABLE SystemEvents
(
        ID int unsigned not null auto_increment primary key,
        CustomerID bigint,
        ReceivedAt datetime NULL,
        DeviceReportedTime datetime NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);

CREATE TABLE SystemEventsProperties
(
        ID int unsigned not null auto_increment primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);

Surely you can change the "Syslog" name to the database name you like.
Execute the createDB.sql file
Assign MySQL account name and password to the Syslog database.
In the case below, account is named to 'syslog' and database is named to 'syslog_db'.

Tell rsyslog to log to the database

Next, we need to tell rsyslogd to write data to the database. As we use the default schema, we do NOT need to define a template for this. We can use the hardcoded one (rsyslogd handles the proper template linking). So all we need to do is add a simple selector line to /etc/rsyslog.conf

*.*       :ommysql:mysqlserver,database,user,password

So we make this change:

        # vi /etc/rsyslog.conf

add the following to the end of the file:

        *.*       >fibrevillage.com,syslog,syslog,<pwd>

Note: it’s important to use TAB spaces between entries above and to modify the values for your setup.

 

Restart rsyslog

        # service rsyslog restart
        Shutting down system logger (rsyslog):                     [  OK  ]
        Starting system logger (rsyslog):                          [  OK  ]

Now, you should be able to see your syslogs in MySQL database!

Logging just some facilities

You can also filter message to MySQL, just apply filters in syslog, for example, if I only want to log warning and above level message.

So I changed the entry in /etc/rsyslog.conf from:

        *.*       >fibrevillage.com,syslog,syslog,<pwd>

to:

        *.warning >firevillage.com,syslog_db,syslog,<pwd>

Then restarted rsyslog

Install phpLogCon 

In the rsyslog document, or same doc you can find in rsyslog pacakge, /usr/share/doc/rsyslog-<verision>/rsyslog_mysql.html
It says you can simply add phpLogCon, a GPLed syslog web interface, to your system and have instant interactive access to your database. So there are some benefits in using the provided schema.

phpLogCon is used to browse the MySQL database from any Web browser. It also supports creating user accounts for people to view those logged rsyslog entries. This is obviously extremely handy and the real power behind the viewing of the rsyslog entries on a central MySQL server.