2
<title>Writing syslog Data to MySQL</title>
3
<meta name="KEYWORDS" content="syslog, mysql, syslog to mysql, howto">
6
<h1>Writing syslog messages to MySQL</h1>
7
<P><small><i>Written by
8
<a href="http://www.adiscon.com/en/people/rainer-gerhards.php">Rainer
9
Gerhards</a> (2005-08-02)</i></small></P>
11
<p><i><b>In this paper, I describe how to write
12
<a href="http://www.monitorware.com/en/topics/syslog/">syslog</a>
13
messages to a <a href="http://www.mysql.com">MySQL</a> database.</b> Having
14
syslog messages in a database is often handy, especially when you intend to set
15
up a front-end for viewing them. This paper describes an approach
16
with <a href="http://www.rsyslog.com/">rsyslogd</a>, an alternative enhanced
17
syslog daemon natively supporting MySQL. I describe the components needed
18
to be installed and how to configure them.</i></p>
20
<p>In many cases, syslog data is simply written to text files. This approach has
21
some advantages, most notably it is very fast and efficient. However, data
22
stored in text files is not readily accessible for real-time viewing and analysis.
23
To do that, the messages need to be in a database. There are various
24
ways to store syslog messages in a database. For example, some have the syslogd
25
write text files which are later feed via a separate script into the database.
26
Others have written scripts taking the data (via a pipe) from a
27
non-database-aware syslogd and store them as they appear. Some others use
28
database-aware syslogds and make them write the data directly to the database.
29
In this paper, I use that "direct write" approach. I think it is superior,
30
because the syslogd itself knows the status of the database connection and thus
31
can handle it intelligently (well ... hopefully ;)). I use rsyslogd to acomplish
32
this, simply because I have initiated the rsyslog project with
33
database-awareness as one goal.</p>
34
<p><b>One word of caution:</b> while message storage in the database provides an
35
excellent foundation for interactive analysis, it comes at a cost. Database i/o
36
is considerably slower than text file i/o. As such, directly writing to
37
the database makes sense only if your message volume is low enough to allow a)
38
the syslogd, b) the network, and c) the database server to catch
39
up with it. Some time ago, I have written a paper on
40
<a href="http://www.monitorware.com/Common/en/Articles/performance-optimizing-syslog-server.php">
41
optimizing syslog server performance</a>. While this paper talks about
42
Window-based solutions, the ideas in it are generic enough to apply here, too.
43
So it might be worth reading if you anticipate medium high to high traffic. If you
44
anticipate really high traffic (or very large traffic spikes), you should
45
seriously consider forgetting about direct database writes - in my opinion, such
46
a situation needs either a very specialised system or a different approach (the
47
text-file-to-database approach might work better for you in this case).
49
<h2>Overall System Setup</h2>
50
<P>In this paper, I concentrate on the server side. If you are thinking about
51
interactive syslog message review, you probably want to centralize syslog. In
52
such a scenario, you have multiple machines (the so-called clients) send their
53
data to a central machine (called server in this context). While I expect such a
54
setup to be typical when you are interested in storing messages in the database,
55
I do not describe how to set it up. This is beyond the scope of this paper. If
56
you search a little, you will probably find many good descriptions on how to
57
centralize syslog. If you do that, it might be a good idea to do it securely, so
58
you might also be interested in my paper on <a href="rsyslog_stunnel.html">
59
ssl-encrypting syslog message transfer</a>.</P>
60
<P>No matter how the messages arrive at the server, their processing is always the
61
same. So you can use this paper in combination with any description for
62
centralized syslog reporting.</P>
63
<P>As I already said, I use rsyslogd on the server. It has intrinsic support for
64
talking to MySQL databases. For obvious reasons, we also need an instance of
65
MySQL running. To keep us focussed, the setup of MySQL itself is also beyond the
66
scope of this paper. I assume that you have successfully installed MySQL and
67
also have a front-end at hand to work with it (for example,
68
<a href="http://www.phpmyadmin.net/">phpMyAdmin</a>). Please make sure that this
69
is installed, actually working and you have a basic understanding of how to
71
<h2>Setting up the system</h2>
72
<p>You need to download and install rsyslogd first. Obtain it from the
73
<a href="http://www.rsyslog.com/">rsyslog site</a>. Make sure that you disable
74
stock syslogd, otherwise you will experience some difficulties.</p>
75
<p>It is important to understand how rsyslogd talks to the database. In rsyslogd,
76
there is the concept of "templates". Basically, a template is a string that
77
includes some replacement characters, which are called "properties" in rsyslog.
78
Properties are accessed via the "<a href="property_replacer.html">Property
79
Replacer</a>". Simply said, you access properties by including their name
80
between percent signs inside the template. For example, if the syslog message is
81
"Test", the template "%msg%" would be expanded to "Test". Rsyslogd supports
82
sending template text as a SQL statement to MySQL. As such, the template must be
83
a valid SQL statement. There is no limit in what the statement might be, but
84
there are some obvious and not so obvious choices. For example, a template "drop
85
table xxx" is possible, but does not make an awful lot of sense. In practice,
86
you will always use an "insert" statment inside the template.</p>
87
<p>An example: if you would just like to store the msg part of the full syslog
88
message, you have probably created a table "syslog" with a single column "message".
89
In such a case, a good template would be "insert into syslog(message) values ('%msg%')".
90
With the example above, that would be expanded to "insert into syslog(message)
91
values('Test')". This expanded string is then sent to the database. It's that
92
easy, no special magic. The only thing you must ensure is that your template
93
expands to a proper SQL statement and that this statement matches your database
95
<p>Does that mean you need to create database schema yourself and also must
96
fully understand rsyslogd's properties? No, that's not needed. Because we
97
anticipated that folks are probably more interested in getting things going instead
98
of designing them from scratch. So we have provided a default schema as well
99
as build-in support for it. This schema also offers an additional
100
benefit: rsyslog is part of <a href="http://www.adiscon.com/en/">Adiscon</a>'s
101
<a href="http://www.monitorware.com/en/">MonitorWare product line</a> (which
102
includes open source and closed source members). All of these tools share the
103
same default schema and know how to operate on it. For this reason, the default
104
schema is also called the "MonitorWare Schema". If you use it, you can simply
105
add <a href="http://www.phplogcon.org/">phpLogCon, a GPLed syslog web interface</a>,
106
to your system and have instant interactive access to your database. So there
107
are some benefits in using the provided schema.</p>
108
<p>The schema definition is contained in the file "createDB.sql". It comes with
109
the rsyslog package. Review it to check that the database name is acceptable for
110
you. Be sure to leave the table and field names unmodified, because
111
otherwise you need to customize rsyslogd's default sql template, which we do not
113
in this paper. Then, run the script with your favourite MySQL tool. Double-check
114
that the table was successfully created.</p>
116
<p>MySQL support in rsyslog is integrated via a loadable plug-in module. To use the database
117
functionality, MySQL must be enabled in the config file BEFORE the first database table action is
118
used. This is done by placing the</p>
120
<p><code>$ModLoad MySQL</code></p>
122
<p>directive at the begining of /etc/rsyslog.conf</p>
124
<p>Next, we need to tell rsyslogd to write data to the database. As we use
125
the default schema, we do NOT need to define a template for this. We can use the
126
hardcoded one (rsyslogd handles the proper template linking). So all we need to
127
do is add a simple selector line to /etc/rsyslog.conf:</p>
129
<p><code>*.*
130
>database-server,database-name,database-userid,database-password</code></p>
132
<p>In many cases, MySQL will run on the local machine. In this case, you can
133
simply use "127.0.0.1" for <i>database-server</i>. This can be especially
134
advisable, if you do not need to expose MySQL to any process outside of the
135
local machine. In this case, you can simply bind it to 127.0.0.1, which provides
136
a quite secure setup. Of course, also supports remote MySQL instances. In that
137
case, use the remote server name (e.g. mysql.example.com) or IP-address. The <i>
138
database-name</i> by default is "syslog". If you have modified the default, use
139
your name here. <i>Database-userid</i> and <i>-password</i> are the credentials
140
used to connect to the database. As they are stored in clear text in
141
rsyslog.conf, that user should have only the least possible privileges. It is
142
sufficient to grant it INSERT privileges to the systemevents table, only. As a
143
side note, it is strongly advisable to make the rsyslog.conf file readable by
144
root only - if you make it world-readable, everybody could obtain the password
145
(and eventually other vital information from it). In our example, let's assume
146
you have created a MySQL user named "syslogwriter" with a password of
147
"topsecret" (just to say it bluntly: such a password is NOT a good idea...). If
148
your MySQL database is on the local machine, your rsyslog.conf line might look
149
like in this sample:</p>
151
<p><code>*.*
152
>127.0.0.1,syslog,syslogwriter,topsecret</code></p>
154
<p>Save rsyslog.conf, restart rsyslogd - and you should see syslog messages
155
being stored in the "systemevents" table!</p>
156
<p>The example line stores every message to the database. Especially if you have
157
a high traffic volume, you will probably limit the amount of messages being
158
logged. This is easy to acomplish: the "write database" action is just a regular
159
selector line. As such, you can apply normal selector-line filtering. If, for
160
example, you are only interested in messages from the mail subsystem, you can
161
use the following selector line:</p>
163
<p><code>mail.*
164
>127.0.0.1,syslog,syslogwriter,topsecret</code></p>
166
<p>Review the <a href="rsyslog_conf.html">rsyslog.conf</a> documentation for
167
details on selector lines and their filtering.</p>
168
<p><b>You have now completed everything necessary to store syslog messages to
169
the MySQL database.</b> If you would like to try out a front-end, you might want
170
to look at <a href="http://www.phplogcon.org/">phpLogCon</a>, which displays
171
syslog data in a browser. As of this writing, phpLogCon is not yet a powerful
172
tool, but it's open source, so it might be a starting point for your own
174
<h2>On Reliability...</h2>
175
<p><b>This section needs updating. You can now solve the issue with failover
176
database servers. Read the <a href="rsyslog_conf.html">rsyslog.conf </a>doc on
178
<p>Rsyslogd writes syslog messages directly to the database. This implies that
179
the database must be available at the time of message arrival. If the database
180
is offline, no space is left or something else goes wrong - rsyslogd can not
181
write the database record. If rsyslogd is unable to store a message, it performs
182
one retry. This is helpful if the database server was restarted. In this case,
183
the previous connection was broken but a reconnect immediately succeeds. However,
184
if the database is down for an extended period of time, an immediate retry does
185
not help. While rsyslogd could retry until it finally succeeds, that would have
186
negative impact. Syslog messages keep coming in. If rsyslogd would be busy
187
retrying the database, it would not be able to process these messages.
188
Ultimately, this would lead to loss of newly arrived messages.</p>
189
<p>In most cases, rsyslogd is configured not only to write to the database but
190
to perform other actions as well. In the always-retry scenario, that would mean
191
no other actions would be carried out. As such, the design of rsyslogd is
192
limited to a single retry. If that does not succeed, the current message is will
193
not be written to the database and the MySQL database writer be suspended for a
194
short period of time. Obviously, this leads to the loss of the current message
195
as well as all messages received during the suspension period. But they are only
196
lost in regard to the database, all other actions are correctly carried out.
197
While not perfect, we consider this to be a better approach then the potential
198
loss of all messages in all actions.</p>
199
<p><b>In short: try to avoid database downtime if you do not want to experience
200
message loss.</b></p>
201
<p>Please note that this restriction is not rsyslogd specific. All approaches to
202
real-time database storage share this problem area.</p>
204
<P>With minimal effort, you can use rsyslogd to write syslog messages to a MySQL
205
database. Once the messages are arrived there, you can interactivley review and
206
analyse them. In practice, the messages are also stored in text files for
207
longer-term archival and the databases are cleared out after some time (to avoid
208
becoming too slow). If you expect an extremely high syslog message volume,
209
storing it in real-time to the database may outperform your database server. In
210
such cases, either filter out some messages or think about alternate approaches
211
involving non-real-time database writing (beyond the scope of this paper).</P>
212
<P>The method outlined in this paper provides an easy to setup and maintain
213
solution for most use cases, especially with low and medium syslog message
214
volume (or fast database servers).</P>
215
<h3>Feedback Requested</h3>
216
<P>I would appreciate feedback on this paper. If you have additional ideas,
217
comments or find bugs, please
218
<a href="mailto:rgerhards@adiscon.com">let me know</a>.</P>
219
<h2>References and Additional Material</h2>
221
<li><a href="http://www.rsyslog.com">www.rsyslog.com</a> - the rsyslog site</li>
223
<a href="http://www.monitorware.com/Common/en/Articles/performance-optimizing-syslog-server.php">
224
Paper on Syslog Server Optimization</a></li>
226
<h2>Revision History</h2>
229
<a href="http://www.adiscon.com/en/people/rainer-gerhards.php">Rainer Gerhards</a> *
230
initial version created</li>
232
<a href="http://www.adiscon.com/en/people/rainer-gerhards.php">Rainer Gerhards</a>
233
* added references to demo site</li>
235
<a href="http://www.adiscon.com/en/people/rainer-gerhards.php">Rainer Gerhards</a>
236
* removed demo site - was torn down because too expensive for usage count</li>
239
<p>Copyright (c) 2005-2007
240
<a href="http://www.adiscon.com/en/people/rainer-gerhards.php">Rainer Gerhards</a>
241
and <a href="http://www.adiscon.com/en/">Adiscon</a>.</p>
242
<p>Permission is granted to copy, distribute and/or modify this document under
243
the terms of the GNU Free Documentation License, Version 1.2 or any later
244
version published by the Free Software Foundation; with no Invariant Sections,
245
no Front-Cover Texts, and no Back-Cover Texts. A copy of the license can be
246
viewed at <a href="http://www.gnu.org/copyleft/fdl.html">
247
http://www.gnu.org/copyleft/fdl.html</a>.</p>
added
removed
doc/rsyslog_mysql.html
