Turn on auditing for Queries, Connections, and users on MySQL or MariaDB database

Enable the Audit plugin on MySQL or MariaDB database:

Modify the database configuration file with the following parameters:

Windows: C:\Program Files\MariaDB 10.6\data\my.in

Linux: /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
plugin_load_add = server_audit # load plugin
server_audit=FORCE_PLUS_PERMANENT  # do not allow users to uninstall plugin
server_audit_file_path=C:\d\mariadb-audit.log # path to the audit log
server_audit_logging=ON  # enable audit logging

Restarting the database Services will cause the change in effects.

Choose the Audit Events that you want for auditing

By default, all events are audited which causes the log file to be filled faster. For the filter, we use the parameter:

SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';

Event	Description
Connect	Log Connects, disconnects, and failed to connect (including the error code)
Query	Log queries executed and their results in plain text, including failed queries due to syntax or permission errors
TABLE	Log table name affected by query execution
QUERY_DDL	Log only DDL Queries
QUERY_DML	Log only DML Queries
QUERY_DML_NO_SELECT	Log-only DML Queries
QUERY_DCL	Log only DCL Queries

Choose the Audit user

If you want to exclude the user from auditing, Need to set the following parameter:

SET GLOBAL server_audit_excl_users = 'HR,SCOTT'

Note: Exclude the user HR and SCOTT from Auditing

Audit file Log Rotation

If we set the parameter server_audit_output_type=file, then the audit log is generated in the file. So on timely bases, we have to rotate it before its difficult to manage:

-- For Manually rotation, we need to fire this command:
SET GLOBAL server_audit_file_rotate_now = 1;

--For Automatic rotation, we need to specify size:
SET GLOBAL server_audit_file_rotate_size=1000000; --bytes
SET GLOBAL server_audit_file_rotations=30;  -- default is 9

Note: 
Parameter server_audit_file_rotations specify the number of rotations to save. 
Parameter server_audit_file_rotate_size specify the limits the size of the log file to the given amount of bytes

Audit Log file specification

Please find the format specification of the log file and a sample picture of the audit log file below:

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode]

Retcode: 0 means the operation returns a success response, non 0 value means error while executing query.

List of parameters involved in Server Audit:

show variables like '%server_audit%'

Variable_name                |Value                 |
-----------------------------+----------------------+
server_audit_events          |                      |
server_audit_excl_users      |                      |
server_audit_file_path       |C:\d\mariadb-audit.log|
server_audit_file_rotate_now |OFF                   |
server_audit_file_rotate_size|1000000               |
server_audit_file_rotations  |9                     |
server_audit_incl_users      |                      |
server_audit_logging         |ON                    |
server_audit_mode            |0                     |
server_audit_output_type     |file                  |
server_audit_query_log_limit |1024                  |
server_audit_syslog_facility |LOG_USER              |
server_audit_syslog_ident    |mysql-server_auditing |
server_audit_syslog_info     |                      |
server_audit_syslog_priority |LOG_INFO              |

Disable the Audit for MySQL or MariaDB:

server_audit=OFF

--Restart the service of MariaDB or MySQL