|MySQL Server 2008 or higher
|Minimum privilege to monitor all G2 templates
|GRANT SELECT ON *.* TO 'username'@'%';
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.
- 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-Database information
- MySQL-Performance information
- MySQL-Slave information
- MySQL-Server information
- MySQL-Variable information
- MySQL-Status information
You can also apply agent-based templates to initiate monitoring.
IPAddress of the server where mysql is running.
Port on which mysql is running.
Username to connect to mysql.
Password of the given username.
Note: All field attributes are mandatory, use default values wherever applicable.
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.
The number of currently open connections.
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:
Optionally, add a database server manually to the infrastructure inventory:
- Select Infrastructure and click Resources.
- Click the Settings icon on the top-right and click Add.
- On the Add Device page, enter the Device Type and any other information you want to enter.
- Click Save.
Prepare the device to start monitoring
- Associate appropriate database credentials to the discovered database.
- 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:
|The 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 Type
|To 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.
|The 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 Name
|The name of the database to connect to. The default name is root.
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.
If you have MySQL monitoring issues, verify gateway, telnet, and database connectivity:
ping <IP Address>
telnet <IP Address> <Port>
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>
|MySQL Global Performance Statistics
|MySQL InnoDB Statistics
|MySQL Variable Statistics
|Advanced 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
|Metric Display Name
MySQL Slave behind master in seconds
|MySQL Slave behind Master
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
0 - if slave is not running.
1 - if slave is running.
2 - if slave NOT configured.
|MySQL Replication Status
0 - if slave SQL is not running.
1 - if slave SQL is running.
2 - if slave SQL NOT configured.
|MySQL Slave SQL Running Status
0 - if slave IO is not running.
1 - if slave IO is running.
2 - if slave IO NOT configured.
|MySQL Slave IO Running Status
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
Total number of times the InnoDB table operations are kept on hold due to a row lock.
|MySQL Innodb Row Lock waits
The number of connected aborted when the client missed to close the connection appropriately.
|MySQL Aborted Clients
The number of failed attempts to connect to the MySQL server.
|MySQL Aborted Connects
Number of active transactions
|MySQL Active Transactions
The number of bytes received from all clients.
|MySQL Bytes Received
The number of bytes sent to all clients.
|MySQL Bytes Sent
The number of MySQL connected threads.
|MySQL Connected Threads count
The number of threads created to handle connections.
|MySQL Created Threads
The number of times CREATE DATABASE command is executed
|MySQL Createdatabase Commands
The number of times CREATE TABLE command is executed
|MySQL CreateTable Commands
The number of times CREATE USER command is executed
|MySQL Createuser Commands
The number of times Delete command is executed
|MySQL Delete Command
The rate of temporary tables created in seconds.
|MySQL Disk Temporary Tables Created
The number of times DROP DATABASE command is executed
|MySQL DropDatabase Commands
The number of times DROP TABLE command is executed
|MySQL DropTable Command
The number of times DROP USER command is executed.
|MySQL Dropuser Commands
The current number of dirty pages in the InnoDB buffer pool.
|Mysql Innodb Bufferpool Dirtypages
The number of free pages in the InnoDB Buffer Pool.
|MySQL Innodb Buffer Pool Pages free
The total number of pages in the InnoDB Buffer Pool.
|Mysql Innodb Bufferpool Pagestotal
The utilization of the InnoDB Buffer Pool.
|MySQL Innodb Bufferpool Utilization
The number of logical reads that InnoDB reads directly from the disk and not from the buffer pool.
|MySQL Innodb Bufferpool Reads
The number of reads added to the InnoDB buffer pool.
|MySQL Innodb Bufferpool Read Requests
The number of writes added to the InnoDB buffer pool.
|MySQL Innodb Bufferpool Write Requests
The current number of pending reads.
|MySQL Innodb Data Pending Reads
The total number of data writes.
|MySQL Innodb Data Pending Writes
The total number of data reads (OS file reads).
|MySQL Innodb Data Reads
The total number of data writes.
|MySQL InnoDB Data Writes
The total time spent in acquiring row locks for InnoDB tables in milliseconds.
|mysql Innodb Row Lock Time
The number of times operations on InnoDB tables waits for a row lock.
|MySQL Innodb Row Lock Waits
The number of times Insert command is executed.
|MySQL Insert Command
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 Errors
The number of connections rejected due to internal server errors.
|MySQL Max Connections Connection Errors
function cannot be counted.
|MySQL Open Files
The number of tables opened with my_open().
|MySQL Open Tables
The number of times a table lock request is granted immediately.
|MySQL Performance Table Locks Immediate
The number of times RollBack command is executed
|MySQL Rollback Command
The number of threads that are not sleeping.
|MySQL Running Threads
The number of times SELECT command is executed.
|MySQL Select Command
The number of connection attempts to the MySQL server.
|MySQL Server Connections
The total number of slow queries that exceeded the long_query_time.
|MySQL Slow Queries
The number of SSL connection attempts to an SSL-enabled master.
|MySQL SSL Client Connects
The number of streams that are open (used mainly for logging).
|MySQL Streams Open
The number of times a table lock request is granted immediately.
|MySQL Table Locks Immediate
The rate of temporary files created in seconds.
|MySQL Temporary Files Created
The number of threads created to handle connections. Increase the thread_cache_size value, when the Threads_created increases.
|MySQL Threads Created
The stack size for each thread. Majority of the limits detected by the crash-me test are dependent on this value.
|MySQL Threads Stacksize
The number of times UPDATE command is executed.
|MySQL Update Commands