Supported Versions
MySQL Server 2008 or higher
JDBC: 8.0.17
PrivilegeDescriptionExample
SELECTMinimum privilege to monitor all G2 templatesGRANT SELECT ON *.* TO 'username'@'%';

This describes the discovery and monitoring setup for the MySQL database.

Prerequisites

  • Database and port(3306) can be accessed by the gateway.
  • A gateway management profile mapped to a resource.
  • Create credentials with type as Database before assigning a template to a resource. The Port, Connection Time Out, and Is Secure values are not used while creating credentials.

Database discovery and monitoring

The administrator can deploy an agent or gateway to support MySQL server agent-based or agentless monitoring respectively.

Discovery with the agent

The agent auto-discovers the MySQL database and collects the following metric information:

  • MySQL-Engine information
  • MySQL-Recommendations
  • MySQL-Database information
  • MySQL-Performance information
  • MySQL-Slave information
  • MySQL-Server information
  • MySQL-Variable information
  • MySQL-Status information
MySQL Dashboard

You can also apply agent-based templates to initiate monitoring.

Discovery with the gateway

The gateway establishes a connection to the database using Java Database Connectivity (JDBC) Java API and collects monitoring metrics using SQL queries. To monitor the MySQL database, install gateway version 5.0.0 or later.

Use one of the following mechanisms to discover MySQL servers to add them to your inventory:

  • WMI
  • SSH
  • SNMP

Optionally, add a database server manually to the infrastructure inventory:

  1. Select Infrastructure and click Resources.
  2. Click the Settings icon on the top-right and click Add.
  3. On the Add Device page, enter the Device Type and any other information you want to enter.
  4. Click Save.

Prepare the device to start monitoring

  1. Associate appropriate database credentials to the discovered database.
  2. Assign one or more database-specific global monitors or templates on the device.
    Optionally, create custom metrics or monitors using SQL queries and assign them to the database.

To track monitoring of MySQL database in your cloud environment, see Monitoring Cloud Database using Gateway.

Create custom metrics

To create a metric using SQL query for MySQL monitoring, navigate to Monitoring > Metrics > Create Metric. For example, to create a metric to check the active transactions use:

SELECT count(*) from performance_schema.events_transactions_current where state='ACTIVE'

For more information, see Create a Metric.

Assign templates from setup

Assign MySQL templates to one or more resources for a selected client and modify the configuration parameters while assigning templates. For more information, see Assign Templates from Setup.

Assign templates at the device level

Applying MySQL templates at the device level helps you to assign one or more templates to a specific resource. You can change the configuration parameter default values while assigning the templates. For more information, see Assign Templates at the Device Level.

Template configuration parameters:

Configuration ParameterDescription
Connection Time OutThe maximum time to connect to the database. The driver waits for the specified time before timing out due to connection failure. The default time is 15000 milliseconds(ms).
Service Transport TypeTo configure the database at a secure end-point. The default type is In-secure. The connection is Secure when the data sent between the client and server is encrypted.
Service PortThe port number where the database is running. The connection is made to the specified port if you specify the port and the database name. The default port is 3306.
DB Instance NameThe name of the database to connect to. The default name is root.
  • If you have multiple database names with different ports, use the following syntax: `DBname1:Port1, DBname2:Port2,..` (Comma-separated). Create separate credential sets on the resource if databases consist of different users. For example, if DBname 1 has credential set A and DBname 2 has credential set B, create separate credentials sets on the resource.
  • If you have multiple database names with the same port, use the following syntax: `DBname1, DBname2,..` (Comma-separated).

Assign template from device management policies

Device management policies help manage resources. You can assign monitoring templates, knowledge base articles, and custom attributes using device management policies. The device management policy can be applied to one or a set of resources. For more information, see Create Policies.

View resource metrics

The gateway monitors the application using the assigned templates and displays the results in graphical format. To view resource metrics, click the database resource name > resource Details > Metrics.

MySQL

Troubleshooting

If you have MySQL monitoring issues, verify gateway, telnet, and database connectivity:

ping <IP Address>
telnet <IP Address> <Port>
gcli
db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <ReadTimeout> <Secure Flag> <Query>

Beginning with gateway version 5.3.0, use the following format to execute the last command, including the result-set:

db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <Read Timeout> <Secure Flag> <ResultSet?: Yes/No> <Query>

Global templates

Collector TypeTemplate Name
AgentMySQL Global Performance Statistics
MySQL InnoDB Statistics
MySQL Variable Statistics
GatewayAdvanced Cloud Database Template for MySQL
Advanced Cloud MySQL Database Command Template
Advanced Cloud MySQL Database Connection Template
Advanced Cloud MySQL Database innoDB Template
Advanced Cloud MySQL Database Miscellaneous Template
Advanced Cloud MySQL Database Network Template
Advanced Cloud MySQL Database Table Template
Advanced Cloud MySQL Database Threads Template
G2 MySQL Database Command Template
G2 MySQL Database Connection Template
G2 MySQL Database innoDB Template
G2 MySQL Database Miscellaneous Template
G2 MySQL Database Network Template
G2 MySQL Database Table Template
G2 MySQL Database Threads Template

Global metrics

MetricMetric Display NameUnit
mysql.innodb.row.lock.waits

Total number of times the InnoDB table operations are kept on hold due to a row lock.
MySQL Innodb Row Lock waitscount
mysql.aborted.clients

The number of connected aborted when the client missed to close the connection appropriately.
MySQL Aborted Clientscount
mysql.aborted.connects

The number of failed attempts to connect to the MySQL server.
MySQL Aborted Connectscount
mysql.active.transactions

Number of active transactions
MySQL Active Transactionscount
mysql.bytes.received

The number of bytes received from all clients.
MySQL Bytes Receivedbytespersec(bps)
mysql.bytes.sent

The number of bytes sent to all clients.
MySQL Bytes Sentbytespersec(bps)
mysql.connected.threads.count

The number of MySQL connected threads.
MySQL Connected Threads count-
mysql.created.threads

The number of threads created to handle connections.
MySQL Created Threadscount
mysql.createdatabase.commands

The number of times CREATE DATABASE command is executed
MySQL Createdatabase Commandspersecond(psec)
mysql.createtable.commands

The number of times CREATE TABLE command is executed
MySQL CreateTable Commandspersecond(psec)
mysql.createuser.commands

The number of times CREATE USER command is executed
MySQL Createuser Commandspersecond(psec)
mysql.delete.commands

The number of times Delete command is executed
MySQL Delete Commandpersecond(psec)
mysql.disk.temporarytables.created

The rate of temporary tables created in seconds.
MySQL Disk Temporary Tables Createdpersecond(psec)
mysql.dropdatabase.commands

The number of times DROP DATABASE command is executed
MySQL DropDatabase Commandspersecond(psec)
mysql.droptable.commands

The number of times DROP TABLE command is executed
MySQL DropTable Commandpersecond(psec)
mysql.dropuser.commands

The number of times DROP USER command is executed.
MySQL Dropuser Commandspersecond(psec)
mysql.innodb.bufferpool.dirtypages

The current number of dirty pages in the InnoDB buffer pool.
Mysql Innodb Bufferpool Dirtypagescount
mysql.innodb.bufferpool.pagesfree

The number of free pages in the InnoDB Buffer Pool.
MySQL Innodb Buffer Pool Pages freecount
mysql.innodb.bufferpool.pagestotal

The total number of pages in the InnoDB Buffer Pool.
Mysql Innodb Bufferpool Pagestotalcount
mysql.innodb.bufferpool.utilization

The utilization of the InnoDB Buffer Pool.
MySQL Innodb Bufferpool Utilizationpercentage(%)
mysql.innodb.buffer_pool_reads

The number of logical reads that InnoDB reads directly from the disk and not from the buffer pool.
MySQL Innodb Bufferpool Readscount
mysql.innodb.buffer_pool_read_requests

The number of reads added to the InnoDB buffer pool.
MySQL Innodb Bufferpool Read Requestscount
mysql.innodb.buffer_pool_write_requests

The number of writes added to the InnoDB buffer pool.
MySQL Innodb Bufferpool Write Requestscount
mysql.innodb.data.pending.reads

The current number of pending reads.
MySQL Innodb Data Pending Readscount
mysql.innodb.data.pending.writes

The total number of data writes.
MySQL Innodb Data Pending Writescount
mysql.innodb.data.reads

The total number of data reads (OS file reads).
MySQL Innodb Data Readscount
mysql.innodb.data.writes

The total number of data writes.
MySQL InnoDB Data Writescount
mysql.innodb.row.lock.time

The total time spent in acquiring row locks for InnoDB tables in milliseconds.
mysql Innodb Row Lock Timemilliseconds (ms)
mysql.innodb.row.lock.waits

The number of times operations on InnoDB tables waits for a row lock.
MySQL Innodb Row Lock Waitscount
mysql.insert.commands

The number of times Insert command is executed.
MySQL Insert Commandpersecond(psec)
mysql.internal.connection.errors

The number of connections rejected due to internal errors in the server such as failure to start a new thread or an out-of-memory condition.
MySQL Internal Connection Errorscount
mysql.max.connections.connection_errors

The number of connections rejected due to internal server errors.
MySQL Max Connections Connection Errorscount
mysql.open.files

function cannot be counted.
MySQL Open Filescount
mysql.open.tables

The number of tables opened with my_open().
MySQL Open Tablescount
mysql.performance.table_locks_immediate

The number of times a table lock request is granted immediately.
MySQL Performance Table Locks Immediatecount
mysql.rollback.commands

The number of times RollBack command is executed
MySQL Rollback Commandpersecond(psec)
mysql.running.threads

The number of threads that are not sleeping.
MySQL Running Threadscount
mysql.select.commands

The number of times SELECT command is executed.
MySQL Select Commandpersecond(psec)
mysql.server.connections

The number of connection attempts to the MySQL server.
MySQL Server Connectionscount
mysql.slow.queries

The total number of slow queries that exceeded the long_query_time.
MySQL Slow Queries
mysql.ssl.client.connects

The number of SSL connection attempts to an SSL-enabled master.
MySQL SSL Client Connectscount
mysql.streams.open

The number of streams that are open (used mainly for logging).
MySQL Streams Opencount
mysql.table.locks_immediate

The number of times a table lock request is granted immediately.
MySQL Table Locks Immediatecount
mysql.temporary.files.created

The rate of temporary files created in seconds.
MySQL Temporary Files Createdpersecond(psec)
mysql.threads.created

The number of threads created to handle connections. Increase the thread_cache_size value, when the Threads_created increases.
MySQL Threads Createdcount
mysql.threads.stacksize

The stack size for each thread. Majority of the limits detected by the crash-me test are dependent on this value.
MySQL Threads Stacksize-
mysql.update.commands

The number of times UPDATE command is executed.
MySQL Update Commandspersecond (psec)