database Integration
mysql (en)


ServicePilot database-mysql


# MySQL Database

Overview

This package monitors a MySQL Server Database.

Description

This package automatically configures the ServicePilot Agent in order to monitor a MySQL Server Database by using SQL requests (see the Notes section below for more information).

The statistics gathered in this way allow system and instances performance management.

Requirements

  • Network Flows - It is is necessary to ensure network connectivity between ServicePilot and the monitored device. In case of a network infrastructure using a firewall, all the ports below must be opened:

    • TCP/3306: Between ServicePilot Agent and MySQL Server
    • ServicePilot Manager Web server access (by default TCP/80 when using HTTP or TCP/443 when using HTTPS although this port is configurable): Between ServicePilot Agent and ServicePilot Manager
  • Third Party Configuration

    A Login Account to the monitored MySQL instance. See the Notes section below on how to create the login account.

  • ServicePilot Requirements

    • ServicePilot Manager minimum version: 8.5
    • ServicePilot Agent minimum version: 8.5 installed and configured

Installation

Before adding a resource to monitor, make certain that all pre-requisites are in place and if a ServicePilot Agent is required, that it is communicating correctly with the ServicePilot Manager. Resources can be added to ServicePilot configuration in a number of ways:

Add resource using Views Configuration web interface

  1. As an administrative user of ServicePilot, open the ServicePilot web interface.
  2. Navigate to Administration. The Configuration > Views web page will open.
  3. Click on the view in which to place the new resource in the Views hierarchy on the left of the interface. The View editor section will show the existing view contents.
  4. From the Packages list on the right of the interface, click and drag the database-mysql package into the View editor and let go.
  5. The Package properties dialog box will open to allow resource configuration.
  6. Click OK to close the Package properties dialog box. Note that the dialog box will not close if required parameters are not set.
  7. Click Save to apply the new resource to ServicePilot configuration.

Add resource by changing servicepilot.conf configuration file

Resources can be added to ServicePilot configuration by directly editing the servicepilot.conf or other included YAML configuration files. The ServicePilot web interface can be used to make these changes and apply them to the running configuration.

  1. As an administrative user of ServicePilot, open the ServicePilot web interface.
  2. Navigate to Administration.
  3. Navigate to Configuration > Edit configuration.
  4. Expand the configuration to find the provisioning: and then packages: section of the view in which the new resource will be placed.
  5. Add the example package configuration line below.
  6. Click on the green - package: word to open then Package properties dialog box to allow resource configuration.
  7. Click OK to close the Package properties dialog box. Note that the dialog box will not close if required parameters are not set.
  8. Click Save to apply the new resource to ServicePilot configuration.

Example:

- package: "database-mysql;;;;;;;;MySQL Database;;127.0.0.1;mysql;;;60"

Key field notes

  1. In the Monitoring Policies tab, specify the policy or policies to apply to the package

  2. Basic Parameters tab: IP address/FQDN: Specify the IP address, host name or FQDN for the MySQL Database, as resolvable by the machine on which the ServicePilot Agent is running

  3. Database Parameters tab:

    1. Database to monitor: Specify the name of a database instance in your MySQL. This is needed to establish the connection to the MySQL
    2. Database Login: Specify the username of the MySQL login account
    3. Database Password: Specify the password of the MySQL login account
    4. Polling Interval (sec): Specify the time interval (in seconds) at which ServicePilot will poll the MySQL Database

Notes

This package uses SELECT statements in the information_schema.GLOBAL_STATUS, information_schema.GLOBAL_VARIABLES and information_schema.TABLES tables. As these tables are virtual tables based on the privileges of the user used to access the MySQL instance, the user used by ServicePilot needs to have correct permissions.

If using a version of MySQL greater than 5.7.5 then a backward compatibility flag needs to be set to allow access to the old status tables.

  SET GLOBAL show_compatibility_56=ON;

As a minimum, if only global MySQL data is requested then a MySQL user might be created with:

  CREATE USER 'spmonitor'@'10.11.12.13' IDENTIFIED BY 'sppassword';

  GRANT USAGE ON *.* TO 'spmonitor'@'10.11.12.13';

  GRANT SELECT ON 'mysql'.* TO 'spmonitor'@'10.11.12.13';

The example above assumes that the ServicePilot Agent will be accessing the MySQL instance from IP address 10.11.12.13 with username spmonitor, password sppassword and database mysql.

If the size of a selection of databases is to be returned then the MySQL user might be created with:

  CREATE USER 'spmonitor'@'%' IDENTIFIED BY 'sppassword';

  GRANT USAGE ON *.* TO 'spmonitor'@'%';

  GRANT SELECT ON 'sakila'.* TO 'spmonitor'@'%'

  GRANT SELECT ON 'test'.* TO 'spmonitor'@'%'

The example above assumes that the ServicePilot Agent will be accessing the MySQL instance from any IP address with username spmonitor, password sppassword and database sakila or test.

Finally if all database sizes are to be returned then a MySQL user might be created with:

  CREATE USER 'spmonitor'@'%' IDENTIFIED BY 'sppassword';

  GRANT SELECT ON *.* TO 'spmonitor'@'%';

The example above assumes that the ServicePilot Agent will be accessing the MySQL instance from any IP address with username spmonitor, password sppassword and database mysql.

database mysql 0

database mysql 1

database mysql 2

database mysql 3

database mysql 4

database mysql 5