This section describes how the
plugin performs logging and the system variables that control
how logging occurs. It assumes familiarity with the log file
format described in Section 18.104.22.168, “The Audit Log File”.
When the audit log plugin opens its log file, it checks whether
the XML declaration and opening
root element tag must be written and writes them if so. When the
audit log plugin terminates, it writes a closing
</AUDIT> tag to the file.
If the log file exists at open time, the plugin checks whether
the file ends with an
</AUDIT> tag and
truncates it if so before writing any
<AUDIT_RECORD> elements. If the log
file exists but does not end with
</AUDIT> or the
</AUDIT> tag cannot be truncated, the
plugin considers the file malformed and fails to initialize.
This can occur if the server crashes or is killed with the audit
log plugin running. No logging occurs until the problem is
rectified. Check the error log for diagnostic information:
[ERROR] Plugin 'audit_log' init function returned error.
To deal with this problem, you must either remove or rename the malformed log file and restart the server.
The MySQL server calls the audit log plugin to write an
<AUDIT_RECORD> element whenever an
auditable event occurs, such as when it completes execution of
an SQL statement received from a client. Typically the first
<AUDIT_RECORD> element written after
server startup has the server description and startup options.
Elements following that one represent events such as client
connect and disconnect events, executed SQL statements, and so
forth. Only top-level statements are logged, not statements
within stored programs such as triggers or stored procedures.
Contents of files referenced by statements such as
INFILE are not logged.
To permit control over how logging occurs, the
audit_log plugin provides several system
variables, described following. For more information, see
Section 22.214.171.124, “Audit Log Plugin Options and System Variables”.
To control the audit log file name, set the
audit_log_file system variable
at server startup. By default, the name is
audit.log in the server data directory. For
security reasons, the audit log file should be written to a
directory accessible only to the MySQL server and users with a
legitimate reason to view the log.
The audit log plugin can use any of several strategies for log
writes. To specify a strategy, set the
variable at server startup. By default, the strategy value is
ASYNCHRONOUS and the plugin logs
asynchronously to a buffer, waiting if the buffer is full. It's
possible to tell the plugin not to wait
PERFORMANCE) or to log synchronously, either
using file system caching (
or forcing output with a
sync() call after
each write request (
Asynchronous logging strategy has these characteristics:
Minimal impact on server performance and scalability.
Blocking of threads that generate audit events for the shortest possible time; that is, time to allocate the buffer plus time to copy the event to the buffer.
Output goes to the buffer. A separate thread handles writes from the buffer to the log file.
A disadvantage of
PERFORMANCE strategy is
that it drops events when the buffer is full. For a heavily
loaded server, it is more likely that the audit log will be
With asynchronous logging, the integrity of the log file may be
compromised if a problem occurs during a write to the file or if
the plugin does not shut down cleanly (for example, in the event
that the server host crashes). To reduce this risk, set
audit_log_strategy to use
synchronous logging. Regardless of strategy, logging occurs on a
best-effort basis, with no guarantee of consistency.
If the file system to which the audit log is being written fills up, a “disk full” error is written to the error log. Audit logging continues until the audit log buffer is full. If free disk space has not been made available by the time the buffer fills, client sessions will hang, and stopping the server at the time of client sessions hanging will result in audit log corruption. To avoid this if client sessions are hung, ensure that free space is available on the audit logging file system before stopping the server.
The audit log plugin provides several system variables that enable you to manage the space used by its log files:
audit_log_buffer_size: Set this variable at server startup to set the size of the buffer for asynchronous logging. The plugin uses a single buffer, which it allocates when it initializes and removes when it terminates. The plugin allocates this buffer only if logging is asynchronous.
audit_log_flush: These variables permit audit log file rotation and flushing. The audit log file has the potential to grow very large and consume a lot of disk space. To manage the space used, either enable automatic log rotation, or manually rename the audit file and flush the log to open a new file. The renamed file can be removed or backed up as desired.
audit_log_rotate_on_size=0and there is no log rotation. In this case, the audit log plugin closes and reopens the log file when the
audit_log_flushvalue changes from disabled to enabled. Log file renaming must be done externally to the server. Suppose that you want to maintain the three most recent log files, which cycle through the names
audit.log.3. On Unix, perform rotation manually like this:
From the command line, rename the current log files:
mv audit.log.2 audit.log.3shell>
mv audit.log.1 audit.log.2shell>
mv audit.log audit.log.1
At this point, the plugin is still writing to the current log file, which has been renamed to
Connect to the server and flush the log file so the plugin closes it and reopens a new
SET GLOBAL audit_log_flush = ON;
audit_log_rotate_on_sizeis greater than 0, setting
audit_log_flushhas no effect. In this case, the audit log plugin closes and reopens its log file whenever a write to the file causes its size to exceed the
audit_log_rotate_on_sizevalue. The plugin renames the original file to have a timestamp extension. For example,
audit.logmight be renamed to
audit.log.13440033615657730. The last 7 digits are a fractional second part. The first 10 digits are a Unix timestamp value that can be interpreted using the
SELECT FROM_UNIXTIME(1344003361);+---------------------------+ | FROM_UNIXTIME(1344003361) | +---------------------------+ | 2012-08-03 09:16:01 | +---------------------------+
The audit log plugin can filter audited events. This enables you to control whether it writes events to the audit log file based on the account from which events originate or event status. Status filtering occurs separately for connection events and statement events.
As of MySQL 5.6.20, to filter audited events based on the originating account, set one of these system variables at server startup or runtime:
The value for either variable can be
a string containing one or more comma-separated account names,
format. By default, both variables are
in which case, no account filtering is done and auditing occurs
for all accounts.
Example: To enable audit logging only for the
user2 local host
account accounts, set the
system variable like this:
SET GLOBAL audit_log_include_accounts = 'user1@localhost,user2@localhost';
-- This sets audit_log_exclude_accounts to NULL SET GLOBAL audit_log_include_accounts =
value; -- This fails because audit_log_include_accounts is not NULL SET GLOBAL audit_log_exclude_accounts =
value; -- To set audit_log_exclude_accounts, first set -- audit_log_include_accounts to NULL SET GLOBAL audit_log_include_accounts = NULL; SET GLOBAL audit_log_exclude_accounts =
SHOW VARIABLES LIKE 'audit_log_include_accounts';+----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | audit_log_include_accounts | | +----------------------------+-------+ mysql>
SELECT @@audit_log_include_accounts;+------------------------------+ | @@audit_log_include_accounts | +------------------------------+ | NULL | +------------------------------+
If a user name or host name requires quoting because it contains
a comma, space, or other special character, quote it using
single quotes. If the variable value itself is quoted with
single quotes, double each inner single quote or escape it with
a backslash. The following statements each enable audit logging
for the local
root account and are
equivalent, even though the quoting styles differ:
SET GLOBAL audit_log_include_accounts = 'root@localhost'; SET GLOBAL audit_log_include_accounts = '''root''@''localhost'''; SET GLOBAL audit_log_include_accounts = '\'root\'@\'localhost\''; SET GLOBAL audit_log_include_accounts = "'root'@'localhost'";
The last statement will not work if the
ANSI_QUOTES SQL mode is enabled because in
that mode double quotes signify identifier quoting, not string
As of MySQL 5.6.20, to filter audited events based on status, set these system variables at server startup or runtime:
Each variable takes a value of
ALL (log all
associated events; this is the default),
ERRORS (log only failed events), or
NONE (do not log events). For example, to log
all statement events but only failed connection events, use
SET GLOBAL audit_log_statement_policy = ALL; SET GLOBAL audit_log_connection_policy = ERRORS;
Before MySQL 5.6.20,
not available. Instead, use
audit_log_policy at server
startup or runtime. It takes a value of
(log all events; this is the default),
(log connection events),
statement events), or
NONE (do not log
events). For any of those values, the audit log plugin logs all
selected events without distinction as to success or failure.
As of MySQL 5.6.20,
audit_log_policy is still
available but can be set only at server startup. At runtime, it
is a read-only variable. Its use at startup works as follows:
If you do not set
audit_log_policyor set it to its default of
ALL, any explicit settings for
audit_log_statement_policyapply as specified. If not specified, they default to
If you set
audit_log_policyto a non-
ALLvalue, that value takes precedence over and is used to set
audit_log_statement_policy, as indicated in the following table. If you also set either of those variables to a value other than their default of
ALL, the server writes a message to the error log to indicate that their values are being overridden.
Startup audit_log_policy Value Resulting audit_log_connection_policy Value Resulting audit_log_statement_policy Value
To check the effect of filtering, you can inspect the values of these status variables:
Audit_log_events: The number of events handled by the audit log plugin, whether or not they were written to the log based on filtering policy.
Audit_log_events_filtered: The number of events handled by the audit log plugin that were filtered (not written to the log) based on filtering policy.
Audit_log_events_written: The number of events written to the audit log.
These variables are available as of MySQL 5.6.20.