Audit log filter functions, options and variables¶
All audit log functions return a string value that shows the operation result. The string “OK” indicates successful completion. “ERROR: message” indicates failure with an accompanying error message.
Audit‑log functions always accept and return strings in utf8mb4
.
Plugin Compatibility
Important: The audit log filter functions, options, and variables are designed specifically for the audit log filter
plugin.
They cannot be used with the audit log
plugin. These functions are
designed to operate on specific data types and structures that are unique to
the audit log filter
plugin. Attempting to use these functions with the audit log
plugin can result in errors because the functions would
operate on the wrong data format.
The following sections describe the audit log filter plugin functionality organized by purpose:
Plugin Management¶
Controls how the audit log filter plugin is loaded and configured.
Name | Type | Description |
---|---|---|
audit-log-filter |
Option | Plugin loading behavior |
audit_log_filter_database |
Variable | Database for configuration tables |
audit_log_filter_disable |
Variable | Disable plugin logging |
Filter Management¶
Create, configure, and manage audit filters and user assignments.
Name | Type | Description |
---|---|---|
audit_log_filter_set_filter() |
Function | Create or update filters |
audit_log_filter_remove_filter() |
Function | Remove filters |
audit_log_filter_set_user() |
Function | Assign filters to users |
audit_log_filter_remove_user() |
Function | Remove user filter assignments |
audit_log_filter_flush() |
Function | Apply filter changes |
audit_log_filter_filter_id |
Variable | Current session filter ID |
Log Reading and Navigation¶
Read audit logs and navigate through log entries.
Name | Type | Description |
---|---|---|
audit_log_read() |
Function | Read audit log entries |
audit_log_read_bookmark() |
Function | Get current log position |
audit_log_filter_read_buffer_size |
Variable | Read buffer size |
Log File Management¶
Configure log file location, rotation, and maintenance.
Name | Type | Description |
---|---|---|
audit_log_rotate() |
Function | Manually rotate log files |
audit_log_filter_file |
Variable | Log file name and location |
audit_log_filter_max_size |
Variable | Maximum total log size |
audit_log_filter_rotate_on_size |
Variable | Auto-rotation size threshold |
audit_log_filter_prune_seconds |
Variable | Log file retention time |
Output Format and Destination¶
Configure how audit logs are formatted and where they are sent.
Name | Type | Description |
---|---|---|
audit_log_filter_format |
Variable | Log format (JSON, XML) |
audit_log_filter_format_unix_timestamp |
Variable | Include UNIX timestamps |
audit_log_filter_handler |
Variable | Output destination (FILE/SYSLOG) |
audit_log_filter_syslog_tag |
Variable | Syslog tag |
audit_log_filter_syslog_priority |
Variable | Syslog priority |
Performance and Buffering¶
Optimize audit logging performance and memory usage.
Name | Type | Description |
---|---|---|
audit_log_filter_buffer_size |
Variable | Write buffer size |
audit_log_filter_strategy |
Variable | Logging strategy |
Encryption and Security¶
Configure encryption for audit log files and manage encryption keys.
Name | Type | Description |
---|---|---|
audit_log_encryption_password_get() |
Function | Get encryption password |
audit_log_encryption_password_set() |
Function | Set encryption password |
audit_log_filter_encryption |
Variable | Encryption type |
audit_log_filter_key_derivation_iterations_count_mean |
Variable | Key derivation iterations |
audit_log_filter_password_history_keep_days |
Variable | Password retention period |
Compression¶
Configure compression for audit log files.
Name | Type | Description |
---|---|---|
audit_log_filter_compression |
Variable | Compression type |
Plugin Management¶
audit-log-filter
¶
Option | Description |
---|---|
Command-line | –audit-log-filter[=value] |
Dynamic | No |
Scope | Global |
Data type | Enumeration |
Default | ON |
This option determines how, at startup, the server loads the audit_log_filter
plugin. The plugin must be registered.
The valid values are the following:
-
ON
- Load the plugin if it is available -
OFF
- Do not load the plugin -
FORCE
- Load the plugin even if it has been disabled -
FORCE_PLUS_PERMANENT
- Load the plugin and prevent it from being unloaded
audit_log_filter_database
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-database |
Dynamic | No |
Scope | Global |
Data type | String |
Default | mysql |
This variable defines the database name where the audit log filter plugin stores its
configuration tables. This is a read-only variable that you must set at system startup.
The database name cannot exceed 64 characters or be NULL
.
An invalid database name prevents the audit log filter plugin from functioning.
audit_log_filter_disable
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-disable |
Dynamic | Yes |
Scope | Global |
Data type | Boolean |
Default | OFF |
This variable disables the plugin logging for all connections and any sessions.
This variable requires the user account to have SYSTEM_VARIABLES_ADMIN
and
AUDIT_ADMIN
privileges.
Filter Management¶
audit_log_filter_set_filter(filter_name, definition)¶
This function, when provided with a filter name and definition, adds the filter.
The new filter has a different filter ID. Generates an error if the filter name exists.
Parameters¶
-
filter_name
- a selected filter name as a string. -
definition
- The filter definition as aJSON
value. TheJSON
structure defines the filtering rules, including which events to log, block, or exclude. Example:{"filter": {"log": true, "block": false}}
Returns¶
This function returns either an OK
for success or an error message for failure.
Example¶
mysql> SET @filter = '{ "filter": { "log": true }}'
mysql> SELECT audit_log_filter_set_filter('filter-name', @filter);
Expected output
+-------------------------------------------------------------+
| audit_log_filter_set_filter('filter-name', @filter) |
+-------------------------------------------------------------+
| OK |
+-------------------------------------------------------------+
audit_log_filter_remove_filter(filter_name)¶
This function removes the selected filter from the current set of filters.
When you remove a filter, any user accounts that were assigned to that filter are affected as follows:
-
The filter assignments for those user accounts are removed from the
audit_log_filter_user
table -
For user accounts currently in active sessions, the filter is immediately detached and they will use the default filter behavior
-
For user accounts not in active sessions, those user accounts will use the default filter behavior when they establish new connections in the future
Parameters¶
filter_name
- a selected filter name as a string.
Returns¶
This function returns either an OK
for success or an error message for failure.
If the filter name does not exist, no error is generated.
Example¶
mysql> SELECT audit_log_filter_remove_filter('filter-name');
Expected output
+------------------------------------------------+
| audit_log_filter_remove_filter('filter-name') |
+------------------------------------------------+
| OK |
+------------------------------------------------+
audit_log_filter_set_user(user_name, filter_name)¶
This function assigns the filter to the selected user account.
Note
Starting from Percona Server for MySQL 8.0.42, audit_log_filter_set_user()
accepts account names with wildcard characters ('%'
and '_'
) in the host part. For example, you can use 'usr1@%'
, 'usr2%192.168.0.%'
, or `’usr3@%.mycorp.com’.
A user account can only have one filter. If the user account already has a filter, this function replaces the current filter. The behavior depends on the user’s session status:
-
Active sessions: The filter change does not take effect immediately. The current session continues using the old filter.
-
Future connections: When the user account establishes a new connection, the new filter will be applied.
The user name, %
, is the default account. The filter assigned to %
is used
by any user account without a defined filter.
Parameters¶
-
user_name
- a selected user name in either theuser_name
@host_name
format or%
. -
filter_name
- a selected filter name as a string.
Returns¶
This function returns either an OK
for success or an error message for failure.
Example¶
mysql> SELECT audit_log_filter_set_user('user-name@localhost', 'filter-name');
Expected output
+-------------------------------------------------------------------+
| audit_log_filter_set_user('user-name@localhost', 'filter-name') |
+-------------------------------------------------------------------+
| OK |
+-------------------------------------------------------------------+
audit_log_filter_remove_user(user_name)¶
This function removes the assignment of a filter from the selected user account.
If the user account is in a current session, they are not affected. New sessions for this user account use the default account filter or are not logged.
If the user-name is %
, the default account filter is removed.
Parameters¶
user_name
- a selected user name in either the user_name
@host_name
format or %
.
Returns¶
This function returns either an OK
for success or an error message for failure.
If the user_name has no filter assigned, no error is generated.
Example¶
mysql> SELECT audit_log_filter_remove_user('user-name@localhost');
Expected output
+------------------------------------------------------+
| audit_log_filter_remove_user('user-name@localhost') |
+------------------------------------------------------+
| OK |
+------------------------------------------------------+
audit_log_filter_flush()¶
This function updates the audit log filter tables and makes any changes operational.
Modifying the audit log filter tables directly with INSERT
, UPDATE
, or
DELETE
does not implement the modifications immediately. The tables must be
flushed to have those changes take effect.
This function forces reloading all filters and should only be used if someone has modified the tables directly.
Important
Avoid using this function. This function performs an operation that is similar to uninstalling and reinstalling the plugin. Filters are detached from all current sessions. To restart logging, the current sessions must either disconnect and reconnect or do a change-user operation.
Parameters¶
None.
Returns¶
This function returns either an OK
for success or an error message for failure.
Example¶
mysql> SELECT audit_log_filter_flush();
Expected output
+--------------------------+
| audit_log_filter_flush() |
+--------------------------+
| OK |
+--------------------------+
audit_log_filter_filter_id
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-file-id |
Dynamic | No |
Scope | Session |
Data type | Integer |
Default | 0 |
Minimum value | 0 |
Maximum value | 4292967295 |
This variable defines the internal ID of the audit log filter in the current session.
The default value is 0 (zero) - the session has no assigned filter.
Log Reading and Navigation¶
audit_log_read()¶
If the audit log filter format is JSON, this function reads the audit log and returns an array of the audit events as a JSON string. Generates an error if the format is not JSON.
Parameters¶
None. If the start position is not provided, the read continues from the current position.
Optional: You can specify a starting position for the read with start
or a
timestamp
and an id
. Both items are considered a bookmark and can be used to
identify an event. You must include both (timestamp
and id
) or an error is
generated. If the timestamp
does not include a time
section, the function
assumes the time is 00:00
.
You can also provide a max_array_length
to limit the number of log events.
Call audit_log_read_bookmark()
to return the most
recently written event.
Returns¶
This function returns a string of a JSON
array of the audit events, or a JSON
NULL value. Returns NULL
and generates an error if the call fails.
Example¶
mysql> SELECT audit_log_read();
Expected output
+------------------------------------------------------------------------------+
| audit_log_read() |
+------------------------------------------------------------------------------+
| [{"timestamp" : "2023-06-02 09:43:25", "id": 10, "class":"connection", |
| "connection_id": 1, "user": "root@localhost", "host": "localhost", |
| "command_class": "select", "sqltext": "SELECT * FROM users"}] |
+------------------------------------------------------------------------------+
mysql> SELECT audit_log_read(audit_log_read_bookmark());
Expected output
+------------------------------------------------------------------------------+
| audit_log_read(audit_log_read_bookmark()) |
+------------------------------------------------------------------------------+
| [{"timestamp" : "2023-06-02 09:43:25", "id": 10, "class":"connection", |
| "connection_id": 1, "user": "root@localhost", "host": "localhost", |
| "command_class": "select", "sqltext": "SELECT * FROM users"}] |
+------------------------------------------------------------------------------+
mysql> SELECT audit_log_read('{"timestamp": "2023-06-02 09:43:25", "id": 10}');
Expected output
+------------------------------------------------------------------------------+
| audit_log_read('{"timestamp": "2023-06-02 09:43:25", "id": 10}') |
+------------------------------------------------------------------------------+
| [{"timestamp" : "2023-06-02 09:43:25", "id": 10, "class":"connection", |
| "connection_id": 1, "user": "root@localhost", "host": "localhost", |
| "command_class": "select", "sqltext": "SELECT * FROM users"}] |
+------------------------------------------------------------------------------+
mysql> SELECT audit_log_read('{"timestamp": "2023-06-02 09:43:25", "id": 10}', 100);
audit_log_read_bookmark()¶
This function provides a bookmark for the most recently written audit log event as a JSON string. Generates an error if the format is not JSON.
If this function is used with audit_log_read()
, the
audit_log_read()
function starts reading at that position.
Parameters¶
None.
Returns¶
This function returns a JSON
string containing a bookmark for success or
NULL
and an error for failure.
Example¶
mysql> SELECT audit_log_read_bookmark();
Expected output
+----------------------------------------------------+
| audit_log_read_bookmark() |
+----------------------------------------------------+
| {"timestamp" : "2023-06-02 09:43:25", "id": 10 } |
+----------------------------------------------------+
audit_log_filter_read_buffer_size
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-read-buffer-size |
Dynamic | Yes |
Scope | Global |
Data type | Integer |
Unit | Bytes |
Default | 32768 |
This option is only supported for JSON-format files.
The size of the buffer for reading from the audit log filter file. The audit_log_read()
function reads only from this buffer size.
Log File Management¶
audit_log_rotate()¶
This function rotates the current audit log file. The current file is renamed with a timestamp suffix. A new empty log file is created with the original name.
Parameters¶
None.
Returns¶
This function returns the renamed file name.
Example¶
mysql> SELECT audit_log_rotate();
Expected output
+----------------------------------+
| audit_log_rotate() |
+----------------------------------+
| audit_filter.log.20250901120000 |
+----------------------------------+
audit_log_filter_file
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-file |
Dynamic | No |
Scope | Global |
Data type | String |
Default | audit_filter.log |
This variable defines the name of the audit log filter file. The plugin writes events to this file.
The file name can be specified in either of the following ways:
-
Relative path: The plugin creates the file in the MySQL data directory
-
Full path: The plugin uses the exact path you specify
If you use a full path, ensure the directory is accessible only to users who need to view the log and the MySQL server.
For more information, see Naming conventions
audit_log_filter_max_size
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-max-size |
Dynamic | Yes |
Scope | Global |
Data type | Integer |
Default | 1GB |
Minimum value | 0 |
Maximum value | 18446744073709551615 |
Unit | bytes |
Block size | 4096 |
Defines pruning based on the combined size of the files:
The default value is 1GB.
A value of 0 (zero) disables pruning based on size.
A value greater than 0 (zero) enables pruning based on size and defines the combined size limit. When the files exceed this limit, they can be pruned.
The value is based on 4096 (block size). A value is truncated to the nearest multiple of the block size. If the value is less than 4096, the value is treated as 0 (zero).
If the values for audit_log_filter_rotate_on_size
and audit_log_filter_max_size
are greater than 0, we recommend that audit_log_filter_max_size
value should be at least seven times the audit_log_filter_rotate_on_size
value.
Pruning requires the following options:
audit_log_filter_max_size
audit_log_filter_rotate_on_size
audit_log_filter_prune_seconds
audit_log_filter_rotate_on_size
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-rotate-on-size |
Dynamic | Yes |
Scope | Global |
Data type | Integer |
Default | 1GB |
Performs an automatic log file rotation based on the size. The default value is 1GB. If the value is greater than 0, when the log file size exceeds the value, the plugin renames the current file and opens a new log file using the original name.
If you set the value to less than 4096, the plugin does not automatically rotate the log files. You can rotate the log files manually using audit_log_rotate()
. If the value is not a multiple of 4096, the plugin truncates the value to the nearest multiple.
audit_log_filter_prune_seconds
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-prune-seconds |
Dynamic | Yes |
Scope | Global |
Data type | Integer |
Default | 0 |
Minimum value | 0 |
Maximum value | 1844674073709551615 |
Unit | seconds |
Defines when the audit log filter file is pruned. This pruning is based on the age of the file. The value is measured in seconds.
A value of 0 (zero) is the default and disables pruning. The maximum value is 18446744073709551615.
A value greater than 0 enables pruning. An audit log filter file can be pruned after this value.
To enable log pruning, you must set one of the following:
- Enable log rotation by setting
audit_log_filter_rotate_on_size
- Add a value greater than 0 (zero) for either
audit_log_filter_max_size
oraudit_log_filter_prune_seconds
Output Format and Destination¶
audit_log_filter_format
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-format |
Dynamic | No |
Scope | Global |
Data type | Enumeration |
Default | NEW |
Available values | OLD, NEW, JSON |
This variable defines the audit log filter file format.
The available values are the following:
audit_log_filter_format_unix_timestamp
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-format-unix-timestamp |
Dynamic | Yes |
Scope | Global |
Data type | Boolean |
Default | OFF |
This option is only supported for JSON-format files. This option does nothing when used with other format types.
Enabling this option adds a time
field to JSON-format files. The integer
represents the UNIX timestamp value and indicates the date and time when the
audit event was generated. Changing the value causes a file rotation because all
records must either have or do not have the time
field. This option requires
the AUDIT_ADMIN
and SYSTEM_VARIABLES_ADMIN
privileges.
audit_log_filter_handler
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-handler |
Dynamic | No |
Scope | Global |
Data type | String |
Default | FILE |
Defines where the plugin writes the audit log filter file. The following values are available:
-
FILE
- plugin writes the log to a location specified inaudit_log_filter_file
-
SYSLOG
- plugin writes to the syslog
When using SYSLOG
, the plugin uses the syslog facility and priority settings
defined by audit_log_filter_syslog_facility
and audit_log_filter_syslog_priority
variables. The syslog tag is controlled by audit_log_filter_syslog_tag
.
audit_log_filter_syslog_tag
¶
Option | Description |
---|---|
Command-line | –audit-log-filter-syslog-tag= |
Dynamic | No |
Scope | Global |
Data type | String |
Default | audit-filter |
Specifies the syslog tag value used when writing audit log entries to syslog. This tag appears in syslog entries to identify the source of the audit log messages. The default value is “audit-filter”.
audit_log_filter_syslog_priority
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-syslog-priority |
Dynamic | No |
Scope | Global |
Data type | String |
Default | LOG_INFO |
Defines the priority
value for the syslog. The option has the same meaning as the appropriate parameter described in the syslog(3) manual.
Performance and Buffering¶
audit_log_filter_buffer_size
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-buffer-size |
Dynamic | No |
Scope | Global |
Data type | Integer |
Default | 1048576 |
Minimum value | 4096 |
Maximum value | 18446744073709547520 |
Units | bytes |
Block size | 4096 |
This variable defines the buffer size in multiples of 4096 when logging is asynchronous. Audit events are temporarily stored in this buffer before being written to the log file, which improves performance by reducing disk I/O operations.
The plugin initializes a single buffer and removes the buffer when the plugin terminates.
audit_log_filter_strategy
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-strategy |
Dynamic | No |
Scope | Global |
Data type | Enumeration |
Default | ASYNCHRONOUS |
Defines the Audit Log filter plugin’s logging method. The valid values are the following:
Values | Description |
---|---|
ASYNCHRONOUS | Waits until there is buffer space available (default, best performance) |
PERFORMANCE | Drops audit events if buffer is full (fastest, may lose events) |
SEMISYNCHRONOUS | Allows OS caching, calls sync() periodically (balanced) |
SYNCHRONOUS | Calls sync() after each event (safest, slowest performance) |
Encryption and Security¶
audit_log_encryption_password_get(keyring_id)¶
This function returns the encryption password. Any keyring plugin or keyring component can be used. The plugin or component must be enabled. If the plugin or component is not enabled, an error occurs.
Parameters¶
keyring_id
- Optional parameter. If omitted, the function returns the current
encryption password. If provided, specify the keyring ID to retrieve a specific
encryption password (current or archived). The keyring ID is a string identifier
used by the keyring system to distinguish between different stored passwords.
Returns¶
This function returns a JSON object with the following structure:
- password
: The encryption password as a string
- iterations
: The number of iterations used for password derivation (integer)
Example¶
mysql> SELECT audit_log_encryption_password_get();
Expected output
+---------------------------------------------+
| audit_log_encryption_password_get() |
+---------------------------------------------+
| {"password":"passw0rd","iterations":5689} |
+---------------------------------------------+
mysql> SELECT audit_log_encryption_password_get('current_key');
Security considerations¶
This function returns sensitive encryption passwords. Ensure proper access controls are in place. The returned password should be handled securely and not logged or stored in plain text. Only users with appropriate privileges should execute this function.
audit_log_encryption_password_set(new_password)¶
This function sets the encryption password and stores the new password in the keyring.
Parameters¶
new_password
- the new password as a string. The maximum length is 766 bytes.
Returns¶
This function returns a string. An OK
indicates a success. ERROR
indicates
a failure.
Example¶
mysql> SELECT audit_log_encryption_password_set('passw0rd');
Expected output
+-----------------------------------------------------+
| audit_log_encryption_password_set('passw0rd') |
+-----------------------------------------------------+
| OK |
+-----------------------------------------------------+
Security considerations¶
This function stores sensitive encryption passwords in the keyring. Ensure the password meets security requirements (sufficient length, complexity). The password is used to encrypt audit log files and should be kept secure. Only users with appropriate privileges should execute this function.
audit_log_filter_encryption
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-encryption |
Dynamic | No |
Scope | Global |
Data type | Enumeration |
Default | NONE |
Valid values | NONE or AES |
This variable defines the encryption type for the audit log filter file. The values can be either of the following:
NONE
- the default value, no encryptionAES
- uses AES-256 encryption for audit log files
When encryption is enabled, the plugin uses AES-256 encryption to secure audit log files. The encryption keys are managed through the MySQL keyring system. Ensure that a keyring plugin is installed and configured before enabling encryption.
audit_log_filter_key_derivation_iterations_count_mean
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-key-derivation-iterations-count-mean |
Dynamic | Yes |
Scope | Global |
Data type | Integer |
Default | 60000 |
Minimum value | 1000 |
Maximum value | 1000000 |
Defines the average number of iterations used when deriving encryption keys from passwords. The actual iteration count is randomly generated and varies by no more than 10% from this mean value.
Higher values provide better security but require more CPU time for key derivation. The default value of 60000 provides a good balance between security and performance. For high-security environments, consider increasing this value to 100000 or higher.
audit_log_filter_password_history_keep_days
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-password-history-keep-days |
Dynamic | Yes |
Scope | Global |
Data type | Integer |
Default | 0 |
Defines when passwords may be removed and measured in days.
Encrypted log files have passwords stored in the keyring. The plugin also stores a password history. A password does not expire, despite being past the value, in case the password is used for rotated audit logs. The operation of creating a password also archives the previous password.
The default value is 0 (zero). This value disables the expiration of passwords. Passwords are retained forever.
If the plugin starts and encryption is enabled, the plugin checks for an audit log filter encryption password. If a password is not found, the plugin generates a random password.
Call audit_log_encryption_password_set(new_password) to set a specific password.
Compression¶
audit_log_filter_compression
¶
Option name | Description |
---|---|
Command-line | –audit-log-filter-compression |
Dynamic | Yes |
Scope | Global |
Data type | Enumeration |
Default | NONE |
Valid values | NONE or GZIP |
This variable defines the compression type for the audit log filter file. The
values can be either NONE
, the default value and file has no compression, or
GZIP
.
Audit log filter status variables¶
The audit log filter plugin exposes status variables. These variables provide information on the operations.
Name | Description |
---|---|
audit_log_filter_current_size |
The current size of the audit log filter file. If the log is rotated, the size is reset to 0. |
audit_log_filter_direct_writes |
Identifies when the log_strategy_type = ASYNCHRONOUS and messages bypass the write buffer and are written directly to the log file |
audit_log_filter_max_drop_size |
In the performance logging mode, the size of the largest dropped event. |
audit_log_filter_events |
The number of audit log filter events |
audit_log_filter_events_filtered |
The number of filtered audit log filter plugin events |
audit_log_filter_events_lost |
If the event is larger than the available audit log filter buffer space, the event is lost |
audit_log_filter_events_written |
The number of audit log filter events written |
audit_log_filter_total_size |
The total size of the events written to all audit log filter files. The number increases even when a log is rotated |
audit_log_filter_write_waits |
In the asynchronous logging mode, the number of times an event waited for space in the audit log filter buffer |