///Writing Syslog Messages to MySQL

Writing Syslog Messages to MySQL

Abstract

In this paper, I describe how to write {

return top.js.OpenExtLink(window,event,this)

}” href=”http://www.mysql.com/” target=”_blank”>MySQL
database. Having syslog messages in a database is often handy, especially when you intend to set up a front-end for viewing them. This paper describes an approach with {

return top.js.OpenExtLink(window,event,this)

}” href=”http://www.monitorware.com/Common/en/Articles/performance-optimizing-syslog-server.php” target=”_blank”>optimizing syslog server performance
. While this paper talks about Window-based solutions, the ideas in it are generic enough to apply here, too. So it might be worth reading if you anticipate medium high to high traffic. If you anticipate really high traffic (or very large traffic spikes), you should seriously consider forgetting about direct database writes – in my opinion, such a situation needs either a very specialised system or a different approach (the text-file-to-database approach might work better for you in this case).

Overall System Setup

In this paper, I concentrate on the server side. If you are thinking about interactive syslog message review, you probably want to centralize syslog. In such a scenario, you have multiple machines (the so-called clients) send their data to a central machine (called server in this context). While I expect such a setup to be typical when you are interested in storing messages in the database, I do not describe how to set it up. This is beyond the scope of this paper. If you search a little, you will probably find many good descriptions on how to centralize syslog. If you do that, it might be a good idea to do it securely, so you might also be interested in my paper on {

return top.js.OpenExtLink(window,event,this)

}” href=”http://www.phpmyadmin.net/” target=”_blank”>phpMyAdmin
). Please make sure that this is installed, actually working and you have a basic understanding of how to handle it.

Setting up the system

You need to download and install rsyslogd first. Obtain it from the {

return top.js.OpenExtLink(window,event,this)

}” href=”http://www.rsyslog.com/Documentation-/property_replacer.html.phtml” target=”_blank”>Property Replacer
". Simply said, you access properties by including their name between percent signs inside the template. For example, if the syslog message is "Test", the template "%msg%" would be expanded to "Test". Rsyslogd supports sending template text as a SQL statement to MySQL. As such, the template must be a valid SQL statement. There is no limit in what the statement might be, but there are some obvious and not so obvious choices. For example, a template "drop table xxx" is possible, but does not make an awful lot of sense. In practice, you will always use an "insert" statement inside the template.

An example: if you would just like to store the msg part of the full syslog message, you have probably created a table "syslog" with a single column "message". In such a case, a good template would be "insert into syslog(message) values (‘%msg%’)". With the example above, that would be expanded to "insert into syslog(message) values(‘Test’)". This expanded string is then sent to the database. It’s that easy, no special magic. The only thing you must ensure is that your template expands to a proper SQL statement and that this statement matches your database design.

Does that mean you need to create database schema yourself and also must fully understand rsyslogd’s properties? No, that’s not needed. Because we anticipated that folks are probably more interested in getting things going instead of designing them from scratch. So we have provided a default schema as well as build-in support for it. This schema also offers an additional benefit: rsyslog is part of {

return top.js.OpenExtLink(window,event,this)

}” href=”http://www.monitorware.com/en/” target=”_blank”>MonitorWare product line
(which includes open source and closed source members). All of these tools share the same default schema and know how to operate on it. For this reason, the default schema is also called the "MonitorWare Schema". If you use it, you can simply add {

return top.js.OpenExtLink(window,event,this)

}” href=”http://127.0.0.1/” target=”_blank”>127.0.0.1
" for database-server. This can be especially advisable, if you do not need to expose MySQL to any process outside of the local machine. In this case, you can simply bind it to {

return top.js.OpenExtLink(window,event,this)

}” href=”http://mysql.example.com/” target=”_blank”>mysql.example.com
) or IP-address. The database-name by default is "syslog". If you have modified the default, use your name here. Database-userid and -password are the credentials used to connect to the database. As they are stored in clear text in rsyslog.conf, that user should have only the least possible privileges. It is sufficient to grant it INSERT privileges to the systemevents table, only. As a side note, it is strongly advisable to make the rsyslog.conf file readable by root only – if you make it world-readable, everybody could obtain the password (and eventually other vital information from it). In our example, let’s assume you have created a MySQL user named "syslogwriter" with a password of "topsecret" (just to say it bluntly: such a password is NOT a good idea…). If your MySQL database is on the local machine, your rsyslog.conf line might look like in this sample:

