How to Install Audit Plugins and Enable Auditing for MySQL/MariaDB

Database auditing is a security feature that records user operations within a database into a log file. For websites with high security requirements, enabling database auditing is essential for post-incident analysis and reporting. Since open-source versions of MySQL and MariaDB do not include an audit plugin by default, we must install one manually to enable this functionality.

Downloading and Installing the Database Audit Plugin

First, download the database audit plugin from the McAfee MySQL Audit GitHub repository.

1. Determine the MySQL Plugin Directory

Log in to your MySQL server and run the following command. The plugin_dir value in the output is the directory where we need to place the audit plugin.

mysql> show global variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.01 sec)

2. Copy the Plugin and Set Permissions

Copy the plugin file to the identified directory and ensure it has the correct ownership for the MySQL service.

[root@centos]/opt# cp /opt/audit/lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/
[root@centos]/opt# chown -R mysql:mysql /usr/local/mysql/lib/plugin/libaudit_plugin.so 

3. Register the Plugin in MySQL

Execute the following SQL command within the MySQL terminal to install the plugin:

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (0.42 sec)

If you encounter the following initialization error, you may need to use the offset-extract.sh script provided in the plugin package to extract the correct offsets for your specific database version and add them to your configuration before retrying.

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

4. Configure Auditing in the MySQL Configuration File

Add the following settings to your my.cnf or my.ini file under the [mysqld] section to enable and customize the audit logs:

[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

Verifying the Installation

Restart your database service and run the show plugins; command. If AUDIT appears in the results, the plugin has been successfully installed and is active.

Important Considerations

• The McAfee audit plugin has strict version requirements for the database. Success has been confirmed on MySQL 8.0.25 on Ubuntu 22.04, but you should verify compatibility for other versions.
• You must manually create the mysql-audit.json log file and grant the MySQL user write permissions for auditing to function.
• Using the offset-extract.sh script requires the gdb debugger. Install it using apt install gdb if necessary.