To configure 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.

The plugin interprets the audit_log_file value as composed of an optional leading directory name, 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:

The effective audit log file name is the name resulting from the addition of applicable 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 the values shown in the following table.

pwd_id indicates the ID of the password used to encrypt or decrypt a file. pwd_id format is pwd_timestamp-seq, where:

Here are some example pwd_id password ID values:

20190403T142359-1
20190403T142400-1
20190403T142400-2

To construct the corresponding keyring IDs for storing passwords in the keyring, the audit log plugin adds a prefix of audit_log- to the pwd_id values. For the example password IDs just shown, the corresponding keyring IDs are:

audit_log-20190403T142359-1
audit_log-20190403T142400-1
audit_log-20190403T142400-2

The ID of the password currently used for encryption by the audit log plugin is the one having the largest pwd_timestamp value. If multiple passwords have that pwd_timestamp value, the current password ID is the one with the largest sequence number. For example, in the preceding set of password IDs, two of them have the largest timestamp, 20190403T142400, so the current password ID is the one with the largest sequence number (2).

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

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

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

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

To configure audit log file compression, 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 Manually Uncompressing and Decrypting Audit Log Files.

Audit log file encryption can be enabled for any logging format. Encryption is based on user-defined passwords (with the exception of the initial password that the audit log plugin generates). 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 6.4.4, “The MySQL Keyring”.

To configure audit log file encryption, 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 an encryption password at runtime, use these user-defined functions (UDFs):

For additional information about audit log encryption UDFs, see Audit Log Functions.

When the audit log plugin initializes, if it finds that log file encryption is enabled, it checks whether the keyring contains an audit log encryption password. If not, the plugin automatically generates a random initial encryption password and stores it in the keyring. To discover this password, invoke audit_log_encryption_password_get().

If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it. See Manually Uncompressing and Decrypting Audit Log Files.

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

For this discussion, assume that audit_log_file is set to audit.log. In that case, an archived audit log file has one of the names shown in the following table.

As discussed in Naming Conventions for Audit Log Files, pwd_id format is pwd_timestamp-seq. Thus, the names of archived encrypted log files actually contain two timestamps. The first indicates file rotation time, and the second indicates when the encryption password was created.

Consider the following set of archived encrypted log file names:

audit.20190410T205827.log.20190403T185337-1.enc
audit.20190410T210243.log.20190403T185337-1.enc
audit.20190415T145309.log.20190414T223342-1.enc
audit.20190415T151322.log.20190414T223342-2.enc

Each file name has a unique rotation-time timestamp. By contrast, the password timestamps are not unique:

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.pwd_id.enc
    -out audit.timestamp.log

To execute that command, you must obtain password, the encryption password. To do this, use audit_log_encryption_password_get(). For example, if the audit log file name is audit.20190415T151322.log.20190414T223342-2.enc, the password ID is 20190414T223342-2 and the keyring ID is audit-log-20190414T223342-2. Retrieve the keyring password like this:

SELECT audit_log_encryption_password_get('audit-log-20190414T223342-2');

If both compression and encryption are enabled for audit logging, compression occurs before encryption. In this case, the file name has .gz and .pwd_id.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.pwd_id.enc
    -out audit.timestamp.log.gz
gunzip -c audit.timestamp.log.gz > audit.timestamp.log

This section covers the differences in audit log file encryption capabilities prior to and as of MySQL 8.0.17, which is when password history was implemented (which includes password archiving and expiration). It also indicates how the audit log plugin handles upgrades to MySQL 8.0.17 or higher from versions lower than 8.0.17.

Prior to MySQL 8.0.17, there is no password history, so setting a new password makes the old password inaccessible, rendering MySQL Enterprise Audit unable to read log files encrypted with the old password. Should you anticipate a need to decrypt those files manually, you must maintain a record of previous passwords.

If audit log file encryption is enabled when you upgrade to MySQL 8.0.17 or higher from a lower version, the audit log plugin performs these upgrade actions:

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 for log file rotation, either manually or automatically based on file size. As of MySQL 8.0.24, the plugin also supports log file pruning, for JSON-format log files.

Audit log file space-management capabilities use the audit_log_rotate_on_size, audit_log_flush, and audit_log_prune_seconds system variables, which have a combined effect as follows:

The following sections describe log file rotation and pruning in greater detail.

Manual Audit Log File Rotation

If audit_log_rotate_on_size is 0 (the default), no log rotation occurs unless 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:

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

Automatic size-based rotation also occurs under these conditions:

The plugin renames the original file by inserting a timestamp just after its base name. For example, if the file name is audit.log, the plugin renames it to a value such as audit.20190115T140633.log. The timestamp is a UTC value in YYYYMMDDThhmmss format. The timestamp indicates rotation time for XML logging, and the timestamp of the last event written to the file for JSON logging.

Audit Log File Pruning

As of MySQL 8.0.24, the audit log plugin supports JSON-format audit log file pruning. To enable this capability:

Log file pruning occurs under these conditions:

The pruning point is the current time minus the value of audit_log_prune_seconds. In rotated JSON-format log files, the timestamp part of each file name indicates the timestamp of the last event written to the file. When pruning occurs, the audit log plugin uses file name timestamps to determine which files contain only events older than the pruning point, and removes them.

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:

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.