Implementing Percona Audit Plugin for Percona XtraDB Cluster with ProxySQL

Introduction

Database auditing is crucial for compliance, security monitoring, and forensic analysis in enterprise environments. The Percona Audit Plugin provides comprehensive database activity logging for Percona XtraDB Cluster, enabling organizations to track user activities, data access patterns, and potential security threats.

This comprehensive runbook guides you through implementing the Percona Audit Plugin in a Percona XtraDB Cluster environment with ProxySQL load balancing. By the end of this guide, you’ll have a fully functional audit system that captures database activities across your entire cluster.

Why Use Percona Audit Plugin?

• Compliance Requirements: Meet regulatory standards like SOX, HIPAA, PCI-DSS
• Security Monitoring: Track unauthorized access attempts and suspicious activities
• Performance Analysis: Identify slow queries and optimize database performance
• Forensic Analysis: Investigate security incidents and data breaches
• Change Tracking: Monitor schema modifications and data changes

Prerequisites

Before implementing the Percona Audit Plugin, ensure you have:

System Requirements

• Operating System: CentOS 7+, Ubuntu 18.04+, or RHEL 7+
• Memory: Minimum 4GB RAM per node (8GB+ recommended)
• Storage: Adequate space for audit logs (plan for 10-20% of database size)
• Network: Stable network connectivity between cluster nodes

Software Requirements

• Percona XtraDB Cluster 8.0.28+ (minimum 3 nodes)
• ProxySQL 2.6.0+ (2.7.x recommended for latest features)
• Percona Server 8.0.28+ (includes Audit Plugin)
• Root or sudo access on all cluster nodes

Version Compatibility Matrix

Network Configuration

# Cluster nodes
Node 1: 192.168.1.101
Node 2: 192.168.1.102
Node 3: 192.168.1.103

# ProxySQL server
ProxySQL: 192.168.1.100

Understanding the Architecture

Percona XtraDB Cluster Overview

Percona XtraDB Cluster is a high-availability solution that combines:

• Percona Server for MySQL: Enhanced MySQL server
• Galera: Synchronous multi-master replication
• ProxySQL: Intelligent proxy for connection routing

Audit Plugin Architecture

The Percona Audit Plugin operates at the MySQL server level, capturing:

• Connection Events: Login/logout activities
• Query Events: All SQL statements executed
• Table Events: Data manipulation operations
• Global Events: Server startup/shutdown

Integration Points

Application Layer
       ↓
   ProxySQL
       ↓
Percona XtraDB Cluster
  ↓      ↓      ↓
Node1  Node2  Node3
  ↓      ↓      ↓
Audit  Audit  Audit
Logs   Logs   Logs

Installation and Setup

Step 1: Install Percona XtraDB Cluster

First, install Percona XtraDB Cluster on all nodes:

# Add Percona repository
sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# Enable Percona XtraDB Cluster repository
sudo percona-release enable-only pxc-80
sudo percona-release enable tools

# Install Percona XtraDB Cluster
sudo yum install -y percona-xtradb-cluster

Step 2: Verify Audit Plugin Availability

The Percona Audit Plugin is bundled with Percona Server for MySQL and doesn’t require separate installation:

# Verify the audit plugin is available
# Plugin location may vary by distribution
find /usr/lib64/mysql/plugin/ -name "audit_log.so" 2>/dev/null || \
find /usr/lib/mysql/plugin/ -name "audit_log.so" 2>/dev/null

# Alternative verification after MySQL is running
mysql -u root -p -e "SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%';"

# Add ProxySQL repository (using current version)
cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql_repo]
name=ProxySQL Repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/centos/8/
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

# Install ProxySQL
sudo yum install -y proxysql2

[mysqld]
# Basic configuration
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# Galera configuration
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.1.103
wsrep_node_name=pxc-node1
wsrep_node_address=192.168.1.101
wsrep_sst_method=xtrabackup-v2

# InnoDB configuration
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=0
innodb_file_per_table=1

# Binary logging
log-bin=mysql-bin
binlog_format=ROW

# Audit plugin configuration (using loose_ prefix to prevent startup failures)
plugin-load-add=audit_log.so
loose_audit_log_file=/var/log/mysql/audit.log
loose_audit_log_format=JSON
loose_audit_log_rotate_on_size=100M
loose_audit_log_rotations=10
loose_audit_log_policy=ALL

# Create audit log directory with proper permissions
sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 755 /var/log/mysql

# Bootstrap the cluster
sudo systemctl start mysql@bootstrap.service

# Secure the installation
sudo mysql_secure_installation

# Create cluster user
mysql -u root -p << EOF
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;
EOF

# Node 2 (/etc/my.cnf)
server-id=2
wsrep_node_name=pxc-node2
wsrep_node_address=192.168.1.102

# Node 3 (/etc/my.cnf)
server-id=3
wsrep_node_name=pxc-node3
wsrep_node_address=192.168.1.103

# On nodes 2 and 3
sudo systemctl start mysql
sudo systemctl enable mysql

-- Connect to MySQL
mysql -u root -p

-- Install the audit plugin
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- Verify installation
SHOW PLUGINS;

-- Configure audit settings
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'JSON';
SET GLOBAL audit_log_file = '/var/log/mysql/audit.log';

datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="admin:admin;radmin:radmin"
    mysql_ifaces="0.0.0.0:6032"
    refresh_interval=2000
}

mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="8.0.25"
    connect_timeout_server=3000
    monitor_username="monitor"
    monitor_password="monitor"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
}

-- Connect to ProxySQL admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Add cluster nodes
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES
(0, '192.168.1.101', 3306, 900),
(0, '192.168.1.102', 3306, 900),
(0, '192.168.1.103', 3306, 900);

-- Create monitor user on cluster
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE ON *.* TO 'monitor'@'%';

-- Load configuration
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Add application users
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES
('app_user', 'app_password', 0),
('read_user', 'read_password', 1);

-- Load users
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- On each node
SHOW STATUS LIKE 'wsrep%';

-- Key metrics to check
wsrep_cluster_size: 3
wsrep_cluster_status: Primary
wsrep_connected: ON
wsrep_local_state_comment: Synced

-- Create test database and table
CREATE DATABASE audit_test;
USE audit_test;
CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(50));

-- Insert test data
INSERT INTO test_table VALUES (1, 'Test Record');

-- Query data
SELECT * FROM test_table;

-- Update data
UPDATE test_table SET name = 'Updated Record' WHERE id = 1;

-- Delete data
DELETE FROM test_table WHERE id = 1;

# View recent audit entries
tail -f /var/log/mysql/audit.log

# Example audit log entry (JSON format)
{
  "audit_record": {
    "name": "Query",
    "record": "1_2023-07-15T10:30:45",
    "timestamp": "2023-07-15T10:30:45 UTC",
    "command_class": "select",
    "connection_id": "8",
    "status": 0,
    "sqltext": "SELECT * FROM test_table",
    "user": "app_user[app_user] @ localhost []",
    "host": "localhost",
    "os_user": "",
    "ip": "127.0.0.1",
    "db": "audit_test"
  }
}

# Test connection through ProxySQL
mysql -u app_user -papp_password -h 192.168.1.100 -P 6033

# Verify connection routing
SELECT @@hostname, @@server_id;

-- Configure automatic rotation
SET GLOBAL audit_log_rotate_on_size = 104857600; -- 100MB
SET GLOBAL audit_log_rotations = 10;

-- Manual rotation
SET GLOBAL audit_log_flush = ON;

#!/bin/bash

# Audit log monitoring script
AUDIT_DIR="/var/log/mysql"
THRESHOLD=80

# Check disk usage
USAGE=$(df -h $AUDIT_DIR | awk 'NR==2 {print $5}' | sed 's/%//')

if [ $USAGE -gt $THRESHOLD ]; then
    echo "WARNING: Audit log directory usage is ${USAGE}%"
    # Send alert or perform cleanup
    find $AUDIT_DIR -name "audit.log.*" -mtime +30 -delete
fi

# Check audit log file size
CURRENT_SIZE=$(stat -c%s "$AUDIT_DIR/audit.log" 2>/dev/null || echo 0)
MAX_SIZE=104857600  # 100MB

if [ $CURRENT_SIZE -gt $MAX_SIZE ]; then
    echo "INFO: Audit log rotation needed"
    mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "SET GLOBAL audit_log_flush = ON;"
fi

-- Check audit plugin status
SHOW GLOBAL STATUS LIKE 'audit_log%';

-- Monitor general performance
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

# Error: Plugin 'audit_log' cannot be loaded
# Solution 1: Check plugin file permissions and location
sudo find /usr/lib*/mysql/plugin/ -name "audit_log.so" -exec ls -la {} \;
sudo chown mysql:mysql /usr/lib*/mysql/plugin/audit_log.so
sudo chmod 755 /usr/lib*/mysql/plugin/audit_log.so

# Solution 2: Verify plugin is available in MySQL
mysql -u root -p -e "SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%';"

# Solution 3: Check if using loose_ prefix in configuration
grep -E "^(loose_)?audit_log" /etc/my.cnf

-- Error: Cannot rotate audit log
-- Solution: Check directory permissions
-- As root:
sudo chown -R mysql:mysql /var/log/mysql/
sudo chmod 755 /var/log/mysql/

-- Set proper log rotation
SET GLOBAL audit_log_rotate_on_size = 104857600;

# Compress old audit logs
find /var/log/mysql -name "audit.log.*" -mtime +7 -exec gzip {} \;

# Archive old logs
find /var/log/mysql -name "audit.log.*.gz" -mtime +30 -exec mv {} /backup/audit_archive/ \;

-- Reduce audit scope for performance
SET GLOBAL audit_log_policy = 'QUERIES';

-- Exclude specific databases
SET GLOBAL audit_log_exclude_databases = 'mysql,information_schema,performance_schema';

-- Exclude monitoring users to reduce log volume
SET GLOBAL audit_log_exclude_accounts = 'monitor@%,mysql.session@localhost';

-- Use connection-based auditing for less overhead
SET GLOBAL audit_log_policy = 'LOGINS';

# Comprehensive SELinux configuration for audit logs
sudo setsebool -P mysql_connect_any 1
sudo setsebool -P mysqld_can_networkconnect 1

# Set proper context for audit log directory
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -R /var/log/mysql

# Allow MySQL to write to audit log location
sudo setsebool -P allow_mysqld_log_write 1

# Verify SELinux status
sudo getsebool -a | grep mysql
sudo ls -Z /var/log/mysql/

# Check MySQL error log
sudo tail -f /var/log/mysqld.log

# Verify plugin status
mysql -u root -p -e "SHOW PLUGINS;" | grep audit

# Check audit log permissions
ls -la /var/log/mysql/audit.log*

# Monitor real-time audit activity
tail -f /var/log/mysql/audit.log | jq '.'

# Create dedicated audit user
sudo useradd -r -s /bin/false audit_reader

# Set file permissions
sudo chown mysql:audit_reader /var/log/mysql/audit.log*
sudo chmod 640 /var/log/mysql/audit.log*

# SELinux configuration (if enabled)
sudo setsebool -P mysql_connect_any 1
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql/audit\.log.*"
sudo restorecon -R /var/log/mysql/

-- Enable SSL for cluster communications
-- Generate SSL certificates first:
-- sudo mysql_ssl_rsa_setup --uid=mysql

-- Add to my.cnf on all nodes:
[mysqld]
# SSL Configuration
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
require_secure_transport=ON

# Galera SSL Configuration
wsrep_provider_options="socket.ssl_key=/var/lib/mysql/server-key.pem;socket.ssl_cert=/var/lib/mysql/server-cert.pem;socket.ssl_ca=/var/lib/mysql/ca.pem;socket.ssl_cipher=AES128-SHA"

# For ProxySQL SSL connections
[client]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem

# Generate checksums for audit logs
find /var/log/mysql -name "audit.log*" -exec sha256sum {} \; > /var/log/mysql/audit_checksums.txt

# Verify integrity periodically
sha256sum -c /var/log/mysql/audit_checksums.txt

-- Monitor audit-related metrics
SHOW GLOBAL STATUS LIKE 'audit_log%';

-- Check overall performance
SHOW GLOBAL STATUS LIKE 'Com_%';
SHOW GLOBAL STATUS LIKE 'Handler_%';
SHOW GLOBAL STATUS LIKE 'Innodb_%';