You’ve already read it in What’s new in 5.7 (So Far) — the MySQL server now has new-and-improved supported for syslog (on unix-like systems) and EventLog (on Windows). In the next few paragraphs, we’ll take a look at what they are, what has changed, and how they can make your life easier.

The MySQL server supplies information in two main ways:

1. The client will receive a reply to every statement. If everything goes right, then we’ll see a simple OK for success, or a result set for SELECT, SHOW, etc.; and even a successful statement may be qualified by a set of warnings or notices. If the statement fails for some reason then we’ll receive an error regarding the failure.
2. On the server, we’ll see a variety of logs depending on the server configuration. Queries exceeding a certain execution time may get logged to the slow query log, while in some debug scenarios all queries may be logged to the general query log either as soon as they are received (--log-raw) or after parsing and having security sensitive information such as passwords replaced. Both the slow query log and the general query log may be sent to a file or table. The third major log is the error log.

The error log — what and why

Like the clients, the error log may receive messages of varying severities. It collects messages that do not have an obvious query, client, or connection that is to blame, messages that need persistence beyond that of a connection or the server’s runtime, and others that have an intended audience of the DBA or system administrator rather than the application developer. Examples are exhaustion of resources on the host machine, certain errors reported by the host operating system, backtraces of crashes, messages about damaged databases and indexes, errors on start-up, failed or refused connections, etc.

The error log — where and why

Unlike the slow and general query logs, the error log cannot be optionally stored in a database table (this would lead to questions such as how to safely log an error about the table engine malfunctioning while using that same engine for error logging), but rather only in a plain text file on the filesystem. This makes the error log persistent beyond the runtime of the server, and makes the log file accessible to specialized log analyzer tools.

Enter syslog

Both Unix and Windows do however already have standardized logging facilities — syslog on Unix and EventLog on Windows. Using those facilities gives us access to a plethora of tools written specifically for them, it lets us log to a different machine, etc. While the MySQL server (mysqld) previously had very basic support for the Windows EventLog, syslogging was implemented by simply writing error messages to a stream (STDOUT/STDERR). Through this stream, whatever started the server (e.g. mysqld_safe) may receive or redirect this information at the OS level (note: if you start mysqld directly these streams will be visible in your terminal window; on Windows you have to use the --console option). The mysqld_safe wrapper script would start the MySQL server and optionally direct this error output to a program that would write it to the Unix syslog.

Onwards and upwards

While that syslog support solved the immediate problem, there was room for improvement — all MySQL server errors were filed with a syslog severity of error, even if the message said something different. A line could contain one time-stamp from the MySQL server, and another from the syslog facilities. And those were just the cosmetic issues!

Native syslogging

Having native support for syslog in the MySQL server itself enables us to turn syslogging on or off at runtime, and to query its status.

The syslog facility—information about what category of software the message were from—is also no longer fixed to daemon but can be queried and configured at runtime. This should make it easier to adapt the MySQL syslog messages to your syslog environment and enable better message filtering. See the manual page for syslog.conf (shell> man syslog.conf) for more on filtering.

The following options have also been added and can be set at start-up as well as viewed and set at runtime via system variables (setting them requires the SUPER privilege):

1. --log_syslog
2. --log_syslog_include_pid
3. --log_syslog_facility
4. --log_syslog-tag

mysqld_safe

While the mysqld_safe wrapper script has been updated to be aware of the server’s new native syslog support, the options to the script remain unchanged. Where before, mysqld_safe used the program logger to pass the server’s error messages to syslog, the script now selects server options consistent with the options given to the script. Therefore mysqld_safe --syslog --syslog-tag=AZOU would result in mysqld being started with mysqld --log-syslog=1 --log-syslog-facility=daemon --log-syslog-tag=AZOU.

It bears noting that the options to mysqld_safe control both the server and the mysqld_safe script itself. In the above example, the MySQL server would log as mysqld-AZOU, whereas the mysqld_safe wrapper script would log as mysqld_safe-AZOU.

systemd-journal

A variety of linux systems have recently adopted systemd. Its logging facilities offer a sink that is compatible to traditional syslog, making the MySQL server’s native support compatible out of the box. When systemd journaling is used, corresponding tools should be used to query and filter the logs, e.g. the journalctl program to query the logs. In the following example, our running MySQL server has the tag production (and therefore logs as mysqld-production).

We could then use journalctl to ask for all log entries using that identifier:
journalctl -f SYSLOG_IDENTIFIER=mysqld-production

Due to the richness of systemd-journal logging, this is somewhat verbose. That’s why in the following example we ask for the output to be JSON-formatted. We’ll then use the jq tool to filter this JSON stream so that only the fields we are interested in remain—SYSLOG_IDENTIFIER, SYSLOG_FACILITY, and MESSAGE (see the systemd.journal-fields manual page for others):
journalctl -f SYSLOG_IDENTIFIER=mysqld-production -o json | jq '.SYSLOG_IDENTIFIER + " " + .SYSLOG_FACILITY + ": " + .MESSAGE' 2>/dev/null

Windows

On Windows EventLog is used in lieu of syslog. Like on Unix logging can be enabled, disabled, and queried at runtime. The facility and process ID options, however, are not available on Windows. A tag can be set and queried at runtime, but to create a new tag (start using a tag that does not already exist) the MySQL server needs to be started with sufficient Windows system privileges needed to create an entry in the Windows Registry. Once this entry is created then the elevated system privileges are not required to use the newly created tag. As the entry exists in the Windows registry and therefore outside the MySQL server, it will persist through restarts of the MySQL server and indeed the host machine. Care should therefore be taken not to pollute the registry with a great number of unused tags.

All’s well that sends well

Finally, after talking so much about the server (and laying the groundwork for future Log Lady reports), I would be amiss not to note that the MySQL client now also supports syslog. I look forward to your feedback on these new features! If you encounter any issues, please let us know by filing a bug or opening a support ticket.

Thank you for using MySQL!