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

Introduction

MySQL is an open source relational database management system (RDBMS) based on Structured Query Language (SQL).

When we apply the template from the “monitors” tab in device details page, we will get a second page to input the values for the required variables before saving the template.

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

Collector Type: Agent

Category: Application Monitors

Application Name: MySQL

Global Template Name: LINUX MySQL Template

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.

Parameters

NameDefault Value
MySQL IPAddress

IPAddress of the server where mysql is running.
127.0.0.1
MySQL Port

Port on which mysql is running.
3306
MySQL Username

Username to connect to mysql.
NA
MYSQL Password

Password of the given username.
NA

Note: All field attributes are mandatory, use default values wherever applicable.

Collected Metrics

Metric NameDisplay Name
mysql.trafficMySQL-Traffic
mysql.slave_status

If a database has replication configured, then this monitor checks the status of the slave instance which is returned by the command SHOW SLAVE STATUS. It also checks the number of seconds the slave is behind the master and if IO and SQL is running on the slave.
MySQL-SlaveStatus
mysql.select_statisticsMySQL-SelectStatistics
mysql.command_statisticsMySQL-CommandStatistics
mysql.threadsMySQL-Threads
mysql.threads_connected

The number of currently open connections.
MySQL-ThreadsConnected

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 change 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>

Supported 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

Supported metrics

MetricMetric Display NameUnit
mysql_slave_Seconds_Behind_Master

MySQL Slave behind master in seconds
MySQL Slave behind MasterSeconds (s)
mysql_slave_IO_State

Input/output state of slave, returns
1- if io state is "Checking master version".
2 - if io state is "Connecting to master".
3 - if io state is "Queueing master event to the relay log".
4 - if io state is "Reconnecting after a failed binlog dump request".
5 - if io state is "Reconnecting after a failed master event read".
6 - if io state is "Registering slave on master".
7 - if io state is "Requesting binlog dump".
8 - if io state is "Waiting for its turn to commit".
9 - if io state is "Waiting for master to send event".
10 - if io state is "Waiting for master update".
11 - if io state is "Waiting for slave mutex on exit".
12 - if io state is "Waiting for the slave SQL thread to free enough relay log space".
13 - if io state is "Waiting to reconnect after a failed binlog dump request".
14 - if io state is "Waiting to reconnect after a failed master event read".
MySQL Slave IO State
mysql_Replication_Status

Returns
0 - if slave is not running.
1 - if slave is running.
2 - if slave NOT configured.
MySQL Replication Status
mysql_slave_Sql_Running_Status

Returns
0 - if slave SQL is not running.
1 - if slave SQL is running.
2 - if slave SQL NOT configured.
MySQL Slave SQL Running Status
mysql_slave_IO_Running_Status

Returns
0 - if slave IO is not running.
1 - if slave IO is running.
2 - if slave IO NOT configured.
MySQL Slave IO Running Status
mysql_slave_Sql_Running_State

The Running state of slave SQL service, returns
1 - if sql state is "Making temporary file (append) before replaying LOAD DATA INFILE".
2 - if sql state is "Making temporary file (create) before replaying LOAD DATA INFILE".
3 - if sql state is "Reading event from the relay log".
4 - if sql state is "Slave has read all relay log; waiting for more updates".
5 - if sql state is "Waiting for an event from Coordinator".
6 - if sql state is "Waiting for slave mutex on exit".
7 - if sql state is "Waiting for Slave Workers to free pending events".
8 - if sql state is "Waiting for the next event in relay log".
9 - if sql state is "Waiting until MASTER_DELAY seconds after master executed event".
Note: Not applicable to MySQL v5.6
MySQL Slave IO State
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)