Dashboard GitHub

MySQL/MariaDB

Monitor MySQL and MariaDB database logs including query logs, error logs, slow query analysis, and replication monitoring using the File Stream plugin

MySQL/MariaDB Integration

MySQL MariaDB

Monitor and analyze MySQL and MariaDB database logs in real-time using LogFlux Agent’s File Stream plugin. This configuration-based approach provides comprehensive database monitoring, query performance analysis, and replication tracking for MySQL-compatible databases.

Overview

The MySQL/MariaDB integration leverages LogFlux Agent’s File Stream plugin to:

  • Real-time monitoring of general query logs, error logs, and slow query logs
  • Query performance analysis with execution time tracking and optimization insights
  • Replication monitoring for master-slave configurations and binary log analysis
  • Security auditing with connection attempts and privilege monitoring
  • InnoDB monitoring with transaction logs and deadlock detection
  • Binary log analysis for data change tracking and point-in-time recovery

Installation

The File Stream plugin is included with LogFlux Agent. Enable it for MySQL/MariaDB log monitoring:

1
2
3
4
5

# Enable File Stream plugin
sudo systemctl enable --now logflux-filestream

# Verify plugin status
sudo systemctl status logflux-filestream

MySQL/MariaDB Configuration

Configure MySQL/MariaDB logging in /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf:

Basic Logging Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

[mysqld]
# Error log
log-error = /var/log/mysql/error.log

# General query log
general_log = 1
general_log_file = /var/log/mysql/general.log

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

# Binary log (replication)
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

# InnoDB logging
innodb_print_all_deadlocks = 1
innodb_status_output = 1
innodb_status_output_locks = 1

# Connection logging
log_warnings = 2

MariaDB-Specific Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

[mysqld]
# MariaDB audit plugin
plugin-load-add = server_audit
server_audit_logging = ON
server_audit_events = 'CONNECT,QUERY,TABLE'
server_audit_output_type = file
server_audit_file_path = /var/log/mysql/audit.log
server_audit_file_rotate_size = 100000000

# MariaDB error log format
log_error_verbosity = 3
log_error_services = 'log_filter_internal; log_sink_system'

# Performance Schema (MySQL 5.6+/MariaDB 10.0+)
performance_schema = ON
performance_schema_consumer_events_statements_current = ON
performance_schema_consumer_events_statements_history = ON

Basic Configuration

Configure the File Stream plugin to monitor MySQL/MariaDB logs by creating /etc/logflux-agent/plugins/filestream-mysql.toml:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

[filestream.mysql_error]
paths = ["/var/log/mysql/error.log"]
format = "mysql_error"
tags = ["mysql", "database", "error"]
fields = {
  service = "mysql",
  log_type = "error"
}

[filestream.mysql_general]
paths = ["/var/log/mysql/general.log"]
format = "mysql_general"
tags = ["mysql", "database", "query"]
fields = {
  service = "mysql",
  log_type = "general"
}

[filestream.mysql_slow]
paths = ["/var/log/mysql/slow.log"]
format = "mysql_slow"
tags = ["mysql", "database", "slow", "performance"]
fields = {
  service = "mysql",
  log_type = "slow_query"
}

[filestream.mysql_binlog]
paths = ["/var/log/mysql/mysql-bin.*"]
format = "mysql_binlog"
tags = ["mysql", "database", "replication"]
fields = {
  service = "mysql",
  log_type = "binary_log"
}

MySQL/MariaDB Log Formats

Error Log Format

1
2
3
4
5
6
7

[filestream.mysql_error_detailed]
paths = ["/var/log/mysql/error.log"]
format = "regex"
regex = '^(?P<timestamp>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}Z) (?P<thread_id>\d+) \[(?P<level>\w+)\] (?P<message>.*)$'
parse_timestamp = true
timestamp_field = "timestamp"
timestamp_format = "2006-01-02T15:04:05.000000Z"

Alternative error log format:

1
2
3
4
5
6
7

[filestream.mysql_error_legacy]
paths = ["/var/log/mysql/error.log"]
format = "regex"
regex = '^(?P<timestamp>\d{6} \d{1,2}:\d{2}:\d{2}) \[(?P<level>\w+)\] (?P<message>.*)$'
parse_timestamp = true
timestamp_field = "timestamp"
timestamp_format = "060102 15:04:05"

General Query Log Format

1
2
3
4
5
6
7

[filestream.mysql_general_detailed]
paths = ["/var/log/mysql/general.log"]
format = "regex"
regex = '^(?P<timestamp>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}Z)\s+(?P<thread_id>\d+) (?P<command>\w+)\s+(?P<argument>.*)$'
parse_timestamp = true
timestamp_field = "timestamp"
timestamp_format = "2006-01-02T15:04:05.000000Z"

Slow Query Log Format

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

[filestream.mysql_slow_detailed]
paths = ["/var/log/mysql/slow.log"]
format = "regex"
regex = '^# Time: (?P<timestamp>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}Z)$'
multiline = true
multiline_pattern = '^# Time:'
multiline_negate = false
multiline_match = after
parse_timestamp = true
timestamp_field = "timestamp"
timestamp_format = "2006-01-02T15:04:05.000000Z"

Binary Log Analysis (using mysqlbinlog)

1
2
3
4
5
6
7
8

[filestream.mysql_binlog_parsed]
paths = ["/var/log/mysql/binlog-parsed.log"]
format = "text"
tags = ["mysql", "binlog", "parsed"]
fields = {
  service = "mysql",
  log_type = "binlog_parsed"
}

Create binary log parsing script:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

#!/bin/bash
# Parse binary logs continuously
MYSQL_BIN_DIR="/var/log/mysql"
OUTPUT_LOG="/var/log/mysql/binlog-parsed.log"

mysqlbinlog --read-from-remote-server \
  --host=localhost \
  --user=replication_user \
  --password=replication_pass \
  --raw \
  --result-file="$OUTPUT_LOG" \
  mysql-bin.000001

Advanced Configuration

Performance Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

[filestream.mysql_performance]
paths = ["/var/log/mysql/slow.log"]
format = "mysql_slow"
tags = ["mysql", "performance"]
fields = {
  service = "mysql",
  log_type = "performance"
}

# Extract performance metrics from slow log
[filestream.mysql_performance.processors.extract]
source_field = "message"
patterns = [
  'Query_time: (?P<query_time>\d+\.\d+)',
  'Lock_time: (?P<lock_time>\d+\.\d+)',
  'Rows_sent: (?P<rows_sent>\d+)',
  'Rows_examined: (?P<rows_examined>\d+)'
]

[filestream.mysql_performance.processors.add_fields]
fields = {
  efficiency_ratio = "{{ if gt .rows_examined 0.0 }}{{ div .rows_sent .rows_examined }}{{ else }}0{{ end }}",
  slow_query = "{{ if gt .query_time 5.0 }}true{{ else }}false{{ end }}"
}

Replication Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

[filestream.mysql_replication]
paths = ["/var/log/mysql/mysql-bin.*", "/var/log/mysql/relay-bin.*"]
format = "text"
tags = ["mysql", "replication"]
fields = {
  service = "mysql",
  log_type = "replication"
}

# Monitor replication status via script
[filestream.mysql_repl_status]
paths = ["/var/log/mysql/replication-status.log"]
format = "json"
tags = ["mysql", "replication", "status"]

Replication monitoring script:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

#!/bin/bash
# Monitor MySQL replication status
while true; do
    mysql -e "SHOW SLAVE STATUS\G" | grep -E "(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error)" | \
    awk -F': ' '{
        if (NR == 1) printf "{"
        printf "\"%s\": \"%s\"", $1, $2
        if (NR < 4) printf ", "
        if (NR == 4) printf ", \"timestamp\": \"%s\"}\n", strftime("%Y-%m-%dT%H:%M:%S.000Z")
    }' >> /var/log/mysql/replication-status.log
    sleep 30
done

Security and Audit Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

[filestream.mysql_audit]
paths = ["/var/log/mysql/audit.log"]
format = "csv"
csv_headers = ["timestamp", "serverhost", "username", "host", "connectionid", "queryid", "operation", "database", "object", "retcode"]
tags = ["mysql", "audit", "security"]
fields = {
  service = "mysql",
  log_type = "audit"
}

# Filter for security events
[filestream.mysql_audit.processors.grep]
patterns = [
  "operation:CONNECT",
  "operation:FAILED_CONNECT",
  "operation:DISCONNECT",
  "retcode:[1-9]"
]

Deadlock and Lock Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

[filestream.mysql_deadlocks]
paths = ["/var/log/mysql/error.log"]
format = "mysql_error"
tags = ["mysql", "deadlocks", "locks"]
fields = {
  service = "mysql",
  log_type = "deadlocks"
}

# Filter for deadlock events
[filestream.mysql_deadlocks.processors.grep]
patterns = [
  "LATEST DETECTED DEADLOCK",
  "deadlock",
  "lock wait timeout",
  "Lock wait timeout exceeded"
]

Usage Examples

Monitor MySQL Database

1
2
3
4
5
6
7
8

