check-mysql-nagios 0.2.3

A Nagios plugin for monitoring MySQL servers, directly or through an SSH tunnel

🐬 Check MySQL

A comprehensive Nagios plugin for monitoring MySQL and MariaDB servers, either directly (PyMySQL) or through an SSH tunnel (sshtunnel/paramiko). Built with modern Python practices and designed for enterprise monitoring environments.

✨ Features

• 🔌 Dual Connectivity - Direct TCP connection or SSH tunnel through a bastion host
• 🎯 22 Checks - Connectivity ping (client/server versions), uptime, connection usage, replication health, slow queries, query latency, arbitrary SQL, plus the cache-hitrate and load checks backported from check_mysql_health
• 🔐 Security Audit - CIS-aligned mysql.user audit: anonymous accounts, missing/weak/expired passwords, wildcard hosts, remote root, dangerous privileges
• 🧙 Guided Setup - check_mysql init writes the config, creates the monitoring user and tests the connection
• 📊 Nagios Compatible - Standard exit codes, performance data and range-based thresholds
• 🏗️ Clean Architecture - Modular design with testable components
• 🔧 Flexible Configuration - File-based configuration with CLI overrides (-H, -P)
• 📈 Verbose Logging - Multi-level debugging support (-v, -vv, -vvv)
• 🐍 Modern Python - Python 3.10+ with strict typing throughout

🚀 Quick Start

Installation

# Create virtual environment (recommended)
python -m venv /usr/local/libexec/nagios/check_mysql
source /usr/local/libexec/nagios/check_mysql/bin/activate

# Install from PyPI
pip install check-mysql-nagios

# Or install from source
pip install git+https://github.com/lduchosal/check_mysql.git

Basic Usage

# Guided setup: prompts for the connection (SSH tunnel supported), writes
# check_mysql.ini, shows the CREATE USER SQL, optionally creates the
# monitoring user on the server and tests the connection.
check_mysql init

# Non-interactive: just write the default template
check_mysql init --yes

# Check connectivity and report the client and server versions
check_mysql ping

# Check server uptime (alert on recent restart)
check_mysql uptime

# Check connection usage (percent of max_connections)
check_mysql connections -W 80 -C 95

# Check replication lag and thread state
check_mysql replication -W 60 -C 300

# Check the slow queries counter
check_mysql slowqueries -W 100 -C 1000

# Check SELECT 1 round-trip latency
check_mysql latency -W 100 -C 500

# Point at another server without editing the config
check_mysql uptime -H db2.example.com -P 3307

📋 Available Commands

Command	Description	Value	Default Thresholds
init	Guided setup: config, monitoring user (SQL shown or created for you), connection test — --yes for non-interactive, --force to overwrite	-	-
ping	Connectivity check reporting the client (PyMySQL) and server versions; CRITICAL when the server is unreachable	milliseconds	-
uptime	Seconds since server start	seconds	W:3600:, C:300:
connections	Threads_connected vs max_connections	percent	W:80, C:95
replication	Replication lag, thread state	seconds behind	W:60, C:300
slowqueries	Slow_queries counter since start	count	W:100, C:1000
latency	SELECT 1 round-trip time	milliseconds	W:100, C:500
threadcache	Thread cache hitrate (Threads_created vs Connections)	percent	W:90:, C:80:
querycache	Query cache hitrate — MariaDB only, UNKNOWN on MySQL 8+	percent	W:90:, C:80:
querycacheprunes	Qcache_lowmem_prunes per second — MariaDB only	rate/s	W:1, C:10
keycache	MyISAM key cache hitrate	percent	W:99:, C:95:
tablecache	Table cache hitrate (Open_tables vs Opened_tables)	percent	W:99:, C:95:
bufferpool	InnoDB buffer pool hitrate	percent	W:99:, C:95:
bufferpoolwaits	Innodb_buffer_pool_wait_free per second	rate/s	W:1, C:10
logwaits	Innodb_log_waits per second	rate/s	W:1, C:10
tablelocks	Table lock contention (waited vs immediate)	percent	W:1, C:2
indexusage	Index usage vs full scans (Handler_read_*)	percent	W:90:, C:80:
tmpdisktables	Share of temporary tables created on disk	percent	W:25, C:50
openfiles	Open_files vs open_files_limit	percent	W:80, C:95
longrunning	Queries running longer than 60s (needs PROCESS)	count	W:10, C:20
security	Over-privileged or insecure accounts: anonymous, passwordless, weak or expired password, wildcard % host, remote root, dangerous privileges reachable remotely (needs SELECT on mysql.user)	count	W:0, C:5
sql	Scalar result of an arbitrary statement (--sql "SELECT ...")	scalar	-

