How to Build a Lightweight MySQL/MariaDB Audit Log Platform with LogAnalyzer

The Database Audit Log Platform (DB Audit) records user actions on MySQL/MariaDB databases in real time, providing fine‑grained compliance auditing and instant risk alerts. It enables DBAs to generate post‑incident reports, trace incidents, and efficiently query audit data through a searchable interface.

Technical Options

Two approaches are possible:

Option 1: Deploy a network‑side tap that captures TCP packets from the database server and processes them in data‑collection, processing, and security‑detection modules.

Option 2 (chosen): Use MariaDB’s built‑in Audit Plugin, forward its syslog output via Rsyslog to a dedicated MySQL database, and visualize the logs with LogAnalyzer.

In MariaDB 10.6, the audit plugin can send logs to Rsyslog, which can then store them in a MySQL database for later analysis.

Deployment Steps

Environment Overview

Install the MariaDB Audit Plugin on the primary MySQL/MariaDB server.

Configure Rsyslog on the primary server to use the ommysql module.

Prepare a dedicated MySQL server to store syslog audit data.

Set up a LAMP stack and deploy the LogAnalyzer web UI on the same host as the syslog MySQL server.

Step‑by‑step Configuration

1. Copy server_audit.so to the plugin directory on each database node and load the plugin:

MySQL> INSTALL PLUGIN server_audit SONAME 'server_audit.so';

2. Set the audit plugin to output to syslog:

MySQL> SET GLOBAL server_audit_output_type = syslog;

3. Configure the syslog facility to filter only audit logs:

MySQL> SET GLOBAL server_audit_syslog_facility = LOG_LOCAL6;

4. Restrict logged events to DDL and DML (exclude SELECT) to reduce noise:

MySQL> SET GLOBAL server_audit_events = 'QUERY_DDL,QUERY_DML_NO_SELECT';

5. Enable audit logging: MySQL> SET GLOBAL server_audit_logging = 1; 6. Install the Rsyslog MySQL driver on the primary server: Shell> yum install rsyslog-mysql -y 7. Create a MySQL user for Rsyslog on the dedicated syslog database:

MySQL> GRANT ALL ON Syslog.* TO 'rsyslog'@'localhost' IDENTIFIED BY 'rsyslogpass';
MySQL> GRANT ALL ON Syslog.* TO 'rsyslog'@'%' IDENTIFIED BY 'rsyslogpass';

8. Create the syslog database and tables using the provided script:

Shell> rpm -ql rsyslog-mysql
/usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql
Shell> mysql -h127.0.0.1 -ursyslog -prsyslogpass < /usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql

9. Configure Rsyslog to forward audit logs to the MySQL database. Edit /etc/rsyslog.conf and load the required modules:

# MODULES
$ModLoad imudp           # load UDP module
$UDPServerRun 514       # listen on UDP 514
$ModLoad imtcp           # load TCP module
$InputTCPServerRun 514   # listen on TCP 514
$ModLoad ommysql          # load MySQL output module

10. Add a rule to send the selected facility (local6) to the syslog MySQL server:

# RULES
$ActionOmmysqlServerPort 3306
local6.* :ommysql:192.168.198.239,Syslog,rsyslog,rsyslogpass

11. Restart Rsyslog to apply changes: Shell> systemctl restart rsyslog At this point, any DDL/DML operations on the primary MySQL/MariaDB instance are logged to the Syslog database’s SystemEvents table.

Install and Configure LogAnalyzer

12. Install the web stack and LogAnalyzer:

Shell> yum -y install httpd php php-mysql php-gd
Shell> systemctl start httpd.service
Shell> cd /var/www/html/
Shell> wget https://download.adiscon.com/loganalyzer/loganalyzer-4.1.12.tar.gz
Shell> tar zxvf loganalyzer-4.1.12.tar.gz
Shell> mv loganalyzer-4.1.12 loganalyzer
Shell> touch loganalyzer-4.1.12/src/config.php
Shell> chmod 755 loganalyzer-4.1.12/src/config.php

13. Open a browser and run the LogAnalyzer installer (e.g., http://your_ip/loganalyzer/src/install.php) and configure the data source:

Source type: MySQL Native

View: Syslog Fields

Table type: MonitorWare

Database host: 127.0.0.1

Database name: Syslog

Table name: SystemEvents

User: rsyslog

Password: the password set earlier

Complete the wizard to finish LogAnalyzer setup. The web UI now provides searchable, real‑time audit reports, allowing administrators to monitor database activity, detect violations, and trace incidents.