MariaDB/MySQL slow query log to syslog (and other output modules)

MariaDB (fork of MySQL), with a few lines of configuration, provide a very interesting log regarding application-level query performances: the slow-logs.
Sadly, and for a reason I have yet to discover, these logs are not syslog-enabled. Thus, you cannot beneficiate of all the advantages of using the syslog protocol, ie: centralization, backup/archive, parsing, alerting… To make things even easier, these very interesting logs are multi-lines…
In a company where application developers are interested by the performances of their application and to optimize their code and queries, receiving information about the production application-level slow queries is necessary.
Here’s a sample mariadb/rsyslog configuration that achieve slow-logs to syslog and also generates a snmptrap per slow query (you could use the smtp output module instead or any other rsyslog output module for what matters).

  • /etc/my.cnf.d/logging.cnf
[mariadb]

# Slow query Logging Settings
slow_query_log=1
slow_query_log_file=/var/log/mariadb/mariadb-slow.log
long_query_time=2
  • /etc/rsyslog.d/zz-mariadb.conf
#
# MariaDB-Syslog Config File
#

if $app-name == 'mysqld' then /var/log/mariadb/mariadb.log
& stop

if $app-name == 'mysqld-scl-helper' then /var/log/mariadb/mariadb.log
& stop

# Slow logs (not syslog-enabled)
module(load="imfile" PollingInterval="10") # once
input(type="imfile"
    File="/var/log/mariadb/mariadb-slow.log"
    Tag="mysqld-slowlog"
    Severity="info"
    Facility="local2"
    # Because logs are multi-line
    # Time: 180618 16:23:33
    # Time: 180618 8:43:16
    startmsg.regex="^# Time: [[:digit:]]{6} "
    readTimeout="10"
    # Remove LF escaping so that it's properly formatted within Castlerock
    escapeLF="off"
)
  • /etc/rsyslog.d/zz-mariadb-slowq-to-trap.conf
# Send SNMP traps for slow logs
module(load="omsnmp") # once
$actionsnmptransport udp
$actionsnmptarget <snmp_server_ip_addr_or_fqdn>
$actionsnmptargetport 162
$actionsnmpversion 1 # means SNMPv2c
$actionsnmpcommunity <snmp_community>
$actionsnmptrapoid <snmp_oid>

if $app-name == 'mysqld-slowlog' then :omsnmp:
& stop

No comments yet, d'oh!

Share your thoughts

*