Configuration and Discovery

The following sections describe how to configure and discover MySQL for monitoring by SL1 using the MySQL PowerPack:

Prerequisites for Monitoring MySQL

To configure the SL1 system to monitor MySQL servers and instances using the MySQL PowerPack, you must first create a read-only MySQL user for each instance to be monitored. For discovery of multiple instances on the same IP address, ScienceLogic recommends creating the same user and password on each instance. The user must have the minimum following privileges:

Privilege	Definition	Level(s)
SELECT	Enables the use of SELECT.	Global, database, table, column.
EXECUTE	Enable the use of statements that execute stored routines (stored procedures and functions). This is necessary for queries on the system database.

Creating a SOAP/XML Credential for MySQL

To configure SL1 to monitor MySQL, you must create a SOAP/XML credential. This credential allows the Dynamic Applications in the MySQL PowerPack to communicate with your MySQL server and instances.

The MySQLPowerPack includes an example SOAP/XML credential that you can use as a template for creating SOAP/XML credentials for MySQL.

NOTE: If you are using an SL1 system prior to version 11.1.0, the new user interface does not include the Duplicate option for sample credential(s). ScienceLogic recommends that you use the classic user interface and the Save As button to create new credentials from sample credentials. This will prevent you from overwriting the sample credential(s).

To configure a SOAP/XML credential to access your MySQL server:

Go to the Credentials page (Manage > Credentials).
Locate the MySQL Example Credential click its Actions icon () and select Duplicate. A copy of the credential appears called MySQL Example Credential copy.

Click the Actions icon () for the MySQL Example Credential copy credential and select Edit. The Edit Credential modal page appears:

Name. Type a new name for the MySQL credential.
All Organizations. Toggle on (blue) to align the credential to all organizations, or toggle off (gray) and then select one or more specific organizations from the Select the organizations the credential belongs to drop-down field to align the credential with those specific organizations.
Timeout (ms). It is recommended to enter a timeout of at least 2000 milliseconds.

URL. Type "https://%D".
HTTP Auth User. Type the username for your MySQL server.
HTTP Auth Password. Type the password for your MySQL server.

NOTE: To discover multiple MySQL instances on the same IP address, ScienceLogic recommends creating the same user and password on each instance, so the user will need to create only one credential.

HTTP Headers. The following headers are in the example credential and are required:

Service:MySQL
Range:<port_begin>-<port_end>. Specify the range of ports on which your MySQL server is running. For example, "Range:3305-3310".
Linux:<ssh_cred_id>. If you have configured credentials to read the error log, enter the credential ID for the SSH credential for a Linux server. For Windows servers, update the field to "Windows:<powershell_cred_id>".

NOTE: To discover a MySQL instance with one port, users must specify the "Range" header as "Range:<port_ begin>-<port_end>". Meaning, "port_begin" and "port_end" must be the same port. For example, "Range:7706-7706".

For all other fields, use the default values.
Click the Save & Close button.

Creating a SOAP/XML Credential for MySQL in the SL1 Classic User Interface

To configure SL1 to monitor MySQL, you must create a SOAP/XML credential. This credential allows the Dynamic Applications in the MySQL PowerPack to communicate with your MySQL server and instances.

The MySQLPowerPack includes an example SOAP/XML credential that you can use as a template for creating SOAP/XML credentials for MySQL.

To configure a SOAP/XML credential to access your MySQL server:

Go to the Credential Management page (System > Manage > Credentials).
Locate the MySQL Example Credential and click its wrench icon (). The Edit SOAP/XML Credential modal page appears.

Enter values in the following fields:

Basic Settings

Profile Name. Type a new name for the MySQL credential.

URL. Type "%D".
HTTP Auth User. Type the username for your MySQL server.
HTTP Auth Password. Type the password for your MySQL server.
Timeout. It is recommended to enter a timeout of at least 2 seconds.

NOTE: To discover multiple MySQL instances on the same IP address, ScienceLogic recommends creating the same user and password on each instance, so the user will need to create only one credential.

HTTP Headers

HTTP Headers. The following headers are in the example credential and are required:

Service:MySQL
Range:<port_begin>-<port_end>. Specify the range of ports on which your MySQL server is running. For example, "Range:3305-3310".
Linux:<ssh_cred_id>. If you have configured credentials to read the error log, enter the credential ID for the SSH credential for a Linux server. For Windows servers, update the field to "Windows:<powershell_cred_id>".

For all other fields, use the default values.
Click the Save As button.

Configuring the Credential to Read the MySQL Error Log

In addition to the SOAP/XML credential created to monitor MySQL, another credential must be created to read the MySQL Error Log. The credentials are configured differently for Linux and Windows servers.

