Database audit logs record who did what and when — essential for compliance (SOC2, PCI-DSS, HIPAA) and forensic investigation. Here is how to implement audit logging in MySQL.
MySQL Enterprise Audit Plugin
sql
-- MySQL Enterprise Edition
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
-- Configure in my.cnf
-- audit_log_policy = ALL
-- audit_log_format = JSON
-- audit_log_file = /var/log/mysql/audit.logOpen Source Alternative: MariaDB Audit Plugin
ini
[mysqld]
plugin_load_add = server_audit
server_audit_logging = ON
server_audit_events = CONNECT,QUERY
server_audit_file_path = /var/log/mysql/audit.log
server_audit_file_rotate_size = 100000000
server_audit_file_rotations = 5
server_audit_excl_users = 'replication_user,orchestrator'General Query Log for Development Auditing
sql
-- Enable general log (high overhead — dev/audit only)
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- Or log to table
SET GLOBAL log_output = 'TABLE';
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 20;Application-Level Audit with Triggers
sql
CREATE TABLE audit_log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(64),
operation ENUM('INSERT','UPDATE','DELETE'),
old_data JSON,
new_data JSON,
changed_by VARCHAR(64) DEFAULT user(),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER orders_audit AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES ('orders', 'UPDATE',
JSON_OBJECT('status', OLD.status, 'amount', OLD.amount),
JSON_OBJECT('status', NEW.status, 'amount', NEW.amount));Shipping Audit Logs to SIEM
bash
# Forward JSON audit logs to CloudWatch Logs
# In CloudWatch agent config:
# {
# "file_path": "/var/log/mysql/audit.log",
# "log_group_name": "/aws/rds/mysql/audit",
# "log_stream_name": "{instance_id}"
# }Key Takeaways
- Use the MariaDB Audit Plugin (free) or MySQL Enterprise Audit for connection and query logging
- Exclude replication and monitoring users from audit logs to reduce noise
- JSON format makes audit logs easy to ship to SIEM tools like Splunk or CloudWatch
- Triggers provide fine-grained application-level auditing for specific tables
JusDB Can Help
Audit logging is a compliance requirement for SOC2, PCI-DSS, and HIPAA. JusDB can implement a complete database audit strategy for your environment.