Skip to content

Rate this page
Thanks for your feedback
Thank you! The feedback has been submitted.

For help, click the link below to get free database assistance or contact our experts for personalized support.

Audit log plugin

Deprecation notice

The audit log plugin is deprecated as of Percona Server for MySQL 8.4 and will be removed in a future release. The audit log filter component is the recommended replacement. Migrate to the component for equivalent functionality with improved flexibility, performance, and filtering capabilities.

For a step-by-step mapping of audit_log_* system variables, audit_log_policy, and include/exclude lists to filter JSON and component variables, see Migrate to the audit log filter component.

The deprecation affects all installations that rely on the audit log plugin for event logging, compliance auditing, or activity tracking. The plugin continues to function, but Percona plans no further development or maintenance.

The audit log plugin and the audit log filter component use different configuration variables and options.

  • Do not use audit log filter variables, options, or configuration syntax with the deprecated audit log plugin. Mixing the two can cause startup failures, unexpected behavior, or data loss.

  • Do not install the audit log plugin and the audit log filter component at the same time.

Audit log entries may look different from earlier 8.0 entries. The audit log plugin itself has not changed, but other server components have, and those changes affect the log output. For example, current releases log a SELECT $$ statement each time a client connects, because the client now supports “dollar-quoted” strings. This feature did not exist in 8.0.

Percona does not plan to modify current audit logs to match the format or content of 8.0 logs.

The Percona Audit Log Plugin monitors and logs connection and query activity on a specific server. The plugin stores the activity information in a log file.

Install the plugin

The Audit Log plugin ships with Percona Server for MySQL but is not enabled by default. To check whether the plugin is enabled, run the following statement. The query searches for plugins with names that contain the word “audit” in the information_schema.PLUGINS table.

SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%';

An empty result indicates that no such plugins are installed or loaded.

Expected output
Empty set (0.00 sec)

The following statement checks for system variables whose names start with audit:

SHOW variables LIKE 'audit%';

An empty result means that no matching system variables exist or are currently defined.

Expected output
Empty set (0.01 sec)

The following statement lists system variables whose names start with plugin. The example output shows the plugin_dir variable, which specifies the directory path where MySQL plugins are stored.

SHOW variables LIKE 'plugin%';
Expected output
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+
1 row in set (0.00 sec)

Note

The location of the MySQL plugin directory depends on the operating system and may differ on your system.

The following statement enables the plugin:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Run the following statement to verify that the plugin installed correctly:

SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'\G
Expected output
*************************** 1. row ***************************
        PLUGIN_NAME: audit_log
        PLUGIN_VERSION: 0.2
        PLUGIN_STATUS: ACTIVE
        PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 4.1
        PLUGIN_LIBRARY: audit_log.so
PLUGIN_LIBRARY_VERSION: 1.7
        PLUGIN_AUTHOR: Percona LLC and/or its affiliates.
    PLUGIN_DESCRIPTION: Audit log
        PLUGIN_LICENSE: GPL
        LOAD_OPTION: ON
1 row in set (0.00 sec)

Review the audit log variables with the following statement:

SHOW variables LIKE 'audit%';
Expected output
+-----------------------------+---------------+
| Variable_name               | Value         |
+-----------------------------+---------------+
| audit_log_buffer_size       | 1048576       |
| audit_log_exclude_accounts  |               |
| audit_log_exclude_commands  |               |
| audit_log_exclude_databases |               |
| audit_log_file              | audit.log     |
| audit_log_flush             | OFF           |
| audit_log_format            | OLD           |
| audit_log_handler           | FILE          |
| audit_log_include_accounts  |               |
| audit_log_include_commands  |               |
| audit_log_include_databases |               |
| audit_log_policy            | ALL           |
| audit_log_rotate_on_size    | 0             |
| audit_log_rotations         | 0             |
| audit_log_strategy          | ASYNCHRONOUS  |
| audit_log_syslog_facility   | LOG_USER      |
| audit_log_syslog_ident      | percona-audit |
| audit_log_syslog_priority   | LOG_INFO      |
+-----------------------------+---------------+
18 rows in set (0.00 sec)

Log format

The plugin supports the following log formats: OLD, NEW, JSON, and CSV. The OLD and NEW formats are both based on XML. The OLD format defines each log record with XML attributes. The NEW format defines each log record with XML tags. The information logged is the same for all four formats. The audit_log_format variable controls the format choice.

