Configuring MariaDB to log to mysql.log using rsyslog

Question

I'm a little stumped - there is something obvious I'm missing here. Recently I made the switch from mySQL to mariaDB. Everything is working so far - except for the logging, as I realized today.

All the logs of mariaDB are going into the /var/log/daemon.log-logfile instead of /var/log/mysql.log - and I cannot, for the life of me, figure out why.

So, according to the my.cnf of mariadb

# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings            = 2

the logging is configured in /etc/mysql/conf.d/mysqld_safe_syslog.cnf. This file contains these line:

[mysqld_safe]
syslog
syslog-facility = mysql

So, okay - that should be fine, right? But ps -ef|grep logger shows:

logger -t mysqld -p daemon.error

So...what? Why? I really do not understand why it is taking the "daemon" facility? Okay, so digging a little deeper - I found the following. When trying to test the logging by invoking the logger myself, I get the following:

$> logger -p mysql.error test
logger: unknown facility name: mysql.

Okay, I thought - although that makes no sense to me, just use local1 then for now. But doing so, in changing the value in the mysqld_safe_syslog.cnf to

syslog-facility = local1

changes exactly nothing:

$> ps -ef|grep logger
logger -t mysqld -p daemon.error

I looked around, but could not find any viable information on this topic. Can you help me out? How do I get mariadb to log somewhere else?

Answer 1

Well, that was easy - my init-script was borked:

# priority can be overriden and "-s" adds output to stderr
ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql -i"

Changed it and everything is fine. Didn't come to think of actually checking the init script... well... next time ;)

Answer 2

For systems with systemd, we will need set an override to send log to syslog with the below directives

`[Service]
 StandardOutput=syslog
 StandardError=syslog
 SyslogFacility=local5
 SyslogLevel=err`

use systemctl edit mariadb for editing the mariadb startup. Then move all selector rules from /etc/rsyslog.conf to /etc/rsyslog.d/rules.conf (note: this is avoid duplication of logs due to default rsyslog select rules), inside /etc/rsyslog.d/rules.conf set the following content

#custom mariDB logging
if $syslogfacility-text == 'local5' or $programname == 'mysqld-scl-helper' or $programname == 'mysqld*' then {
        action(type="omfile" file="/var/log/mariadb/mariadb-error.log")
        action(type="omfwd" Target="syslogserver.com" Port="514" Protocol="tcp")
        *.* stop
}

# Log all kernel messages to the console.
# Logging much else clutters up the screen.
#kern.*                                                 /dev/console

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none;local5.none                /var/log/messages

# The authpriv file has restricted access.
authpriv.*                                              /var/log/secure

# Log all the mail messages in one place.
mail.*                                                  -/var/log/maillog


# Log cron stuff
cron.*                                                  /var/log/cron

# Everybody gets emergency messages
*.emerg                                                 :omusrmsg:*

# Save news errors of level crit and higher in a special file.
uucp,news.crit                                          /var/log/spooler

# Save boot messages also to boot.log
local7.*                                                /var/log/boot.log

Finally you will need to comment log-error directive from mariadb configuration as it might prevent sending complete log messages to remote syslog server.