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:
ValueMeaning
ASYNCHRONOUSLog asynchronously, wait for space in output buffer
PERFORMANCELog asynchronously, drop request if insufficient space in output buffer
SEMISYNCHRONOUSLog synchronously, permit caching by operating system
SYNCHRONOUSLog 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  audit_log.so 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:

  • The file can grow very large on disk:

Searching the Audit Log entries

MySQL utilities provide a useful tool, mysqlauditgrep, to search / grep the logs file.  Unfortunately, I was not able to make it work (tried both v. 1.3 and v 1.4)  with audit plugin format created by Percona server. According to this bug  it can’t parse the “new” audit format. In my case, mysqlauditgrep will return a parsing error when I use the default format and returned no results when I set the “audit_log_format=NEW”. It will be nice to use the mysqlauditgrep as it looks like a very powerful tool, but for now our searching options are limited to conventional linux grep (which is not very easy for XML documents) or custom application to parse/search XML.

Performance overhead of Audit Log Plugin and General Log 

Finally, I wanted to measure the overhead of the Audit Log Plugin compared to General Log. I did a quick benchmark with sysbench OLTP test (CPU bound workload) with 4 modes:

  1. Audit Plugin disabled (to measure baseline)
  2. Audit Plugin enabled and logs all queries
  3. Audit Plugin enabled and logs only logins
  4. General Log enabled, Audit Plugin disabled

Here are the results:

TestOverhead
Plugin +  audit_log_policy = ALL~15% overhead
Plugin +  audit_log_policy = LOGINS~0% overhead (sysbench only connects once, so there may be bigger overhead here)
General_log~62% overhead

As we can see here, audit log is not free from overhead, however, it is much smaller than enabling general_log to log all and every query. Those are quick benchmark results and more tests are need for more accurate measurements. Also, as always, your milage can vary.

Nice to have features

What I would love to have for audit plugin is the ability to log only some specific actions. For example, only log activity from a specific user or access to a specific table (i.e. a table with a sensitive data), etc. This will give more control and less overhead (=better performance).

Conclusion

The MySQL Audit Plugin is a great feature – it is a valuable tool for MySQL security and performance audits. The performance overhead may be a concern for a highly loaded systems, however, it looks reasonable and is much better than using general log to log all queries.

If you use general log or any other audit plugins, please share your experience in the comments.

21 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Baron

The wording of this article makes it completely unclear whether this plugin is THE official audit log plugin, or an open-source clone of it. This is not a minor nitpick. You make it sound like a user can somehow get a proprietary product for free by some kind of end-run around Oracle’s MySQL Enterprise. Please clarify if this is the case, and if it is NOT the case, please be very careful not to make opensource alternatives to official MySQL products sound like the official products themselves. It’s vitally important for users to know whether they are using a genuine trademarked product or an aftermarket alternative.

0
Alexander Rubin

Hi Baron,

This is very important, thank you for pointing out this concern. 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.
Also noted here: https://www.percona.com/doc/percona-server/5.5/management/audit_log_plugin.html

I’ve updated the post with this information. And also made a correction regarding passwords in MySQL 5.6.

0
Yuvi

Hi Alexander,

I am trying to install the Percona audit plugin for MySQL 5.5.38 – 35.2 but I am running into below issues.

mysql> install plugin audit_log soname ‘audit_log.so’;
ERROR 1123 (HY000): Can’t initialize function ‘audit_log’; Plugin initialization function failed.

In the error log it is showing up as below

[ERROR] Plugin ‘audit_log’ registration as a AUDIT failed.

 [ERROR] Plugin ‘audit_log’ init function returned error.

First I modifed the my.cnf file with the following variables like “audit_log_policy=All, audit_log_rotate_on_size=1G, audit_log_format=CSV,etc.,”. and then tried to install the plugin as above which is throwing the error.

Can you please provide me the input why its throwing up above errors.

Thanks.

0
Daniël van Eeden

I think you should mention the mcafee and skysql audit plugins.

It’s good to see some competition for audit solutions.

0
Peter Zaitsev

Alex,

Looking at the Audit you’re observing overhead 15% vs 62% for general log. Do you have any explanation for such difference ? Is it much more efficient implementation or is it something else – like amount of information captured is a lot different ? Did you enable plugin in Asynchronous mode so all queries were captured or Performance which means some queries could not be logged due to lack of log space ?

0
Franck Leveneur

Good to know.
We’ve been using MacAfee plugin for several weeks now.
The log file can be quiet big (> 20GB).
Glad to see you’re using XML instead of MacAfee json format.

0
Paul Carlucci

Thank you, your timing is impeccible. I can only hope that you have whitelist filtering on the list of future enhancements. I don’t want to log heavy activity against 1000 tables if only 5 of them have sensitive data. i might not want to log certain commands and i may only want to log interactive users and ignore logging sessions from the web server pool.

