Installing and Configuring Agents

This section describes the installation of Foglight for MySQL. Follow the steps in the order mentioned below:

  1. Configuring the MySQL Server
  2. Creating and Configuring Agents
  3. Configuring Agent Properties

Configuring the MySQL Server

To enable full monitoring of the MySQL server, create a user with sufficient privileges to execute system queries for the agent. If desired, follow additional steps to enable an SSL connection or monitor the slow query log. This section covers the following key areas:

MySQL Agent User Permissions

The Foglight MySQL agent requires the MySQL database user to have specific minimum privileges to monitor the database effectively.

Log in to the MySQL server and create a MySQL database user by granting the following privileges.

User privileges required for the MySQL agent on the host machine:

  • SELECT
  • REPLICATION CLIENT (if monitoring replication)
  • PROCESS

If Administration is enabled, ensure the Admin user specified in the agent properties has the necessary privileges to perform operations or request explain plans for the desired functions.

To monitor a replication slave server with the agent, a database user must have:

  • SELECT
  • REPLICATION CLIENT

Example 1:

CREATE USER '<user>'@'<localhost or DB HostName or IP>' IDENTIFIED BY '<password>'; 
GRANT SELECT, REPLICATION CLIENT, PROCESS ON *.* TO '<user>'@'<localhost or DB HostName or IP>'; 
FLUSH PRIVILEGES;

For example:

CREATE USER 'MySQLuser'@'localhost' IDENTIFIED BY 'MySQLpassword';
GRANT SELECT, REPLICATION CLIENT, PROCESS ON *.* TO 'MySQLuser'@'localhost';
FLUSH PRIVILEGES;

Example 2:

GRANT SELECT, REPLICATION CLIENT, PROCESS ON *.* TO '<user>'@'<localhost or DB HostName or IP>' IDENTIFIED BY '<password>'; 
FLUSH PRIVILEGES;

For example:

GRANT SELECT, REPLICATION CLIENT, PROCESS ON *.* TO 'MySQLuser'@'localhost' IDENTIFIED BY 'MySQLpassword';
FLUSH PRIVILEGES;

Configuring an Encrypted Connection

The below instructions cover common steps used to configure an encrypted connection from the MySQL Agent client. For detail information on secure connections and server-side configuration, refer to the Using Secure Connections🔗 section of the MySQL documentation for your version.

In order to use SSL, your MySQL server must be built with OpenSSL or yaSSL. To check whether SSL is enabled, run this query:

SHOW VARIABLES LIKE 'have_ssl';

If the query returns YES, your server can use SSL. If it returns DISABLED, the server must be started with the SSL options listed in the above mentioned section. SSL and RSA certificates and keys must also be generated in order to use SSL. Information on generating those can be found here🔗.

The client requires a client certificate and a certificate authority (CA) certificate, named client-cert.pem and ca.pem, respectively, if generated by the MySQL server. These certificates should be located in the data directory. You have to convert the client certificate into DER format. This can be performed by downloading OpenSSL and running the following command:

openssl x509 -outform DER -in client-cert.pem -out client.cert

Then, the certificates must be imported into the FglAM keystore. You can use the bundled keytool, which will be located in the Foglight Agent Manager\jre\1.8.0.72\jre\bin directory, or the equivalent on your system, with the following commands:

keytool.exe -import -file ca.pem -keystore truststore -alias mysqlServerCACert
keytool.exe -import -file client.cert -keystore keystore -alias mysqlClientCertificate

The default password for keystore is “changeit”. Next, edit the baseline.jvmargs.config file in the Foglight Agent Manager\state\default\config directory and add the following parameters with file paths and passwords appropriate for your system. Escape any quotes with a ‘\’.

vmparameter.0 = "-Djavax.net.ssl.keyStore=\"C:/Program Files/Common Files/Dell/Foglight Agent Manager/jre/1.8.0.72/jre/bin/keystore\"";
vmparameter.1 = "-Djavax.net.ssl.keyStorePassword=changeit";
vmparameter.2 = "-Djavax.net.ssl.trustStore=\"C:/Program Files/Common Files/Dell/Foglight Agent Manager/jre/1.8.0.72/jre/bin/truststore\"";
vmparameter.3 = "-Djavax.net.ssl.trustStorePassword=changeit";

Restart FglAM, then proceed with the agent configuration. Set the Use SSL option in the Agent Properties to true.

Creating and Configuring Agents

Agents can be created in one of two ways:

Using the Agent Installer Wizard

Foglight for MySQL provides a graphic, intuitive method for creating and configuring agents. This can be used instead of Foglight’s default method for creating agents and editing their properties using the Agent Status dashboard. Use the Foglight for MySQL wizard to add database instances and configure them for monitoring through a common entry point.

