Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.7Mb
PDF (A4) - 1.7Mb
HTML Download (TGZ) - 349.2Kb
HTML Download (Zip) - 357.7Kb

Security in MySQL  /  ...  /  Audit Log Logging Control

7.5.5 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, the format of written events, and whether compression and encryption are enabled.

For additional information about the user-defined functions and system variables that affect audit logging, see Section, “Audit Log Functions”, and Section, “Audit Log Options and 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 content. See Section 7.5.6, “Audit Log Filtering”.

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.

The plugin interprets the audit_log_file value as composed of a base name and an optional suffix. If compression or encryption are enabled, the effective file name (the name actually used to create the log file) differs from the configured file name because it has additional suffixes:

  • If compression is enabled, the plugin adds a suffix of .gz.

  • If encryption is enabled, the plugin adds a suffix of .enc.

The effective audit log file name is the resulting name after adding possible compression and encryption suffixes to the configured file name. For example, if the configured audit_log_file value is audit.log, the effective file name is one of these values:

audit.log        Not compressed or encrypted
audit.log.gz     Compressed
audit.log.enc    Encrypted
audit.log.gz.enc Compressed and encrypted

The audit log plugin performs certain actions at initialization and termination time based on the audit log file name:

  • During initialization, the plugin checks whether a file with the audit log file name already exists and renames it if so. (In this case, the plugin assumes that the previous server invocation exited unexpectedly with the audit log plugin running.) The plugin then writes to a new empty audit log file.

  • At termination, the plugin renames the audit log file.

  • When renaming occurs (whether at plugin initialization or termination), the renamed file has a timestamp inserted after its base name and before its suffix. For example, if the file name is audit.log, the plugin renames it to a value such as audit.20180115T140633.log. The timestamp is a UTC value in YYYYMMDDThhmmss format.

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 NEW (new-style XML format). For details about each format, see Section 7.5.4, “Audit Log File Formats”.

If you change audit_log_format, it is recommended that you also change audit_log_file. Otherwise, there will be two sets of log files with the same base name but different formats.

Audit Log File Compression

Audit log file compression can be enabled for any logging format.

To control whether audit log file compression is enabled, set the audit_log_compression system variable at server startup. Permitted values are NONE (no compression; the default) and GZIP (GNU Zip compression).

If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it. See Audit Log File Manual Uncompression and Decryption.

Audit Log File Encryption

Audit log file encryption can be enabled for any logging format. Encryption is based on a user-defined password. To use this feature, the MySQL keyring must be enabled because audit logging uses it for password storage. Any keyring plugin can be used; for instructions, see Section 7.4, “The MySQL Keyring”.

To control whether audit log file encryption is enabled, set the audit_log_encryption system variable at server startup. Permitted values are NONE (no encryption; the default) and AES (AES-256-CBC cipher encryption).

To set or get the encryption password, use these user-defined functions (UDFs):

  • To set the encryption password, invoke audit_log_encryption_password_set(), which stores the password in the keyring, renames the current log file, and begins a new log file encrypted with the new password. The renamed file has a timestamp inserted after its base name and before its suffix. For example, if the file name is audit.log.enc, the plugin renames it to a value such as audit.20180115T140633.log.enc. The timestamp is a UTC value in YYYYMMDDThhmmss format.

    Previously written audit log files are not re-encrypted with the new password. Remember the previous password should you need to decrypt those files.

  • To get the current encryption password, invoke audit_log_encryption_password_get(), which retrieves the password from the keyring.

For the first server startup after audit log encryption is enabled, the audit log plugin automatically generates the initial encryption password and stores it in the keyring. To discover this password, invoke audit_log_encryption_password_get().

For additional information about audit log encryption functions, see Section, “Audit Log Functions”.

If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it. See Audit Log File Manual Uncompression and Decryption.

Audit Log File Manual Uncompression and Decryption

Audit log files can be uncompressed and decrypted using standard tools. This should be done only for log files that have been closed and are no longer in use, not for the log file that the audit log plugin is currently writing. You can recognize closed log files because they will have been renamed by the audit log plugin to include a timestamp in the file name.

For this discussion, assume that audit_log_file is set to audit.log. In that case, a closed audit log file has one of these names:

audit.timestamp.log        Not compressed or encrypted
audit.timestamp.log.gz     Compressed
audit.timestamp.log.enc    Encrypted
audit.timestamp.log.gz.enc Compressed and encrypted

To uncompress a compressed log file manually, use gunzip, gzip -d, or equivalent command. For example:

gunzip -c audit.timestamp.log.gz > audit.timestamp.log

To decrypt an encrypted log file manually, use the openssl command. For example:

openssl enc -d -aes-256-cbc -pass pass:password -md sha256
    -in audit.timestamp.log.enc -out audit.timestamp.log

If both compression and encryption are enabled for audit logging, compression occurs before encryption. In this case, the file name has .gz and .enc suffixes added, corresponding to the order in which those operations occur. To recover the original file manually, perform the operations in reverse. That is, first decrypt the file, then uncompress it:

openssl enc -d -aes-256-cbc -pass pass:password -md sha256
    -in audit.timestamp.log.gz.enc -out audit.timestamp.log.gz
gunzip -c audit.timestamp.log.gz > audit.timestamp.log

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.xml through audit.log.3.xml. On Unix, perform rotation manually like this:

  1. From the command line, rename the current log files:

    mv audit.log.2.xml audit.log.3.xml
    mv audit.log.1.xml audit.log.2.xml
    mv audit.log audit.log.1.xml

    At this point, the plugin is still writing to the current log file, which has been renamed to audit.log.1.xml.

  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;

For JSON-format logging, renaming audit log files manually makes them unavailable to the log-reading functions because the audit log plugin no longer can determine that they are part of the log file sequence (see Audit Log File Reading). Consider setting audit_log_rotate_on_size greater than 0 to use size-based rotation instead.

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.

When the plugin renames the original file, the renamed file has a timestamp inserted after its base name and before its suffix. For example, if the file name is audit.log, the plugin renames it to a value such as audit.20180115T140633.log. The timestamp is a UTC value in YYYYMMDDThhmmss format.


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.

Audit Log File Reading

The audit log plugin enables bookmarking and reading of JSON-format audit log files. (These capabilities do not apply to files written in other log formats.)

When the audit log plugin initializes and is configured for JSON logging, it uses the directory containing the audit log file (determined from the audit_log_file value) as the location to search for readable audit log files. To do this, it uses the value of audit_log_file to determine the file base name and suffix values, then looks for files with names that match the following pattern, where [...] indicates optional file name parts:


The plugin opens each matching file, checks that it really contains JSON audit records, and sorts them using the timestamps from the first record of each file to construct a list of log files that are subject to use with the log-reading functions.

The plugin cannot include in the sequence files that were renamed manually and do not match the preceding pattern, or that were encrypted with a password different from the current password.

To read events from the audit log, use these user-defined functions (UDFs):

  • audit_log_read_bookmark() returns a JSON string representing a bookmark for the most recently written audit log event. This bookmark is suitable for passing to audit_log_read() to indicate to that function where to begin reading. Example bookmark:

    { "timestamp": "2018-01-15 21:03:44", "id": 0 }
  • audit_log_read() reads events from the audit log and returns a JSON string containing an array of audit events.

Example audit_log_read() invocation using the current bookmark:

mysql> SELECT audit_log_read(audit_log_read_bookmark());
| audit_log_read(audit_log_read_bookmark())                             |
| [ {"timestamp":"2018-01-15 22:41:24","id":0,"class":"connection", ... |

Each event in the audit_log_read() return value is a JSON hash, except that the last array element may be a JSON null value to indicate no following events are available to read. For example:

  { "timestamp": "2018-01-15 22:08:08", "id": 10,
    "class": "general", "event": "status",
    "timestamp": "2018-01-15 22:08:08", "id": 11,
    "class": "connection", "event": "disconnect",
    "timestamp": "2018-01-15 13:39:33", "id": 0,
    "class": "connection", "event": "connect",
    "timestamp": "2018-01-15 13:39:33", "id": 1,
    "class": "general", "event": "status",
    "timestamp": "2018-01-15 13:39:33", "id": 2,
    "class": "connection", "event": "disconnect",

Use audit_log_read() like this:

  • For the first call to audit_log_read() within a session, pass a bookmark indicating where to begin reading.

  • If the final value of the returned array is not a JSON null value, there are more events following those just read and audit_log_read() can be called without or with a bookmark argument. Without an argument, reading continues with the next unread event. With a bookmark argument, reading continues from the bookmark.

  • If the final value of the returned array is a JSON null value, there are no more events left to be read and the next call to audit_log_read() must include a bookmark argument.

A bookmark is a JSON hash that indicates where and how much to read. The following items are significant in the bookmark value (other items are ignored):

  • timestamp, id: The location within the audit log of the first event to read. Both items must be present to completely specify a position.

  • max_array_length: The maximum number of events to read from the log. If omitted, the default is to read to the end of the log or until the read buffer is full, whichever comes first.

The result returned from either log-reading function is a binary string. To use the string with functions that require a nonbinary string (such as the functions that manipulate JSON values), convert it to utf8mb4. Suppose that a bookmark has this value:

mysql> SELECT @mark := audit_log_read_bookmark() AS mark;
| mark                                            |
| { "timestamp": "2018-01-15 16:10:28", "id": 2 } |

Calling audit_log_read() with that bookmark can return multiple events. To set a limit on the number of events read by audit_log_read(), convert the bookmark to utf8mb4, then add to it a max_array_length item with a value of 1. For example, using the preceding bookmark, convert and modify it as follows:

mysql> SET @mark = CONVERT(@mark USING utf8mb4);
mysql> SET @mark := JSON_SET(@mark, '$.max_array_length', 1);
mysql> SELECT @mark;
| @mark                                                                |
| {"id": 2, "timestamp": "2018-01-15 16:10:28", "max_array_length": 1} |

The modified bookmark, when passed to audit_log_read(), produces a result of a single audit record.

To set a limit on the number of bytes that audit_log_read() reads, set the audit_log_read_buffer_size system variable. As of MySQL 8.0.12, this variable has a default of 32KB and can be set at runtime. Each client should set its session value of audit_log_read_buffer_size appropriately for its use of audit_log_read(). Prior to MySQL 8.0.12, audit_log_read_buffer_size has a default of 1MB, affects all clients, and can be changed only at server startup.

Each call to audit_log_read() returns as many available items as fit within the buffer size, skipping items that do not fit within the buffer size. Given this behavior, consider these factors when assessing the proper buffer size for an application:

  • There is a tradeoff between number of calls to audit_log_read() and items returned per call. With a smaller buffer size, calls return fewer items, so more calls are needed. With a larger buffer size, calls return more items, so fewer calls are needed.

  • With a smaller buffer size, such as the default size of 32KB, there is a greater chance that items will exceed the buffer size and audit_log_read() will skip them. Skipped items generate warnings.

For additional information about audit log-reading functions, see Section, “Audit Log Functions”.