For Linux servers, you must create an SSH/Key credential. To create the credential:

Go to the Credentials page (Manage > Credentials).
Locate the MySQL SSH Example credential, click its Actions icon () and select Duplicate. A copy of the credential appears.
Click the Actions icon () for the credential copy and select Edit. The Edit Credential modal page appears:

Name. Type a new name for the credential.
All Organizations. Toggle on (blue) to align the credential to all organizations, or toggle off (gray) and then select one or more specific organizations from the What organization manages this service? drop-down field to align the credential with those specific organizations.

Hostname/IP. Type "%D".
Username. Type the username for your Linux server.
Password. Type the password for your Linux server.

For all other fields, use the default values.
Click the Save & Close button.

The private key can have a maximum of 64 characters per line. Therefore, you cannot use keys in the OpenSSH format, because that format uses 70 characters per line. When you attempt to save the credential, SL1 will validate that the private key entered is in the correct format. You will be able to save the credential only if the private key is correctly formatted.

For Windows servers, you must create a PowerShell credential. To create the credential:

Go to the Credentialspage (Manage > Credentials).
Locate the MySQL PowerShell Example credential, click its Actions icon () and select Duplicate. A copy of the credential appears.

Click the Actions icon () for the credential copy and select Edit. The Edit Credential modal page appears:

Name. Type a new name for the credential.
All Organizations. Toggle on (blue) to align the credential to all organizations, or toggle off (gray) and then select one or more specific organizations from the What organization manages this service? drop-down field to align the credential with those specific organizations.
Hostname/IP. Type "%D".
Username. Type your username for the Windows server.
Password. Type your password for the Windows server.

For all other fields, use the default values.
Click the Save & Close button.

To configure the existing SOAP credential:

Go to the Credentials page (Manage > Credentials).
Locate the MySQL credential you created, click its Actions icon () and select Edit/Test.
In the HTTP Headers section, enter the credential ID for the SSH credential for a Linux server. For Windows servers, update the field to "Windows:<powershell_cred_id>".

Click the Save & Close button.

Configuring the Credential to Read the MySQL Error Log in the SL1 Classic User Interface

For Linux servers, you must create an SSH/Key credential. To create the credential:

Go to the Credential Management page (System > Manage > Credentials).
Click the Actions button and select the option Create SSH/Key Credential for Linux servers or Create PowerShell Credential for Windows Servers.

Enter values in the following fields:

For Linux Servers:

Credential Name. Type a new name for the credential.

Hostname/IP. Type "%D".
Username. Type the username for your Linux server.
Password. Type the password for your Linux server.

For all other fields, use the default values.
Click the Save button.

For Windows servers, you must create a PowerShell credential. To create the credential:

Go to the Credential Management page (System > Manage > Credentials).
Click the Actions button and select Create PowerShell Credential.

Enter values in the following fields:

Profile Name. Type a new name for the credential.
Hostname/IP. Type "%D".
Username. Type your username for the Windows server.
Password. Type your password for the Windows server.

For all other fields, use the default values.
Click the Save button.

To configure the existing SOAP credential:

Go to the Credential Management page (System > Manage > Credentials).
Locate the MySQL credential you created and click its wrench icon (). The Edit SOAP/XML Credential modal page appears.
In the HTTP Headers pane, enter the credential ID for the SSH credential for a Linux server. For Windows servers, update the field to "Windows:<powershell_cred_id>".

Click the Save button.

Creating a SOAP/XML Credential for an SSL Certificate

In addition to the SOAP/XML credential created to monitor MySQL, another credential must be created to support loading your SSL certificate on a database connection.

To create the credential:

Go to the Credentialspage (Manage > Credentials).
Locate the MySQL SSH Example Credential SSL credential, click its Actions icon () and select Duplicate. A copy of the credential appears.
Click the Actions icon () for the credential copy and select Edit. The Edit Credential modal page appears:

Name. Type a new name for the credential.
All Organizations. Toggle on (blue) to align the credential to all organizations, or toggle off (gray) and then select one or more specific organizations from the What organization manages this service? drop-down field to align the credential with those specific organizations.
Timeout (ms). It is recommended to enter a timeout of at least 2000 milliseconds.

URL. Type "https://%D".
HTTP Auth User. Type the username for your server.
HTTP Auth Password. Type the password for your server.

NOTE: To discover multiple MySQL instances on the same IP address, ScienceLogic recommends creating the same user and password on each instance, so the user will need to create only one credential.

HTTP Headers. The following headers are in the example credential and are required:

Service:MySQL
Range:<port_begin>-<port_end>. Specify the range of ports on which your MySQL server is running. For example, "Range:3305-3310".
Linux:<ssh_cred_id>. If you have configured credentials to read the error log, enter the credential ID for the SSH credential for a Linux server. For Windows servers, update the field to "Windows:<powershell_cred_id>".

CURL Options. Edit the following fields in this section:

CAPATH. Type the CA path for your SSL certificate.
SSLKEY. Type the key path for your SSL certificate.
SSLPEERCERT. Type the certificate path for your SSL certificate.

For all other fields, use the default values.
Click the Save & Close button.

Creating a SOAP/XML Credential for an SSL Certificate in the SL1 Classic User Interface

In addition to the SOAP/XML credential created to monitor MySQL, another credential must be created to support loading your SSL certificate on a database connection.

To configure the credential:

Go to the Credential Management page (System > Manage > Credentials).
Locate the MySQL SSH Example Credential SSL and click its wrench icon (). The Edit SOAP/XML Credential modal page appears.

Enter values in the following fields:

Basic Settings

Profile Name. Type a new name for the MySQL credential.

URL. Type "%D".
HTTP Auth User. Type the username for your MySQL server.
HTTP Auth Password. Type the password for your MySQL server.
Timeout. It is recommended to enter a timeout of at least 2 seconds.

NOTE: To discover multiple MySQL instances on the same IP address, ScienceLogic recommends creating the same user and password on each instance, so the user will need to create only one credential.

HTTP Headers

HTTP Headers. The following headers are in the example credential and are required:

Service:MySQL
Range:<port_begin>-<port_end>. Specify the range of ports on which your MySQL server is running. For example, "Range:3305-3310".
Linux:<ssh_cred_id>. If you have configured credentials to read the error log, enter the credential ID for the SSH credential for a Linux server. For Windows servers, update the field to "Windows:<powershell_cred_id>".

CURL Options

CAPATH. Type the CA path for your SSL certificate.
SSLKEY. Type the key path for your SSL certificate.
SSLPEERCERT. Type the certificate path for your SSL certificate.

For all other fields, use the default values.
Click the Save As button.

Discovering MySQL Servers

To model and monitor your MySQL servers and instances, you must run a discovery session to discover the MySQL server that SL1 will use as the root device for monitoring the MySQL instances.

Several minutes after the discovery session has completed, the "MySQL: Discovery" Dynamic Application in the MySQL PowerPack should automatically align to the MySQL server, creating the MySQL server container. The remaining Dynamic Applications in the PowerPack will then discover, model, and monitor the remaining MySQL instances.

To discover the MySQL server that you want to monitor, perform the following steps:

On the Devices page () or the Discovery Sessions page (Devices > Discovery Sessions), click the Add Devices button. The Select page appears.
Click the Unguided Network Discovery button. Additional information about the requirements for discovery appears in the General Information pane to the right.
Click Select. The Basic Information page appears. Complete the following fields:

Name. Type a unique name for this discovery session. This name is displayed in the list of discovery sessions on the Discovery Sessions tab.
Description. Optional. Type a short description of the discovery session. You can use the text in this description to search for the discovery session on the Discovery Sessions tab.
Select the organization to add discovered devices to. Select the name of the organization to which you want to add the discovered devices

Click Next. The Credential Selection page of the Add Devices wizard appears:

On the Credential Selection page, locate and select the credential you created for the MySQL server.
Click Next. The Discovery Session Details page of the Add Devices wizard appears:

Complete the following fields:

List of IPs/Hostnames. Type the IP address(es) of the MySQL server you want to discover.
Which collector will monitor these devices?. Required. Select an existing collector to monitor the discovered devices.
Run after save. Select this option to run this discovery session as soon as you save the session.

In the Advanced options section, click the down arrow icon () to complete the following fields:

Discover Non-SNMP. Enable this setting.
Model Devices. Enable this setting.

Click Save and Run if you enabled the Run after save setting, or Save and Close to save the discovery session. The Discovery Sessions page (Devices > Discovery Sessions) displays the new discovery session.
If you selected the Run after save option on this page, the discovery session runs, and the Discovery Logs page displays any relevant log messages. If the discovery session locates and adds any devices, the Discovery Logs page includes a link to the Device Investigator page for the discovered device.

Discovering MySQL Servers in the SL1 Classic User Interface

To model and monitor your MySQL servers and instances, you must run a discovery session to discover the MySQL server that SL1 will use as the root device for monitoring the MySQL instances.

To discover the MySQL server that you want to monitor, perform the following steps:

Go to the Discovery Control Panel page (System > Manage > Classic Discovery).
In the Discovery Control Panel, click the Create button.

The Discovery Session Editor page appears. In the Discovery Session Editor page, define values in the following fields:

IP Address/Hostname Discovery List. Type the IP address(es) of the MySQL server you want to discover.

