Write Audit Log Filter definitions¶

Percona Server’s audit‑log-filter plugin lets you control which events are recorded by supplying a JSON filter definition.

A filter is stored in the mysql.audit_log_filter table and then attached to one or more MySQL accounts through the mysql.audit_log_user table.

The following is a step‑by‑step guide that shows how to create a filter, attach it to a user, and verify that the desired events are captured or suppressed.

What you can filter¶

The table shows the available event classes and their subclasses:

Class name	Event subclass	Details
`connection`	`connect`	Tracks when a connection is initiated (successful or not)
`connection`	`change_user`	Tracks when a user changes during a session
`connection`	`disconnect`	Tracks when a connection is terminated
`general`	`status`	Tracks the status of general server operations (for example, query success or failure)
`general`	`command`	Logs SQL commands issued to the server
`table_access`	`read`	Logs read statements, like `SELECT` or `INSERT INTO … SELECT`
`table_access`	`delete`	Logs delete statements, like `DELETE` or `TRUNCATE TABLE`
`table_access`	`insert`	Logs insert statements, like `INSERT` or `REPLACE`
`table_access`	`update`	Logs update statements, like `UPDATE`

Percona Server for MySQL 8.0.40 — Audit Log Filter Master Reference¶

Operations (Functions, Commands, and User Actions)¶

Operation family	Example / Exact Command	Notes
Enable / disable	`SET GLOBAL audit_log_filter_disable = ON;` / `OFF;`	Controls whether filtering is disabled. Plugin can also be disabled with `UNINSTALL PLUGIN`.
Select filter mode	JSON filter definitions use `"log": true/false`	No global `ALLOW/DENY` keyword; you control via JSON rule definitions.
Define a rule/filter	`CALL audit_log_filter_set_filter('filter_name', '{ "rules": [...] }');`	Creates or replaces a filter with JSON rule specification.
Remove a rule/filter	`CALL audit_log_filter_remove_filter('filter_name');`	Drops the named filter.
Bind filter to a user	`CALL audit_log_filter_set_user('user@host','filter_name');`	Assigns filter to a specific account.
Unbind user	`CALL audit_log_filter_remove_user('user@host');`	Removes filter association from the user.
Inspect filters	`SELECT * FROM mysql.audit_log_filter;`	Shows defined filters.
Inspect user bindings	`SELECT * FROM mysql.audit_log_user;`	Shows which users have filters applied.
Flush filter definitions	`SELECT audit_log_filter_flush();`	Reloads filter definitions after direct table changes.
Control log rotation	`SET GLOBAL audit_log_filter_rotate_on_size = N;`	Rotate logs after size N (bytes).
	`SELECT audit_log_rotate();`	Force immediate log rotation.
Control pruning	`SET GLOBAL audit_log_filter_prune_seconds = N;`	Keep logs only N seconds, prune older logs.
	`CALL audit_log_filter_set_prune('{"prune_size":..., "prune_seconds":...}');`	Advanced prune control.
Reopen log file	`FLUSH LOGS;`	Closes and reopens the audit log file.
Read audit logs	`SELECT audit_log_read();`	Reads current log in text/JSON/XML formats.
	`SELECT audit_log_read_bookmark(...);`	Read from bookmark position.
Encryption operations	`SELECT audit_log_encryption_password_get();`	Retrieve encryption password (if enabled).
	`CALL audit_log_encryption_password_set('secret');`	Set new encryption password.
Query plugin info	`SHOW PLUGINS LIKE 'audit_log_filter';`	Verify plugin load status.
	`SELECT @@audit_log_filter_version;`	Show plugin version.
Privileges required	`AUDIT_ADMIN` privilege	Required for most filter/rule management operations.

Global Variables (Configuration / Tuning)¶

Variable Name	Purpose / Description	Typical Values / Notes
audit_log_filter_disable	Enable/disable the filter plugin globally	ON / OFF
audit_log_filter_rotate_on_size	Maximum log file size (bytes) before rotation	Integer, e.g., 1073741824 for 1GB
audit_log_filter_prune_seconds	Automatically prune audit logs older than N seconds	Integer, e.g., 604800 for 7 days
audit_log_filter_format	Output format of audit logs	JSON, OLD_JSON, XML
audit_log_filter_version	Plugin version	Read-only, e.g., 8.0.40
audit_log_filter_max_size	Maximum size for audit logs before triggering rotation	Integer in bytes
audit_log_filter_rotate_interval	Time interval for automatic log rotation (seconds)	Integer, e.g., 86400 for daily rotation
audit_log_filter_sync	Whether audit logging is synchronous	ON / OFF
audit_log_filter_buffer_size	Size of internal buffer for audit logging	Integer in bytes
audit_log_filter_flush_time	Interval (seconds) to flush buffered audit log events	Integer
audit_log_filter_max_backup_files	Maximum number of rotated log files to retain	Integer
audit_log_filter_json_include_users	Optionally limit JSON logging to specific users	Comma-separated list of user@host