*.* >127.0.0.1,syslog,syslogwriter,topsecret

Save rsyslog.conf, restart rsyslogd – and you should see syslog messages being stored in the "systemevents" table!

The example line stores every message to the database. Especially if you have a high traffic volume, you will probably limit the amount of messages being logged. This is easy to acomplish: the "write database" action is just a regular selector line. As such, you can apply normal selector-line filtering. If, for example, you are only interested in messages from the mail subsystem, you can use the following selector line:

mail.* >127.0.0.1,syslog,syslogwriter,topsecret

Review the {

return top.js.OpenExtLink(window,event,this)

}” href=”http://www.phplogcon.org/” target=”_blank”>phpLogCon
, which displays syslog data in a browser. As of this writing, phpLogCon is not yet a powerful tool, but it’s open source, so it might be a starting point for your own solution.

On Reliability…

Rsyslogd writes syslog messages directly to the database. This implies that the database must be available at the time of message arrival. If the database is offline, no space is left or something else goes wrong – rsyslogd can not write the database record. If rsyslogd is unable to store a message, it performs one retry. This is helpful if the database server was restarted. In this case, the previous connection was broken but a reconnect immediately succeeds. However, if the database is down for an extended period of time, an immediate retry does not help. While rsyslogd could retry until it finally succeeds, that would have negative impact. Syslog messages keep coming in. If rsyslogd would be busy retrying the database, it would not be able to process these messages. Ultimately, this would lead to loss of newly arrived messages.

In most cases, rsyslogd is configured not only to write to the database but to perform other actions as well. In the always-retry scenario, that would mean no other actions would be carried out. As such, the design of rsyslogd is limited to a single retry. If that does not succeed, the current message is will not be written to the database and the MySQL database writer be suspended for a short period of time. Obviously, this leads to the loss of the current message as well as all messages received during the suspension period. But they are only lost in regard to the database, all other actions are correctly carried out. While not perfect, we consider this to be a better approach then the potential loss of all messages in all actions.

In short: try to avoid database downtime if you do not want to experience message loss.

Please note that this restriction is not rsyslogd specific. All approachs to real-time database storage share this problem area.

Conclusion

With minumal effort, you can use rsyslogd to write syslog messages to a MySQL database. Once the messages are arrived there, you can interactivley review and analyse them. In practice, the messages are also stored in text files for longer-term archival and the databases are cleared out after some time (to avoid becoming too slow). If you expect an extremely high syslog message volume, storing it in real-time to the database may outperform your database server. In such cases, either filter out some messages or think about alternate approaches involving non-real-time database writing (beyond the scope of this paper).

The method outline in this paper provides an easy to setup and maintain solution for most use cases, especially with low and medium syslog message volume (or fast database servers).

I have set up a site to {

return top.js.OpenExtLink(window,event,this)

}” href=”http://demo.rsyslog.com/” target=”_blank”>visit
it to get a glimpse of how such a beast might look.

Feedback Requested

I would appreciate feedback on this paper. If you have additional ideas, comments or find bugs, please {

function anonymous()

{

return top.js.OpenExtLink(window,event,this)

}

}” href=”http://www.rsyslog.com/” target=”_blank”>www.rsyslog.com
– the rsyslog site

  • {

    function anonymous()

    {

    return top.js.OpenExtLink(window,event,this)

    }

    }” href=”http://www.monitorware.com/Common/en/Articles/performance-optimizing-syslog-server.php” target=”_blank”>Paper on Syslog Server Optimization
  • Revision History

    Copyright

    Copyright (c) 2005 {

    return top.js.OpenExtLink(window,event,this)

    }” href=”http://www.adiscon.com/en/” target=”_blank”>Adiscon
    .

    Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license can be viewed at

    2010-05-25T23:10:40+00:00 August 4th, 2005|MySQL|0 Comments

    About the Author:

    Leave A Comment