MySQLAuditPluginnowavailableinPerconaServer5.5and5_MySQL

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

Originally, the only “easy” option was toenable 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 showthe overhead of MySQL general and slow log. The main benefit of MySQL Log Audit plugin is that it logs all queriesasynchronously(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 asdecribed in the doc:

mysql> select version();+-------------+| version() |+-------------+| 5.5.37-35.0 |+-------------+1 row in set (0.00 sec)mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';Query OK, 0 rows affected (0.00 sec)

Now can see all MySQL audit plugin options:

mysql> show global variables like '%audit%';+--------------------------+--------------+| Variable_name| Value|+--------------------------+--------------+| audit_log_buffer_size| 1048576|| audit_log_file | audit.log|| audit_log_flush| OFF|| audit_log_format | OLD|| audit_log_policy | ALL|| audit_log_rotate_on_size | 0|| audit_log_rotations| 0|| audit_log_strategy | ASYNCHRONOUS |+--------------------------+--------------+8 rows in set (0.00 sec)

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

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.

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:

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

Copy the file:

# cp audit_log.so /usr/local/mysql/lib/plugin/

Install plugin:

Server version: 5.5.37 MySQL Community Server (GPL)mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';Query OK, 0 rows affected (0.00 sec)Server version: 5.6.17 MySQL Community Server (GPL)mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';Query OK, 0 rows affected (0.00 sec)

Using MySQL audit plugin

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

…

Important notes:

ls -lah /var/lib/mysql/audit.log-rw-rw---- 1 mysql mysql 7.1G May 4 07:30 /var/lib/mysql/audit.log

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 tothis 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 withsysbenchOLTP 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:

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.

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com