MySQL Audit Plugin now available in Percona Server 5.5 and 5.6

The MySQL Audit Plugin is now available for free in Percona ServerThe new Percona Server 5.5.37-35.0 and Percona Server 5.6.17-65.0-56, announced yesterday (May 6), both include the open source version of the MySQL Audit Plugin. The MySQL Audit Plugin is used to log all queries or connections (“audit” MySQL usage). Until yesterday’s release, the MySQL Audit Plugin was only available in MySQL Enterprise.

EDIT:  Just to be clear, this implementation is alternative to the MySQL Enterprise Audit Log Plugin. Percona re-implemented the Audit Plugin code as GPL as Oracle’s code was closed source.

EDIT 2: I should also mention: two other Open Source Audit Plugin implementations existed for a while: McAfee MySQL Audit Plugin and MariaDB Audit Plugin for MySQL. Both these implementation use their own audit log formats different from what Oracle’s implementation is using. Percona’s implementation is the first to be a drop-in replacement for MySQL Enterprise Audit Plugin.

Logging all MySQL usage is very important for a number of applications, for example:

  • Required: applications which deals with sensitive data (credit cards, medical records, etc); required for security compliances (i.e. HIPAA)
  • Very helpful: multi-tenants applications or MySQL as a service; MySQL administrators can audit the MySQL usage from the security and performance standpoint
  • Very helpful: investigating and troubleshooting; it is great to have a full log of all queries, which can help a lot for troubleshooting of MySQL and even for performance audit.

Originally, the only “easy” option was to enable general log. (Other options included using binary logs which does not include select queries or enabling queries “trace” in the application or MySQL connector). However, logging all queries using a general log may dramatically decrease performance in the highly loaded MySQL applications: Aleksandr Kuzminsky published a benchmark in 2009 to show the overhead of MySQL general and slow log. The main benefit of MySQL Log Audit plugin is that it logs all queries asynchronously (can be changed in the config). I’ve decided to try the new audit plugin in Percona Server and measure the performance impact of the new plugin compared to enabling the general log for the CPU bound applications.

How to start with MySQL Audit Plugin

First, we will need to enable (or “install”) MySQL audit plugin as decribed in the doc:

Now can see all MySQL audit plugin options:

There are a bunch of options we can tweak here, the most important for MySQL performance are:

  • audit_log_buffer_size; this buffer is used to cache the queries (for asynchronous operation).
  • audit_log_strategy; All options are listed in the documentation page:
Value Meaning
ASYNCHRONOUS Log asynchronously, wait for space in output buffer
PERFORMANCE Log asynchronously, drop request if insufficient space in output buffer
SEMISYNCHRONOUS Log synchronously, permit caching by operating system
SYNCHRONOUS Log synchronously, call sync() after each request

The most useful option in my mind is ASYNCHRONOUS, providing us with good balance between performance and not loosing transactions if the output buffer is not large enough.

  •  audit_log_policy; we can log all queries or MySQL logins only (very useful if we only need to audit MySQL connections)

Open Source Audit Plugin in MySQL Community server

You can also use Percona Open Source version of Audit Plugin in MySQL community version (5.5.37 and 5.6.17). Simply download the linux tarball of Percona Server and copy the to your MySQL plugin dir.

Find plugin dir:

Copy the file:

Install plugin:

Using MySQL audit plugin

When plugin is enabled, it will log entries in audit.log file in XML format. Example:

 Important notes: 

  • As all queries will be logged here, the passwords from “GRANT” will also be saved in clear text (as you can see above). It is very important to secure the file on disk.

EDIT: Clear text passwords issue only applies to MySQL 5.5 version.  As of MySQL 5.6.3, passwords in statements written to the general query log are rewritten by the server not to occur literally in plain text (quote from the documentation).

In MySQL 5.6 version here is what we will see: