Which MySQL Audit Plugin Performs Best? A Deep Dive into MariaDB, Percona, and McAfee

Audit Overview

Database audit functions record user operations for later query, analysis, and troubleshooting. This article surveys open‑source audit plugins and explains their installation and usage.

1. General Log

MySQL provides a general log that records every statement, but it has drawbacks: it logs all statements including failed ones, generates large volumes of useless data, impacts I/O under high concurrency, and lacks automatic rotation.

1.2 Binlog

Binary log (binlog) records all data‑modifying operations, allowing audit of data changes and schema modifications. However, binlog does not contain connection user information, so additional steps are required to correlate thread IDs with user sessions.

Example: root@localhost process id 18494655 creates a table.

Parsing binlog can retrieve thread IDs and match them to process IDs, but storing this information requires configuring init_connect and creating a table to record connection details.

Problems: all users need write permission on the access_log table, the table requires manual maintenance, super‑user connections are not recorded, and binlog cannot audit events not logged in binlog.

MariaDB Audit Plugin

2.1 Supported Versions

MySQL 5.7 and earlier.

2.2 Installation

a. Install plugin.

b. Configuration file.

c. Load plugin on MySQL 5.7.

d. Loading issues on MySQL 8.0.

2.3 Related Parameters

Configuration parameters for enabling and customizing audit events.

2.4 Audit Events

List of audit events that can be captured.

2.5 Example

a. Audit only connect DDL.

b. Audit only connect DML.

c. Audit only connect DCL.

Percona Audit Plugin

3.1 Supported Versions

Percona MySQL branch.

3.2 Installation

a. Plugin is built‑in, no extra installation required.

b. Configuration file.

3.3 Related Parameters

a. audit_log_format (OLD, NEW, JSON).

3.4 Example

JSON log example:

{"audit_record":{"name":"Query","record":"4082_2020-05-07T03:28:39","timestamp":"2020-05-07T03:30:42Z","command_class":"insert","connection_id":"8","status":1136,"sqltext":"insert into t1 values(0)","user":"root[root]@localhost []","host":"localhost","os_user":"","ip":"","db":""}}

{"audit_record":{"name":"Query","record":"4083_2020-05-07T03:28:39","timestamp":"2020-05-07T03:30:50Z","command_class":"insert","connection_id":"8","status":0,"sqltext":"insert into t1 values(0,1)","user":"root[root]@localhost []","host":"localhost","os_user":"","ip":"","db":""}}

{"audit_record":{"name":"Query","record":"4084_2020-05-07T03:28:39","timestamp":"2020-05-07T03:30:52Z","command_class":"insert","connection_id":"8","status":0,"sqltext":"insert into t1 values(0, 2)","user":"root[root]@localhost []","host":"localhost","os_user":"","ip":"","db":""}}

McAfee Audit Plugin

4.1 Supported Versions

MySQL 5.7 and earlier.

4.2 Installation

a. Install plugin.

b. Configuration file.

c. Load plugin on MySQL 5.7.

d. Load plugin on MySQL 8.0.

4.3 Related Parameters

Parameters for controlling audit command whitelist and JSON file synchronization.

4.4 Example

JSON activity log example:

{"msg-type":"activity","date":"1588835829430","thread-id":"7","query-id":"30","user":"msandbox","priv_user":"msandbox","ip":"127.0.0.1","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"18280","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql"},"rows":"5","status":"0","cmd":"show_databases","objects":[{"db":"information_schema","name":"/home/vicigel/sandboxes/msb_5_7_25_1/tmp/#sql_3b49_0","obj_type":"TABLE"}],"query":"show databases"}

{"msg-type":"activity","date":"1588835829430","thread-id":"7","query-id":"31","user":"msandbox","priv_user":"msandbox","ip":"127.0.0.1","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"18280","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql"},"status":"0","cmd":"show_tables","objects":[{"db":"information_schema","name":"/home/vicigel/sandboxes/msb_5_7_25_1/tmp/#sql_3b49_0","obj_type":"TABLE"}],"query":"show tables"}

Comparison Summary

Audit granularity: Percona = McAfee > MariaDB.

Log format control: Percona > McAfee = MariaDB.

Performance impact controllability: Percona > McAfee > MariaDB. Percona’s plugin offers the most flexible configuration and best performance‑security trade‑off; McAfee follows; MariaDB lags behind. For MySQL deployments on the Percona branch, the built‑in audit plugin is the optimal choice, while the other plugins require thorough testing before production use.