Logging MariaDB User Activity

DBA, MariaDB, MariaDB Performance, MinervaDB

Complete Guide to MariaDB User Activity Logging and Transaction Auditing for Performance & Security

Introduction: Essential MariaDB Logging for Enterprise Operations

Implementing comprehensive MariaDB user activity logging and transaction auditingis crucial for maintaining database security, ensuring regulatory compliance, and enabling effective performance troubleshooting. This detailed guide covers advanced logging configurations that serve dual purposes: MariaDB performance monitoring and database security auditing.

Understanding MariaDB Logging Architecture

Core Logging Components

MariaDB provides multiple logging mechanisms for comprehensive activity tracking:

  • General Query Log: Records all SQL statements and connection events
  • Slow Query Log: Captures performance-impacting queries for optimization
  • Binary Log: Tracks data-modifying transactions for replication and recovery
  • Error Log: Documents server errors and critical events
  • Audit Plugin: Advanced user activity and security event logging

Logging vs. Auditing: Key Differences

  • Performance Logging: Focuses on query execution times, resource usage, and optimization opportunities
  • Security Auditing: Emphasizes user access patterns, privilege escalations, and compliance requirements
  • Transaction Logging: Captures data modifications for both performance analysis and security forensics

Configuring General Query Log for User Activity Tracking

Basic General Log Configuration

-- Enable general query logging
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Configure log output destination
SET GLOBAL log_output = 'FILE,TABLE';

-- Verify configuration
SHOW VARIABLES LIKE 'general_log%';
SHOW VARIABLES LIKE 'log_output';

Advanced General Log Settings

# /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
# General query log configuration
general_log = 1
general_log_file = /var/log/mysql/general.log
log_output = FILE,TABLE

# Log additional connection information
log_warnings = 2
log_queries_not_using_indexes = 1

# Rotate logs automatically
max_binlog_size = 100M
expire_logs_days = 30

Selective Logging for Performance Analysis

-- Create filtered logging for specific users or databases
CREATE TABLE performance_log_filter (
    user_pattern VARCHAR(100),
    database_pattern VARCHAR(100),
    log_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert filtering rules
INSERT INTO performance_log_filter (user_pattern, database_pattern) 
VALUES 
    ('app_user_%', 'production_db', TRUE),
    ('admin_%', '%', TRUE),
    ('readonly_%', '%', FALSE);

Implementing Slow Query Logging for Performance Troubleshooting

Comprehensive Slow Query Configuration

-- Enable slow query logging
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2.0;

-- Log queries not using indexes
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_slow_admin_statements = 'ON';

-- Log all queries for detailed analysis (use carefully)
SET GLOBAL min_examined_row_limit = 1000;

Advanced Slow Query Analysis Setup

# Enhanced slow query logging configuration
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
min_examined_row_limit = 100

# Additional performance logging
log_slow_verbosity = query_plan,innodb
log_slow_rate_limit = 1000
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

Automated Slow Query Analysis

#!/bin/bash
# slow_query_analyzer.sh

SLOW_LOG="/var/log/mysql/slow.log"
ANALYSIS_DIR="/var/log/mysql/analysis"
DATE=$(date +%Y%m%d)

# Create analysis directory
mkdir -p $ANALYSIS_DIR

# Generate slow query summary
mysqldumpslow -s c -t 20 $SLOW_LOG > $ANALYSIS_DIR/slow_summary_$DATE.txt

# Generate detailed analysis
pt-query-digest $SLOW_LOG > $ANALYSIS_DIR/query_digest_$DATE.txt

# Extract top problematic queries
mysqldumpslow -s t -t 10 $SLOW_LOG | \
awk '/^Count:/ {print; getline; print; getline; print; print "---"}' > \
$ANALYSIS_DIR/top_slow_queries_$DATE.txt

echo "Slow query analysis completed: $ANALYSIS_DIR"

Binary Logging for Transaction Auditing

Comprehensive Binary Log Configuration

# Binary logging for transaction tracking
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1
binlog_cache_size = 32K
max_binlog_cache_size = 512M

# Enhanced transaction logging
binlog_annotate_row_events = ON
binlog_checksum = CRC32
binlog_commit_wait_count = 0
binlog_commit_wait_usec = 100000

# Retention and rotation
expire_logs_days = 7
max_binlog_size = 100M

Transaction Monitoring Queries

-- Monitor binary log status
SHOW BINARY LOGS;
SHOW MASTER STATUS;

-- Analyze transaction patterns
SELECT 
    DATE(FROM_UNIXTIME(timestamp)) as log_date,
    COUNT(*) as transaction_count,
    SUM(CASE WHEN event_type = 'Query' THEN 1 ELSE 0 END) as query_events,
    SUM(CASE WHEN event_type = 'Write_rows' THEN 1 ELSE 0 END) as write_events
FROM mysql.general_log 
WHERE command_type IN ('Query', 'Execute')
GROUP BY DATE(FROM_UNIXTIME(timestamp))
ORDER BY log_date DESC;

Advanced Audit Plugin Configuration

Installing MariaDB Audit Plugin

-- Install audit plugin
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

-- Configure audit settings
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';
SET GLOBAL server_audit_file_rotate_size = 100000000;
SET GLOBAL server_audit_file_rotations = 10;

Comprehensive Audit Configuration

# /etc/mysql/mariadb.conf.d/audit.cnf
[mysqld]
plugin_load_add = server_audit.so

# Audit plugin settings
server_audit_logging = ON
server_audit_events = CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML
server_audit_file_path = /var/log/mysql/audit.log
server_audit_file_rotate_size = 100000000
server_audit_file_rotations = 30

# Detailed audit configuration
server_audit_incl_users = admin,dba,app_user
server_audit_excl_users = monitoring,backup
server_audit_query_log_limit = 2048
server_audit_syslog_facility = LOG_USER
server_audit_syslog_ident = mysql-audit

Selective Auditing for Security Compliance

-- Configure user-specific auditing
SET GLOBAL server_audit_incl_users = 'admin,privileged_user,external_app';
SET GLOBAL server_audit_excl_users = 'monitoring,replication';

-- Database-specific auditing
CREATE TABLE audit_rules (
    rule_id INT AUTO_INCREMENT PRIMARY KEY,
    database_name VARCHAR(64),
    table_name VARCHAR(64),
    operation_type ENUM('SELECT','INSERT','UPDATE','DELETE','ALL'),
    user_pattern VARCHAR(100),
    audit_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert audit rules
INSERT INTO audit_rules (database_name, table_name, operation_type, user_pattern) 
VALUES 
    ('financial_db', 'transactions', 'ALL', '%'),
    ('user_data', 'personal_info', 'SELECT', 'admin_%'),
    ('logs', '%', 'DELETE', '%');

Performance Schema Configuration for Advanced Monitoring

Enabling Performance Schema Instrumentation

-- Enable performance schema
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement%' OR NAME LIKE '%transaction%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%' OR NAME LIKE '%events_transactions%';

-- Configure statement history
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME = 'events_statements_history_long';

Advanced Performance Monitoring Views

-- Create comprehensive monitoring view
CREATE VIEW user_activity_summary AS
SELECT 
    USER as database_user,
    HOST as client_host,
    COUNT(*) as total_statements,
    SUM(TIMER_WAIT)/1000000000 as total_time_seconds,
    AVG(TIMER_WAIT)/1000000000 as avg_time_seconds,
    SUM(ROWS_EXAMINED) as total_rows_examined,
    SUM(ROWS_SENT) as total_rows_sent,
    COUNT(DISTINCT SQL_TEXT) as unique_queries
FROM performance_schema.events_statements_history_long
WHERE EVENT_NAME LIKE 'statement/sql/%'
GROUP BY USER, HOST
ORDER BY total_time_seconds DESC;

-- Transaction analysis view
CREATE VIEW transaction_analysis AS
SELECT 
    THREAD_ID,
    EVENT_ID,
    STATE,
    TIMER_WAIT/1000000000 as duration_seconds,
    ACCESS_MODE,
    ISOLATION_LEVEL,
    AUTOCOMMIT,
    NESTING_EVENT_ID
FROM performance_schema.events_transactions_history_long
WHERE TIMER_WAIT > 1000000000  -- Transactions longer than 1 second
ORDER BY TIMER_WAIT DESC;

Log Analysis and Monitoring Automation

Automated Log Analysis Script

#!/usr/bin/env python3
# mariadb_log_analyzer.py

import re
import json
import mysql.connector
from datetime import datetime, timedelta
from collections import defaultdict

class MariaDBLogAnalyzer:
    def __init__(self, config):
        self.config = config
        self.connection = mysql.connector.connect(**config['database'])

    def analyze_slow_queries(self, hours_back=24):
        """Analyze slow queries for performance issues"""
        cursor = self.connection.cursor(dictionary=True)

        query = """
        SELECT 
            sql_text,
            COUNT(*) as execution_count,
            AVG(query_time) as avg_query_time,
            MAX(query_time) as max_query_time,
            SUM(rows_examined) as total_rows_examined,
            AVG(rows_examined) as avg_rows_examined
        FROM mysql.slow_log 
        WHERE start_time >= NOW() - INTERVAL %s HOUR
        GROUP BY sql_text
        ORDER BY avg_query_time DESC
        LIMIT 20
        """

        cursor.execute(query, (hours_back,))
        results = cursor.fetchall()

        return self.format_slow_query_report(results)

    def analyze_user_activity(self, hours_back=24):
        """Analyze user activity patterns"""
        cursor = self.connection.cursor(dictionary=True)

        query = """
        SELECT 
            user_host,
            COUNT(*) as query_count,
            COUNT(DISTINCT command_type) as command_types,
            MIN(event_time) as first_activity,
            MAX(event_time) as last_activity
        FROM mysql.general_log 
        WHERE event_time >= NOW() - INTERVAL %s HOUR
        AND command_type IN ('Query', 'Execute', 'Prepare')
        GROUP BY user_host
        ORDER BY query_count DESC
        """

        cursor.execute(query, (hours_back,))
        results = cursor.fetchall()

        return self.format_user_activity_report(results)

    def detect_security_anomalies(self):
        """Detect potential security issues"""
        cursor = self.connection.cursor(dictionary=True)

        # Check for failed login attempts
        failed_logins_query = """
        SELECT 
            user_host,
            COUNT(*) as failed_attempts,
            MAX(event_time) as last_attempt
        FROM mysql.general_log 
        WHERE event_time >= NOW() - INTERVAL 1 HOUR
        AND command_type = 'Connect'
        AND argument LIKE '%Access denied%'
        GROUP BY user_host
        HAVING failed_attempts > 5
        """

        cursor.execute(failed_logins_query)
        failed_logins = cursor.fetchall()

        # Check for privilege escalation attempts
        privilege_query = """
        SELECT 
            user_host,
            argument,
            event_time
        FROM mysql.general_log 
        WHERE event_time >= NOW() - INTERVAL 1 HOUR
        AND (argument LIKE '%GRANT%' OR argument LIKE '%CREATE USER%')
        ORDER BY event_time DESC
        """

        cursor.execute(privilege_query)
        privilege_changes = cursor.fetchall()

        return {
            'failed_logins': failed_logins,
            'privilege_changes': privilege_changes
        }

    def format_slow_query_report(self, results):
        """Format slow query analysis results"""
        report = {
            'timestamp': datetime.now().isoformat(),
            'total_slow_queries': len(results),
            'queries': []
        }

        for row in results:
            report['queries'].append({
                'sql_text': row['sql_text'][:200] + '...' if len(row['sql_text']) > 200 else row['sql_text'],
                'execution_count': row['execution_count'],
                'avg_query_time': float(row['avg_query_time']),
                'max_query_time': float(row['max_query_time']),
                'avg_rows_examined': float(row['avg_rows_examined'])
            })

        return report

    def format_user_activity_report(self, results):
        """Format user activity analysis results"""
        report = {
            'timestamp': datetime.now().isoformat(),
            'active_users': len(results),
            'users': []
        }

        for row in results:
            report['users'].append({
                'user_host': row['user_host'],
                'query_count': row['query_count'],
                'command_types': row['command_types'],
                'session_duration': str(row['last_activity'] - row['first_activity'])
            })

        return report

if __name__ == "__main__":
    config = {
        'database': {
            'host': 'localhost',
            'user': 'monitoring',
            'password': 'secure_password',
            'database': 'mysql'
        }
    }

    analyzer = MariaDBLogAnalyzer(config)

    # Generate reports
    slow_query_report = analyzer.analyze_slow_queries()
    user_activity_report = analyzer.analyze_user_activity()
    security_report = analyzer.detect_security_anomalies()

    # Save reports
    with open(f'/var/log/mysql/analysis/slow_queries_{datetime.now().strftime("%Y%m%d_%H%M")}.json', 'w') as f:
        json.dump(slow_query_report, f, indent=2, default=str)

    with open(f'/var/log/mysql/analysis/user_activity_{datetime.now().strftime("%Y%m%d_%H%M")}.json', 'w') as f:
        json.dump(user_activity_report, f, indent=2, default=str)

    print("Log analysis completed successfully")

Security-Focused Audit Queries

Compliance Reporting Queries

-- User access audit report
SELECT 
    DATE(event_time) as audit_date,
    user_host,
    COUNT(*) as connection_count,
    COUNT(DISTINCT argument) as unique_operations,
    MIN(event_time) as first_connection,
    MAX(event_time) as last_connection
FROM mysql.general_log 
WHERE command_type = 'Connect'
AND event_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(event_time), user_host
ORDER BY audit_date DESC, connection_count DESC;

-- Privilege changes audit
SELECT 
    event_time,
    user_host,
    argument as privilege_change,
    CASE 
        WHEN argument LIKE '%GRANT%' THEN 'PRIVILEGE_GRANTED'
        WHEN argument LIKE '%REVOKE%' THEN 'PRIVILEGE_REVOKED'
        WHEN argument LIKE '%CREATE USER%' THEN 'USER_CREATED'
        WHEN argument LIKE '%DROP USER%' THEN 'USER_DROPPED'
        ELSE 'OTHER'
    END as change_type
FROM mysql.general_log 
WHERE (argument LIKE '%GRANT%' OR argument LIKE '%REVOKE%' 
       OR argument LIKE '%CREATE USER%' OR argument LIKE '%DROP USER%')
AND event_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY event_time DESC;

-- Data access patterns
SELECT 
    DATE(event_time) as access_date,
    user_host,
    COUNT(CASE WHEN argument LIKE 'SELECT%' THEN 1 END) as select_count,
    COUNT(CASE WHEN argument LIKE 'INSERT%' THEN 1 END) as insert_count,
    COUNT(CASE WHEN argument LIKE 'UPDATE%' THEN 1 END) as update_count,
    COUNT(CASE WHEN argument LIKE 'DELETE%' THEN 1 END) as delete_count
FROM mysql.general_log 
WHERE command_type = 'Query'
AND event_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(event_time), user_host
HAVING (select_count + insert_count + update_count + delete_count) > 0
ORDER BY access_date DESC, (select_count + insert_count + update_count + delete_count) DESC;

Log Rotation and Maintenance

Automated Log Rotation Configuration

#!/bin/bash
# /etc/logrotate.d/mariadb-custom

/var/log/mysql/general.log {
    daily
    missingok
    rotate 30
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysql -e 'FLUSH LOGS'
    endscript
}

/var/log/mysql/slow.log {
    daily
    missingok
    rotate 30
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysql -e 'FLUSH LOGS'
    endscript
}

/var/log/mysql/audit.log {
    daily
    missingok
    rotate 90
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysql -e 'SET GLOBAL server_audit_file_rotate_now = ON'
    endscript
}

Log Cleanup and Archival Script

#!/bin/bash
# mariadb_log_maintenance.sh

LOG_DIR="/var/log/mysql"
ARCHIVE_DIR="/backup/mysql_logs"
RETENTION_DAYS=90

# Create archive directory
mkdir -p $ARCHIVE_DIR

# Archive old logs
find $LOG_DIR -name "*.log.*" -mtime +7 -exec mv {} $ARCHIVE_DIR/ \;

# Compress archived logs
find $ARCHIVE_DIR -name "*.log.*" -not -name "*.gz" -exec gzip {} \;

# Remove very old archives
find $ARCHIVE_DIR -name "*.gz" -mtime +$RETENTION_DAYS -delete

# Clean up general_log table
mysql -e "
DELETE FROM mysql.general_log 
WHERE event_time < DATE_SUB(NOW(), INTERVAL 7 DAY);
OPTIMIZE TABLE mysql.general_log;
"

# Clean up slow_log table
mysql -e "
DELETE FROM mysql.slow_log 
WHERE start_time < DATE_SUB(NOW(), INTERVAL 7 DAY);
OPTIMIZE TABLE mysql.slow_log;
"

echo "Log maintenance completed: $(date)"

Performance Impact Optimization

Minimizing Logging Overhead

-- Optimize logging for performance
SET GLOBAL log_output = 'FILE';  -- Avoid table logging for high-volume systems
SET GLOBAL general_log_file = '/fast_storage/mysql/general.log';
SET GLOBAL slow_query_log_file = '/fast_storage/mysql/slow.log';

-- Use selective logging
SET GLOBAL log_slow_rate_limit = 100;  -- Log every 100th slow query
SET GLOBAL log_queries_not_using_indexes = 'OFF';  -- Disable if too verbose

-- Configure binary log for optimal performance
SET GLOBAL sync_binlog = 0;  -- For performance (reduce for durability)
SET GLOBAL binlog_cache_size = 64K;
SET GLOBAL binlog_stmt_cache_size = 32K;

Monitoring Logging Performance Impact

-- Monitor logging overhead
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_STATUS 
WHERE VARIABLE_NAME IN (
    'Binlog_cache_disk_use',
    'Binlog_cache_use',
    'Binlog_stmt_cache_disk_use',
    'Binlog_stmt_cache_use'
);

-- Check log file sizes and growth
SELECT 
    'general_log' as log_type,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb
FROM information_schema.tables 
WHERE table_schema = 'mysql' AND table_name = 'general_log'
UNION ALL
SELECT 
    'slow_log' as log_type,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb
FROM information_schema.tables 
WHERE table_schema = 'mysql' AND table_name = 'slow_log';

Conclusion: Comprehensive MariaDB Logging Strategy

Implementing robust MariaDB user activity logging and transaction auditing provides essential capabilities for both performance troubleshooting and security compliance. This comprehensive logging strategy delivers:

Key Benefits:

  • Enhanced Security Posture: Complete audit trails for compliance and forensic analysis
  • Performance Optimization: Detailed query analysis for continuous improvement
  • Operational Visibility: Real-time insights into database usage patterns
  • Regulatory Compliance: Comprehensive logging for audit requirements

Best Practices Summary:

  1. Selective Logging: Balance detail with performance impact
  2. Automated Analysis: Regular log analysis and reporting
  3. Proper Retention: Appropriate log retention policies
  4. Security Integration: Integration with SIEM and security monitoring tools

By implementing these logging configurations and monitoring practices, organizations can maintain secure, high-performance MariaDB environments while meeting compliance requirements and enabling effective troubleshooting capabilities.

Need expert assistance with MariaDB logging implementation? Contact our database specialists for customized configuration and ongoing support services.

 

How are Global Indexes implemented in PostgreSQL?

 

PostgreSQL Log File Mastery: Optimizing Database Performance Through Advanced Log Analysis

 

Troubleshooting Writes in Galera Cluster

About MinervaDB Corporation 85 Articles
Full-stack Database Infrastructure Architecture, Engineering and Operations Consultative Support(24*7) Provider for PostgreSQL, MySQL, MariaDB, MongoDB, ClickHouse, Trino, SQL Server, Cassandra, CockroachDB, Yugabyte, Couchbase, Redis, Valkey, NoSQL, NewSQL, Databricks, Amazon Resdhift, Amazon Aurora, CloudSQL, Snowflake and AzureSQL with core expertize in Performance, Scalability, High Availability, Database Reliability Engineering, Database Upgrades/Migration, and Data Security.
Website Facebook Twitter YouTube LinkedIn

Related Articles

Be the first to comment

Leave a Reply