0
Antony Curtis

PeterZ,

The Audit API was intended to provide a superset functionality of the General/Slow log and the original intent was that MySQL 6.0 would have the Audit API and the existing General/Slow log code be removed and their functionality replaced with plugins.

0
Alexander Rubin

Daniël,

Yes, I’ve updated the post to add information about McAfee Audit plugin and MariaDB Audit Plugin. Thank you for pointing it out as well.

0
Alexander Rubin

PeterZ,

I’ve used Asynchronous mode for my tests. I will make another set of tests to measure the overhead.

0
rucypli

good! i will test it right now!

0
Arnoldas

Alexander thanks for great article, I am fency trying to audit now what mysql server is doing and what could be optimized. Yeah tried to see for myself by installing mysql-utilities-1.3.6-1.el6.noarch : MySQL Utilities

ERROR: Malformed XML – Cannot parse log file: ‘/var/lib/mysql/audit.log’
Invalid XML element: ‘<AUDIT_RECORD "NAME"="Audit"

Maybe you could also share some linux grep filters to group audit.log into some humand readable report?

0
Arnoldas

Okay I bashed my keyboard for a bit 🙂 here’s what I came with, to start you going

grep “SQLTEXT” /var/lib/mysql/audit.log | grep -E “SELECT|INSERT|UPDATE|DELETE|REPLACE” | sed -E ‘s/’.*+’/?/g’ | sort | uniq -c | sort -nr -k1 | head -10

1. Grep out only sql queries from audit log file
2. As I am intrested only in standart queries, I explicitly list them
3. We want to group queries with sort, so we replace/delete values in single quote part like select * from where user=’Arnas’ to change it to select * from where user=?
4. We sort the queries
5. Count only uniq lines
6. Sort by numbers, in column 1
7. List only 10 lines from top

Good luck 🙂

0
Jörg

Doesnt seem to work with the Debian Build:
mysql> select version();
+———————-+
| version() |
+———————-+
| 5.5.37-0+wheezy1-log |
+———————-+
mysql> INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
ERROR 1126 (HY000): Can’t open shared library ‘/usr/lib/mysql/plugin/audit_log.so’ (errno: 2 /usr/lib/mysql/plugin/audit_log.so: undefined symbol: my_pthread_fastmutex_init)

0
Egezon Berisha

It would be really nice for some variables to be dynamic. For example audit_log_format, audit_log_flush, audit_log_strategy etc. Any chance this would happen in the future?

0
Bisser Todorov

mysqlauditgrep version 1.5.2 for example works perfect with OLD format audit.log files if you just comment out 2 rows in /usr/lib/python2.7/site-packages/mysql/utilities/common/audit_log_parser.py
# if self.header_rows:
# record[‘SERVER_ID’] = self.header_rows[0][‘SERVER_ID’]

There is no SERVER_ID in Percona’s audit log 🙁

0
Pavel Katiushyn

I’ve recently tested plugins from Percona and MariaDB. The first one really adds about 15% overhead, while second one adds about 40%.
+1 for having filters for specific queries. For example, in my case SELECTS are not needed for audit, while they are filling audit.log significantly.

0
Pavel Katiushyn

McAffee Audit plugin is the biggest performance killer with 80% overhead.
But also it has the most reach config options.
When I excluded select statements, the overhead was only about 20%, that is close to Percona. It may not be the same for write heavy db.
Laos McAfee plugin has more filters, than previous two plugins and logs all changes done by procedures (not only call procedure).

0
Himanshu

Hi,
I am encountering below error in 5.6 while installing audit plugin.
mysql> INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
ERROR 1126 (HY000): Can’t open shared library ‘/usr/lib64/mysql/plugin/audit_log.so’ (errno: 2 /usr/lib64/mysql/plugin/audit_log.so: cannot open shared object file: No such file or directory)

Can someone help ?

0
Yudhveer Kandukuri

Hi,

I am running into an error below when I am trying to install the Percona audit plugin for MySQL

I defined first the audit plugin variables based on my requirement (like audit_log_file_format=CSV,audit_log_rotation_size=1G,etc.,) and then tried to install the plugin but ran into below issues

mysql> install plugin audit_log soname ‘audit_log.so’;
ERROR 1123 (HY000): Can’t initialize function ‘audit_log’; Plugin initialization function failed.

Can any please provide me your opinion why the above issue got raised

0
Yudhveer Kandukuri

Here are my version details
mysql> show global variables like ‘%version%’;
+————————-+————————————————–+
| Variable_name | Value |
+————————-+————————————————–+
| innodb_version | 5.5.38-35.2 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.38-35.2-log |
| version_comment | Percona Server (GPL), Release 35.2, Revision 674 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+————————-+————————————————–+
7 rows in set (0.00 sec)

0