Enable audit logging plugin in MySQL or MariaDB

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

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';
ConnectLog Connects, disconnects, and failed to connect (including the error code)
QueryLog queries executed and their results in plain text, including failed queries due to syntax or permission errors
TABLELog table name affected by query execution
QUERY_DDLLog only DDL Queries
QUERY_DMLLog only DML Queries
QUERY_DCLLog only DCL Queries
Audit Event Table

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

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:


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:


--Restart the service of MariaDB or MySQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.