How to Monitor MySQL Server Performance with Nagios XI

MySQL powers millions of websites and applications worldwide, making it one of the most trusted and widely-used database management systems. But with great power comes great responsibility – ensuring your MySQL server is always available and performing at its best is crucial to keeping your systems running smoothly. In this guide, we’ll walk you through the process of monitoring MySQL databases using Nagios XI, so you can stay ahead of any issues and ensure peak performance at all times.

Prerequisites

Before setting up MySQL monitoring in Nagios XI, ensure the following requirements are met:

• A running Nagios XI instance
• A running MySQL server (local or remote).
• MySQL credentials with sufficient privileges for monitoring (e.g., SELECT, SHOW DATABASES, PROCESS).
• Unrestricted network connectivity between the Nagios XI server and the MySQL server (port 3306 by default).

Verify your MySQL version by running:

mysql --version

Example output:

MySQL 8.0.34-0ubuntu0.20.04.1

Step 1: Access the MySQL Monitoring Wizard

1. Log in to the Nagios XI web interface.
2. Navigate to Configure > Configuration Wizards.
3. Search for and select the MySQL Server wizard.

Step 2: Configure MySQL Monitoring

1. Enter the required details:
   • Monitor Name: A descriptive name for the MySQL check.
   • Address: IP address or hostname of the MySQL server.
   • Port: MySQL server port (default: 3306).
   • Username: MySQL user with monitoring privileges.
   • Password: Password for the MySQL user.
2. Click Next to proceed.

Step 3: Select Metrics to Monitor

Choose the MySQL performance metrics you want to track, such as:

• Connection Status: Checks if MySQL is responding.
• Uptime: Displays how long MySQL has been running.
• Slow Queries: Monitors queries exceeding the slow query threshold.
• Query Cache Usage: Evaluates query cache efficiency.
• Buffer Pool Usage: Tracks InnoDB buffer pool utilization.
• Table Locks: Detects locking issues.
• Replication Status: Monitors MySQL replication health.

Set warning and critical thresholds for each metric based on your environment.

Step 4: Finalize Configuration

1. Review all settings.
2. Click Finish to apply the configuration.
3. Nagios XI will now begin monitoring the specified MySQL server metrics.

Video Tutorial: Monitoring MySQL with Nagios XI

For a visual guide to the setup process, watch this step-by-step tutorial:

This video demonstrates the configuration steps outlined above, offering additional clarity for visual learners.

Step 5: Advanced Monitoring with Plugins (Optional)

For more granular monitoring, you can use check_mysql_health, which provides insights into:

• Index usage
• Buffer pool hit rate
• Temporary tables on disk
• Table cache hit rate
• Connected threads

Installing the Plugin

cd /usr/local/nagios/libexec
wget https://exchange.nagios.org/check_mysql_health.tar.gz
tar -xzf check_mysql_health.tar.gz
cd check_mysql_health
./configure
make
make install

Configuring the Plugin

1. In Nagios XI, navigate to Configure > Core Config Manager.
2. Define a new command (e.g., check_mysql_health) with appropriate arguments (e.g., -H $HOSTADDRESS$ -u $ARG1$ -p $ARG2$ –mode $ARG3$).
3. Add a service check for the MySQL host, linking it to the new command.
4. Set thresholds (e.g., –warning 80 –critical 90 for buffer pool hit rate) and enable notifications.

Step 6: Validate the Monitoring Setup

Confirm that Nagios XI is actively monitoring MySQL:

1. Go to Views > Service Status in the Nagios XI interface.
2. Search for the MySQL-related services and review their status (e.g., “OK,” “Warning,” or “Critical”).
3. Investigate and resolve any unexpected alerts by checking logs or connectivity.

Example Output:

Service: MySQL Connection Status - Status: OK - Response Time: 0.12s
Service: MySQL Slow Queries - Status: OK - Slow Queries: 2/min

Conclusion

By following this guide, you have successfully set up MySQL monitoring in Nagios XI. If you encounter any issues, refer to the Nagios Support Forum or the Nagios Knowledge Base for troubleshooting assistance.