Dawnstar Australis

CAVEAT: This was written for RHEL/Fedora and derivative setups. Same principles apply to other distros but configuration files may be in different areas.

NOTE: The rsyslog daemon was only introduced into RHEL as of 5.2 and is not present on older releases.

This is quick and easy guide on getting rsyslog to log everything to a MySQL database. It is by no means a detailed guide and should be used as a base for getting this sort of logging started. Some things are assumed here:

• You already have a MySQL server running. Configuring a MySQL server and appropriate SQL user to access the database will not be covered here.
• You have basic SQL skills. Querying of the logging database is not discussed here.
• For the purpose of examples the MySQL server will be 192.168.0.5, the MySQL database will be called Syslog and the MySQL username/password the remote loghost can use will be “logme” and “access”
• That we intend to log everything to the database. You will need to set up logging appropriate for your environment.

Setting up the server

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 appropiate tables in place. Since this file is present in the rsyslog-mysql package you can obtain a copy from the client machine as this package will be installed there (see below). The file required is located at;

/usr/share/doc/rsyslog-mysql-[version]/createDB.sql

The quickest and easiest way to pull this script is show below but please use what is appropiate for your MySQL server.

mysql < createDB.sql

Don’t forget to set up a MySQL user with appropiate levels of access to the database that the remote logging host can connect as.

Setting up the client

RHEL 5.2 does not ship with rsyslog by default. You will need to install it and then phase out the existing syslog as they should not be run concurrently. To install rsyslog and the MySQL plugin for it run:

yum install rsyslog rsyslog-mysql

Once installed we want to shutdown the default syslog and bring up rsyslog in it’s place. The default rsyslog configuration is exactly the same as the default syslog configuration that ships with RHEL so this should cause minimal issues unless you already have custom logging in place. Firstly shutdown syslog and set it not to start on boot:

service syslog stop
chkconfig syslog off

Then we bring rsyslog up and set it to start on boot:

service rsyslog start
chkconfig rsyslog on

By this point the MySQL database should be configured and ready for use. As such we can now configure rsyslog to start sending all log entires to it. The configuration file used by rsyslog is located at /etc/rsyslog.conf – you will see that the default version shipped with RHEL is the same one use by the venerable syslog. We will be adding two extra lines to this configuration to enable the MySQL rsyslog plugin and to define the MySQL logging parameters.

To enable the MySQL rsyslog plugin add the following the line. This line must be present before any MySQL specific logging entries in the configuration file, as such you may wish to place it at the start of rsyslog.conf to ensure that any configurations added later on come after it’s addition. The entry should read as:

$ModLoad ommysql.so

Once this has been added we can now add an entry to log everything to the MySQL server. Be aware that on a busy server this may be a conderiable about of information so please ensure that you tune the logging level as appropiate for your scenario. The below entry will log all events to the MySQL server we outlined above:

*.*          :ommysql:192.168.0.5,Syslog,logme,access

Once this in place we can restart the rsyslog daemon to pick up the new changes and log entries will be sent to the MySQL database immediately. To restart rsyslog run:

service rsyslog restart

From here on in all system events will be logged directly to the MySQL database. Accessing these details is only limited by what you can achieve with SQL queries. There are many possibilities open with this and should enable system administrators to locate and sort log entries a lot more effeciently.

References

Writing syslog messages to MySQL