MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
This section describes how to control general characteristics of audit logging, 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 6.4.4.5, “Audit Log Filtering”.
To control the audit log file name, set the
audit_log_file
system
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 <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 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 control the audit log file format, set the
audit_log_format
system
variable at server startup. By default, the format is
OLD
(old-style XML format). For information
about available formats, see
Section 6.4.4.3, “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_file
system
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
audit_log_strategy
system
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
(SYNCHRONOUS
).
For asynchronous write strategy, the
audit_log_buffer_size
system
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
synchronous logging.
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
the audit_log_rotate_on_size
and audit_log_flush
system
variables, which control audit log file rotation and flushing.
Rotation can be done manually, or automatically based on file
size.
Manual audit log file rotation.
If audit_log_rotate_on_size
is 0 (the default), no log rotation occurs except that
performed manually. In this case, the audit log plugin
closes and reopens the log file when the
audit_log_flush
value
changes from disabled to enabled. Log file renaming must be
done externally to the server. Suppose that the log file
name is audit.log
and you want to
maintain the three most recent log files, cycling through
the names audit.log.1
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
audit.log.1
.
Connect to the server and flush the log file so the plugin
closes it and reopens a new audit.log
file:
SET GLOBAL audit_log_flush = ON;
Automatic size-based audit log file rotation.
If audit_log_rotate_on_size
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
audit_log_rotate_on_size
value, the audit log plugin automatically closes the file,
renames it, and opens a new log file.
The renamed file has a timestamp and .xml
added to the end. For example, if the file name is
audit.log
, the plugin renames it to a
value such as
audit.log.15081807937726520.xml
. The
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
FROM_UNIXTIME()
function:
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 do not rotate off the end of the name sequence. Instead, they have unique names and accumulate indefinitely. To avoid excessive space use, remove old files periodically, backing them up first as necessary.