Bug #55370 mysqld general log output to syslog
Submitted: 19 Jul 2010 15:46 Modified: 25 Sep 2014 3:51
Reporter: Kyle Joiner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Logging Severity:S4 (Feature request)
Version:>=5.1.20 OS:Any
Assigned to: Tatjana Nuernberg CPU Architecture:Any
Tags: error log, syslog
Triage: Needs Triage: D3 (Medium)

[19 Jul 2010 15:46] Kyle Joiner
Description:
When --syslog is enabled there is not method to determine if syslog is enabled from the system variables.  Or if a system variable could be added to allow enabling/disabling syslog.

How to repeat:
use >=5.1.20 and enable --syslog.

Suggested fix:
Add a system variable to enable/disable syslog.
[20 Jul 2010 5:56] Simon Mudd
1. I suggest the variable: 'log_with_syslog = 0 or 1'
2. I also suggest that there is an option to determine which syslog facility is used for logging. I believe this is currently hardcoded.

The current implementation is not transparent and for servers which can not be shutdown easily (busy master servers) the inability to "see" the running config, or to change it is not convenient.
[25 Aug 2010 2:09] Marc Alff
Bug#55370 Give a system variable or indication when syslog is enabled.

ANALYSIS
========

Overall, several places are using logging and or syslog,
so some clarifications are needed to understand the context.

(a)

The server itself (mysqld) is capable of logging messages:
- in stdout / stderr
- in a log file
- in a log table
bug the general server logs can not currently be written in syslog,
so I assume this request is not related to the general log.

(b)

When the server is configured --with-libwrap, TCP wrappers are used.
In this case, the server is compiled with
#define HAVE_LIBWRAP
which includes some code that does use openlog() and syslog().

What is printed in the syslog() in this case is:
- always printed, there is no runtime on/off flag
- consist of a message like "refused connect from <client>"

However, the mysqld process itself does not have a --syslog command line
option. If the code is build with TCP wrappers, syslog is used, otherwise
it's not. In that regards, usage of syslog() is hard coded and not flexible.

(c)

A shell script, named mysqld_safe.sh, also uses syslog() in a different
context.
This script does have --syslog and related options, and can print messages
to syslog() or log files.

(d)

In the NDB cluster, some programs also uses syslog(), and have --syslog
command line options.

Please clarify in which context the syslog() is used, as this is unclear.

As far as:
- knowing if syslog is used
- dynamically changing syslog() logging on or off,
the answer depends on the context.

In context (a), this is not applicable.

In context (b), in the mysqld server itself,
- adding a command line option --syslog to mysqld
- adding a global system variable, writable, to turn the logging on/off
dynamically
is a reasonable feature request.

In context (c), in the mysqld_safe.sh script,
the problem is that there are no system variables outside of mysqld
that can be read / modified using SQL statements like:
- show variables like "syslog"
- set @@syslog=0
because this level of logging is completely external -- and unknown --
to the server.

To know whether syslog() is used, a suggestion could be to do
a "ps | grep mysqld_safe" to see if "--syslog" was passed as a
command line argument to the script.

The script could also be modified to print that information in a file,
similar to a .pid file.

As far as changing logging on the fly, this is another matter.
A possible way is to use a custom logger capable of that.

I would expect the volume of messages written to the syslog()
by mysqld_safe to be low volume, since in my understanding,
only critical messages like "the server started" or "the server was stopped"
are generated. A rationale explaining why turning log on/off in this
context is important, would be welcome, to help understand the issue better.

In any case, in context (c), the desired functionality can
be achieved by writing/modifying administration scripts,
and no change is needed in the core server itself,
at least from my understanding.

In context (d), further analysis from the NDB team will be needed.

Changing this report to "need feedback",
as the resolution will be very different depending on which
exact component using syslog() is involved and needs to be changed.

Regards,
-- Marc Alff
[25 Aug 2010 7:11] Simon Mudd
Thanks for the clarification in how syslog is used in mysql.

I was pointed to the fact that in MySQL 5.1 syslogging can be enabled and looked at http://dev.mysql.com/doc/refman/5.1/en/mysqld-safe.html, but the documentation is rather vague as I'm now not entirely sure _exactly_ what is logged to syslog.

Perhaps the documentation can be improved in this respect?

However, my [mis]understanding was that it was possible with mysqld to send the error log (log-error parameter) information also to syslog. If that's not the case then perhaps this requires a slightly different feature request:

1. Permit the possibility of sending logging that can be sent to file/database to also be sent to syslog.
2. Ensure that a global variable shows if this setting is enabled or not, and that it can be configurable dynamically.
3. Provide a way to determine an appropriate syslog facility to ensure the logging goes to the right place.
4. If possible provide a mechanism so the severity of logging can be configured as required. I'm not sure if this is appropriate/possible in mysql.

Thus it seems that my initial understanding of what --syslog actually does was wrong. The "enhanced" request above may be a much more significant change than in principal I had originally thought but it would certainly be useful to be able to send "mysql logging" to syslog.

I hope this clarifies what I was looking for.
[25 Aug 2010 7:17] Simon Mudd
You also asked why one should want to disable/enable this functionality.

I would expect the default behaviour (like with logging to tables rather than the log-error file) to be disabled.  That is current default behaviour does not change.

If I want to change this behaviour I really DO NOT want to have to restart mysqld. Some of the servers I manage stay up for a long time (over a year) and only get restarted for a version upgrade, or perhaps hardware replacement. From my point of view it therefore makes a lot of sense to make the syslogging behaviour configurable and not to require unnecessary server restarts. Having any configuration options available as "global variables" means that the current configuration is always visible to the dba, which is also important.
[25 Aug 2010 17:44] Marc Alff
Thanks for the feedback.

To summarize, the request is as follows

1)

The scope of the change is the general log facility within the mysqld server,
noted "(a)" in my previous comments.

Currently, the general log output can be directed to:
- a log file
- a log table

The request is to also allow to direct the general log output to:
- the system syslog()

2)

Existing system variables can be used to:
- read whether the general log is enabled or disabled
- read what is the current output of the general log is directed
to a: NONE, FILE, or TABLE

These system variables should be reused / extended to also indicate
whether the general log output is directed to SYSLOG

The existing "log_output" system variable should be extended
to accept "NONE", "FILE", "TABLE" (all existing), and "SYSLOG" (new).

3)

Existing system variables can be dynamically modified to:
- turn the general log on of off
- change the general log output(s).

This existing feature should be extended for SYSLOG,
so that changing the output between all logging facilities is allowed.

For example, a DBA could:
- change the output from FILE to SYSLOG
- change the output from SYSLOG to TABLE
- enable of disable logging to SYSLOG,
- etc, all dynamically, without restarting the server.

4)

When logging to SYSLOG, the SYSLOG facility should be named.
This is similar in concept to giving a file name when logging to a FILE.

5)

When logging to SYSLOG, the message severity in the server (note, warning, error) should be logged with different severities in SYSLOG, to:
- preserve the severity information,
- allow for easier filtering / monitoring / reporting / alerts

------

Based on these clarifications, the issue here shifted from
"expose a new system variable to see some existing functionality"
to
"implement a new functionality and expose it with system variables",
since logging to syslog in the general log is not an existing feature.

Changing the report title to a more descriptive name.
Changing category to Tracing & Logging
Changing to verified

Thanks for this valuable feature request.

Regards,
-- Marc Alff
[26 Aug 2010 6:45] Simon Mudd
Hi Marc,

One comment. I had anticipated that syslog logging configuration be independent of the "log to file/table" options.

Why? This originally came up when looking at a master / hot standby server pair.
If the master server crashes we anticipate switching over to the "hot standby" by mounting the db storage on the new box which previously would have been idle.
The configuration of mysqld on both servers would be identical. We currently have logging stored to /var/log/mysqld.log LOCALLY.

So the syslog functionality was to enable us to have an EXTRA copy of the mysqld logging sent via syslog (configured to send the logging over the network to a central syslog server) to enable us to have a global view of logging before and after the server switchover.

While it's possible to log to the filer location where the db is stored (/mysql/<instance_name>/log/... in our case) we prefer to only store the database files on the filer.

So your interpretation to make the syslogging an extra option (rather than to file/table) rather than an independent option (independently of how logging is configured to file/table) is completely valid but not what I had in mind.