To run the instance installation wizard:

  1. On the Welcome page, click Monitor Databases or click Databases in the navigation pane. On a fresh installation, no instances are listed for MySQL in the Databases dashboard.
  2. On the Databases dashboard, click Monitor and select MySQL. The Agent Installer wizard opens.
  3. In the Agent Name field, enter the name of the agent to be created. This represents the database instance that the agent monitors.
  4. In the Agent Manager field, select the agent manager on which the agent should run.
    Considerations for this may include physical or virtual locality to the monitored instance, allocated resources, or grouping with other agents of the same type or monitored environment.
    If the agent package has not yet been deployed to this Agent Manager, the system will install it when you create the first agent of this type.
  5. Click Next.
  6. Enter the connection parameters to connect and monitor the database instance. For detailed information about these properties, refer to Agent Properties.
  7. Click Next.
  8. The Agent Summary displays the details about the agent properties. ClickFinish.

The database instance gets created and appears in the list of all instances.

If the agent gets created successfully but the data does not appear, then navigate to Administration > Agents > Agent Status and click the icon in the Log File column for the agent you created. In most cases, the reason for the failure will be obvious. You can also refer to the Foglight for MySQL Installation and Troubleshooting document for common errors and solutions. If the solution requires reconfiguring the agent properties, follow steps 3-7 of the Using the Agent Status Dashboard section.

Using the Agent Status Dashboard

The Agent Status page can be used to create new agents and configure and manage existing agents. To access the page from the navigation pane, click Administration > Agents > Agent Status.

To create a new agent instance:

Deploy the MySQL agent package to the FglAM before creating the agent if it has not been deployed yet. You can use the Deploy Agent Package button on the Agent Status or Agent Managers page to perform this.

  1. Navigate to Administration > Agents > Agent Status.
  2. Click Create Agent.
  3. Select the hosts to which you want to deploy agent packages.
    Considerations for this may include physical or virtual locality to the monitored instance, allocated resources, or grouping with other agents of the same type or monitored environment.
  4. Click Next.
  5. Select the MySQLAgent type. Then, select the Specify Name radio button and enter agent instance name. Click Next.
  6. Click Finish.
  7. Once the agent has been created, click the checkbox next to the MySQL agent.
  8. Click Edit Properties.
  9. Select Modify the default properties for this agent.
  10. Edit the agent properties for the MySQL agent instance:
  11. Click Activate.

To modify the properties of an existing agent, go to step 3, deactivate the agent, update the configuration, and then reactivate the agent.

Configuring Agent Properties

When an agent connects to the Foglight Management Server, it receives a set of properties used to configure its running state.

The Foglight Cartridge for MySQL includes default agent properties. Agent properties can be specific to a single agent instance or apply to multiple agents. To configure the agent properties, navigate to Administration > Agent > Agent Status. On the Agent Status page, select the checkbox for the required agent, and click Edit Properties. Click Modify the private properties for this agent to edit the properties of the selected agent or click Modify the properties for all MySQLAgent agents to edit the properties of all MySQL agents.

This section includes the following key areas:

Setting Connection (mandatory)

  • Database Host - Host where MySQL server is running. Enter a hostname or IP address. Default value is localhost.

  • Database Port - The port on which the MySQL database is running. Default is 3306.

  • Database Name - The name of a valid MySQL database that the user is permitted to connect to. This is used for connection purposes only; all non-system databases will still be monitored.

  • Database User - The user that can connect to the MySQL server being monitored.

  • Database Password - User password that can connect to the MySQL database being monitored.

  • SSLMode - Specifies the security level for encrypting the connection to the MySQL server. The following values are allowed:

    • DISABLED - Establish unencrypted connections
    • PREFERRED - (default) Establish encrypted connections if the server supports them; otherwise, use unencrypted connections.
    • REQUIRED - Establish secure connections if the server supports them; otherwise, fail the connection.
    • VERIFY_CA - Similar to “REQUIRED,” but also verifies the server TLS certificate against the configured Certificate Authority (CA) certificates.
    • VERIFY_IDENTITY - Similar to “VERIFY_CA,” but also verifies that the server certificate matches the host to which the connection is being attempted.

    For more information about enabling SSL, refer to Configuring an Encrypted Connection.

  • Allow Public Key Remote Retrieval - Allows the client to retrieve the public key from the server to secure the connection. Enable this option if an SSL connection is not configured and the public key is not available locally.

  • Local Public Key File Path - The file path to the public key for the MySQL server that the agent is attempting to connect to. The public key must be locally accessible by the agent, which is hosted on the FglAM machine. Using this option can prevent public key retrieval and reduce the risk of potential man-in-the-middle (MITM) interception.

  • Use Cleartext - Enables the cleartext authentication method, which sends the unhashed password to the MySQL server. It is recommended to enable SSL when using this method.

Setting Administration (optional)

  • Enable Administration - Set to true in order to use the MySQL Administration Panel for this agent. See the MySQL Administration Panel for more information. Default value is false.
  • Enable Explain Plans - Set to true in order to enable Explain Plan requests from the Administration Panel and Statement Digests pages. Default value is false.
  • Set SQL Mode to IGNORE_SPACE for Explain Plans - Set to true to enable this mode during Explain Plan requests. This is only relevant if the SQL contains certain reserved function names without surrounding whitespace. For more information, refer to Function Name Parsing and Resolution🔗 section of the MySQL documentation for your version.
  • Admin User - User that can perform administrative actions on the monitored MySQL server. This user must have the required privileges to execute the actions; otherwise, the actions will fail.
  • Admin Password - Password of the user that can perform administrative actions on the monitored MySQL server.

Setting Replication (optional)

  • Monitor Source Status - Set to true to monitor the Source replication status on the MySQL server.
  • Monitor Replica Status - Set to true to monitor the Replica replication status on the MySQL server.
  • Monitor Remote Replica Status - Set to true to monitor only the replica replication status on the secondary replication server referenced by the parameters below.
  • Database Host - Host where Replica Server is running.
  • Database Port - Port on which the Replica Server is listening.
  • Database Name - Name of the Replica Server database to be monitored.
  • Database User - User that can connect to the Replica Server database being monitored.
  • Database Password - Password of the user that can connect to the Replica Server database being monitored.
  • Use SSL - Requires the connection to the MySQL server to use SSL/TLS. For more information on enabling SSL, refer to the Configuring an Encrypted Connection.
  • Allow Public Key Remote Retrieval - Allows the client to retrieve the public key from the server in order to secure the connection. This must be enabled if an SSL connection is not configured and the public key is not available locally.
  • Local Public Key File Path - The file path to the public key for the MySQL server that the agent is attempting to connect with. The public key must be locally accessible by the agent, which is hosted on the FglAM machine. This can be used instead of allowing public key retrieval, preventing possible MITM interception.
  • Use Cleartext - Enables the cleartext authentication method, which sends the unhashed password to the MySQL server. It is recommended to enable SSL while using this method.

Setting Collection Intervals (optional)

The Collection Interval list in the agent properties is used to set the sample frequencies (in seconds) for the various collections performed by the agent. Default settings are provided in the DefaultIntervals list installed with the cartridge, but can be copied and modified by the user. Collections can be disabled by setting the interval to 0.

  • Blocked Transactions - Default value is set to 60 seconds.
  • Buffer Pool (i.e. Innodb_Buffer_Pool) - Default value is set to 300 seconds.
  • Configuration Monitoring - Default value is set to 300 seconds.
  • Connection Status - Default value is set to 60 seconds.
  • Database Information - Default value is set to 300 seconds.
  • Database Stats - Default value is set to 300 seconds.
  • Failed Logins - If the MySQL server version is 5.6.0+ and the performance_schema engine is enabled, the agent will collect information on failed login attempts at this interval, shown on the Connections dashboard. Default value is set to 300 seconds.
  • Galera - Default value is set to 60 seconds.
  • Handler - Default value is set to 300 seconds.
  • Index Structure - Default value is set to 3600 seconds.
  • Index/Table-level Compression - Default value is set to 1800 seconds.
  • InnoDB Compression - Default value is set to 300 seconds.
  • InnoDB Engine (i.e. Innodb_Storage_Engine) - Default value is set to 300 seconds.
  • Joins - Default value is set to 300 seconds.
  • Key Buffer - Default value is set to 300 seconds.
  • Network Interface - Default value is set to 300 seconds.
  • Query Cache - Default value is set to 300 seconds.
  • Replication - Default value is set to 300 seconds.
  • Sort Buffer - Default value is set to 300 seconds.
  • Tables - Default value is set to 1800 seconds.
  • Table Locks - Default value is set to 60 seconds.
  • Thread Pool - Default value is set to 300 seconds.
  • Top Sessions - Default value is set to 60 seconds.
  • Transaction Log (i.e. Innodb_Transaction_Log) - Default value is set to 1800 seconds.
  • Users - Default value is set to 3600 seconds.

