MySQL :: MySQL 9.6 Reference Manual :: 8.4.7.5 Configuring Audit Logging Characteristics for Audit Log Component

Naming Conventions for Audit Log Files

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 component 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:

If compression is enabled, the component adds a suffix of .gz.
If encryption is enabled, the component adds a suffix of .pwd_id.enc, where pwd_id indicates which encryption password to use for log file operations. The audit log component stores encryption passwords in the keyring; see Encrypting Audit Log Files.

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.

Enabled Features	Effective File Name
No compression or encryption	`audit.log`
Compression	`audit.log.gz`
Encryption	`audit.log.pwd_id.enc`
Compression, encryption	`audit.log.gz.pwd_id.enc`

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

pwd_timestamp is a UTC value in YYYYMMDDThhmmss format indicating when the password was created.
seq is a sequence number. Sequence numbers start at 1 and increase for passwords that have the same pwd_timestamp value.

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 component 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 component 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 component performs certain actions during initialization and termination based on the effective audit log file name:

During initialization, the component checks whether a file with the audit log file name already exists and renames it if so. (In this case, the component assumes that the previous server invocation exited unexpectedly with the audit log component running.) The component then writes to a new empty audit log file.
During termination, the component renames the audit log file.
File renaming (whether during component initialization or termination) occurs according to the usual rules for automatic size-based log file rotation; see Manual Audit Log File Rotation.

Audit Log File Format

The audit log file is only available in JSON format. The component writes the audit log as a JSON array. The audit log file supports the optional query time and size statistics.

For more details, see Section 8.4.7.4, “Audit Log Component File Format”.

Enabling the Audit Log Flush Task

MySQL Enterprise Audit provides the capability of setting a refresh interval to dispose of the in-memory cache automatically. A flush task configured using the audit_log.flush_interval_seconds system variable has a value of zero by default, which means the task is not scheduled to run.

When the task is configured to run (the value is non-zero), MySQL Enterprise Audit attempts to call the scheduler component at its initialization and configure a regular, recurring flush of its memory cache:

If the audit log cannot find an implementation of the scheduler registration service, it does not schedule the flush and continue loading.
Audit log implements the dynamic_loader_services_loaded_notification service and listens for new registrations of mysql_scheduler so that audit log can register its scheduled task into the newly loaded scheduler.
Audit log only registers itself into the first scheduler implementation loaded.

Similarly, MySQL Enterprise Audit calls the scheduler component at its deinitialization and unconfigures the recurring flush that it has scheduled. It keeps an active reference to the scheduler registration service until the scheduled task is unregistered, ensuring that the scheduler component cannot be unloaded while there are active scheduled jobs. All of the results from executing the scheduler and its tasks are written to the server error log.

To schedule an audit log flush task:

Confirm that the scheduler component is loaded and enabled. The component is enabled (ON) by default (see component_scheduler.enabled).

SELECT * FROM mysql.components;
+--------------+--------------------+----------------------------+
| component_id | component_group_id | component_urn              |
+--------------+--------------------+----------------------------+
|            1 |                  1 | file://component_scheduler |
+--------------+--------------------+----------------------------+

Install the audit_log component, if it is not installed already (see Section 8.4.7.2, “Installing or Uninstalling MySQL Enterprise Audit with The Audit Log Component”).
Start the server using audit_log.flush_interval_seconds and set the value to a number greater than 59. The upper limit of the value varies by platform. For example, to configure the flush task to recur every two minutes:
```
$> mysqld --audit_log.flush_interval_seconds=120
```
For more information, see the audit_log.flush_interval_seconds system variable.

Adding Query Statistics for Outlier Detection

You can extend log files with optional data fields to show the query time, the number of bytes sent and received, the number of rows returned to the client, and the number of rows examined. This data is available in the slow query log for qualifying queries, and in the context of the audit log it similarly helps to detect outliers for activity analysis.

The query statistics are delivered to the audit log through component services that you set up as an audit log filtering function. The services are named mysql_audit_print_service_longlong_data_source and mysql_audit_print_service_double_data_source. You can choose either data type for each output item. For the query time, longlong outputs the value in microseconds, and double outputs the value in seconds.

You add the query statistics using the audit_log_filter_set_filter() audit log function, as the service element of the JSON filtering syntax, as follows:

