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:
*.*mmysql: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