Enable Audit Logging in MySQL/MariaDB with the Audit Plugin

Audit logging is essential for high-security applications. It tracks user activity in the database, providing valuable records for later investigation. By default, open-source versions of MySQL and MariaDB do not include audit logging, so we must manually install an audit plugin to enable this feature.

In this post, we’ll walk through how to install the McAfee MySQL Audit Plugin for MySQL or MariaDB, configure it, and verify that audit logging is working correctly.

Step 1: Download the Audit Plugin

You can download the McAfee MySQL Audit Plugin from its GitHub repository:

https://github.com/mcafee/mysql-audit

Step 2: Locate the MySQL Plugin Directory

To find the plugin directory used by your MySQL or MariaDB instance, log in to your database and run:

SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

This will output a directory path, such as:

+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+

This is where you’ll copy the audit plugin.

Step 3: Copy the Plugin File

Assuming the compiled plugin is located at /opt/audit/lib/libaudit_plugin.so, use the following commands to copy it into your plugin directory and ensure MySQL has ownership:

cp /opt/audit/lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/
chown -R mysql:mysql /usr/local/mysql/lib/plugin/libaudit_plugin.so

Step 4: Install the Plugin via SQL

In the MySQL client, run:

INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

If you encounter this error:

ERROR 1123 (HY000): Can't initialize function 'AUDIT'; Plugin initialization function failed.

…it usually means the plugin doesn’t support your MySQL version directly. Try using the provided offset-extract.sh script from the plugin repo to generate the correct offsets and add them to your config file.

Step 5: Configure the Audit Plugin

Edit your MySQL configuration file (usually my.cnf or my.ini) and add:

[mysqld]
audit_json_file = on
plugin-load = AUDIT=libaudit_plugin.so
audit_record_cmds = 'insert,delete,update,create,drop,alter,grant,truncate'
audit_json_log_file = /home/mysql/mysql-audit.json
audit_offsets = 8544, 8584, 4064, 5536, 520, 0, 0, 32, 64, 160, 608, 8700, 5168, 4208, 4216, 4220, 6840, 1656, 32, 7800, 7840, 7824, 11624, 140, 664, 320

The audit_offsets line should match your MySQL binary version. You can get this info using the offset-extract script mentioned above.

Step 6: Restart and Verify

After saving the configuration file, restart your MySQL/MariaDB server. Then verify that the plugin was installed correctly:

SHOW PLUGINS;

If AUDIT appears in the list, your plugin is active. You should also find the audit log file being written to the path you specified, e.g., /home/mysql/mysql-audit.json.