Knowledge Base

Monitoring MySQL Server 5.5 with Monitoring Studio

KB1121 - Jun 07, 2013 - Last reviewed on Apr 30, 2020

Type: Configuration Template

Description: How to monitor MySQL Server 5.5 with Monitoring Studio.

Additional Keywords: Configuration Template, MySQL, MySQL Server

Related Topics

Download File
Important Note: This pre-built configuration template does not cover all the monitoring aspects of MySQL Server 5.5. It is provided as an example and can be used as a working base for advanced monitoring. No support will be provided for this pre-built configuration. Should you need further assistance to customize this template, please contact our Professional Services team.

Making-Of

Play the tutorial and find out how the configuration template was actually created thanks to the Monitoring Studio easy-to-use graphical interface:

Watch the videos

In less than 30 minutes split into 9 easy steps, you will be able to thoroughly monitor MySQL to detect failures, errors and performance problems.

Objective

Our goal is to monitor MySQL Server in BMC PATROL to detect failures, errors and performance problems. This article only applies to version 5.5 of MySQL, but can certainly be adapted to other versions.

Solution

Monitoring Studio KM for PATROL

Our solution relies on Monitoring Studio KM for PATROL, which is a configurable knowledge module for BMC PATROL and BMC TrueSight Operations Management. Typically, a PATROL administrator will use Monitoring Studio KM to set up the monitoring of almost any application for which there is no out-of-the-box monitoring solution available, with no coding involved.

Sentry Software has built a configuration for Monitoring Studio KM to monitor MySQL Server 5.5. This article outlines all the elements monitored by this pre-built configuration and explains how to import it.

Note: This configuration is provided as an example and cannot be considered as a complete monitoring solution. It can however be modified and customized to meet your specific needs.

More information about Monitoring Studio KM for PATROL is available on the Sentry Software’s Web site.

Monitored components

1/ Windows Service

The solution monitors the MySQL Windows Service:

inline

2/Status of MySQL Server

The solution monitors the status of MySQL Server and reports:

  • The number of connections that were aborted because the client died without closing the connection properly (Aborted_clients instance)
  • The number of joins performing table scans (Full Joins instance)
  • The number of tables that have been opened (Opened tables instance)
  • The number of joins that did a full scan of the first table (Scans instance)
  • The number of queries that have taken too long (Slow Queries instance)
  • The number of clients currently connected (Threads Connected instance)
  • The number of threads created to handle connections (Threads Created instance)
  • The number of queries that are currently processed (Threads Running instance).

The solution also returns the value of all the handler type of parameters.

inline

3/Database Performance

The solution runs the show engine innodb status query to monitor the database performance. It more particularly reports:

  • The number of average bytes read
  • The number of deletes/s
  • The number of fsyncs/s
  • The number of inserts/s
  • The number of pending normal aio reads
  • The number of reads/s
  • The number of updates/s
  • The number of writes/s

inline

4/Full Process List

The solution runs the show full processlist query to monitor processes. It more especially reports:

  • The number of processes (Process Count instance)
  • The average execution time (Average Process Execution Time instance)
  • The longest process running (Longest Process Running instance)

inline

5/MYSQL Log File

The MySQL Log File is constantly monitored by the solution and any ERROR or WARNING event is reported in the console.

inline

The following parameters are also available for the MYSQL Log file:

  • Exists
  • LastChanged
  • Size

Installation

Pre-requisites

Before monitoring MySQL Server, make sure that the following items are available, installed, and properly configured:

  • A fully functional BMC PATROL environment (optionally part of a larger TrueSight environment), with a BMC PATROL Console.
  • A PATROL Agent on the machine where MySQL Server is installed.
  • A compatible version of Monitoring Studio KM for PATROL properly loaded on the agent and the console:
    Templates Compatible version
    MySQLServer.zip
    MySQLServer_Unix.zip    		 v8.6.54
    SEN_MSv91+_MySQL_Unix.cfg
    SEN_MSv91+_MySQL_Windows.cfg    		 v9.1.00
  • The required configuration template file installed on the machine where MySQL Server is installed (Monitoring Studio v8.6.54) or where a PATROL Agent is installed (Monitoring Studio v9.1 and higher).
  • An account with at least the PROCESS, SHOW DATABASES, and SHOW VIEW privileges to connect to the database server. To verify the privileges currently set, you can run the following command: 
    select * from information_schema.user_privileges where grantee like  "'mysql_user%'";