# Stream all MySQL logs
logflux-cli stream --filter 'service:mysql'

# Monitor specific log type
logflux-cli stream --filter 'service:mysql AND log_type:slow_query'

# Track error logs only
logflux-cli stream --filter 'service:mysql AND log_type:error'

Performance Analysis

1
2
3
4
5
6
7
8

# Monitor slow queries (>5 seconds)
logflux-cli stream --filter 'service:mysql AND query_time:>5'

# Track queries with low efficiency
logflux-cli stream --filter 'service:mysql AND efficiency_ratio:<0.1'

# Monitor lock waits
logflux-cli stream --filter 'service:mysql AND lock_time:>1'

Security Monitoring

1
2
3
4
5
6
7
8

# Track failed connections
logflux-cli stream --filter 'service:mysql AND operation:FAILED_CONNECT'

# Monitor privilege escalation
logflux-cli stream --filter 'service:mysql AND (message:Access OR message:denied)'

# Track administrative operations
logflux-cli stream --filter 'service:mysql AND (command:DROP OR command:CREATE OR command:ALTER)'

Replication Monitoring

1
2
3
4
5
6
7
8

# Monitor replication status
logflux-cli stream --filter 'service:mysql AND log_type:replication'

# Track replication lag
logflux-cli stream --filter 'service:mysql AND Seconds_Behind_Master:>30'

# Monitor binary log operations
logflux-cli stream --filter 'service:mysql AND log_type:binary_log'

Database-Specific Monitoring

Performance Schema Integration

Monitor Performance Schema tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Enable Performance Schema consumers
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

-- Monitor long-running queries
SELECT 
    THREAD_ID,
    EVENT_ID,
    SQL_TEXT,
    TIMER_WAIT/1000000000000 as DURATION_SECONDS,
    LOCK_TIME/1000000000000 as LOCK_SECONDS,
    ROWS_SENT,
    ROWS_EXAMINED
FROM performance_schema.events_statements_history_long
WHERE TIMER_WAIT/1000000000000 > 5
ORDER BY TIMER_WAIT DESC;

InnoDB Status Monitoring

1
2
3
4
5
6
7
8

#!/bin/bash
# InnoDB status monitoring
while true; do
    mysql -e "SHOW ENGINE INNODB STATUS\G" | \
    grep -E "(deadlocks|lock waits|pending|fsyncs)" | \
    logger -t mysql-innodb
    sleep 60
done

Table Statistics Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Monitor table usage statistics
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    (DATA_LENGTH + INDEX_LENGTH) as TOTAL_SIZE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY TOTAL_SIZE DESC;

Monitoring and Alerting

Key Metrics to Monitor

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# Slow query alert
[alerts.mysql_slow_queries]
query = "service:mysql AND query_time:>10"
threshold = 5
window = "2m"
message = "MySQL slow queries detected: {{ .database }}"

# Replication lag alert
[alerts.mysql_replication_lag]
query = "service:mysql AND Seconds_Behind_Master:>60"
threshold = 1
window = "1m"
message = "MySQL replication lag: {{ .Seconds_Behind_Master }} seconds"

# Connection failure alert
[alerts.mysql_connection_failures]
query = "service:mysql AND operation:FAILED_CONNECT"
threshold = 10
window = "1m"
message = "High MySQL connection failure rate"

# Deadlock alert
[alerts.mysql_deadlocks]
query = "service:mysql AND message:deadlock"
threshold = 1
window = "30s"
message = "MySQL deadlock detected"

# Error rate alert
[alerts.mysql_error_rate]
query = "service:mysql AND log_type:error AND level:ERROR"
threshold = 5
window = "1m"
message = "High MySQL error rate"

Dashboard Metrics

Monitor these key MySQL/MariaDB metrics:

  • Query performance (execution time, slow queries count)
  • Connection metrics (active connections, connection rate, failed attempts)
  • Replication status (lag, I/O thread status, SQL thread status)
  • Lock monitoring (deadlocks, lock waits, timeouts)
  • Table statistics (table sizes, row counts, index usage)
  • InnoDB metrics (buffer pool usage, log file usage)
  • Binary log (log file size, rotation frequency)
  • Error rates (by error type and severity)

Troubleshooting

Common Issues

MySQL logs not appearing:

1
2
3
4
5
6
7
8

# Check MySQL is running
sudo systemctl status mysql

# Verify log configuration
mysql -e "SHOW VARIABLES LIKE '%log%';"

# Check log file permissions
sudo ls -la /var/log/mysql/

Log parsing errors:

1
2
3
4
5
6
7

# Check log formats
mysql -e "SHOW VARIABLES LIKE 'log_timestamps';"
mysql -e "SHOW VARIABLES LIKE 'log_error_verbosity';"

# Test log entries
tail -n 10 /var/log/mysql/error.log
tail -n 5 /var/log/mysql/slow.log

Replication monitoring issues:

1
2
3
4
5
6
7
8
9

# Check replication status
mysql -e "SHOW SLAVE STATUS\G"
mysql -e "SHOW MASTER STATUS\G"

# Verify binary log configuration
mysql -e "SHOW VARIABLES LIKE 'log_bin%';"

# Check relay logs
ls -la /var/log/mysql/relay-bin.*

Performance Schema problems:

1
2
3
4
5
6

# Check Performance Schema status
mysql -e "SHOW VARIABLES LIKE 'performance_schema';"
mysql -e "SELECT * FROM performance_schema.setup_consumers WHERE ENABLED='NO';"

# Enable missing consumers
mysql -e "UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE '%events_statements%';"

Best Practices

Performance

  • Enable slow query log with appropriate threshold
  • Use Performance Schema for detailed query analysis
  • Monitor binary log size and implement proper rotation
  • Regular OPTIMIZE TABLE operations for MyISAM tables

Security

  • Enable audit logging for security compliance
  • Monitor failed connection attempts and implement rate limiting
  • Log DDL operations for change tracking
  • Use SSL connections for remote access

High Availability

  • Monitor replication lag continuously
  • Implement proper binary log backup procedures
  • Monitor master-slave synchronization status
  • Set up proper alerting for replication failures

Log Management

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# Optimize log rotation for MySQL
/var/log/mysql/*.log {
    daily
    rotate 30
    missingok
    notifempty
    compress
    delaycompress
    postrotate
        systemctl reload mysql.service > /dev/null 2>&1 || true
        systemctl reload logflux-filestream.service > /dev/null 2>&1 || true
    endpostrotate
}

Query Optimization

Enable additional logging for query optimization:

1
2
3
4
5
6
7
8

-- Enable general log temporarily for debugging
SET GLOBAL general_log = 'ON';
-- Run problematic queries
-- Analyze logs
SET GLOBAL general_log = 'OFF';

-- Use pt-query-digest for slow log analysis
-- pt-query-digest /var/log/mysql/slow.log

Integration Examples

Docker Deployment

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

version: '3.8'
services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: myapp
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypassword
    volumes:
      - mysql_data:/var/lib/mysql
      - mysql_logs:/var/log/mysql
      - ./my.cnf:/etc/mysql/conf.d/my.cnf
    command: --log-bin=mysql-bin --server-id=1
    
  logflux-agent:
    image: logflux/agent:latest
    volumes:
      - mysql_logs:/var/log/mysql:ro
      - ./logflux-config:/etc/logflux-agent/plugins
    depends_on:
      - mysql

volumes:
  mysql_data:
  mysql_logs:

Kubernetes Deployment

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: mysql
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: root-password
        - name: MYSQL_DATABASE
          value: "myapp"
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
        - name: mysql-logs
          mountPath: /var/log/mysql
        - name: mysql-config
          mountPath: /etc/mysql/conf.d/my.cnf
          subPath: my.cnf
      volumes:
      - name: mysql-config
        configMap:
          name: mysql-config
      - name: mysql-logs
        emptyDir: {}
  volumeClaimTemplates:
  - metadata:
      name: mysql-data
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 100Gi

Master-Slave Replication Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

#!/bin/bash
# Complete replication health check
check_replication() {
    local lag=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
    local io_running=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running" | awk '{print $2}')
    local sql_running=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running" | awk '{print $2}')
    
    echo "{\"timestamp\": \"$(date -u +%Y-%m-%dT%H:%M:%S.000Z)\", \"lag\": $lag, \"io_running\": \"$io_running\", \"sql_running\": \"$sql_running\"}" >> /var/log/mysql/replication-health.log
}

while true; do
    check_replication
    sleep 30
done

This comprehensive MySQL/MariaDB integration provides real-time database monitoring, query performance analysis, and replication tracking using LogFlux Agent’s File Stream plugin. The configuration-based approach offers detailed insights into database operations, performance characteristics, and security events across different MySQL-compatible database deployments.

Disclaimer

The MySQL logo and trademarks are the property of Oracle Corporation and/or its affiliates. The MariaDB logo and trademarks are the property of MariaDB Corporation Ab. LogFlux is not affiliated with, endorsed by, or sponsored by Oracle Corporation or MariaDB Corporation Ab. The MySQL and MariaDB logos are used solely for identification purposes to indicate compatibility and integration capabilities.