The first step, if you didn’t specifically tell the installer to install database support, is to install MySQL server:

sudo yum install mysql-server

We’ll then start the service and set the password to ‘MYSQLPASSWORD’:

sudo service mysqld start

mysqladmin -h localhost — user root password ‘MYSQLPASSWORD’

I’m going to be using the ‘root’ MySQL user for brevity, but feel free to create an account for you that has permission to create databases and grant permissions, if you’d like.

At this point, you should be able to connect in and query the database server:

mysql -p — user root

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 123991

Server version: 5.1.52 MySQL Community Server (GPL) by Utter Ramblings Copyright © 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql> show databases;

+ — — — — — — — — — — +

| Database |

+ — — — — — — — — — — +

| information_schema |

| mysql |

| test |

+ — — — — — — — — — — +

3 rows in set (0.01 sec)

Great, the MySQL server is up and running. Now lets get rsyslog set up to talk to it. Although rsyslog is installed by default, MySQL support for it isn’t. To do that, we’ve got to install the shared object that lets that happen:

yum install rsyslog-mysql

Now we have /lib64/rsyslog/ommysql.so which we’ll add to the configuration shortly.

Before rsyslogd can write to the database, we need to create a database for it to write to (and make sure it has the right schema). Fortunately, the rsyslog-mysql package that we installed above provides a script to help us. Here’s how you run it:

mysql — user root -p < /usr/share/doc/rsyslog-mysql-[ver]/createDB.sql

where [ver] is the numerical version of rsyslog included. You can find out by running:

rsyslog -v

rsyslogd 4.6.2, compiled with:

…

etc

…

so in this case, the filename that I need to use is /usr/share/doc/rsyslog-mysql-4.6.2/createDB.sql — just make sure you check first. Yours is probably newer than mine, by the time you read this.

If you’re comfortable with SQL syntax, check out this script. Basically, it just creates a Syslog database with a couple of tables in the format that rsyslog needs.

Since we’ve now got a database, lets create a user for rsyslog to use and grant it permissions on the table:

mysql> GRANT INSERT ON Syslog.* to ‘sysloguser’@’localhost’ identified by ‘SYSLOGPASS’;

flush privileges;

(I used to have “grant all privileges …. WITH GRANT OPTION” here, but according to the author of rsyslog, only INSERT is needed.)

You can test that this new user works with the following:

mysql — user sysloguser -p Syslog

Enter password:

mysql> SELECT count(ID) from SystemEvents;

+ — — — — — -+

| count(ID) |

+ — — — — — -+

| 0 |

+ — — — — — -+

1 row in set (0.00 sec)

This tells us that we can successfully authenticate and query the table. This is progress! Now, we just have to tell rsyslog to USE the database.

Edit the /etc/rsyslog.conf and near the top of the file, add the following line:

$ModLoad ommysql.so

As you’ll remember from above, that’s the mysql shared object we installed with the “rsyslog-mysql” package.

Next find the following line:

*.info;mail.none;authpriv.none;cron.none /var/log/messages

To clarify things, this sends all alerts at the “info” priority, no mail facility, no authpriv facility, and no cron facility logs to the /var/log/messages file. Read the line in order from left to right, with later entries taking precedence over prior ones. This means that cron.info will not get sent to “messages”.

OK, this seems like a pretty good target to send to the database as an example. Because we’d like to maintain the functionality (at least for now) of outputting log entries to the messages file (otherwise rsyslogd can’t tell us about problems it has connecting!), we’re going to copy that line exactly.

After copying the line, erase the “/var/log/messages” part at the end and substitute this:

:ommysql:localhost,Syslog,sysloguser,SYSLOGPASS

The format of that block of text is the following:

:ommysql:DBserver,Database,DBuser,DBpassword

The entire line should wind up looking like this:

*.info;mail.none;authpriv.none;cron.none \

:ommysql:localhost,Syslog,sysloguser,SYSLOGPASS

Alright, at this point, rsyslog is configured! Lets kick the tires.

sudo service rsyslog restart

Will restart the service. You can tail /var/log/messages to make sure it didn’t have any specific problems. A successful start will be a single line entry from rsyslogd. Mine looks like this:

Aug 26 03:33:04 syslogserver rsyslogd: [origin software=”rsyslogd” swVersion=”4.6.2" x-pid=”16788" x-info=”http://www.rsyslog.com"] (re)start

This is great, and means the service started appropriately. Lets send it a message:

logger -p local0.info “This is a test event”

This will log a message with the facility local0 and the priority info. You can tail the messages file again, and you should see it:

Aug 26 03:37:30 syslogserver msimmons: This is a test message

This means the logging server is working. Lets verify that it’s writing to MySQL as well:

mysql — user sysloguser -p Syslog

Enter password:

mysql> SELECT Facility, Priority, Message from SystemEvents;

+ — — — — — + — — — — — + — — — — — — — — — — — — -+

| Facility | Priority | Message |

+ — — — — — + — — — — — + — — — — — — — — — — — — -+

| 17 | 6 | This is a test message |

+ — — — — — + — — — — — + — — — — — — — — — — — — -+

1 row in set (0.01 sec)

Ta-Da!

Left as an exercise to the reader is pointing the database to a specific MySQL machine, if desired. Also, it’s technically possible to have all of the logging machines write to the same MySQL database, but I would recommend against that.

The security gains you see from moving logging off onto a remote server are realized because syslog is essentially an “append only” service (i.e. you can’t remove earlier entries from the log using the syslog protocol). If you suddenly start logging via MySQL, the attacker can then use the MySQL credentials to remove his logs (and all of the other logs in the infrastructure). Not cool.

So set up remote syslog using syslog, and have the centralized syslog server write to a database (and for extra security, just keep the database on the centralized rsyslog server).

Thanks for reading. Drop me a comment if you have any questions or concerns!