Open Source

Set up rsyslog to store syslog messages in MySQL

Vincent Danen tells you how to use rsyslog with phpLogCon, which allows you to store your syslog messages in MySQL and view them as a Web page.

The de facto system logger on Linux systems is sysklogd, which provides the syslog and klog services that allow system events and application events to be logged and written to standard log files such as /var/log/messages. While sysklogd works well enough, there are other alternatives. If you were ever interested in storing syslog messages in MySQL so they could be viewed through a Web page, using rsyslog in combination with phpLogCon is an ideal solution.

Rsyslog comes with Red Hat Enterprise Linux 5, and CentOS 5, but is not installed by default. It can easily be installed via yum, as well as the other pre-requisites to have Web-enabled log viewing. For other distributions, use the appropriate apt-get or urpmi commands.

# yum install rsyslog rsyslog-mysql mysql-server php-mysql php-gd httpd mod_ssl
# chkconfig rsyslog on
# chkconfig httpd on
# chkconfig mysqld on
# chkconfig syslog off

Now that the packages are installed and the defaults are out of the way, syslog will be disabled on subsequent boots (but is currently still running), and rsyslog, httpd, and mysqld will start on subsequent boots (but are currently not running).

Rsyslog uses the same syntax as syslog, so if you have made modifications to /etc/syslog.conf, you can copy that file to rsyslog.conf and rsyslog will continue to log to the same files and in the same way that syslog did.

Next, start the MySQL service, create a database for the syslog messages, and import the schema for rsyslog to use:

# service mysqld start
# mysql
mysql> GRANT SELECT, UPDATE, INSERT ON Syslog.* TO rsyslog@localhost IDENTIFIED BY 'password';
mysql> \q
# mysql </usr/share/doc/rsyslog-mysql-2.0.6/createDB.sql

To add MySQL support to rsyslog, edit /etc/rsyslog.conf and add the following to the top of the file:

$ModLoad ommysql.so
*.*  :ommysql:localhost,Syslog,rsyslog,password

Once this is all done, you can stop syslog and start rsyslog. Once you start rsyslog, check /var/log/messages to make sure it noted the rsyslog startup:

# service syslog stop
# service rsyslog start
# tail /var/log/messages

Also make sure that rsyslog made the connection to MySQL. If you are using CentOS or Red Hat Enterprise Linux, with SELinux in enforcing mode, you will need to update the SELinux rules to allow rsyslog to talk to the MySQL socket (otherwise you will see failures in the logs). This can be done by doing:

# setenforce 0
# service rsyslog restart
# cat /var/log/audit/audit.log | grep rsyslogd | audit2allow -M myselinuxmod; semodule -i myselinuxmod.pp
# setenforce 1
# service rsyslog restart

After the above is done, you should no longer see connection errors in /var/log/messages. You can log into MySQL and take a look at the SystemEvents table to make sure data is being logged there.

Having the logs in MySQL is fine, but in order to make use of them and view them easily, phpLogCon should be installed. It is a Web-based front-end that will allow you to view the logs with a nice interface. The latest version can be downloaded from http://www.phplogcon.org/downloads. The tarball comes with an INSTALL file with the instructions on how to set it up; it's no more difficult than any other PHP Web application.

Once it is installed, you will be able to see all of the log messages, as well as search and filter them. If you wanted to view logs from other hosts on the network as well, you can set up the syslog daemons on the other systems to do remote logging to rsyslog; you will need to edit /etc/sysconfig/rsyslog and change the SYSLOGD_OPTIONS to "-m 0 -r" first, however. This tells rsyslog to listen and receive logs from remote systems. On the remote systems, you would add the following to the end of syslog.conf:

*.* @hostname_or_IP_of_syslog_server

Be sure to restart any syslog server you make configuration changes to.

Get the PDF version of this tip here.

About Vincent Danen

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

Editor's Picks

Free Newsletters, In your Inbox