Notes

Operations that modify filters or assign users require the AUDIT_ADMIN privilege.
JSON-based filter rules are flexible: you can define per-class, per-subclass, per-user rules.
Log rotation and pruning can be automatic (via global variables) or manual (via functions like audit_log_rotate()).
Encryption operations are optional and only needed if audit_log_encrypt is enabled.
Combining operations + variables allows full control: define rules, assign users, rotate logs, prune old data, and inspect activity.

Filtering approaches¶

Inclusive filtering - Log only specific events you want to track
Exclusive filtering - Log everything except events you want to exclude
Mixed filtering - Combine inclusion and exclusion rules

Basic structure¶

A filter consists of a top‑level object named filter.

Inside this object you can set a global log flag and optionally define an array called class.

Each element of the class array identifies a class name (for example, connection, general, query, table_access).

Each class element may contain an event object that specifies particular events and whether they should be logged.

You can also specify which user accounts the filter applies to using the user field within each class element. This allows you to include or exclude specific accounts from audit logging.

{
  "filter": {
    "log": true,
    "class": [
      {
        "name": "connection",
        "event": {
          "name": "connect",
          "log": true
        }
      },
      {
        "name": "query",
        "event": {
          "name": "execute",
          "log": true
        }
      }
    ]
  }
}

If the log flag is omitted, the default value is true. When log is set to false, the filter disables logging for all statement‑type events unless a class entry overrides that setting.

Filter result values¶

Understanding the values used in filter definitions:

Log values¶

"log": true - Enable logging for this event or class
"log": false - Disable logging for this event or class
When omitted - Defaults to true (logging enabled)

Status values¶

"status": [0] - Log only successful operations
"status": [1] - Log only failed operations
"status": [0, 1] - Log both successful and failed operations

Negate values¶

"negate": true - Exclude the specified users/events from logging
"negate": false or omitted - Include the specified users/events in logging

Operation values¶

"operation": ["connect", "disconnect"] - Log specific connection events
"operation": ["insert", "update", "delete"] - Log specific table access operations
"operation": ["select"] - Log only SELECT queries

Practical example¶

Suppose you want a user named excludeUserTest to generate no audit records for statements, but the server should keep a generic record that a connection was established.

First create a filter that disables logging globally:

mysql> INSERT INTO mysql.audit_log_filter (filter_id, name, filter) VALUES
(1, 'log_none',
 '{"filter": {"log": false}}');

Ensure filter_id is unique; attempting to reuse an existing ID raises a duplicate-key error. For more information on managing filter identifiers, see Add filter identifier.

Next map the filter to the user:

mysql> INSERT INTO mysql.audit_log_user (username, userhost, filtername) VALUES
('excludeUserTest', '%', 'log_none');

After the insertion, reload the audit log filter plugin so the new configuration becomes active. For detailed implementation steps, see Implement the filter:

mysql> CALL mysql.audit_log_reload();

Verify the plugin is enabled and the filter is active:

mysql> SHOW PLUGINS WHERE Name = 'audit_log';
mysql> SHOW GLOBAL VARIABLES LIKE 'audit_log_filter';

When excludeUserTest connects and runs queries, the audit log contains only the generic connection record that the plugin writes automatically.

Limitation – connection‑related events logged¶

Even when a filter contains only {"filter": {"log": false}}, two kinds of audit records continue to appear:

Event	Class	When generated
`pre_authenticate`	`connection`	Before the server identifies the user. The default mapping (`% → log_all`) applies.
`general / log` (status 0)	`general`	At session start. The filter does not disable the `general` class, so a record is written.

To prevent these records, create a filter that explicitly disables the connection and general classes and assign it to the user. For more information on filter implementation, see Implement the filter:

mysql> INSERT INTO mysql.audit_log_filter (filter_id, name, filter) VALUES
(2, 'log_none_strict',
 '{
    "filter": {
      "log": false,
      "class": [
        {"name": "connection", "event": {"name": "pre_authenticate", "log": false}},
        {"name": "general",     "event": {"name": "log",            "log": false}}
      ]
    }
  }');


