How to Configure Audit Logging in MySQL

This guide explains how to enable and configure audit logging in a MySQL database instance to track and monitor user activity. It covers the installation of the audit log plugin, configuration of essential system variables, and setting up filters to record specific database events, such as logins, connections, and executed queries, to ensure security and compliance.

1. Choose an Audit Log Plugin

MySQL Community Edition does not include a built-in audit log plugin by default, whereas MySQL Enterprise Edition comes with the proprietary audit_log plugin. If you are using the Community Edition, you can use popular open-source alternatives such as the MariaDB Audit Plugin or the Percona Audit Log Plugin.

The installation and configuration steps below primarily focus on the standard MySQL Enterprise Audit plugin, but the configuration principles remain similar for open-source alternatives.

2. Install the Audit Log Plugin

To enable audit logging, you must first load the plugin into your MySQL instance.

Run the following SQL command in your MySQL terminal to install the plugin dynamically:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

(Note: Use audit_log.dll instead of audit_log.so if your MySQL instance is running on Windows.)

To verify that the plugin was successfully installed and is active, run:

SHOW PLUGINS;

Look for audit_log in the output list; its status should be ACTIVE.

3. Configure the Audit Log in the MySQL Configuration File

To ensure the audit log plugin remains active after a database restart, you must add the configuration settings to your MySQL option file (my.cnf on Linux or my.ini on Windows) under the [mysqld] section.

Open the configuration file and add the following lines:

[mysqld]
# Load the audit log plugin on startup
plugin-load-add=audit_log.so

# Set the audit logging policy (ALL, NONE, LOGINS, QUERIES)
audit_log_policy=ALL

# Define the log file format (JSON is recommended for modern log parsers)
audit_log_format=JSON

# Specify the path and name of the audit log file
audit_log_file=/var/lib/mysql/audit.log

# Rotate log files when they reach a certain size (in bytes)
audit_log_rotate_on_size=52428800
audit_log_rotations=10

After saving the configuration file, restart your MySQL service to apply the changes:

sudo systemctl restart mysql

4. Key Configuration Variables Explained

You can fine-tune what the plugin records by adjusting the following variables:

• audit_log_policy: Controls which events are logged.
  • ALL: Logs all connections, disconnections, and queries.
  • NONE: Disables logging.
  • LOGINS: Logs only login and connection attempts.
  • QUERIES: Logs executed SQL statements.
• audit_log_format: Defines the output format. Options are XML (default in older versions) or JSON (easier to read and integrate with log analyzers like ELK or Splunk).
• audit_log_exclude_accounts / audit_log_include_accounts: Allows you to exclude or include specific users from logging to reduce log noise (e.g., ignoring internal backup users).

To change these variables dynamically at runtime without restarting the server, use the SET GLOBAL command:

SET GLOBAL audit_log_policy = 'LOGINS';

5. View and Analyze the Audit Logs

The audit log file is saved to the directory specified in your configuration (usually the MySQL data directory).

If you configured the format as JSON, you can view the log entries using standard Linux commands:

tail -f /var/lib/mysql/audit.log

A typical entry will record the timestamp, connection ID, host, user, and the exact query executed:

{
  "audit_record": {
    "timestamp": "2023-10-24T12:00:01Z",
    "record_id": "1_2023-10-24T12:00:00",
    "class": "connection",
    "event": "login",
    "connection_id": "14",
    "user": "db_admin",
    "host": "localhost",
    "ip": "127.0.0.1"
  }
}