MySQL Plugin

Monitor MySQL and MariaDB database servers with comprehensive metrics tracking queries, connections, replication, InnoDB storage engine, and more.

Overview

The MySQL plugin collects detailed metrics from MySQL/MariaDB servers including:

• Connection Statistics - Active connections, connection attempts, aborted connections
• Query Performance - Queries per second, slow queries, query cache performance
• Table Locks - Immediate and waited table locks
• InnoDB Metrics - Buffer pool, row operations, log writes, deadlocks
• Replication - Master/slave status, replication lag, relay log position
• Threads - Running threads, connected threads, cached threads
• Temporary Objects - Temporary tables, temporary files on disk

Requirements

MySQL/MariaDB Version

• MySQL: 5.7 or later
• MariaDB: 10.3 or later
• Tested with: MySQL 5.7, 8.0, 8.1; MariaDB 10.3, 10.5, 10.6, 10.11

Python Dependencies

pip install pymysql>=1.0.0

Auto-installed when using PLUGINS=mysql during agent installation.

Network Access

The agent must be able to connect to MySQL:

# Test connectivity
mysql -h localhost -u monitor -p -e "SELECT 1"

Configuration

Basic Configuration

plugins:
  mysql:
    enabled: true
    host: localhost
    port: 3306
    user: monitor
    password: 'monitoring-password'

With Database Selection

plugins:
  mysql:
    enabled: true
    host: localhost
    port: 3306
    user: monitor
    password: 'monitoring-password'
    database: 'information_schema'  # Optional test database

Remote MySQL Server

plugins:
  mysql:
    enabled: true
    host: mysql-server.example.com
    port: 3306
    user: monitor
    password: 'secure-password'

With SSL/TLS

plugins:
  mysql:
    enabled: true
    host: mysql.example.com
    port: 3306
    user: monitor
    password: 'secure-password'
    ssl: true
    ssl_ca: '/path/to/ca-cert.pem'       # Optional
    ssl_cert: '/path/to/client-cert.pem' # Optional
    ssl_key: '/path/to/client-key.pem'   # Optional

All Configuration Options

plugins:
  mysql:
    enabled: true              # Enable/disable plugin
    host: localhost            # MySQL hostname or IP
    port: 3306                 # MySQL port (default: 3306)
    user: monitor              # MySQL username
    password: ''               # MySQL password
    database: ''               # Database to connect to (optional)
    charset: utf8mb4           # Connection charset (default: utf8mb4)
    connect_timeout: 10        # Connection timeout in seconds
    ssl: false                 # Use SSL/TLS (default: false)
    ssl_ca: ''                 # Path to CA certificate file
    ssl_cert: ''               # Path to client certificate file
    ssl_key: ''                # Path to client key file

Environment Variables

Configuration can be overridden with environment variables:

export MYSQL_HOST="localhost"
export MYSQL_PORT="3306"
export MYSQL_USER="monitor"
export MYSQL_PASSWORD="secret"
export MYSQL_DATABASE=""

MySQL Setup

Create Monitoring User

For MySQL 5.7 and MariaDB:

CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'your-secure-password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';
FLUSH PRIVILEGES;

For MySQL 8.0+:

CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'your-secure-password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';
FLUSH PRIVILEGES;

For remote monitoring:

CREATE USER 'monitor'@'%' IDENTIFIED BY 'your-secure-password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

Required Privileges

Minimum required:

• PROCESS - View all threads and processes
• REPLICATION CLIENT - View replication status

Why these privileges?

• PROCESS allows SHOW FULL PROCESSLIST and SHOW ENGINE INNODB STATUS
• REPLICATION CLIENT allows SHOW MASTER STATUS and SHOW SLAVE STATUS

Security note: These are read-only privileges. The monitoring user cannot modify data.

Verify User Permissions

SHOW GRANTS FOR 'monitor'@'localhost';

Expected output:

+----------------------------------------------------------------+
| Grants for monitor@localhost                                   |
+----------------------------------------------------------------+
| GRANT PROCESS, REPLICATION CLIENT ON *.* TO `monitor`@`localhost` |
+----------------------------------------------------------------+

Collected Metrics

Connection Metrics

Monitoring:

• threads_connected approaching max_connections = need to increase limit
• High aborted_connections = authentication issues or connection floods
• threads_running spike = query bottleneck

Query Metrics

Queries per second calculation:

qps = (questions_total_now - questions_total_before) / time_elapsed

Table Lock Metrics

Lock contention ratio:

lock_contention = table_locks_waited / (table_locks_immediate + table_locks_waited)

Healthy: < 0.01 (1%)

InnoDB Buffer Pool

Buffer pool hit rate:

hit_rate = ((read_requests - reads) / read_requests) * 100

Healthy: > 99%

InnoDB Row Operations

InnoDB Locking

Average lock wait time:

avg_wait = innodb_row_lock_time / innodb_row_lock_waits

Replication Metrics (Slave Only)

Replication health:

• replication_running must be 1 (both IO and SQL threads running)
• replication_lag_seconds should be < 10 seconds

Temporary Objects

Disk temp table ratio:

tmp_disk_table_ratio = (created_tmp_disk_tables / created_tmp_tables) * 100

High ratio (> 25%) = increase tmp_table_size and max_heap_table_size

Memory Configuration

System Metrics

Query Cache (MySQL 5.7 and Earlier Only)

Note: Query cache was removed in MySQL 8.0. These metrics only appear on MySQL 5.7 and MariaDB servers.

Dashboard Metrics

The StatusRadar dashboard displays:

Overview Card

• Status - MySQL server up/down
• Version - MySQL/MariaDB version
• Uptime - Server uptime
• QPS - Queries per second

Connection Chart

• Connected threads
• Running threads
• Connection attempts
• Aborted connections

Query Performance Chart

• Queries per second
• SELECT/INSERT/UPDATE/DELETE breakdown
• Slow queries

InnoDB Buffer Pool Chart

• Buffer pool size (pages)
• Data pages
• Dirty pages
• Buffer pool hit rate

InnoDB Operations Chart

• Rows read/inserted/updated/deleted per second

Replication Status (if slave)

• Replication lag (seconds)
• IO thread status
• SQL thread status

Table Locks

• Immediate locks
• Waited locks
• Lock contention ratio

Installation

Quick Install

PLUGINS='mysql' \
MYSQL_USER='monitor' \
MYSQL_PASSWORD='your-password' \
TOKEN='your-agent-token' \
bash -c "$(curl -sL https://statusradar.dev/install-agent.sh)"

Install on Existing Agent

1. Create MySQL user:

   CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'secure-password';
   GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';
   FLUSH PRIVILEGES;

2. Install Python dependency:

   cd /opt/statusradar
   source venv/bin/activate  # If using venv
   pip install pymysql

3. Enable plugin in config:

   sudo nano /opt/statusradar/config/agent.yaml

   Add:

   plugins:
     mysql:
       enabled: true
       host: localhost
       port: 3306
       user: monitor
       password: 'secure-password'

4. Restart agent:

   sudo systemctl restart statusradar-agent

5. Verify:

   sudo journalctl -u statusradar-agent -n 50 --no-pager | grep mysql

   Expected:

   INFO: Plugin mysql: Metrics collected successfully

Testing

Manual Plugin Test

cd /opt/statusradar
python3 plugins/mysql_plugin.py

Expected Output:

Plugin: mysql
Enabled: True
Available: True

Collecting metrics...
{
  "threads_connected": 15,
  "threads_running": 2,
  "threads_cached": 8,
  "connections": 1250,
  "questions": 45000,
  "slow_queries": 5,
  "innodb_buffer_pool_pages_total": 8192,
  "innodb_buffer_pool_pages_data": 7500,
  "innodb_buffer_pool_read_requests": 1000000,
  "innodb_buffer_pool_reads": 1000,
  "innodb_rows_read": 500000,
  "innodb_rows_inserted": 10000,
  "slave_running": 0,
  ...
}

Test MySQL Connectivity

# Basic connection test
mysql -h localhost -u monitor -p -e "SELECT 1"

# Test with password from command line
mysql -h localhost -u monitor -p'your-password' -e "SELECT 1"

# Remote server
mysql -h mysql.example.com -u monitor -p'password' -e "SELECT 1"

# Check permissions
mysql -h localhost -u monitor -p'password' -e "SHOW GRANTS"

Test Status Commands

# Test SHOW STATUS
mysql -h localhost -u monitor -p'password' -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"

# Test SHOW VARIABLES
mysql -h localhost -u monitor -p'password' -e "SHOW GLOBAL VARIABLES LIKE 'max_connections'"

# Test replication status (slave only)
mysql -h localhost -u monitor -p'password' -e "SHOW SLAVE STATUS\G"

Troubleshooting

Plugin Not Collecting Metrics

Check 1: Is MySQL running?

sudo systemctl status mysql
# or
sudo systemctl status mariadb

Check 2: Can agent connect?

mysql -h localhost -u monitor -p'password' -e "SELECT 1"

Check 3: Are credentials correct?

# Check config
cat /opt/statusradar/config/agent.yaml | grep -A8 mysql

Check 4: Is Python package installed?

python3 -c "import pymysql; print(pymysql.__version__)"

Check 5: Check agent logs

sudo journalctl -u statusradar-agent -n 100 --no-pager | grep mysql

Common Errors

"Access denied for user"

Error:

ERROR: Plugin mysql: (1045, "Access denied for user 'monitor'@'localhost' (using password: YES)")

Causes:

1. Wrong password in configuration
2. User doesn't exist
3. User doesn't have access from agent's host

Solution:

-- Check if user exists
SELECT User, Host FROM mysql.user WHERE User = 'monitor';

-- Check user permissions
SHOW GRANTS FOR 'monitor'@'localhost';

-- Recreate user if needed
DROP USER 'monitor'@'localhost';
CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'new-secure-password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';
FLUSH PRIVILEGES;

Update agent config with correct password and restart.

"Can't connect to MySQL server"

Error:

ERROR: Plugin mysql: (2003, "Can't connect to MySQL server on 'localhost' (111)")

Causes:

1. MySQL not running
2. Wrong host/port
3. Firewall blocking connection
4. MySQL not listening on expected interface

Solution:

# Check if MySQL is running
sudo systemctl status mysql

# Check MySQL port
sudo netstat -tlnp | grep 3306

# Check MySQL bind address
mysql -e "SHOW VARIABLES LIKE 'bind_address'"

# If bind_address is 127.0.0.1, can only connect from localhost
# To allow remote connections, edit /etc/mysql/my.cnf:
# bind-address = 0.0.0.0

"No module named 'pymysql'"

Error:

ERROR: No module named 'pymysql'

Solution:

pip install pymysql
# Or if using venv:
cd /opt/statusradar && source venv/bin/activate && pip install pymysql

"Command denied to user"

Error:

ERROR: (1227, 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation')

Cause: Missing PROCESS or REPLICATION CLIENT privilege

Solution:

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';
FLUSH PRIVILEGES;

Metrics Showing Zero

Symptom: Some metrics always show 0

Check status variables:

mysql -u monitor -p -e "SHOW GLOBAL STATUS"

Common reasons:

• slow_queries = 0 - No slow queries (this is good!)
• innodb_deadlocks = 0 - No deadlocks (this is good!)
• seconds_behind_master = NULL - Server is not a slave
• aborted_connects = 0 - No failed connections (this is good!)

Performance Impact

On MySQL

Negligible impact:

• Plugin executes SHOW GLOBAL STATUS and SHOW SLAVE STATUS once per collection interval
• These are lightweight read-only queries (< 10ms execution time)
• No table scans, no locks, no data modification
• Uses internal MySQL statistics counters

Benchmark:

• MySQL QPS with monitoring: 10,000+ queries/sec
• MySQL QPS without monitoring: 10,000+ queries/sec
• Overhead: < 0.01%

On Agent

Resource usage:

• Memory: +15 MB
• CPU: +3% during collection (0.2-0.5 seconds)
• Network: +2 KB per collection

Use Cases

1. Query Performance Monitoring

Monitor:

• Queries per second
• Slow queries
• SELECT vs INSERT/UPDATE/DELETE ratio

Alert on:

• QPS drop (application issue)
• Slow queries spike
• Sudden query type change

2. Connection Pool Monitoring

Monitor:

• Connected threads
• Running threads
• Aborted connections
• Max used connections

Alert on:

• Connections approaching max_connections
• High aborted_connects (> 5%)
• Many idle connections

3. InnoDB Buffer Pool Tuning

Monitor:

• Buffer pool hit rate
• Dirty pages percentage
• Free pages

Alert on:

• Hit rate < 99%
• Free pages = 0 (buffer pool too small)
• Dirty pages > 75% (checkpoint issues)

4. Replication Lag Monitoring

Monitor:

• seconds_behind_master
• Slave IO/SQL thread status
• Relay log position

Alert on:

• Lag > 30 seconds
• Slave threads stopped
• Replication errors

5. Lock Contention Analysis

Monitor:

• Table locks waited
• InnoDB row lock waits
• Deadlocks

Alert on:

• Lock contention > 5%
• Deadlock spike
• Average lock wait > 100ms

Best Practices

1. Use Dedicated Monitoring User

Don't:

-- Don't use root or application user
user: root
password: root_password

Do:

-- Create dedicated read-only user
CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'secure-random-password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';

2. Set Strong Password

# Generate strong password
openssl rand -base64 32

Use in configuration:

plugins:
  mysql:
    password: 'Y8jK9mN4pQ3rS2tU7vW1xZ5aB6cD0eF=='

3. Monitor Key InnoDB Metrics

Essential metrics to watch:

• Buffer pool hit rate (should be > 99%)
• Dirty pages (should be < 75%)
• Row lock waits (should be minimal)
• Deadlocks (investigate if > 0)

4. Set Appropriate Alerts

Connection alerts:

threads_connected > max_connections * 0.8
aborted_connects > 100/hour

Performance alerts:

slow_queries > 10/minute
innodb_buffer_pool_hit_rate < 0.99
seconds_behind_master > 30 (slaves only)

5. Regular Maintenance

Weekly:

• Review slow query log
• Check for replication lag trends
• Monitor buffer pool efficiency

Monthly:

• Optimize tables if fragmentation > 20%
• Review and adjust buffer pool size
• Check for unused indexes

6. Secure Configuration File

chmod 600 /opt/statusradar/config/agent.yaml
chown root:root /opt/statusradar/config/agent.yaml

Advanced Configuration

MySQL with Socket Connection

plugins:
  mysql:
    enabled: true
    unix_socket: '/var/run/mysqld/mysqld.sock'
    user: monitor
    password: 'password'

MySQL with Different Charset

plugins:
  mysql:
    enabled: true
    host: localhost
    user: monitor
    password: 'password'
    charset: utf8mb4  # or latin1, utf8, etc.

Multiple MySQL Instances

To monitor multiple MySQL instances on same server:

Option 1: Multiple agents (recommended)

• Install separate agent for each instance
• Each agent monitors one MySQL instance

Option 2: Custom configuration

• Currently not supported
• Feature planned for future release

Example Configurations

Development Server

plugins:
  mysql:
    enabled: true
    host: localhost
    port: 3306
    user: monitor
    password: 'dev-password'

Production Master

plugins:
  mysql:
    enabled: true
    host: localhost
    port: 3306
    user: monitor
    password: 'strong-production-password'
    connect_timeout: 10

Production Slave

plugins:
  mysql:
    enabled: true
    host: localhost
    port: 3306
    user: monitor
    password: 'strong-production-password'
    # Plugin automatically detects slave status

Remote MySQL (SSL)

plugins:
  mysql:
    enabled: true
    host: mysql-prod.example.com
    port: 3306
    user: monitor
    password: 'secure-password'
    ssl: true
    ssl_ca: '/etc/ssl/certs/mysql-ca.pem'
    connect_timeout: 15

Docker MySQL

plugins:
  mysql:
    enabled: true
    host: 172.17.0.2  # Docker container IP
    port: 3306
    user: monitor
    password: 'password'

Metric Reference

Full Metric List

The plugin collects 30+ metrics from SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES:

Connection Metrics:

• threads_connected, threads_running
• max_used_connections, max_connections
• aborted_connections, connection_errors_max_connections
• connection_percent (calculated)

Query Metrics:

• questions_total, queries_total, slow_queries_total
• com_select, com_insert, com_update, com_delete

Table Lock Metrics:

• table_locks_immediate, table_locks_waited

InnoDB Buffer Pool:

• innodb_buffer_pool_pages_total, innodb_buffer_pool_pages_data
• innodb_buffer_pool_pages_free
• innodb_buffer_pool_read_requests, innodb_buffer_pool_reads
• innodb_buffer_pool_hit_rate (calculated)
• innodb_buffer_pool_usage_percent (calculated)

InnoDB Row Operations:

• innodb_rows_read, innodb_rows_inserted
• innodb_rows_updated, innodb_rows_deleted

InnoDB Locks:

• innodb_row_lock_waits, innodb_row_lock_time

Replication (Slave Only):

• replication_running, replication_lag_seconds

Temporary Objects:

• created_tmp_tables, created_tmp_disk_tables
• tmp_disk_table_ratio (calculated)

Memory Configuration:

• innodb_buffer_pool_size_mb, key_buffer_size_mb

System:

• uptime_seconds, bytes_sent, bytes_received

Query Cache (MySQL 5.7 only):

• query_cache_size_mb, qcache_hits, qcache_inserts

Next Steps

• View all plugins
• Redis Plugin
• PostgreSQL Plugin
• MongoDB Plugin
• Agent Configuration Guide