Format examples

The following formats are available:

<AUDIT_RECORD
NAME="Query"
RECORD="3_2021-06-30T11:56:53"
TIMESTAMP="2021-06-30T11:57:14 UTC"
COMMAND_CLASS="select"
CONNECTION_ID="3"
STATUS="0"
SQLTEXT="select * from information_schema.PLUGINS where PLUGIN_NAME like '%audit%'"
USER="root[root] @ localhost []"
HOST="localhost"
OS_USER=""
IP=""
DB=""
/>
<AUDIT_RECORD>
<NAME>Query</NAME>
<RECORD>16684_2021-06-30T16:07:41</RECORD>
<TIMESTAMP>2021-06-30T16:08:06 UTC</TIMESTAMP>
<COMMAND_CLASS>select</COMMAND_CLASS>
<CONNECTION_ID>2</CONNECTION_ID>
<STATUS>0</STATUS>
<SQLTEXT>select id, holder from one</SQLTEXT>
<USER>root[root] @ localhost []</USER>
<HOST>localhost</HOST>
<OS_USER></OS_USER>
<IP></IP>
<DB></DB>
{"audit_record":{"name":"Query","record":"13149_2021-06-30T15:03:11","timestamp":"2021-06-30T15:07:58 UTC","command_class":"show_databases","connection_id":"2","status":0,"sqltext":"show databases","user":"root[root] @ localhost []","host":"localhost","os_user":"","ip":"","db":""}}
"Query","22567_2021-06-30T16:10:09","2021-06-30T16:19:00 UTC","select","2",0,"select count(*) from one","root[root] @ localhost []","localhost","","",""

Audit log events

The Audit Log plugin generates log records for the following events.

An Audit event indicates that audit logging started or finished. The NAME field contains Audit when logging started and NoAudit when logging finished. The audit record also includes the server version and command-line arguments.

??? example "Audit event"

    ```{.text .no-copy}
    <AUDIT_RECORD
    NAME="Audit"
    RECORD="1_2021-06-30T11:56:53"
    TIMESTAMP="2021-06-30T11:56:53 UTC"
    MYSQL_VERSION="5.7.34-37"
    STARTUP_OPTIONS="--daemonize --pid-file=/var/run/mysqld/mysqld.pid"
    OS_VERSION="x86_64-debian-linux-gnu"
    />
    ```

The connect record event uses the NAME field Connect when a user logs in or a login fails, and Quit when the connection closes.

The additional fields for this event are as follows:

* `CONNECTION_ID`

* `STATUS`

* `USER`

* `PRIV_USER`

* `OS_LOGIN`

* `PROXY_USER`

* `HOST`

* `IP`

STATUS is 0 for successful logins and non-zero for failed logins.

Disconnect event
<AUDIT_RECORD
NAME="Quit"
RECORD="5_2021-06-29T19:33:03"
TIMESTAMP="2021-06-29T19:34:38Z"
CONNECTION_ID="14"
STATUS="0"
USER="root"
PRIV_USER="root"
OS_LOGIN=""
PROXY_USER=""
HOST="localhost"
IP=""
DB=""
/>

The additional fields for this event are COMMAND_CLASS, CONNECTION_ID, STATUS, SQLTEXT, USER, HOST, OS_USER, and IP.

  • COMMAND_CLASS — Values come from the com_status_vars array in the sql/mysqld.cc file of the MySQL source distribution. Example values include select, alter_table, and create_table.

  • STATUS — Non-zero value indicates an error.

  • SQLTEXT — Text of the SQL statement.

Possible values for the NAME field include Query, Prepare, Execute, and Change user.

Query event
<AUDIT_RECORD
NAME="Query"
RECORD="4_2021-06-29T19:33:03"
TIMESTAMP="2021-06-29T19:33:34Z"
COMMAND_CLASS="show_variables"
CONNECTION_ID="14"
STATUS="0"
SQLTEXT="show variables like 'audit%'"
USER="root[root] @ localhost []"
HOST="localhost"
OS_USER=""
IP=""
DB=""
/>

Stream the audit log to syslog

To stream the audit log to syslog, set the audit_log_handler variable to SYSLOG. Use audit_log_syslog_ident, audit_log_syslog_facility, and audit_log_syslog_priority to control the syslog handler. These variables share the meanings of the corresponding parameters documented in the syslog(3) manual.

Note

