Percona Audit Log Plugin Instant

Check errors:

-- Verify SHOW VARIABLES LIKE 'audit_log%';

2025-03-15T10:23:45 UTC,app_user[app_user] @ localhost [],localhost,INSERT,INSERT INTO orders...,ecommerce,0 OLD (XML – deprecated but still works) <AUDIT_RECORD> <TIMESTAMP>2025-03-15T10:23:45 UTC</TIMESTAMP> <USER>app_user[app_user] @ localhost []</USER> <COMMAND>INSERT</COMMAND> <SQLTEXT>INSERT INTO orders...</SQLTEXT> </AUDIT_RECORD> Set format: percona audit log plugin

| 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

-- 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 = ''; Check errors: -- Verify SHOW VARIABLES LIKE 'audit_log%';

SHOW VARIABLES LIKE 'audit_log%'; SHOW ERRORS; Goal : Log all INSERT , UPDATE , DELETE from app_user on database prod , except on table temp_cache .

[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 . SET GLOBAL audit_log_strategy = 'ASYNCHRONOUS'

-- Enable plugin INSTALL PLUGIN audit_log SONAME 'audit_log.so'; -- Configure SET GLOBAL audit_log_format = 'NEW'; SET GLOBAL audit_log_strategy = 'ASYNCHRONOUS'; SET GLOBAL audit_log_rotate_on_size = 52428800; -- 50 MB