MariaDB Audit Plugin - Set Up a Remote Log File using rsyslog

While running a test on MariaDB Enterprise Beta for installing MariaDB Galera Cluster, I thought that this is a perfect environment to check out how to setup a remote log file for the MariaDB Audit Plugin. Although I am using MariaDB Galera Cluster in this case, you can setup a remote log file in the same way, if you are:

  • Using a group of MariaDB Servers with standard replication enabled
  • Using a group of isolated MariaDB Servers
  • Using just one MariaDB Server

What we want to achieve by using a remote log file is:

  • To get a central log file, which we can use to analyze what is going on in our database server environment
  • To get the auditing log file separated from the host(s) running the database server(s)
  • To be able to use an even stricter access right for the auditing log, than we have for the database server

My Environment

For installing MariaDB Galera Cluster via MariaDB Enterprise Beta, I have used four CentOS VMs. The first VM is acting as Manager Node for MariaDB Enterprise (Cluster Management and Monitoring). The three other ones are running MariaDB Cluster Nodes (5.5.33a-MariaDB-log MariaDB Server, wsrep_23.7.6), installed via the MariaDB Manager.

Schematic 1

Now as I have three MariaDB Servers running on three different hosts, I want to:

  1. Enable auditing for these servers
  2. Use the management node as the host to store the remote auditing log file

Schematic 2

Enable Auditing

To enable auditing for the three nodes, we firstly need to copy the MariaDB Audit Plugin library server_audit.so in the plugin directory of each server. Then we can load the plugin on all our nodes:

INSTALL PLUGIN server_audit SONAME 'server_audit.so';

If you are running a Galera Cluster, you will realize that, by executing the INSTALL PLUGIN statement on one node, all three nodes will load the plugin as the INSTALL PLUGIN statement will be replicated.

Configure Audit Plugin

Based on the default settings for the Maria Audit Plugin we would get a file server_audit.log in the data directory as the audit log file, defined for the MariaDB instance.

The MariaDB Audit Plugin also supports to use rsyslog to log events. rsyslog itself gives us a lot of options, one of these is to forward log entries to a remote rsysog process.

To be able to use the local rsyslog, we need to change the variable server_audit_output_type. We can do this by executing the following on each server:

SET GLOBAL server_audit_output_type = syslog;

You also need to execute it on all nodes, when you are running MariaDB Galera Cluster. As we only want to forward syslog entries created by the MariaDB Audit Plugin, we will use the variable server_audit_syslog_facility to be able to configure a filter for rsyslog.

SET GLOBAL server_audit_syslog_facility=LOG_LOCAL6;

Configure rsyslog

Everything is configured now for the MariaDB Audit Plugin. We could enable auditing now, but it would only use the local syslog. Therefore we leave the auditing itself for now to have a look at what needs to be set up for rsyslog, to get syslog entries forwarded to remote rsyslog process.

As mentioned at the beginning of this blog, in my case the VM, which is running the MariaDB Manager, is the server, which I want the central audit log file to be stored at. I therefore need to configure rsyslog on this machine to capture events sent from my three MariaDB Galera Cluster nodes. I can do this by editing file /etc/rsyslog.conf.

What I need to do is to search for the text

$ModLoad imtcp
$InputTCPServerRun 514

and to add the lines:

:FROMHOST-IP, isequal, "192.168.56.21" /var/log/MariaDBAudit.log
:FROMHOST-IP, isequal, "192.168.56.22" /var/log/MariaDBAudit.log
:FROMHOST-IP, isequal, "192.168.56.23" /var/log/MariaDBAudit.log

You will be not surprised that the three IP addresses are the three MariaDB Galera Cluster nodes. In this example entries will be added to the central file /var/log/MariaDBAudit.log for all nodes.

After restarting rsyslog using:

/sbin/service rsyslog restart

the new setting is active.

The next step is to create a forwarding rule for the rsyslog service running on the VMs, which are serving the MariaDB Galera Cluster nodes. We again need to open /etc/rsyslog.conf and search for the text:

### begin forwarding rule ###

Now we edit this block, which at the end will look like

# ### begin forwarding rule ###
# The statement between the begin ... end define a SINGLE forwarding
# rule. They belong together, do NOT split them. If you create multiple
# forwarding rules, duplicate the whole block!
# Remote Logging (we use TCP for reliable delivery)
#
# An on-disk queue is created for this action. If the remote host is
# down, messages are spooled to disk and sent when it is up again.
$WorkDirectory /var/lib/rsyslog # where to place spool files
$ActionQueueFileName fwdRule1 # unique name prefix for spool files
$ActionQueueMaxDiskSpace 1g # 1gb space limit (use as much as possible)
$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
$ActionQueueType LinkedList # run asynchronously
$ActionResumeRetryCount -1 # infinite retries if host is down
# remote host is: name/ip:port, e.g. 192.168.0.1:514, port optional
local2.* @@192.168.56.20:514
# ### end of the forwarding rule ###

The first settings are about creating an on-disk queue, which is recommended to not get any log entry lost.

The last line

local6.* @@192.168.56.20:514

is defining the forwarding rule. Do you remember? We changed the variable server_audit_syslog_facility to LOG_LOCAL6 for the audit plugin. local6.* is now used as filter to only forward syslog entries using facility local6 to rsyslog running on 192.168.56.20, my MariaDB Enterprise Management node. Port 514 is the standard port used by rsyslog, "@@" stands for "TCP is used".

To activate the changes for rsyslog, the last step is to restart rsyslog again.

/sbin/service rsyslog restart

Now, as rsyslog is correctly configured on all nodes, we can enable auditing by using:

SET server_audit_logging=ON;

Least but not last we should assure, by adding the MariaDB Audit Plugin variables to my.cnf, that our configuration will not get lost after a MariaDB restart.

[mysqld]
server_audit_output_type = syslog
server_audit_syslog_facility=LOG_LOCAL6
server_audit_logging = ON

If you need more information about the MariaDB Audit Plugin itself, please use one of:

About the Author

Ralf Gebhardt is Principal Sales Engineer at SkySQL Ab with the primary responsibility for Germany, Austria and Switzerland. Ralf has over 20 years industry experience in Software Development, Support, Training and Consulting. Ralf started at MySQL GmbH as Sales Engineer in 2002 up until 2011 through the acquisition of Sun Microsystems and Oracle. In 2011 Ralf rejoined the original founder of MySQL at SkySQL Ab.