mysql> UPDATE mysql.audit_log_user
   SET filtername = 'log_none_strict'
 WHERE username = 'excludeUserTest' AND userhost = '%';

mysql> CALL mysql.audit_log_reload();

Verify the reload was successful:

mysql> SHOW GLOBAL VARIABLES LIKE 'audit_log_filter';

After the reload, connections made by excludeUserTest will no longer generate the pre_authenticate or general / log entries.

Log all events¶

To log all events, create a filter with the global log flag set to true:

{
  "filter": {
    "log": true
  }
}

This configuration captures all database activities, providing comprehensive audit coverage. For more targeted filtering options, see Inclusive filters and Exclusive filters.

Log specific event classes¶

To focus on specific types of events, define the classes you want to monitor:

{
  "filter": {
    "class": [
      {
        "name": "connection",
        "log": true
      },
      {
        "name": "table_access",
        "log": true
      }
    ]
  }
}

This filter logs only connection events and table access operations, excluding other activities like general server events. For more advanced filtering techniques, see Inclusive filters and Exclusive filters.

Log multiple classes or events¶

For more granular control, specify multiple classes and their specific events:

{
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": {
          "name": "connect",
          "log": true
        }
      },
      {
        "name": "table_access",
        "event": {
          "name": "insert",
          "log": true
        }
      },
      {
        "name": "table_access",
        "event": {
          "name": "update",
          "log": true
        }
      }
    ]
  }
}

This configuration logs connection events and specific table operations (insert and update), giving you detailed visibility into database modifications. For more comprehensive filtering strategies, see Inclusive filters and Exclusive filters.

Inclusive filters¶

Inclusive filters capture specific database events you want to log. They allow you to precisely target and record only the actions you care about.

Basic Structure¶

An inclusive filter uses a JSON configuration that defines which events to include in your audit logging. The filter specifies:

What type of events to capture
Which users to track
What specific actions to log

Common use cases for inclusive filters include security audits, compliance tracking, performance monitoring, and user behavior analysis.

Event tracking can be more precise, which helps reduce unnecessary log noise. By focusing on the specific events that matter, you can enhance security monitoring and ensure that only the most relevant data is logged. This approach not only improves the clarity of your logs but also helps optimize performance by limiting the number of events being recorded, reducing overhead and making it easier to manage the system.

It’s important to consider the performance impact of logging and how it might affect your server. Before deploying your filters in a production environment, test them thoroughly to ensure everything works as expected. For detailed guidance on testing and deployment, see Best practices.

Inclusive filter example¶

This filter is useful for monitoring and auditing changes to the database performed by administrative users, particularly to ensure that updates and deletions are tracked.

{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "user": ["admin"],
        "operation": ["update", "delete"]
      }
    ]
  }
}

This filter does one thing: log all update and delete operations performed by the user admin. The filter uses the following components:

“class”: The top-level key specifies that the filter applies to the table_access class. This class monitors events related to database table interactions.
“name”: “table_access”: This defines the event class you want to track. This class captures interactions with database tables such as read, insert, update, and delete operations. Specifies the specific class of events
user: [“admin”]: This specifies that the filter applies only to events performed by the admin user. It restricts the filter to only log actions executed by this user.
operation: [“update”, “delete”]: This narrows down the filter to track only specific operations. In this case, it captures update and delete operations. Any SELECT (read) or INSERT operations on tables will not be logged, as they are excluded by this filter.

Account inclusion options¶

You can specify which user accounts to include in your audit logging using the user field:

{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "user": ["admin", "finance_team", "auditor"],
        "operation": ["update", "delete"]
      }
    ]
  }
}

User field options:

Specific usernames: ["admin", "user1", "user2"] - Include only these specific accounts
Wildcard patterns: ["admin%", "finance_%"] - Include accounts matching these patterns
Host specifications: ["admin@localhost", "user@%"] - Include accounts with specific host restrictions
Multiple patterns: Combine different patterns in the same array

Examples:

["admin"] - Include only the admin user
["admin", "finance_team"] - Include admin and finance_team users
["admin@localhost", "user@%"] - Include admin from localhost and user from any host
["%_admin", "finance_%"] - Include any user ending with “admin” or starting with “finance“

Inclusive filters give you granular control over your MySQL audit logging, allowing you to capture exactly the information you need without overwhelming your logging system. For implementation details, see Implement the filter.

Exclusive filters¶

Exclusive filters in the audit_log_filter for Percona Server for MySQL let you exclude certain activities from being logged, helping you reduce log size and focus on what matters most. For example, you can filter out routine operations like health checks or background processes to avoid unnecessary clutter in your logs.

