The audit log plugin supports functions that provide an SQL interface for reading JSON-format audit log files. (This capability does not apply to log files written in other formats.)

When the audit log plugin initializes and is configured for JSON logging, it uses the directory containing the current audit log file as the location to search for readable audit log files. The plugin determines the file location, base name, and suffix from the value of the audit_log_file system variable, then looks for files with names that match the following pattern, where [...] indicates optional file name parts:

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

If a file name ends with .enc, the file is encrypted and reading its unencrypted contents requires a decryption password obtained from the keyring. The audit log plugin determines the keyring ID of the decryption password as follows:

For more information about encrypted audit log files, see Encrypting Audit Log Files.

The plugin ignores files that have been renamed manually and do not match the pattern, and files that were encrypted with a password no longer available in the keyring. The plugin opens each remaining candidate file, verifies that the file actually contains JSON audit events, and sorts the files using the timestamps from the first event of each file. The result is a sequence of files that are subject to access using the log-reading functions:

audit_log_read() takes an optional JSON string argument, and the result returned from a successful call to either function is a JSON string.

To use the functions to read the audit log, follow these principles:

To specify a position to audit_log_read(), include an argument that indicates where to begin reading. For example, pass a bookmark, which is a JSON hash containing timestamp and id elements that uniquely identify a particular event. Here is an example bookmark, obtained by calling the audit_log_read_bookmark() function:

mysql> SELECT audit_log_read_bookmark();
+-------------------------------------------------+
| audit_log_read_bookmark()                       |
+-------------------------------------------------+
| { "timestamp": "2020-05-18 21:03:44", "id": 0 } |
+-------------------------------------------------+

Passing the current bookmark to audit_log_read() initializes event reading beginning at the bookmark position:

mysql> SELECT audit_log_read(audit_log_read_bookmark());
+-----------------------------------------------------------------------+
| audit_log_read(audit_log_read_bookmark())                             |
+-----------------------------------------------------------------------+
| [ {"timestamp":"2020-05-18 22:41:24","id":0,"class":"connection", ... |
+-----------------------------------------------------------------------+

The argument to audit_log_read() is optional. If present, it can be a JSON null value to close the read sequence, or a JSON hash.

Within a hash argument to audit_log_read(), items are optional and control aspects of the read operation such as the position at which to begin reading or how many events to read. The following items are significant (other items are ignored):

To specify a starting position to audit_log_read(), pass a hash argument that includes either a start item or a bookmark consisting of timestamp and id items. If a hash argument includes both a start item and a bookmark, an error occurs.

If a hash argument specifies no starting position, reading continues from the current position.

If a timestamp value includes no time part, a time part of 00:00:00 is assumed.

Example arguments accepted by audit_log_read():

A JSON string returned from either log-reading function can be manipulated as necessary. Suppose that a call to obtain a bookmark produces this value:

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

Calling audit_log_read() with that argument can return multiple events. To limit audit_log_read() to reading at most N events, add to the string a max_array_length item with that value. For example, to read a single event, modify the string as follows:

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

The modified string, when passed to audit_log_read(), produces a result containing at most one event, no matter how many are available.

If an audit log function is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

To set a limit on the number of bytes that audit_log_read() reads, set the audit_log_read_buffer_size system variable. 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().

Each call to audit_log_read() returns as many available events as fit within the buffer size. Events that do not fit within the buffer size are skipped and generate warnings. Given this behavior, consider these factors when assessing the proper buffer size for an application:

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