Procedure

The procedure will slightly differ depending on the version of Monitoring Studio used:

Monitoring Studio 8.6.54

  1. From the PATROL Console, [right-click] the main “Monitoring Studio” icon › [KM Commands] › [Configuration] › [Import Configuration…]
  2. Enter the path of the folder where you have stored the configuration file (this path is on the agent, on the MySQL Server) and click [Next].
  3. Select the configuration file and click [Next]:
  4. Monitoring Studio checks the content of the file. This process can take a few minutes.
  5. Monitoring Studio is ready to import the configuration. Click [Finish] to start the import.
  6. The import process can take a few minutes. Click [OK] when it completes.
  7. After the import process completes, Monitoring Studio KM starts creating the icons corresponding to the monitored objects in the PATROL Console. All icons are kept OFFLINE and the actual monitoring does not start until you enter a valid value for the application constants:

    inline
  8. To set the application constants, [right-click] the “MySQL Server” icon in the PATROL Console › [KM Commands] › [Modify Application Constants…]
  9. Set the following application constants and click [OK].
    • %{MYSQL_PATH}: corresponds to the path to the MySQL application (e.g.: C:\Program Files\MySQL\MySQL Server 5.5\bin)
    • %{MYSQL_USER}: corresponds to the user required to connect to MySQL
    • %{MYSQL_PASSWORD}: corresponds to the password required to connect to MySQL
    • %{MYSQL_DATAPATH}: corresponds to the folder where MySQL Server stores its data (e.g.: %ALLUSERSPROFILE%\MySQL\MySQL Server 5.5\data)
    Note: It is important to properly set both the MYSQL_PATH and MYSQL_DATAPATH constants according to your environment.
  10. You will then have to specify the credentials required to run the "show engine innodb status" and "show full process list" queries:
    inline
    1. [Right-click] the Database Query: show engine innodb status instance > [KM Commands] > [Edit]

    2. Click [Next]
    3. Enter the username and password to be used to connect to the database server and click [Next].

    4. Click [Next]
    5. Click [Finish]
    6. Repeat the above steps for the Database Query: show full processlist instance

Monitoring Studio 9.1 and higher

  1. From the PATROL Console, [right-click] the main “Monitoring Studio” icon › [KM Commands] › [Configuration] › [Import Configuration…]
  2. Enter the path of the folder where you have stored the configuration file and click [Next].
  3. Select the configuration file and click [Next].
  4. Set the following application constants:
    • %{MYSQL_PATH}: corresponds to the path to the MySQL application (e.g.: C:\Program Files\MySQL\MySQL Server 5.5\bin)
    • %{MYSQL_USER}: corresponds to the user required to connect to MySQL
    • %{MYSQL_PASSWORD}: corresponds to the password required to connect to MySQL
    • %{MYSQL_DATAPATH}: corresponds to the folder where MySQL Server stores its data (e.g.: %ALLUSERSPROFILE%\MySQL\MySQL Server 5.5\data)
    and click [OK]
    Note: It is important to properly set both the MYSQL_PATH and MYSQL_DATAPATH constants according to your environment.
  5. One host requires specific information before being imported. Click [Next]
  6. Enter the Hostname, IP address or Fully Qualified Domain Name of the host where MySQL Server is installed.
  7. Enter the systems credentials and click [Next]
  8. Enter the patrol credentials and click [Next]
  9. The import summary is displayed. Click [Next]
  10. Click [Finish] when import is complete.