This example defines a filter that excludes (negate: true) all table access events (“table_access”) by the user “readonly_user”. Events for other users or other classes of activity are still be logged unless additional filters are defined.

{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "user": ["readonly_user"],
        "negate": true
      }
    ]
  }
}

Exclusive filter example¶

{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "user": ["readonly_user", "monitoring_user"],
        "operation": ["read"],
        "negate": true
      },
      {
        "name": "connection",
        "user": ["backup_user", "replication_user"],
        "negate": true
      }
    ]
  }
}

This filter excludes read operations by readonly and monitoring users, and excludes all connection events from backup and replication users. All other events are logged normally. For implementation guidance, see Implement the filter.

Account exclusion options¶

You can exclude specific user accounts from audit logging using the user field with the negate option:

{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "user": ["readonly_user", "monitoring_user", "backup_user"],
        "negate": true
      }
    ]
  }
}

Exclusion patterns:

Exclude specific users: ["user1", "user2"] with "negate": true
Exclude by pattern: ["%_readonly", "monitoring_%"] with "negate": true
Exclude by host: ["user@localhost", "service@%"] with "negate": true
Mixed inclusion/exclusion: Use multiple class elements for complex filtering

Common exclusion scenarios:

["readonly_user"] - Exclude read-only accounts from logging
["%_monitor", "health_check"] - Exclude monitoring and health check accounts
["backup_user", "replication_user"] - Exclude system maintenance accounts
["user@localhost"] - Exclude local connections for a specific user

Advanced exclusion example:

{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "user": ["readonly_%", "monitoring_%"],
        "negate": true
      },
      {
        "name": "connection",
        "user": ["backup_user", "replication_user"],
        "negate": true
      }
    ]
  }
}

This configuration excludes all users starting with “readonly_” or “monitoring_” from table access logging, and excludes backup and replication users from connection logging.

Best practices¶

Start broad, then refine¶

Begin with broad, inclusive filters that capture a wide range of events. This approach gives you a comprehensive view of your database activity and helps you understand what’s happening.

Examples of broad filters:

Log all actions from administrative users
Log all operations on critical databases
Log all connection events

As you analyze the captured data, refine your filters to focus on specific events, users, or operations that matter most to your organization.

Note

While starting broad helps you understand your system’s activity patterns, be mindful of performance impact. Start with broad but lightweight logging (fewer detailed events), then gradually increase detail as needed rather than implementing overly aggressive logging from the beginning.

Test thoroughly before production¶

Testing is crucial before deploying filters in production.

Testing checklist: * Set up a non-production environment that mirrors your production setup

Verify that your filters capture the intended events
Ensure you’re not missing critical information
Test how different filter combinations interact
Check for unexpected gaps in your audit coverage

Manage log files effectively¶

Audit logs can grow rapidly, especially with detailed filtering configurations.

Log management considerations: * Monitor log file sizes regularly

Implement appropriate rotation policies
Consider storage capacity and retention requirements
Calculate expected log growth based on typical database activity
Adjust rotation policies accordingly

Monitor performance impact¶

More granular filters typically require more system resources to process and store audit data.

Performance monitoring:

Watch for changes in query response times
Monitor CPU usage and I/O operations
Test different filter configurations
Adjust filters if you notice performance degradation

Remember: Start with broad but lightweight logging, then gradually increase detail and granularity as you understand your system’s patterns and performance requirements.

Implement the filter¶

Here’s how to define and implement an audit log filter:

Add filter identifier¶

An audit log filter identifier is your filter’s unique name within the audit_log_filter system. You create this name to label and track your specific filter setup. The audit_log_filter_id system variable stores this name, and you should choose descriptive identifiers like ‘finance_audit’ or ‘security_tracking’.

After you name your filter with an identifier, you attach your rules. The identifier makes it easy to manage multiple filter setups and update them as needed. When you want to change your logging rules, you first reference your chosen identifier and then add your new filter settings.

Remember that when you apply new filter settings to an existing identifier, the system replaces the old settings. It doesn’t add the new rules to what’s already there.

mysql> SET GLOBAL audit_log_filter_id = 'financial_tracking';

Add filter definition¶

mysql> SET GLOBAL audit_log_filter = '{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "user": ["admin", "finance_team"],
        "database": ["financial_db"],
        "table": ["accounts", "transactions"],
        "operation": ["insert", "update", "delete"],
        "status": [0, 1]
      },
      {
        "name": "connection",
        "user": ["admin", "finance_team"],
        "operation": ["connect", "disconnect"],
        "status": [0, 1]
      }
    ]
  }
}';