SELECT audit_log_filter_set_filter('QueryStatistics',  
                                   '{ "filter": { "class": { "name": "general", "event": { "name": "status", "print" : '
                                   '{ "service": { "implementation": "mysql_server", "tag": "query_statistics", "element": [ '
                                   '{ "name": "query_time",     "type": "double" }, '
                                   '{ "name": "bytes_sent",     "type": "longlong" }, '
                                   '{ "name": "bytes_received", "type": "longlong" }, '
                                   '{ "name": "rows_sent",      "type": "longlong" }, '
                                   '{ "name": "rows_examined",  "type": "longlong" } ] } } } } } }');

For the bytes_sent and bytes_received fields to be populated, the system variable log_slow_extra must be set to ON. If the system variable value is OFF, a null value is written to the log file for these fields.

If you want to stop collecting the query statistics, use the audit_log_filter_set_filter() audit log function to remove the filter, for example:

SELECT audit_log_filter_remove_filter('QueryStatistics');

Compressing Audit Log Files

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.

Encrypting Audit Log Files

Encryption is based on user-defined passwords (with the exception of the initial password that the audit log component generates). To use this feature, the MySQL keyring must be enabled because audit logging uses it for password storage. Any keyring component or plugin can be used; for instructions, see Section 8.4.5, “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 audit log functions:

To set the current encryption password, invoke audit_log_encryption_password_set(). This function stores the new password in the keyring. If encryption is enabled, it also performs a log file rotation operation that renames the current log file, and begins a new log file encrypted with the password. File renaming occurs according to the usual rules for automatic size-based log file rotation; see Manual Audit Log File Rotation.
If the audit_log.password_history_keep_days system variable is nonzero, invoking audit_log_encryption_password_set() also causes expiration of old archived audit log encryption passwords. For information about audit log password history, including password archiving and expiration, see the description of that variable.

To get the current encryption password, invoke audit_log_encryption_password_get() with no argument. To get a password by ID, pass an argument that specifies the keyring ID of the current password or an archived password.

To determine which audit log keyring IDs exist, query the Performance Schema keyring_keys table:

mysql> SELECT KEY_ID FROM performance_schema.keyring_keys
       WHERE KEY_ID LIKE 'audit_log%'
       ORDER BY KEY_ID;
+-----------------------------+
| KEY_ID                      |
+-----------------------------+
| audit_log-20190415T152248-1 |
| audit_log-20190415T153507-1 |
| audit_log-20190416T125122-1 |
| audit_log-20190416T141608-1 |
+-----------------------------+

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

When the audit log component initializes, if it finds that log file encryption is enabled, it checks whether the keyring contains an audit log encryption password. If not, the component 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.

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 component is currently writing. You can recognize archived log files because they have been renamed by the audit log component 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.

Enabled Features	Archived File Name
No compression or encryption	`audit.timestamp.log`
Compression	`audit.timestamp.log.gz`
Encryption	`audit.timestamp.log.pwd_id.enc`
Compression, encryption	`audit.timestamp.log.gz.pwd_id.enc`

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:

The first two files have the same password ID and sequence number (20190403T185337-1). They have the same encryption password.
The second two files have the same password ID (20190414T223342) but different sequence numbers (1, 2). These files have different encryption passwords.

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

Space Management of Audit Log Files

The audit log file has the potential to grow quite large and consume a great deal of disk space. If you are collecting the optional query time and size statistics, this increases the space requirements.

To manage the space used, employ these methods:

Log file rotation. This involves rotating the current log file by renaming it, then opening a new current log file using the original name. Rotation can be performed manually, or configured to occur automatically.
Pruning of rotated log files, if automatic rotation is enabled. Pruning can be performed based on log file age or combined log file size.

To configure audit log file space management, use the following system variables:

If audit_log.rotate_on_size is 0 (the default), automatic log file rotation is disabled.
- No rotation occurs unless performed manually.
- To rotate the current file, run SELECT audit_log_rotate(); to rename the file and open a new audit log file using the original name.
  With this file rotation method, pruning of rotated log files occurs if audit_log.max_size or audit_log.prune_seconds has a value greater than 0.
  See Manual Audit Log File Rotation, for more information.
If audit_log.rotate_on_size is greater than 0, automatic audit log file rotation is enabled:
- Automatic rotation occurs when a write to the current log file causes its size to exceed the audit_log.rotate_on_size value, as well as under certain other conditions; see Automatic Audit Log File Rotation. When automatic rotation occurs, the audit log component renames the current log file and opens a new current log file using the original name.
- Pruning of rotated log files occurs if audit_log.max_size or audit_log.prune_seconds has a value greater than 0.

