IntroductiontothePerconaMySQLAuditLogPlugin_MySQL

Percona has developed a MySQL Audit Log plugin that is now included in Percona Server since the recent5.5and5.6releases. 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. This post is a quick introduction to this plugin.

Installation
There are two ways to install the Percona MySQL Audit Plugin:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

INSTALL PLUGINaudit_logSONAME'audit_log.so';

or in my.cnf

[mysqld]plugin-load="audit_log=audit_log.so"

[mysqld] plugin-load="audit_log=audit_log.so"

Verify installation

mysql> SHOW PLUGINS/G...*************************** 38. row ***************************  Name: audit_logStatus: ACTIVE  Type: AUDITLibrary: audit_log.soLicense: GPL38 rows in set (0.00 sec)

mysql>SHOWPLUGINS/G ... ***************************38.row*************************** Name: Status: Type: Library: License: 38rowsinset(0.00

Configuration
Let’s see variables provided by the Percona MySQL Audit Plugin:

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 |+--------------------------+--------------+7 rows in set (0.00 sec)

mysql>showglobalvariableslike'audit%'; +--------------------------+--------------+ |Variable_name |Value | +--------------------------+--------------+ | |audit_log_file |audit.log | | | |audit_log_policy |ALL | | | | +--------------------------+--------------+ 7rowsinset(0.00

The Percona MySQL Audit Plugin can log using the memory buffer to deliver better performance. Messages will be written into memory buffer first and then flushed to file in background. A certain amount of events can be lost in case of server crash or power outage. Another option is to log directly to file without using memory buffer. There is also an option to fsync every event.

Set audit_log_strategy to control log flushing:

• ASYNCHRONOUS log using memory buffer, do not drop events if buffer is full
• PERFORMANCE log using memory buffer, drop events if buffer is full
• SEMISYNCHRONOUS log directly to file, do not fsync every event
• SYNCHRONOUS log directly to file, fsync every event

audit_log_buffer_size specifies the size of memory buffer, it makes sense only for ASYNCHRONOUS and PERFORMANCE strategy.

Variable audit_log_file specifies the file to log into. It’s value can be path relative to datadir or absolute path.

The Percona MySQL Audit Plugin can automatically rotate log file based on size. Set audit_log_rotate_size to enable this feature. File is rotated when log grew in size to specified amount of bytes. Set audit_log_rotations to limit the number of log files to keep.

It is possible to log only logins or only queries by setting audit_log_policy value.

Log file format
Lets see how audit records look like

OLD format (audit_log_format = OLD):

"NAME"="Connect" "RECORD"="2_2014-04-21T12:34:32" "TIMESTAMP"="2014-04-21T12:34:32 UTC" "CONNECTION_ID"="1" "STATUS"="0" "USER"="root" "PRIV_USER"="root" "OS_LOGIN"="" "PROXY_USER"="" "HOST"="localhost" "IP"="" "DB"=""/>

NEW format (audit_log_format = NEW):

 Connect 17481_2014-04-21T12:39:03 2014-04-21T12:39:05 UTC 4 0 root root   localhost  test

Connect 17481_2014-04-21T12:39:03 2014-04-21T12:39:05 UTC 4 0 root root localhost test

The difference is that the audit record in the OLD format was written as a single element with attributes, while in the NEW format it is written as a single element with sub-elements.

A good idea of what each sub-element means can be found in Audit Plugin API documentation here:https://dev.mysql.com/doc/refman/5.6/en/writing-audit-plugins.html.

Performance
Lets compare performance of different audit_log_strategy modes. I used readonly sysbench on my laptop for it. Workload is CPU-bound with dataset fit in buffer pool and I set number of sysbench threads to the amount for which count of transactions per seconds is maximum.

I got TPS drop for PERFORMANCE and ASYNCHRONOUS strategies around 7%, 9% for SEMISYNCHRONOUS and 98% for SYNCHRONOUS which shows that syncing every logged statement to disk is not the best thing for performance.

Conclusion
Of course any software has bugs and this plugin has plenty of them. Please give it a try and provide us your feedback. Report any issues here:https://bugs.launchpad.net/percona-server.