Thresholds are standard Nagios ranges: 95 alerts above 95, 300: alerts below 300, 10:20 alerts outside the interval.

check_mysql_health Heritage

The threadcache … longrunning block and the sql command are backported from lausser's check_mysql_health with the same default thresholds (security is check_mysql's own). One deliberate difference: the original computes rates as deltas between two runs persisted in a state file; check_mysql reports averages since server start instead, which needs no local state. The query cache checks (querycache, querycacheprunes) apply to MariaDB only — the query cache was removed in MySQL 8.0 and those commands exit UNKNOWN there.

Replication Semantics

• OK/WARNING/CRITICAL on Seconds_Behind_Source against the thresholds
• CRITICAL immediately when the IO or SQL thread is stopped (the last replication error is included in the output)
• CRITICAL immediately when the lag is NULL while threads run
• UNKNOWN when the server is not a replica
• Both modern (Replica_*/Source_*) and legacy (Slave_*/Master_*) column names are supported

Security Audit Semantics

The security command reads mysql.user (one SELECT, no other table) and flags accounts matching any of these criteria, aligned with the CIS Oracle MySQL Benchmark and mysql_secure_installation:

Check	Condition on the mysql.user row	Reported as	Reference
Anonymous account	User is empty	anonymous account	CIS 4.11
Missing password	Password and authentication_string both empty, on a password-based plugin	no password	CIS 4.10
Weak password	mysql_native_password hash matches a common password (offline wordlist, unsalted hashes only)	weak password	MySQLTuner
Expired password	password_expired = Y (stale credential left in place)	password expired	CIS 4.x
Wildcard host	Host is % or empty (reachable from any address)	wildcard host	CIS 4.9
Remote root	User is root with a host other than localhost/127.0.0.1/::1	root reachable remotely	mysql_secure_installation
Remote dangerous privileges	Non-local host holding SUPER, GRANT OPTION, FILE, PROCESS, SHUTDOWN, CREATE USER, RELOAD, CREATE/ALTER ROUTINE, EVENT or TRIGGER — or every *_priv column, reported as ALL PRIVILEGES	remote privileges (…)	CIS 5.2–5.8

The value is the number of flagged accounts (an account with several findings counts once); the long output lists one 'user'@'host' line per account and the headline breaks the count down by category. Defaults: WARNING above 0, CRITICAL above 5. Run with -vvv to trace every criterion and its verdict per account (see Debug Mode).

What is deliberately not flagged:

• Locked accounts (account_locked = Y) and the mysql_no_login plugin — they refuse every connection, no attack surface. They are excluded from the audited count.
• Socket/external authentication (unix_socket, auth_socket, PAM, LDAP, GSSAPI, Kerberos, Windows) — an empty credential column is normal there, not a missing password.
• Powerful local accounts — root@localhost or debian-sys-maint@localhost are expected on every install; privileges are only flagged on remotely reachable accounts.
• The monitoring user's % host — the [mysql] account must be remotely reachable to do its job; it stays subject to every other check (no password, dangerous privileges).
• Expected admins — accounts listed in [security] admins are exempted from the remote-privileges check only (they are meant to be powerful) and stay subject to every other check.
• Accounts listed in [security] allow — exempted from every check (see the Configuration section below).
• Scoped host patterns (10.0.%, %.example.com) — only the pure % wildcard is flagged.

Known limits (candidates for later versions): salted-hash accounts cannot be tested for weak passwords offline (caching_sha2_password is salted by design — only mysql_native_password is covered); legacy authentication plugins (mysql_native_password, pre-4.1 hashes — CIS 4.7), absent validate_password policy, presence of the test database, non-admin grants on the mysql schema (CIS 5.1) and per-account TLS requirements are not yet checked.

⚙️ Configuration

Run check_mysql init for a guided setup: it prompts for the connection settings (password generated by default, SSH tunnel supported), writes check_mysql.ini with mode 600, prints the CREATE USER SQL, and can create the monitoring user on the server (with admin credentials, through the tunnel if configured) then test the connection. Or create the file by hand — it is searched in the working directory, then in /usr/local/etc/nagios and /etc/nagios; -c also accepts an absolute path:

Direct Connection

[mysql]
host = db.example.com
port = 3306
user = nagios
password = secret
# Optional default schema (useful with the sql command):
#database = mydb
# Connection timeout in seconds:
timeout = 10

Connection Through an SSH Tunnel

[mysql]
# Host/port as seen FROM THE BASTION
host = 10.0.0.12
port = 3306
user = nagios
password = secret
timeout = 10

[ssh]
host = bastion.example.com
port = 22
user = nagios
private_key = ~/.ssh/id_ed25519
# or: password = only-if-no-key

When the [ssh] section is present, the plugin opens the tunnel first and connects to MySQL through it; remove the section to connect directly.

MySQL Monitoring User

CREATE USER 'nagios'@'%' IDENTIFIED BY 'secret';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'nagios'@'%';
GRANT SELECT ON mysql.user TO 'nagios'@'%';

This is exactly what check_mysql init creates (or prints) for you.

