Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.2Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 26.1Mb
HTML Download (TGZ) - 6.5Mb
HTML Download (Zip) - 6.5Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 170.8Kb
Man Pages (Zip) - 279.2Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Audit Log Logging Control

6.5.2.4 Audit Log Logging Control

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 Section 6.5.2.7, “Audit Log Options and System Variables”.

Audit Log File Name

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 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 exited 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.

Audit Log File Format

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.5.2.3, “Audit Log File Formats”.

Note

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:

  1. Stop the server.

  2. 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.

  3. 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.

Audit Logging Write Strategy

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.

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.

Audit Log File Space Management and Name Rotation

The audit log file has the potential to grow very large and consume a lot 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.  By default, audit_log_rotate_on_size=0 and there is no log rotation except that which you perform 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:

  1. 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.

  2. 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 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 last 7 digits are a fractional second part. The first 10 digits are a Unix timestamp value that can be interpreted using the FROM_UNIXTIME() function:

mysql> SELECT FROM_UNIXTIME(1508180793);
+---------------------------+
| FROM_UNIXTIME(1508180793) |
+---------------------------+
| 2017-10-16 14:06:33       |
+---------------------------+
Note

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.


User Comments
Sign Up Login You must be logged in to post a comment.