Percona Audit Log Plugin Official

| Variable | Description | Example | |----------|-------------|---------| | audit_log_format | OLD (XML), NEW (JSON), CSV | NEW | | audit_log_file | Log file path | /var/log/mysql/audit.log | | audit_log_rotate_on_size | Auto-rotate size in bytes | 104857600 (100MB) | | audit_log_rotations | Number of rotated files to keep | 9 | | audit_log_strategy | ASYNCHRONOUS , PERFORMANCE , SEMISYNCHRONOUS , SYNCHRONOUS | ASYNCHRONOUS | [mysqld] audit_log_format = JSON audit_log_file = /var/log/mysql/audit.log audit_log_rotate_on_size = 104857600 audit_log_rotations = 9 audit_log_strategy = ASYNCHRONOUS audit_log_exclude_accounts = 'root@localhost' 💡 ASYNCHRONOUS gives the best performance. SYNCHRONOUS guarantees logging but slows down queries. 4. Filtering (Most Important Feature) Without filters, audit logs grow enormous. Use audit_log_include_accounts / audit_log_exclude_accounts and audit_log_include_commands / audit_log_exclude_commands . Filter by user account -- Log only 'app_user' and 'replication' SET GLOBAL audit_log_include_accounts = 'app_user@%,replication@%'; -- Exclude 'monitor' and 'backup' users SET GLOBAL audit_log_exclude_accounts = 'monitor@%,backup@%'; Filter by SQL command type -- Log only SELECT, INSERT, UPDATE, DELETE SET GLOBAL audit_log_include_commands = 'SELECT,INSERT,UPDATE,DELETE'; -- Exclude SHOW and SET commands SET GLOBAL audit_log_exclude_commands = 'SHOW,SET'; Filter by database -- Log only activity on 'payments' or 'users' DB SET GLOBAL audit_log_include_databases = 'payments,users'; -- Exclude 'test' and 'tmp' DB SET GLOBAL audit_log_exclude_databases = 'test,tmp'; 🔁 Filters are additive – if you specify both include_commands and exclude_accounts , both are applied. 5. Log Formats JSON (recommended) "audit_record": "timestamp": "2025-03-15T10:23:45 UTC", "user": "app_user[app_user] @ localhost []", "host": "localhost", "command": "INSERT", "sqltext": "INSERT INTO orders VALUES (123, 'pending')", "database": "ecommerce", "status": 0

SET GLOBAL audit_log_exclude_accounts = 'root@localhost,admin@%'; | Problem | Likely cause | Solution | |---------|--------------|----------| | No logs written | Plugin not loaded | SHOW PLUGINS; – if missing, reinstall | | Log file huge | No filters applied | Set audit_log_exclude_commands = 'SHOW,SELECT' etc. | | Performance drop | SYNCHRONOUS strategy | Change to ASYNCHRONOUS | | Rotation not working | audit_log_rotate_on_size = 0 | Set positive value | | Missing JSON format | Using OLD format | Set audit_log_format = 'NEW' | percona audit log plugin

-- Filtering SET GLOBAL audit_log_include_accounts = 'app_user@%'; SET GLOBAL audit_log_include_commands = 'INSERT,UPDATE,DELETE'; SET GLOBAL audit_log_include_databases = 'prod'; SET GLOBAL audit_log_exclude_commands = ''; "user": "app_user[app_user] @ localhost []"

[mysqld] plugin-load-add = audit_log.so audit_log = FORCE_PLUS_PERMANENT SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; 3. Basic Configuration All audit log settings are dynamic (can be changed without restarting) using SET GLOBAL . "sqltext": "INSERT INTO orders VALUES (123

-- Verify SHOW VARIABLES LIKE 'audit_log%';