Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 47.0Mb
PDF (A4) - 47.1Mb
PDF (RPM) - 42.4Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.9Mb
HTML Download (RPM) - 9.4Mb
Man Pages (TGZ) - 227.0Kb
Man Pages (Zip) - 333.8Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Audit Log Logging Configuration

6.4.5.5 Audit Log Logging Configuration

This section describes how to configure audit logging characteristics, such as the file to which the audit log plugin writes events, the format of written events, and whether to enable log file compression and encryption.

Note

Encryption capabilities described here apply as of MySQL 8.0.17, with the exception of the section that compares current encryption capabilities to the previous more-limited capabilities (see Audit Log File Encryption Prior to MySQL 8.0.17).

For additional information about the user-defined functions and system variables that affect audit logging, see Audit Log Functions, and Audit Log Options and Variables.

The audit log plugin can also control which audited events are written to the audit log file, based on event content or the account from which events originate. See Section 6.4.5.6, “Audit Log Filtering”.

Audit Log File Name

To configure 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, write the audit log file 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 .pwd_id.enc, where pwd_id indicates which encryption password to use for log file operations.

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 password ID values:

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

The audit log plugin stores encryption passwords in the keyring (see Section 6.4.4, “The MySQL Keyring”). The IDs of audit log passwords in the keyring are based on pwd_id values, with a prefix of audit_log-. 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 password currently used for encryption by the audit log plugin is the one having the largest pwd_timestamp value. If multiple passwords have the largest pwd_timestamp value, the current password 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 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:

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

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

  • File renaming (whether during plugin initialization or termination) occurs according to the usual rules for automatic log file rotation; see Automatic Audit Log File Rotation.

Audit Log File Format

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, 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 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 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 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, use these user-defined functions (UDFs):

  • 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 log file rotation; see Automatic Audit Log File Rotation.

  • To get the current encryption password, invoke audit_log_encryption_password_get() with no argument. To get a password by ID, pass an argument specifying 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 * FROM performance_schema.keyring_keys
           WHERE KEY_ID LIKE 'audit_log%'
           ORDER BY KEY_ID;
    +-----------------------------+-----------+----------------+
    | KEY_ID                      | KEY_OWNER | BACKEND_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 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 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 (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.

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 Audit Log File Name, pwd_id format is pwd_timestamp-seq. Thus, the names of archived encrypted log files actually contain two timestamps. The first indicates rotation time, and the second indicates when the 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 paswword 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

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
Audit Log File Encryption Prior to MySQL 8.0.17

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

Feature Prior to MySQL 8.0.17 As of MySQL 8.0.17
Number of passwords Single password only Multiple passwords permitted
Encrypted log file names .enc suffix .pwd_id.enc suffix
Password keyring ID audit_log audit_log-pwd_id
Password history No Yes
Password expiration No Yes

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. You must keep a record of the previous password should you need to decrypt those files manually.

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:

  • During plugin initialization, the plugin checks for an encryption password with a keyring ID of audit_log. If it finds one, the plugin duplicates the password using a keyring ID in audit_log-pwd_id format and uses it as the current encryption password.

  • Existing encrypted log files have a suffix of .enc. The plugin does not rename these to have a suffix of .pwd_id.enc, but can read them as long as the key with the ID of audit_log remains in the keyring.

  • When password cleanup occurs, if the plugin expires any password with a keyring ID in audit_log-pwd_id format, it also expires the password with a keyring ID of audit_log, if it exists. (At this point, encrypted log files that have a suffix of .enc rather than .pwd_id.enc become unreadable by the plugin, so it is assumed that you no longer need them.)

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 for log file rotation, either manually or automatically. Rotation capabilities use the audit_log_flush and audit_log_rotate_on_size system variables:

  • By default, audit_log_rotate_on_size=0 and no log rotation occurs unless performed manually. In this case, use audit_log_flush to close and reopen the current log file after manually renaming it.

  • If audit_log_rotate_on_size is greater than 0, automatic rotation occurs when a write to the current log file causes its size to exceed this value. The audit log plugin closes the file, renames it, and opens a new log file. With automatic rotation enabled, audit_log_flush has no effect.

  • Automatic rotation also occurs under several other conditions, described later.

Note

Renamed log files are not removed automatically. For example, 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. 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 discussion describes log file rotation methods in greater detail.

Manual Audit Log File Rotation

If audit_log_rotate_on_size=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:

  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

    This strategy overwrites the current audit.log.3 contents, placing a bound on the number of archived log files and the space they use.

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

    audit_log_flush is special in that its value remains OFF so that you need not disable it explicitly before enabling it again to perform another flush.

Note

If compression or encryption are enabled, log file names include suffixes that signify the enabled features, as well as a password ID if encryption is enabled. If file names include a password ID, be sure to retain the ID in the name of any files you rename manually so that the password to use for decryption operations can be determined.

Note

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 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 closes the file, renames it, and opens a new log file.

Automatic rotation also occurs under these conditions:

  • During plugin initialization, if a file with the audit log file name already exists (see Audit Log File Name).

  • During plugin 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 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 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 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:

basename[.timestamp].suffix[.gz][[.pwd_id].enc]

A file is encrypted if its name ends with .enc. The plugin determines the keyring ID of the decryption password needed to read it as follows:

  • If .enc is preceded by pwd_id, the keyring ID is audit_log-pwd_id.

  • If .enc is not preceded by pwd_id, the file has an old name from before password history was implemented. The keyring ID is audit_log.

The plugin opens each file matching the pattern, checks that the file really contains JSON audit records, and sorts the files using the timestamps from the first record of each file to construct a list of 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 no longer available in the keyring.

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": "2019-10-03 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":"2019-10-03 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": "2019-10-03 22:08:08", "id": 10,
    "class": "general", "event": "status",
    ...
  },
  {
    "timestamp": "2019-10-03 22:08:08", "id": 11,
    "class": "connection", "event": "disconnect",
    ...
  },
  {
    "timestamp": "2019-10-03 13:39:33", "id": 0,
    "class": "connection", "event": "connect",
    ...
  },
  {
    "timestamp": "2019-10-03 13:39:33", "id": 1,
    "class": "general", "event": "status",
    ...
  },
  {
    "timestamp": "2019-10-03 13:39:33", "id": 2,
    "class": "connection", "event": "disconnect",
    ...
  },
  null
]

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 string, which can be manipulated as necessary. Suppose that a bookmark has this value:

mysql> SET @mark := audit_log_read_bookmark();
mysql> SELECT @mark;
+-------------------------------------------------+
| @mark                                           |
+-------------------------------------------------+
| { "timestamp": "2019-10-03 16:10:28", "id": 2 } |
+-------------------------------------------------+

Calling audit_log_read() with that bookmark can return multiple events. To limit audit_log_read() to reading a single event, add to the bookmark a max_array_length item with a value of 1. For example, convert the preceding bookmark as follows:

mysql> SET @mark := JSON_SET(@mark, '$.max_array_length', 1);
mysql> SELECT @mark;
+----------------------------------------------------------------------+
| @mark                                                                |
+----------------------------------------------------------------------+
| {"id": 2, "timestamp": "2019-10-03 16:10:28", "max_array_length": 1} |
+----------------------------------------------------------------------+

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

Prior to MySQL 8.0.19, string return values from audit log UDFs are binary strings. To use a binary string with functions that require a nonbinary string (such as functions that manipulate JSON values), perform a conversion to utf8mb4. For example, before passing the bookmark to JSON_SET(), convert it as follows:

SET @mark = CONVERT(@mark USING utf8mb4);

That statement can be included even for MySQL 8.0.19 and higher because for those versions it is essentially a no-op and is harmless.

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 Audit Log Functions.