REPLICATION CLIENT (or REPLICATION_SLAVE_ADMIN on MySQL 8+) is only needed for the replication command, and SELECT on mysql.user only for the security command. Add GRANT PROCESS ON *.* for the longrunning command (to see other users' queries), and the relevant SELECT grants for whatever the sql command queries.

The security audit is tuned through the optional [security] section (the full criteria and built-in exemptions are described under Security Audit Semantics above). Entries are user@host, exactly like the User and Host columns of mysql.user — comma separated, % needs no escaping. allow exempts accounts from every check; admins lists accounts expected to be powerful, exempted from the remote-privileges check only:

[security]
allow = backup@10.0.0.5, debian-sys-maint@localhost, app@%
admins = dba@10.0.0.0/24, ops@%

🔧 Command Line Options

Option	Description	Example
-c, --config	Configuration file path	-c /custom/path/config.ini
-H, --hostname	MySQL host (overrides [mysql])	-H db2.example.com
-P, --port	MySQL port (overrides [mysql])	-P 3307
-W, --warning	Warning threshold (Nagios range)	-W 80
-C, --critical	Critical threshold (Nagios range)	-C 95
-v, --verbose	Verbosity level	-v, -vv, -vvv
--version	Show version	--version

Command-specific options: sql requires --sql "SELECT ..." (the statement whose first row / first column is checked); init accepts --yes (non-interactive, write the default template) and --force (overwrite an existing file).

🏢 Nagios Integration

Command Definitions

# MySQL Commands
define command {
    command_name    check_mysql_ping
    command_line    $USER1$/check_mysql/bin/check_mysql ping -H $HOSTADDRESS$
}

define command {
    command_name    check_mysql_uptime
    command_line    $USER1$/check_mysql/bin/check_mysql uptime -H $HOSTADDRESS$
}

define command {
    command_name    check_mysql_connections
    command_line    $USER1$/check_mysql/bin/check_mysql connections -H $HOSTADDRESS$ -W 80 -C 95
}

define command {
    command_name    check_mysql_replication
    command_line    $USER1$/check_mysql/bin/check_mysql replication -H $HOSTADDRESS$ -W 60 -C 300
}

define command {
    command_name    check_mysql_slowqueries
    command_line    $USER1$/check_mysql/bin/check_mysql slowqueries -H $HOSTADDRESS$ -W 100 -C 1000
}

define command {
    command_name    check_mysql_latency
    command_line    $USER1$/check_mysql/bin/check_mysql latency -H $HOSTADDRESS$ -W 100 -C 500
}

define command {
    command_name    check_mysql_security
    command_line    $USER1$/check_mysql/bin/check_mysql security -H $HOSTADDRESS$
}

Every command in the table above follows the same pattern; all ship sensible default thresholds, so -W/-C can be omitted.

Service Definitions

# MySQL Services
define service {
    use                     generic-service
    service_description     MYSQL_PING
    check_command           check_mysql_ping
    hostgroup_name          mysql
}

define service {
    use                     generic-service
    service_description     MYSQL_UPTIME
    check_command           check_mysql_uptime
    hostgroup_name          mysql
}

define service {
    use                     generic-service
    service_description     MYSQL_CONNECTIONS
    check_command           check_mysql_connections
    hostgroup_name          mysql
}

define service {
    use                     generic-service
    service_description     MYSQL_REPLICATION
    check_command           check_mysql_replication
    hostgroup_name          mysql-replicas
}

define service {
    use                     generic-service
    service_description     MYSQL_SLOWQUERIES
    check_command           check_mysql_slowqueries
    hostgroup_name          mysql
}

define service {
    use                     generic-service
    service_description     MYSQL_LATENCY
    check_command           check_mysql_latency
    hostgroup_name          mysql
}

define service {
    use                     generic-service
    service_description     MYSQL_SECURITY
    check_command           check_mysql_security
    hostgroup_name          mysql
}

🏗️ Architecture

This plugin follows clean architecture principles with clear separation of concerns:

check_mysql/
├── 📁 cli/                     # Command-line interface
│   ├── commands/               # One module per command (or command family)
│   │   ├── init.py            # Guided setup (config, monitoring user, test)
│   │   ├── ping.py            # Connectivity + client/server versions
│   │   ├── uptime.py          # Uptime command
│   │   ├── connections.py     # Connections command
│   │   ├── replication.py     # Replication command
│   │   ├── slowqueries.py     # Slow queries command
│   │   ├── latency.py         # Latency command
│   │   ├── longrunning.py     # Long-running queries command
│   │   ├── openfiles.py       # Open files command
│   │   ├── security.py        # mysql.user security audit
│   │   ├── sql.py             # Arbitrary scalar SQL command
│   │   ├── ratios.py          # Hitrate family: threadcache, querycache,
│   │   │                      #   keycache, tablecache, bufferpool,
│   │   │                      #   tablelocks, indexusage, tmpdisktables
│   │   └── counters.py        # Rate family: querycacheprunes,
│   │                          #   bufferpoolwaits, logwaits
│   ├── decorators.py          # Common CLI decorators
│   └── handlers.py            # Shared command execution path
├── 📁 core/                    # Core business logic
│   ├── config.py              # Configuration handling
│   ├── connection.py          # Direct / SSH tunnel connector
│   ├── mysql_client.py        # MySQL query client
│   ├── provisioning.py        # Monitoring-user creation (init)
│   ├── status.py              # SHOW GLOBAL STATUS/VARIABLES helpers
│   ├── exceptions.py          # Custom exceptions
│   ├── models.py              # Dataclasses, TypedDicts, Protocols
│   ├── nagios.py              # Nagios plugin framework
│   └── logging_config.py      # Logging configuration
├── 📁 services/                # One service per check
│   ├── ping_service.py        # …one file per command above…
│   ├── ratio_service.py       # RATIO_SPECS table + shared hitrate logic
│   ├── counter_service.py     # COUNTER_SPECS table + shared rate logic
│   └── security_service.py    # Account audit rules
└── 📁 tests/                   # Comprehensive test suite
    ├── unit/                   # Unit tests
    ├── integration/            # CLI integration tests
    ├── e2e/                    # End-to-end tests (real local MySQL server)
    └── fixtures/               # Test fixtures (mock client, datasets)

Key Design Principles

• 🎯 Single Responsibility - Each module has one clear purpose
• 🔌 Dependency Injection - Services depend on a client Protocol, trivially mockable
• 🧪 Testable - The default test suite runs without any MySQL server; the e2e suite drives the real binary against a local one
• 📈 Extensible - Adding a check = one service + one command file
• 🔒 Secure - No secrets in code, key-based SSH authentication

🧪 Development

Development Setup

# Clone repository
git clone https://github.com/lduchosal/check_mysql.git
cd check_mysql

# Install with PDM (creates .venv)
pdm install
pdm install -G dev

Code Quality Tools

# The full quality pipeline (format, lint, types, docstrings, tests, gate)
pdm run check

# Individual steps
pdm run lint          # ruff
pdm run typecheck     # pyright (strict)
pdm run test          # pytest + coverage (no MySQL server needed)
pdm run test-e2e      # end-to-end against the local server (check_mysql.ini)
pdm run metrics       # local quality metrics snapshot

See doc/code-quality.md for the full quality standard (blocking gate + best-ever ratchet).

Building & Publishing

# Quality checks only
sh publish.sh --quality

# Full pipeline: quality, local e2e, SonarCloud gate, bump, build, publish, tag
sh publish.sh --patch          # or --minor / --major

# CI mode (skips the end-to-end suite, which needs the local MySQL server)
sh publish.sh --ci --quality

🔍 Output Examples

Successful Check

MYSQL OK - Server up for 10 days, 0:00:00 (864000 seconds) | uptime=864000s;3600:;300:

Ping (versions)

MYSQL OK - client PyMySQL 2.2.8, server 8.4.9 | ping=0.25ms

Security Audit

MYSQL OK - 0 risky accounts out of 16 audited | security=0;0;5

Warning State

MYSQL WARNING - Connections: 130/151 (86.09% of max_connections) | connections=86.09%;80;95

Critical State (replication stopped)

MYSQL CRITICAL - Replication threads stopped (IO: No, SQL: Yes): error connecting to source

Unknown State

UNKNOWN: Configuration file not found: check_mysql.ini

🔧 Troubleshooting

Common Issues

Issue	Solution
Access denied	Verify the monitoring user credentials and grants
SSH tunnel failures	Check bastion reachability, key permissions (600) and known_hosts
Replication UNKNOWN	The target is not a replica — point at the right server
Configuration Issues	Validate config file syntax and search paths

Debug Mode

# Maximum verbosity (SQL queries, tunnel lifecycle, method traces)
check_mysql replication -vvv

# Check a specific configuration
check_mysql uptime -c /path/to/config.ini -vv

📊 Exit Codes

Code	Status	Description
0	OK	Value within acceptable range
1	WARNING	Value exceeds warning threshold
2	CRITICAL	Value exceeds critical threshold, or replication stopped
3	UNKNOWN	Error occurred during execution

🤝 Contributing

We welcome contributions! Here's how to get started:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Development Guidelines

• Follow PEP 8 style guide
• Add tests for new features
• Update documentation as needed
• pdm run check must be green before submitting

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

• Built with nagiosplugin framework
• Uses PyMySQL for MySQL connectivity
• Uses sshtunnel for bastion traversal
• Powered by Click for CLI interface

---

⭐ Star this repository if you find it useful!

🐛 Report Bug • 💡 Request Feature • 📖 Documentation

💖 Sponsor

If this project helps you, consider supporting its development: