This section describes how to configure audit logging characteristics, such as the file to which the audit log plugin writes events and the format of written events.
For additional information about the system variables that affect audit logging, see Audit Log Options and System Variables.
The audit log plugin can also control which audited events are written to the audit log file, based on the account from which events originate or event status. See Section 22.214.171.124, “Audit Log Filtering”.
To control the audit log file name, set the
variable at server startup. The default name is
audit.log in the server data directory.
For best security, write the audit log to a directory
accessible only to the MySQL server and to users with a
legitimate reason to view the log.
When the audit plugin initializes, it checks whether a file
with the audit log file name already exists. If so, the plugin
checks whether the file ends with an
</AUDIT> tag and truncates the tag
before writing any
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 exits unexpectedly 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, either remove or rename the malformed log file and restart the server.
To configure the audit log file format, set the
variable at server startup. These formats are available:
NEW: New-style XML format.
OLD: Old-style XML format. This is the default.
For details about each format, see Section 126.96.36.199, “Audit Log File Formats”.
Changing the value of
audit_log_format can result
in writing log entries in one format to an existing log file
that contains entries in a different format. To avoid this
issue, use the following procedure:
Stop the server.
Either change the value of the
audit_log_filesystem variable so the plugin writes to a different file, or rename the current audit log file manually.
Restart the server with the new value of
audit_log_format. The audit log plugin creates a new log file and writes entries to it in the selected format.
The audit log plugin can use any of several strategies for log writes. Regardless of strategy, logging occurs on a best-effort basis, with no guarantee of consistency.
To specify a write 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
SEMISYNCHRONOUS) or forcing output with a
sync() call after each write request
For asynchronous write strategy, the
variable is the buffer size in bytes. Set this variable at
server startup to change the buffer size. The plugin uses a
single buffer, which it allocates when it initializes and
removes when it terminates. The plugin does not allocate this
buffer for nonasynchronous write strategies.
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.
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 exits unexpectedly). To reduce
this risk, set
audit_log_strategy to use
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 hang, and stopping the server at the time of client sessions hanging results 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.
A disadvantage of
PERFORMANCE strategy is
that it drops events when the buffer is full. For a heavily
loaded server, the audit log may have events missing.
The audit log file has the potential to grow quite large and
consume a great deal of disk space. To enable management of
the space used by its log files, the audit log plugin provides
variables, which control audit log file rotation and flushing.
Rotation can be done manually, or automatically based on file
Manual audit log file rotation.
is 0 (the default), no log rotation occurs except when
performed manually. In this case, the audit log plugin
closes and reopens the log file when the
changes from disabled to enabled. Log file renaming must be
done externally to the server. Suppose that the log file
audit.log and you want to
maintain the three most recent log files, cycling through
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.3 mv audit.log.1 audit.log.2 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;
Automatic size-based audit log file rotation.
is greater than 0, setting
audit_log_flush has no
effect. Instead, whenever a write to the log file causes its
size to exceed the
value, the audit log plugin automatically closes the file,
renames it, and opens a new log file.
The renamed file has a timestamp and
added to the end. For example, if the file name is
audit.log, the plugin renames it to a
value such as
timestamp value is similar to a Unix timestamp, with the last
7 digits representing the fractional second part. By inserting
a decimal point, the value can be interpreted using the
mysql> SELECT FROM_UNIXTIME(1508180793.7726520); +-----------------------------------+ | FROM_UNIXTIME(1508180793.7726520) | +-----------------------------------+ | 2017-10-16 14:06:33.772652 | +-----------------------------------+
With size-based log file rotation, renamed log files have unique names and accumulate indefinitely. They do not rotate off the end of the name sequence. To avoid excessive use of space, remove old files periodically, backing them up first as necessary.