Posted inMySQL

Professional Database Monitoring with MySQL Audit Log Plugin

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Why the General Log Isn’t Enough

Have you ever woken up to find a critical data table has suddenly “disappeared”? Even worse, when checking the General Log, you find yourself lost among millions of trivial query lines. The Slow Query Log doesn’t help either because it only records slow-running statements, not “dangerous” ones.

With strict standards like PCI DSS, HIPAA, or GDPR, accurately tracking Who made a change, When, and from which IP address is mandatory. The MySQL Audit Log Plugin was created to fill this gap. It doesn’t just record logs; it provides intelligent filtering capabilities, helping you control every change in the system.

Based on my experience managing a 50GB system: Enabling the Audit Log for all 2,000 queries per second causes CPU usage to spike by 30%. However, when I configured it to filter only DROP, DELETE, or GRANT commands, performance remained almost unchanged. This gives me peace of mind when granting the Dev team access to the staging environment.

Which Audit Plugin Should You Choose for the Community Edition?

If you aren’t using the expensive Enterprise edition, you still have three excellent community choices:

  • Percona Audit Log Plugin: The top choice today. It supports JSON format smoothly and is perfectly compatible with MySQL 8.0.
  • MariaDB Audit Plugin: Can run on MySQL but sometimes encounters script errors during major version upgrades.
  • McAfee MySQL Audit Plugin: Once very famous, but currently slow to update with new features.

In this article, I will guide you using the Percona Audit Log Plugin due to its superior stability in real-world environments.

Quick Installation Steps

First, you need to identify where the system stores its plugins. Log into MySQL and type:

SHOW VARIABLES LIKE 'plugin_dir';

After copying the audit_log.so file into that directory, activate the plugin with the following command:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

To ensure everything is ready, check the status again:

SHOW PLUGINS;
-- Check configuration parameters
SHOW VARIABLES LIKE 'audit_log%';

If the audit_log row shows a status of ACTIVE, congratulations—you’re halfway there.

Optimal Configuration: Avoiding Disk Overflow

Never use the default configuration if you don’t want your disk to fill up in a few hours. Here is how I optimize for production servers.

1. Prioritize JSON Format

Forget the outdated XML format. JSON allows tools like ELK Stack or Python scripts to process logs up to 40% faster.

SET GLOBAL audit_log_format = 'JSON';

2. Set Log Recording Policy

You have four options: ALL (Record everything), NONE (Disabled), LOGINS (Only logins), and QUERIES (Only queries). Pro tip: Choose ALL but use the exclude_accounts feature to filter out system users that generate junk logs.

3. Persistent Configuration in my.cnf

To ensure the configuration persists after a MySQL restart, add the following lines to your my.cnf file:

[mysqld]
audit_log_policy=ALL
audit_log_format=JSON
audit_log_file=/var/log/mysql/audit.log
audit_log_rotate_on_size=100M
audit_log_rotations=10

Splitting log files at 100MB allows you to open and inspect them quickly, rather than waiting for your text editor to hang while opening a multi-gigabyte file.

Results: Tracking the Evidence

Try executing a sensitive command and monitor the log file immediately:

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

You will receive a detailed record like this:

{
  "audit_record": {
    "name": "Query",
    "timestamp": "2026-05-19T10:00:00Z",
    "command_class": "drop_table",
    "user": "dev_user[dev_user] @ localhost [192.168.1.15]",
    "sqltext": "DROP TABLE customer_data"
  }
}

Looking at the log, it’s clear that dev_user from IP 192.168.1.15 executed the table deletion command. Everything is transparent.

Tips for Better Sleep

Don’t store logs indefinitely on the database server. After gaining access, hackers often delete logs to cover their tracks. I always use logrotate combined with a small script to push logs to S3 once per hour. This ensures your audit data remains safe even if the server fails.

If you are only interested in tables containing salaries or customer info, use Audit Filtering to focus on those targets. This approach saves resources and speeds up your searches significantly.

MySQL Audit Log isn’t a silver bullet, but it is a sturdy shield for your data. Good luck with your implementation!

Share:
Tags: