# MySQL Database
This package monitors a MySQL Server Database.
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.
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 Manager minimum version: 8.5
- ServicePilot Agent minimum version: 8.5 installed and configured
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.
Add resource using Views Configuration web interface
- As an administrative user of ServicePilot, open the ServicePilot web interface.
- Navigate to Administration. The Configuration > Views web page will open.
- 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.
- From the Packages list on the right of the interface, click and drag the database-mysql package into the View editor and let go.
- The Resource properties dialog box will open to allow resource configuration.
- Click OK to close the Resource properties dialog box. Note that the dialog box will not close if required parameters are not set.
- Click Save to apply the new resource to ServicePilot configuration.
Key field notes
In the Monitoring Policies tab, specify the policy or policies to apply to the resource
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
Database Parameters tab:
- Database to monitor: Specify the name of a database instance in your MySQL. This is needed to establish the connection to the MySQL
- Database Login: Specify the username of the MySQL login account
- Database Password: Specify the password of the MySQL login account
- Polling Interval (sec): Specify the time interval (in seconds) at which ServicePilot will poll the MySQL Database
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.