Note

Rotation also occurs when the value of the audit_log.format_unix_timestamp system variable is changed at runtime. However, this does not occur for space-management purposes, but rather so that all records in the file either do or do not include the time field.

Note

Rotated (renamed) log files are not removed automatically. For example, 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:

Enable log file pruning as described in Audit Log File Pruning.
Otherwise: Remove old files periodically, backing them up first as necessary. If backed-up log files are encrypted, also back up the corresponding encryption passwords to a safe place, should you need to decrypt the files later.

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.

To rotate the audit log file manually, run SELECT audit_log_rotate(); to rename the current audit log file and open a new audit log file. Files are renamed according to the conventions described in Naming Conventions for Audit Log Files.

The AUDIT_ADMIN privilege is required to use the audit_log_rotate() function.

Managing the number of archived log files (the files that have been renamed) and the space they use is a manual task that involves removing archived audit log files that are no longer needed from your file system.

The content of audit log files that are renamed using the audit_log_rotate() function can be read by audit_log_read() function.

Automatic Audit Log File Rotation

Automatic size-based rotation occurs under these conditions:

During component initialization, if a file with the audit log file name already exists (see Naming Conventions for Audit Log Files).
During component termination.
When the audit_log_encryption_password_set() function is called to set the encryption password, if encryption is enabled. (Rotation does not occur if encryption is disabled.)

The component renames the original file by inserting a timestamp just after its base name. For example, if the file name is audit.log, the component renames it to a value such as audit.20250115T140633.log. The timestamp is a UTC value in YYYYMMDDThhmmss format. For JSON logging, the timestamp is that of the last event written to the file.

If log files are encrypted, the original file name already contains a timestamp indicating the encryption password creation time (see Naming Conventions for Audit Log Files). In this case, the file name after rotation contains two timestamps. For example, an encrypted log file named audit.log.20250110T130749-1.enc is renamed to a value such as audit.20250115T140633.log.20250110T130749-1.enc.

Audit Log File Pruning

The audit log component supports pruning of rotated log files if automatic log file rotation is enabled. To use this capability:

Set audit_log.rotate_on_size greater than 0 to specify the size in bytes at which automatic log file rotation occurs.
By default, no pruning of automatically rotated log files occurs. To enable pruning, set one of these system variables to a value greater than 0:
- Set audit_log.max_size greater than 0 to specify the limit in bytes on the combined size of rotated log files above which the files become subject to pruning.
- Set audit_log.prune_seconds greater than 0 to specify the number of seconds after which rotated log files become subject to pruning.
Nonzero values of audit_log.max_size take precedence over nonzero values of audit_log.prune_seconds. If both are set greater than 0 at component initialization, a warning is written to the server error log. If a client sets both greater than 0 at runtime, a warning is returned to the client.

Note

Warnings to the error log are written as Notes, which are information messages. To ensure that such messages appear in the error log and are not discarded, make sure that error-logging verbosity is sufficient to include information messages. For example, if you are using priority-based log filtering, as described in Section 7.4.2.5, “Priority-Based Error Log Filtering (log_filter_internal)”, set the log_error_verbosity system variable to a value of 3.

Pruning of log files, if enabled, occurs as follows:

When automatic rotation takes place; for the conditions under which this happens, see Automatic Audit Log File Rotation.
When the global audit_log.max_size or audit_log.prune_seconds system variable is set at runtime.

For pruning based on combined rotated log file size, if the combined size is greater than the limit specified by audit_log.max_size, the audit log component removes the oldest files until their combined size does not exceed the limit.

For pruning based on rotated log file age, the pruning point is the current time minus the value of audit_log.prune_seconds. In rotated log files, the timestamp part of each file name indicates the timestamp of the last event written to the file. The audit log component uses file name timestamps to determine which files contain only events older than the pruning point, and removes them.

Write Strategies for Audit Logging

The audit log component 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 component logs asynchronously to a buffer, waiting if the buffer is full. You can tell the component 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).

In many cases, the component writes directly to a log file if the current query is too large for the buffer. The write strategy determines how the component increments the direct write count. You can track the number direct writes with the audit_log.events_direct_writes status variable.

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 component uses a single buffer, which it allocates when it initializes and removes when it terminates. The component 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 component 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.