Other Credentials. Select the SOAP/XML credential(s) you created for the MySQL server.
Discover Non-SNMP. Select this checkbox.
Model Devices. Select this checkbox.

Optionally, you can enter values in the other fields on this page. For more information about the other fields on this page, see the Discovery & Credentials section.
Click the Save button to save the discovery session and then close the Discovery Session Editor window.
The discovery session you created appears at the top of the Discovery Control Panel page. Click its lightning-bolt icon () to run the discovery session.
The Discovery Session window appears. When the cluster root device(s) are discovered, click the device icon () to view the Device Properties page for each device.

Verifying Discovery and Dynamic Application Alignment

To verify that SL1 has automatically aligned the correct Dynamic Applications during discovery, perform the following steps:

After discovery has completed, locate the device for the MySQL server in the Devices page.
From the Device Investigator page for the MySQL server, click the Collections tab.
The "MySQL: Discovery" Dynamic Application for the server is automatically aligned during discovery.

It can take several minutes after the discovery session has completed for Dynamic Applications to appear in the Dynamic Application Collections page.

The MySQL server container will then be created and the "MySQL: Instance Discovery" Dynamic Application will auto-align to the server container. The MySQL server container will then discover, model, and monitor the remaining MySQL instances.

The following Dynamic Applications will auto-align to the MySQL instances:

MySQL: Instance Commands Performance
MySQL: Instance Handler Performance
MySQL: Instance InnoDB Buffer Pool Performance
MySQL: Instance InnoDB Data Performance
MySQL: Instance InnoDB Row Performance
MySQL: Instance Overall Performance
MySQL: Instance Sort and Select Performance
MySQL: Instance Table Locking Performance
MySQL: Instance Threads and Connections Performance
MySQL: Instance Configuration
MySQL: Instance InnoDB Configuration

The following Dynamic Applications will not automatically align during discovery and will need to be manually aligned:

MySQL: Events Errors Summary Configuration
MySQL: Performance Schema Statements Configuration
MySQL: Performance Schema Summary Statement Configuration
MySQL: Process List Configuration
MySQL: Statements With Error/Warning Configuration

NOTE: To collect data for the manually-aligned Dynamic Applications, you will need to enable the system database and performance_schema in the MySQL instance.

To manually align Dynamic Applications, perform the following steps:

Go to the Device Manager page (Devices > Device Manager) and click the wrench icon () for the device you want to manually align Dynamic Applications to.
Click the Collections tab, then click the Action button and select Add Dynamic Application. The Dynamic Application Alignment page appears.
In the Dynamic Applications field, select the Dynamic Application you want to align.
In the Credentials field, select the credential specified in the table.
Click the Save button.
Repeat steps 1-4 for the other unaligned Dynamic Applications.

Enabling the Slow Query Log in MySQL or MariaDB

To view the metrics related to slow queries collected in the "MySQL: Instance Configuration" Dynamic Application, you will need to enable the Slow Query Log. To enable the slow query log:

Log in to your server as the root user via SSH.
Open the my.cnf file with a text editor and add the following under the mysqld section:

slow_query_log = 1

slow-query_log_file = /var/log/mysql-slow.log

long_query_time = 2

If you are using MySQL version 5.6 or older, use the log-slow-queries variable instead of the slow-query_log_file variable.

Run the following commands to create the /var/log/mysql-slow.log file and set its user as the mysql user:

touch /var/log/mysql-slow.log

chown mysql:mysql /var/log/mysql-slow.log

Restart MySQL or MariaDB by running the following command:

/usr/local/cpanel/scripts/restartsrv_mysql

Viewing MySQL Component Devices

In addition to the Device Manager page (Devices > Classic Devices, or Registry > Devices > Device Manager in the classic SL1 user interface), you can view the MySQL server and all associated component devices in the following places in the user interface:

The Device Investigator Map page (click Map in the Device Investigator page) displays a map of a particular device and all of the devices with which it has parent-child relationships. Double-clicking any of the listed devices reloads the page to make the selected device the primary device.

The Device Components page (Devices > Device Components) displays a list of all root devices and component devices discovered by SL1 in an indented view, so you can easily view the hierarchy and relationships between child devices, parent devices, and root devices. To view the component devices associated with a MySQL server, find the MySQL server and click its plus icon (+):

The Component Map page (Classic Maps > Device Maps > Components) allows you to view devices by root node and view the relationships between root nodes, parent components, and child components in a map. This makes it easy to visualize and manage root nodes and their components. SL1 automatically updates the Component Map as new component devices are discovered. The platform also updates each map with the latest status and event information. To view the map for a MySQL server, go to the Component Map page and select the map from the list in the left NavBar. To learn more about the Component Map page, see the section on Maps.