The FILE handler is the only handler that captures the effects of audit_log_strategy, audit_log_buffer_size, audit_log_rotate_on_size, and audit_log_rotations.

Filter methods

Filter the results with any of the following methods.

Filtering by user uses two global variables, audit_log_include_accounts and audit_log_exclude_accounts, which specify the user accounts to include in or exclude from audit logging.

Only one variable at a time may hold a list; the other must be NULL. An attempt to set the second variable fails while the first variable is non-NULL. An empty string represents an empty list.

Changes to audit_log_include_accounts and audit_log_exclude_accounts do not apply to existing server connections.

Filtering by SQL command type uses two global variables, audit_log_include_commands and audit_log_exclude_commands, which specify the command types to include in or exclude from audit logging.

Only one variable at a time may hold a list; the other must be NULL. An attempt to set the second variable fails while the first variable is non-NULL. An empty string represents an empty list.

When both audit_log_exclude_commands and audit_log_include_commands are NULL, the component logs all commands.

Filtering by SQL database uses two global variables, audit_log_include_databases and audit_log_exclude_databases, which specify the databases to include in or exclude from audit logging.

Only one variable at a time may hold a list; the other must be NULL. An attempt to set the second variable fails while the first variable is non-NULL. An empty string represents an empty list.

A query that accesses any database listed in audit_log_include_databases is logged. A query that accesses only databases listed in audit_log_exclude_databases is not logged. CREATE TABLE statements are logged unconditionally.

Changes to audit_log_include_databases and audit_log_exclude_databases do not apply to existing server connections.

Filter examples

The following are examples of the different filters.

The following example adds users who will be monitored:

SET GLOBAL audit_log_include_accounts = 'user1@localhost,root@localhost';
Expected output
Query OK, 0 rows affected (0.00 sec)

If you try to add users to both the include list and the exclude list, the server returns the following error:

SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost';
Expected output
ERROR 1231 (42000): Variable 'audit_log_exclude_accounts' can't be set to the value of 'user1@localhost,root@localhost'

To switch from filtering by included user list to the excluded user list or back, first set the currently active filtering variable to NULL:

SET GLOBAL audit_log_include_accounts = NULL;
Expected output
Query OK, 0 rows affected (0.00 sec)
SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost';
Expected output
Query OK, 0 rows affected (0.00 sec)
SET GLOBAL audit_log_exclude_accounts = "'user'@'host'";
Expected output
Query OK, 0 rows affected (0.00 sec)
SET GLOBAL audit_log_exclude_accounts = '''user''@''host''';
Expected output
Query OK, 0 rows affected (0.00 sec)
SET GLOBAL audit_log_exclude_accounts = '\'user\'@\'host\'';
Expected output
Query OK, 0 rows affected (0.00 sec)

To see which user accounts have been added to the exclude list, run the following command:

SELECT @@audit_log_exclude_accounts;
Expected output
+------------------------------+
| @@audit_log_exclude_accounts |
+------------------------------+
| 'user'@'host'                |
+------------------------------+
1 row in set (0.00 sec)

Account names from mysql.user table are logged in the audit log. For example when you create a user:

CREATE USER 'user1'@'%' IDENTIFIED BY '111';
Expected output
Query OK, 0 rows affected (0.00 sec)

When user1 connects from localhost, the user is listed:

<AUDIT_RECORD
NAME="Connect"
RECORD="2_2021-06-30T11:56:53"
TIMESTAMP="2021-06-30T11:56:53 UTC"
CONNECTION_ID="6"
STATUS="0"
USER="user1" ;; this is a 'user' part of account
PRIV_USER="user1"
OS_LOGIN=""
PROXY_USER=""
HOST="localhost" ;; this is a 'host' part of account
IP=""
DB=""
/>

To exclude user1 from logging, set:

SET GLOBAL audit_log_exclude_accounts = 'user1@%';

The value may be NULL or a comma-separated list of accounts in the form user@host or 'user'@'host' (use the quoted form when the user or host contains a comma).

The available command types can be listed by running:

SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/%" ORDER BY name;
Expected output
+------------------------------------------+
| name                                     |
+------------------------------------------+
| statement/sql/alter_db                   |
| statement/sql/alter_db_upgrade           |
| statement/sql/alter_event                |
| statement/sql/alter_function             |
| statement/sql/alter_procedure            |
| statement/sql/alter_server               |
| statement/sql/alter_table                |
| statement/sql/alter_tablespace           |
| statement/sql/alter_user                 |
| statement/sql/analyze                    |
| statement/sql/assign_to_keycache         |
| statement/sql/begin                      |
| statement/sql/binlog                     |
| statement/sql/call_procedure             |
| statement/sql/change_db                  |
| statement/sql/change_master              |
...
| statement/sql/xa_rollback                |
| statement/sql/xa_start                   |
+------------------------------------------+
145 rows in set (0.00 sec)

You can add commands to the include filter by running:

SET GLOBAL audit_log_include_commands= 'set_option,create_db';

Create a database with the following command:

CREATE DATABASE sample;
Expected output
<AUDIT_RECORD>
<NAME>Query</NAME>
<RECORD>24320_2021-06-30T17:44:46</RECORD>
<TIMESTAMP>2021-06-30T17:45:16 UTC</TIMESTAMP>
<COMMAND_CLASS>create_db</COMMAND_CLASS>
<CONNECTION_ID>2</CONNECTION_ID>
<STATUS>0</STATUS>
<SQLTEXT>CREATE DATABASE sample</SQLTEXT>
<USER>root[root] @ localhost []</USER>
<HOST>localhost</HOST>
<OS_USER></OS_USER>
<IP></IP>
<DB></DB>
</AUDIT_RECORD>

To switch the command type filtering type from included type list to the excluded list or back, first reset the currently-active list to NULL:

SET GLOBAL audit_log_include_commands = NULL;
Expected output
Query OK, 0 rows affected (0.00 sec)
SET GLOBAL audit_log_exclude_commands= 'set_option,create_db';
Expected output
Query OK, 0 rows affected (0.00 sec)

A stored procedure has the call_procedure command type. All the statements executed within the procedure have the same type call_procedure as well.

To add databases to be monitored, run:

SET GLOBAL audit_log_include_databases = 'test,mysql,db1';
Expected output
Query OK, 0 rows affected (0.00 sec)
SET GLOBAL audit_log_include_databases= 'db1','db3';
Expected output
Query OK, 0 rows affected (0.00 sec)

When you try to add databases to both the include and exclude lists, the server returns the following error:

SET GLOBAL audit_log_exclude_databases = 'test,mysql,db1';
Error message
ERROR 1231 (42000): Variable 'audit_log_exclude_databases can't be set to the value of 'test,mysql,db1'

To switch from filtering by included database list to the excluded one or back, first set the currently active filtering variable to NULL:

SET GLOBAL audit_log_include_databases = NULL;
Expected output
Query OK, 0 rows affected (0.00 sec)
SET GLOBAL audit_log_exclude_databases = 'test,mysql,db1';
Expected output
Query OK, 0 rows affected (0.00 sec)

System variables

audit_log_strategy

Option Description
Command Line: Yes
Scope: Global
Dynamic: No
Data type String
Default value ASYNCHRONOUS
Allowed values ASYNCHRONOUS, PERFORMANCE, SEMISYNCHRONOUS, SYNCHRONOUS

Specifies the audit log strategy. The allowed values are as follows:

  • ASYNCHRONOUS — (default) log through a memory buffer and do not drop messages when the buffer is full.

  • PERFORMANCE — log through a memory buffer and drop messages when the buffer is full.

  • SEMISYNCHRONOUS — log directly to the file without flushing and syncing every event.

  • SYNCHRONOUS — log directly to the file and flush and sync every event.

The variable has effect only when audit_log_handler is set to FILE.

audit_log_file

Option Description
Command Line: Yes
Scope: Global
Dynamic: No
Data type String
Default value audit.log

Specifies the filename that stores the audit log. The value may be a path relative to the data directory or an absolute path.

audit_log_flush

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type String
Default value OFF

When the variable is set to ON, the server closes and reopens the log file.

audit_log_buffer_size

Option Description
Command Line: Yes
Scope: Global
Dynamic: No
Data type Numeric
Default value 1 Mb

Specifies the size of the memory buffer used for logging when audit_log_strategy is ASYNCHRONOUS or PERFORMANCE. The variable has effect only when audit_log_handler is set to FILE.

audit_log_exclude_accounts

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type String

Specifies the list of users to which filtering by user applies. The value may be NULL or a comma-separated list of accounts in the form user@host or 'user'@'host' (use the quoted form when the user or host contains a comma). When audit_log_exclude_accounts is set, leave audit_log_include_accounts unset, and vice versa.