Setting Statements (optional)

  • Statements - If the MySQL server version is 5.6.5+ and the performance_schema engine is enabled, the agent will collect statement digest information at this interval. Default value is set to 300 seconds.
  • # of Top Statements - Number of statements that will be collected, ordered by the Sort By property. Default value is 1000.
  • Sort By - Ordering parameter for statement collection limit. Default is Total Executions.

Setting Group Replication/InnoDB Cluster (optional)

  • Enable Monitoring - Set to True to enable this collection.
  • Collection Interval (sec) - Frequency of collection sample. Default is 120 seconds.
  • Member Host Aliases - Use this property list to modify the host names of other members in the InnoDB cluster collected from the monitored server. You can adjust these for display purposes or to reconcile host names if a different name is collected from other monitored member servers.

Setting Slow Query Log (optional)

  • Monitor Slow Query Log - Set to True to enable this collection.
  • File Access - If the MySQL Agent is running locally on the same machine as the MySQL server it is monitoring, you may select Local. The other option, Remote_SSH, allows an SSH connection to a remote server in order to retrieve the slow query log.
  • Collection Period - Specify how often the data collection occurs, in seconds. Set this value to at least 30–60 minutes or longer for optimal results. Unique queries are currently aggregated only within their respective collection periods, so using a longer collection period allows for a more accurate analysis of a unique query without requiring navigation through multiple data collections.
  • Line Limit - Restrict the number of lines read from the slow query log during each collection. Set this parameter to 0 unless an unusually large amount of data is being written to the slow query log, in which case you should consider increasing the long_query_time parameter. Setting this parameter to -1 causes the agent to read all prior data during its first collection instead of marking the current file position and reading from that point during subsequent collections.
  • File Path - File path to slow query log directory.
  • File Name - File name of slow query log. Optionally, for Local collection only, a regex string may be used and the most recently modified file matching the regex string name will be used.

If using remote file access, the following values must be provided:

  • Remote Hostname or IP - A hostname or IP address valid from the location of MySQL Agent.
  • SSH User - An SSH user with access to the location of slow query log location and file permissions to perform a read action.
  • SSH Password - The password for the provided SSH user.

Setting Additional Options (optional)

  • Use Basic Table Collection - Enabling this option causes the agent to submit data for MySQL tables as ComplexObservation samples instead of persistent objects, reducing storage usage in the FMS repository. The agent still collects and displays the same data on the Tables page, and historical data remains accessible. However, previous metrics are treated as snapshot values rather than being graphed.
  • Enable Dynamic Memory Allocation - Allows the agent to request more memory from the FglAM when monitoring a server with over 10,000 tables. Default value is false.
  • Agent Host Name - The hostname alias used for monitoring purposes that is different than the one provided by the MySQL server. If using the Infrastructure cartridge for OS monitoring, this name should match any alias provided for OS monitoring in order to link the data correctly.
  • Server Time Zone Override - Allows the client to override the server time zone, usually for the purpose of correcting a java-incompatible time zone ID. A list of acceptable time zone IDs can be found here🔗.
  • Client Time Zone Override - Allows the client to override the time zone used for time-based performance data. A list of acceptable time zone IDs can be found here🔗.
  • Password Character Encoding - Allows you to change the character encoding for the password provided to the MySQL server. Default value is UTF-8.
  • Exclude DB Table/Index Collections - Allows you to disable Table and index collections for individual databases. This will decrease resource usage by the agent and used space in the FMS repository. By default, the list is populated with system databases.
  • Skip Startup Connection Test - When you activate the agent for the first time, it performs a connection test to verify connectivity with the MySQL server. If the agent cannot establish a connection, it assumes a persistent failure due to incorrect configuration or other issues, and it will display as failed until resolved and restarted. If the agent and the MySQL server are on the same machine and you expect the agent to activate before the server becomes accessible (for example, after a restart), you may want to disable this test. To skip the connection test, set this option to true.
  • Minimum TRX Block Time (sec) - This is the minimum wait time for a transaction to be considered deadlocked and eligible for inclusion in the Blocked Transactions collection. The default time is 15 seconds.
  • Include Views in Table Collection - Option to include Views in the table collection. Default value is false.
  • Use Alternate MariaDB JDBC Driver - This changes the driver used by the agent from the MySQL/J connector to the MariaDB driver. This driver can be used for both MariaDB and MySQL and may serve as a workaround if issues arise with the standard MySQL/J connector.
  • Enable MariaDB trustServerCertificate - Enables a driver option with the MariaDB driver that allows the client to trust the identity of the server it is connecting to, without requiring a valid certificate. This can be enabled as a workaround for certificate issues with minimal risk, as no sensitive data will be transmitted over the connection to the target server.