How to Configure MySQL Enterprise Auditing

Configuring Enterprise Audit

Enterprize auditing can be implemented using the audit_log server plugin. It is available with Enterprise Edition subscriptions.

To install the audit_log plugin, use the INSTALL PLUGIN syntax, as in the following example:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Alternatively, set the plugin-load option at server startup:

[mysqld]
plugin-load=audit_log.so

Policy-based logging:

– Is set with the audit_log_policy option – Offers the choice of logging ALL, NONE, LOGINS, or QUERIES – Defaults to ALL

When auditing is eanbled, it produces an audit record of server activity in a log file. The contents depend on policy, and can include:

• A record of errors that occur on the system
• When clients connect and disconnect
• What actions they perform while connected
• Which databases and tables they access

By default, loading the plugin enables logging. Setting the option audit-log to OFF disables logging. To prevent the plugin from being removed at run time, set the following option:

audit-log=FORCE_PLUS_PERMANENT

The log file is named audit.log, and by default is in the server data directory. To change the name or location of the file, set the audit_log_filesystem variable at server startup. To balance compliance with performance, use the audit_log_strategyoption to choose between SYNCHRONOUS, ASYNCHRONOUS, SEMISYNCHRONOUS, and PERFORMANCE. If you set the audit_log_rotate_on_size option to some number greater than 0, the log file is rotated when its size exceeds that number of 4 KB blocks.

For more information about configuring Enterprise Audit, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/audit-log-plugin-options-variables.html

Audit Log File

The audit log file is written as XML, using UTF-8. The root element is <AUDIT> and the closing <AUDIT> tag of the root element is written when the plugin terminates. The tag is not present in the file while the audit log plugin is active. Each audit entry is an <AUDIT_RECORD /> element as shown below:

<AUDIT_RECORD TIMESTAMP="2012-10-12T09:35:15" NAME="Connect" CONNECTION_ID="4" STATUS="0" USER="root" PRIV_USER="root" OS_LOGIN="" PROXY_USER="" HOST="localhost" IP="127.0.0.1" DB=""/>
<AUDIT_RECORD TIMESTAMP="2012-10-12T09:38:33" NAME="Query" CONNECTION_ID="4" STATUS="0" SQLTEXT="INSERT INTO tbl VALUES(1, 2)"/>

The TIMESTAMP of each audit record is in UTC. The NAME attribute represents the type of event. For example, “Connect” indicates a login event, “Quit” indicates a client disconnect, and “Shutdown” indicates a server shutdown. “Audit” and “NoAudit” indicate the points at which auditing starts and stops. The STATUS attribute provides the command status. This is the same as the Code value displayed by the MySQL command ‘SHOW ERRORS’. Some attributes appear only for specific event types. For example, a “Connect” event includes attributes such as HOST, DB, IP, and USER, and a “Query” event includes the SQLTEXT attribute.

For more information about the Enterprise Audit log file, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/audit-log-file.html

Log File Rotation

Log files take up space over time. Periodically back up and remove old log files and recommence logging to new log files. Use caution if you are using binary logs for replication. After backup, flush the logs. Flushing the logs - creates new binary log files, Closes and re-opens the general and slow query log files. To create new logs, you must rename the current log files before flushing.

You can also schedule log file rotation at regular intervals. Create your own script or use the provided mysql-logrotate script (RHEL only). If you are using a Red Hat Enterprise-based operating system such as Oracle Linux, use the mysql-log-rotatescript in /usr/share/mysql/, provided as part of the RPM installation.

After backing up all the log files, flush the logs to force the MySQL server to start writing to new log files. Execute the FLUSH LOGS SQL statement, or run a suitable client command. Flushing the logs causes binary logging to recommence with the next file in the sequence. With the general and slow query logs however, flushing merely closes the log files, reopens them and then recommences logging under the same file name. To start new logs, rename the existing log files before flushing. Example:

# cd mysql-data-directory
# mv mysql.log mysql.old
# mv mysql-slow.log mysql-slow.old
# mysqladmin flush-logs

For more information about managing log files, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/log-file-maintenance.html.