audit_log_exclude_commands

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type String

Specifies the list of commands to which filtering by SQL command type applies. The value may be NULL or a comma-separated list of commands. When audit_log_exclude_commands is set, leave audit_log_include_commands unset, and vice versa.

audit_log_exclude_databases

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type String

Specifies the databases to filter. The value may be NULL or a comma-separated list of databases. When audit_log_exclude_databases is set, leave audit_log_include_databases unset, and vice versa.

audit_log_format

Option Description
Command Line: Yes
Scope: Global
Dynamic: No
Data type String
Default value OLD
Allowed values OLD, NEW, CSV, JSON

Specifies the audit log format. The audit log plugin supports four log formats: OLD, NEW, JSON, and CSV. The OLD and NEW formats are both based on XML. The OLD format outputs log record properties as XML attributes; the NEW format outputs them as XML tags. The information logged is the same in all four formats.

audit_log_include_accounts

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type String

Specifies the list of users to which filtering by user applies. The value may be NULL or a comma-separated list of accounts in the form user@host or 'user'@'host' (use the quoted form when the user or host contains a comma). When audit_log_include_accounts is set, leave audit_log_exclude_accounts unset, and vice versa.

audit_log_include_commands

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type String

Specifies the list of commands to which filtering by SQL command type applies. The value may be NULL or a comma-separated list of commands. When audit_log_include_commands is set, leave audit_log_exclude_commands unset, and vice versa.

audit_log_include_databases

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type String

Specifies the list of databases to filter. The value may be NULL or a comma-separated list of databases. When audit_log_include_databases is set, leave audit_log_exclude_databases unset, and vice versa.

audit_log_policy

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type String
Default ALL
Allowed values ALL, LOGINS, QUERIES, NONE

Specifies which events to log. The allowed values are as follows:

  • ALL — log all events.

  • LOGINS — log only logins.

  • QUERIES — log only queries.

  • NONE — log no events.

audit_log_rotate_on_size

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type Numeric
Default value 0

Specifies the maximum audit log file size in bytes. When the audit log reaches this size, the server rotates the log. Rotated log files remain in the same directory as the current log file. The server appends a sequence number to the log file name on rotation.

When the value is 0 (the default), the audit log files do not rotate.

Set audit_log_handler to FILE to enable this variable.

audit_log_rotations

Option Description
Command Line: Yes
Scope: Global
Dynamic: Yes
Data type Numeric
Default value 0

Specifies how many log files to keep when audit_log_rotate_on_size is set to a non-zero value. The variable has effect only when audit_log_handler is set to FILE.

audit_log_handler

Option Description
Command Line: Yes
Scope: Global
Dynamic: No
Data type String
Default value FILE
Allowed values FILE, SYSLOG

Specifies where to write the audit log. FILE writes to the file specified by audit_log_file. SYSLOG writes to syslog.

audit_log_syslog_ident

Option Description
Command Line: Yes
Scope: Global
Dynamic: No
Data type String
Default value percona-audit

Specifies the ident value for syslog. The variable has the same meaning as the corresponding parameter in the syslog(3) manual.

audit_log_syslog_facility

Option Description
Command Line: Yes
Scope: Global
Dynamic: No
Data type String
Default value LOG_USER

Specifies the facility value for syslog. The variable has the same meaning as the corresponding parameter in the syslog(3) manual.

audit_log_syslog_priority

Option Description
Command Line: Yes
Scope: Global
Dynamic: No
Data type String
Default value LOG_INFO
Allowed values LOG_EMERG, LOG_ALERT, LOG_CRIT, LOG_ERR, LOG_WARNING, LOG_NOTICE, LOG_INFO, LOG_DEBUG

Specifies the severity level for syslog. The audit_log_syslog_priority variable does not include the facility. The variable selects only the severity level (LOG_EMERG through LOG_DEBUG).

The server builds the full syslog priority passed to syslog() by OR-ing the configured facility (audit_log_syslog_facility) with this level.

The default LOG_INFO represents ordinary informational messages. Raise or lower the level as needed. The facility stays at the default unless you change it explicitly.

For more details about syslog priority levels, see the syslog(3) manual.

Status Variables

Audit_log_buffer_size_overflow

Option Description
Scope: Global
Data type Numeric

The number of times an audit log entry was dropped or written directly to the file because the entry size exceeded audit_log_buffer_size.

Additional reading