How do you implement audit logging in Percona Server for MySQL?

Photo by Ranae Smith on Unsplash

How do you implement audit logging in Percona Server for MySQL?

·

1 min read


Steps to Implement Audit Logging

1. Install the Audit Log Plugin

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

2. Configure the Plugin

Add the following configuration to your my.cnf file:

[mysqld]
audit_log_file = /var/log/mysql/audit.log
audit_log_format = JSON
audit_log_strategy = PERFORMANCE
audit_log_policy = QUERIES

3. Customize Logging Settings

  • Set audit_log_strategy to control log flushing (ASYNCHRONOUS, PERFORMANCE, SEMISYNCHRONOUS, SYNCHRONOUS)

  • Adjust audit_log_buffer_size to specify the memory buffer size for logging

  • Use audit_log_rotate_size to enable automatic log file rotation based on size

  • Set audit_log_rotations to limit the number of log files to keep

4. Configure Account Filtering

Include or exclude specific accounts using:

SET GLOBAL audit_log_include_accounts = 'user1@host,root@localhost';
SET GLOBAL audit_log_exclude_accounts = 'user2@%';

5. Database Exclusion

To exclude specific databases, add to my.cnf:

audit_log_exclude_databases=database_name

6. Monitor and Analyze Logs

  • Review the audit log file (default location: ${data_dir}/audit.log)

  • Use tools to parse and analyze JSON-formatted logs

7. Final Steps

  • Restart the MySQL server to apply configuration changes

  • Regularly review and rotate audit logs to manage disk space

  • Monitor system performance to ensure logging isn't impacting operations

Remember to regularly review and rotate audit logs to manage disk space and maintain optimal database performance.