This filter tracks two main activities:

Table changes: Logs all insert, update, and delete operations on your accounts and transactions tables
User connections: Records when admin and finance team members connect or disconnect

The filter tracks both successful and failed operations. This comprehensive logging provides valuable information about attempted changes that failed, which helps with troubleshooting and security monitoring.

Here’s what gets logged:

Every insert, update, and delete operation on your financial tables
All connection attempts from your admin and finance teams, including when they log in and out
Whether each operation succeeded (status 0) or failed (status 1)

The filter focuses only on activity in your financial_db database. This targeted approach makes it easier to find the information you need when you need it.

Tracking all these elements gives you a comprehensive view of who’s modifying your financial data, what changes they’re making, and whether those changes are successful. This ability is beneficial for security monitoring and compliance requirements.

To verify your filter:

mysql> SHOW GLOBAL VARIABLES LIKE 'audit_log_filter';

To check if events are being logged, you can examine your audit log file (default location is the data directory). For more verification steps, see Verify the filter.

View existing filters and mappings¶

You can list the filters that are currently defined.

mysql> SELECT filter_id, name, filter FROM mysql.audit_log_filter;

You can also list the user‑to‑filter mappings.

mysql> SELECT username, userhost, filtername FROM mysql.audit_log_user;

These queries help you verify that the intended filter is attached to the correct accounts. For additional verification steps, see Verify the filter.

Verify the filter¶

Rotate or remove the current audit log filter file so you can see fresh output.
Connect to the server as excludeUserTest and run a simple query, for example, SELECT 1;.
Examine the audit log filter file (default location /var/lib/mysql/audit.log) with tail -f /var/lib/mysql/audit.log.
Confirm that only the events you intended to keep appear.

If unexpected events are still present, double‑check the JSON syntax, ensure the filter ID is unique, and verify that the reload statement succeeded without errors. For detailed troubleshooting guidance, see Common errors and solutions.

Common errors and solutions¶

JSON syntax errors¶

Error: ERROR 3130 (22032): Invalid JSON text in argument 1 to function audit_log_filter_set_filter

Solution: Validate your JSON syntax before inserting. Common issues include: - Missing commas between array elements - Unmatched quotes or braces - Trailing commas

Use a JSON validator or test with a simple filter first:

mysql> SELECT JSON_VALID('{"filter": {"log": false}}');

Duplicate key errors¶

Error: ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Solution: Use a unique filter_id or check existing filters:

mysql> SELECT MAX(filter_id) FROM mysql.audit_log_filter;

Plugin reload failures¶

Error: ERROR 1305 (42000): FUNCTION mysql.audit_log_reload does not exist

Solution: Ensure the audit log plugin is installed and active:

mysql> SHOW PLUGINS WHERE Name = 'audit_log';
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Filter not taking effect¶

Symptoms: Expected events still appear in audit log

Solutions:

Verify the filter is properly assigned to the user:

mysql> SELECT * FROM mysql.audit_log_user WHERE username = 'your_user';

Check for conflicting filters or default mappings:

mysql> SELECT * FROM mysql.audit_log_user WHERE userhost = '%';

Ensure the plugin reload completed successfully:

mysql> CALL mysql.audit_log_reload();
mysql> SHOW GLOBAL VARIABLES LIKE 'audit_log_filter';

Troubleshoot and checklist¶

Define the JSON filter with the desired global log flag.
Add class entries for any event type that must be treated differently from the global setting.
Insert the filter into mysql.audit_log_filter with a unique filter_id.
Map the filter to the appropriate MySQL accounts in mysql.audit_log_user.
Reload the audit plugin to apply the changes.
Test the configuration with a fresh connection and inspect the audit log.

This checklist ensures that the audit log filter records exactly the events you need while omitting unnecessary noise. For additional troubleshooting help, see Common errors and solutions.

Resources¶

Audit Log Filter overview - Learn about the plugin’s capabilities and architecture
Install the Audit Log Filter - Installation instructions and prerequisites
Audit log filter functions, options and variables - Complete reference for configuration options
Filter the Audit Log Filter logs - Advanced filtering using function calls
Audit Log Filter file format overview - Understanding log file formats (JSON, XML)
Audit Log Filter security - Security considerations and best practices
Audit Log Filter restrictions - Known limitations and constraints
Manage the Audit Log Filter files - File management and rotation

Troubleshooting and Support¶

Frequently asked questions - Common questions and answers
Get help from Percona - Community support and expert services
Glossary - Technical terms and definitions
Percona Monitoring and Management - Monitoring and performance analysis