I can see arguments for both sides, and having centralised syslogging wouldn't be bad as of course the local syslog daemon can be told to write locally AND to any remote syslog daemon so perhaps this is not really an issue after all.
[24 Oct 2010 3:26] Terry Nycum
Would it be possible to broaden this to ALL logging--not just what currently goes in the "general" log? (The only thing I can think of that couldn't go through syslog would be information relevant to troubleshooting problems calling syslog.)

In my case the current logging is a security issue. I'd like our host-based intrusion detection system (IDS) to be able to protect mysqld from brute force attacks just as it does sshd. To do this, though, it needs to know when a login failure occurs. It seems the only circumstance under which mysqld exposes this information is if general logging is enabled, but that means a large appetite for disk space for what is 99% noise (e.g. queries). There's also the issue of format. Every host-based intrusion detection package I've seen has syslog as a supported event source. I'm not aware of any that support parsing mysql's general log format. There's a lot of existing software compatible with syslog for monitoring, viewing, analysis, etc, that would become applicable to mysql events if only syslog were used to log them.

Concretely, it would be great if mysqld would
1. send authentication events (failures and successes, with user and source IP:port) to authpriv.log or auth.log,
2. send other non-query events (e.g. mysqld startup/shutdown, recovery start/finish) events to daemon.log, and
3. send query events (e.g. slow, all, errors, warnings, whatever the user has opted for) and anything else that would only show up in the current general log to a custom mysql.log with event type/category used to distinguish them.

It's a farther-reaching request, but it subsumes several more specific existing feature requests, reduces a whole field of future ones, and takes logging implementation and featureset off the plate of the MySQL team. Logging to syslog already represents the ablity to log to file(s), database(s), e-mail inbox(es), you name it, with virtually limitless configurability. The logging-related options handled by mysqld could be generally limited to the names of the log facilities to which various types of events should be sent (or not sent), and perhaps their severities. Some of those "types" that come to mind:
daemon start/stop
authentication failures
authentication successes
recovery start/stop
slow queries
queries causing errors
queries causing warnings
non-slow, successful queries

Any type of event you might want to change the handling of without restarting mysqld you could configure mysqld to always send to syslog, then use syslog rules to modify their destination or severity, or censor them altogether. That way a "hot" change could be done by signaling syslog to reload its configuration--no restart of mysqld required. You could also see the current syslog rules anytime in a text editor.

For syslog implementations that offer configuration directories, MySQL packages could easily add/remove rulesets by adding a, e.g., mysqld.conf file to that directory. Of course it could still just use defaults for the facility and severity options mentioned above, but it would be a potential option.

Ideally, this request could be mapped to the system-wide logging facilities of other supported OSes. For instance, in Windows, it would make sense to send events to the Security and Application logs, and a custom MySQL log, respectively, for actions 1-3 above. Windows Log (2000+) and Windows Eventing (Vista+) also offer configurable filtering and forwarding, and a well-known format with which some existing software tools can work.

Thanks for your consideration.
[11 Jan 2012 23:14] Richard Secrist
The ability to log at least failed connection attempts to the SYSLOG is highly desirable for security purposes.  If the proposal by Marc Alff in his 25-Aug-2010 17:44 entry is not possible, please at least consider providing the ability to log failed connection attempts to the SYSLOG.  Alternatively, stronger password protections like those in the Oracle RDBMS Server, such as the ability to lockout an account after a number of failed attempts, and the enforcement of password complexity, etc. would be even better.
[13 Sep 2013 11:59] Simon Mudd
While this is a really old ticket another issue which has come up repeatedly since then is that sometimes it's not possible to filter out certain unwanted entries.

Two easy examples I can give are:

(from 5.6)
2013-09-13 13:53:04 9872 [Warning] Client failed to provide its character set. 'utf8' will be used as client character set.
(from another server running 5.5)
130913 13:53:48 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave. Statement: ...removed_unsafe_statement_as_not_relevant...

syslog daemons, or at least applications which manage syslog such as syslog-ng, have the ability if configured that way, to filter out, or process certain messages. If I can't get mysqld to not tell me about issues, then using syslog I can redirect the output to an application which is capable of helping me not see the unwanted noise.

While improving the logging may be a large topic enabling mysqld to be syslog aware potentially helps resolve some issues by taking the problem away from mysqld. That's good for us all.

We're moving to development in 5.7 now, so perhaps this can be reassessed?
[25 Sep 2014 3:51] Paul Dubois
Noted in 5.7.5 changelog.

Previously, on Unix and Unix-like systems, MySQL support for sending
the server error log to syslog was implemented by having mysqld_safe
capture server error output and pass it to syslog. The server now
includes native syslog support, which has been extended to include
Windows. mysqld_safe still has syslog capability, but this is now
deprecated and native server support should be used instead.