Logging

When you are for too long in business you think you know already everything and you are getting lazy. This happened to me again a few weeks ago. A customer asked me about the SQL Error Log Plugin. First I though he was talking about the MariaDB Error Log or the General Query Log. But then I have learned that there is something “new” I did not know yet…

MariaDB introduced in 5.5.22 (March 2012) a new plugin called the SQL Error Log Plugin. This Plugin collects all the errors which were …

Sources:

• RSyslog Documentation
• How to Configure Remote Logging with Rsyslog on Ubuntu 18.04

This notes are intended for Ubuntu 18.04!

Install and Configure Rsyslog Server

dpkg -l | grep rsyslogd
apt-get update && apt-get install rsyslog

systemctl start rsyslog
systemctl enable rsyslog
systemctl status rsyslog

Check rsyslog version (v7 vs. v8!):

rsyslogd -v

General configuration

#
# /etc/rsyslog.conf
#

# provides UDP syslog reception
module(load="imudp")
input(type="imudp" …

Modern Linux Systems have a mechanism called logrotate to rotate different log files.

The general configuration file is located under /etc/logrotate.conf and specific changes are under /etc/logrotate.d/

By default the logrotate job is started once a day by a O/S cron.daily job: /etc/cron.daily/logrotate

Because the default log rotation configuration does not exactly what I want I have adapted it a bit:

First I need a database user for log rotation:

CREATE USER 'logrotate'@'localhost' IDENTIFIED BY 'secret'; …

We typically suggest our customers to use our MySQL/Galera Cluster my.cnf configuration template to avoid MySQL configuration and performance problems.

And we are paranoid as well. Thus we enable all useful logging:

wsrep_log_conflicts = 1

But this has also some consequences of more visibility…

If you monitor carefully your Galera Cluster for example with the FromDual Performance Monitor for MySQL and MariaDB, you might probably see some strange values increasing from time to time:

mysql< SHOW …

MySQL hosting providers can easily loose the overview over their customers and which user or schema is still in use and which not.

The MySQL database becomes bigger and bigger, uses more and more RAM and disk space and the backup takes longer and longer.

In this situation it would be nice to know which MySQL database user has logged in within the last 6 months for example. MySQL database users who did not login within a defined period can be backuped and removed from the production MySQL database.

The …

Problem

A customer recently showed up with the following problem:

*With your guidelines
[ 1
] I am now able to send the MySQL error log to the syslog and in particular to an external log server.
But I cannot see which user connects to the database in the error log.

How can I achieve this?*

Idea

During night when I slept my brain worked independently on this problem and in the morning he had prepared a possible solution for it.

What came out is the following:

• We create an UDF which allows an application to …