MySQL monitoring integration

Our MySQL integration collects and sends inventory and metrics from your MySQL database to our platform, where you can see the health of your database server and analyze metric data so that you can easily find the source of any problems.

Read on to install the integration, and to see what data we collect.

Compatibility and requirements

Our integration is compatible with MySQL version 5.6 or higher.

Before installing the integration, make sure that you meet the following requirements:

A New Relic account. Don't have one? Sign up for free! No credit card required.
If MySQL is not running on Kubernetes or Amazon ECS, you can install the infrastructure agent on a Linux or Windows OS host or on a host capable of remotely accessing where MySQL is installed. Otherwise:
- If running on Kubernetes, see these requirements.
- If running on ECS, see these requirements.

Important

For MySQL v8.0 and higher we do not support the following metrics: cluster.slaveRunning, db.qCacheFreeMemoryBytes, db.qCacheHitRatio, db.qCacheNotCachedPerSecond.

Quick start

Instrument your MySQL database quickly and send your telemetry data with guided install. Our guided install creates a customized CLI command for your environment that downloads and installs the New Relic CLI and the infrastructure agent.

Ready to get started? Click one of these button to try it out.

Guided install

EU Guided install

Our guided install uses the infrastructure agent to set up the MySQL integration. Not only that, it discovers other applications and log sources running in your environment and then recommends which ones you should instrument.

The guided install works with most setups. But if it doesn't suit your needs, you can find other methods below to get started monitoring your MySQL database.

Install and activate

To install the MySQL integration, follow the instructions for your environment:

Follow the instructions for installing an integration, using the file name nri-mysql.
Change the directory to the integration's folder.
```
cd /etc/newrelic-infra/integrations.d
```

Copy the sample configuration file:

sudo cp mysql-config.yml.sample mysql-config.yml

Edit the configuration file mysql-config.yml as explained in the next section.
Restart the infrastructure agent.

Download the nri-mysql .MSI installer image from:
https://download.newrelic.com/infrastructure_agent/windows/integrations/nri-mysql/nri-mysql-amd64.msi

To install from the Windows command prompt, run:

msiexec.exe /qn /i PATH\TO\nri-mysql-amd64.msi

In the Integrations directory, C:\Program Files\New Relic\newrelic-infra\integrations.d\, create a copy of the sample configuration file by running:
```
cp mysql-config.yml.sample mysql-config.yml
```
Edit the mysql-config.yml file as described in the configuration settings.
Restart the infrastructure agent.

Additional notes:

Advanced: Integrations are also available in tarball format to allow for install outside of a package manager.
On-host integrations do not automatically update. For best results, regularly update the integration package and the infrastructure agent.

Configuration

Enabling your MySQL Server

To capture data from the MySQL integration, you must first create a MySQL user with replication and select permissions to allow the integration to fetch metrics:

From the command line, create a user newrelic@localhost with a specific password.

sudo mysql -e "CREATE USER 'newrelic'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD' WITH MAX_USER_CONNECTIONS 5;"

Grant replication privileges to newrelic@localhost with a maximum of 5 connections.

sudo mysql -e "GRANT REPLICATION CLIENT ON *.* TO 'newrelic'@'localhost' WITH MAX_USER_CONNECTIONS 5;"

Grant privileges to newrelic@localhost with a maximum of 5 connections.

sudo mysql -e "GRANT SELECT ON *.* TO 'newrelic'@'localhost' WITH MAX_USER_CONNECTIONS 5;"

Configure the integration

There are several ways to configure the integration, depending on how it was installed:

If enabled via Kubernetes: see Monitor services running on Kubernetes.
If enabled via Amazon ECS: see Monitor services running on ECS.
If installed on-host: edit the config in the integration's YAML config file, nginx-config.yml.

An integration's YAML-format configuration is where you can place required login credentials and configure how data is collected. Which options you change depend on your setup and preference.

The configuration file has common settings applicable to all integrations like interval, timeout, inventory_source. To read all about these common settings refer to our Configuration Format document.

Important

If you are still using our Legacy configuration/definition files please refer to this document for help.

The MySQL integration collects both Metrics and Inventory information. In the table, use the Applies To column for the settings available to each collection:

MySQL Instance Settings

The MySQL integration collects both Metrics and Inventory information. In the table, use the Applies To column for the settings available to each collection:

Setting	Description	Default	Applies To
HOSTNAME	Hostname or IP where MySQL is running.	localhost	Metrics/Inventory
PORT	Port on which MySQL server is listening.	3306	Metrics/Inventory
USERNAME	Username for accessing the MySQL server.	N/A	Metrics/Inventory
PASSWORD	Password for the given user.	N/A	Metrics/Inventory
SOCKET	Path to Unix socket file on which MySQL server is listening. Use this instead of Hostname/Port.	N/A	Metrics/Inventory
DATABASE	Name of the database to be monitored. If omitted all databases will be monitored.	N/A	Metrics/Inventory
USE_TLS	Use TLS when communicating with the MySQL server.	false	Metrics/Inventory
INSECURE_SKIP_VERIFY	Disable server name verification when connecting over TLS.	false	Metrics/Inventory
OLD_PASSWORDS	Use old password hashing method.	false	Metrics/Inventory
EXTRA_CONNECTION_URL_ARGS	Specify extra connection parameters as attr1=val1&attr2=val2.	N/A	Metrics/Inventory
EXTENDED_METRICS	Captures an extended set of metrics. This also enables the capture of slave metrics.	false	Metrics
EXTENDED_INNODB_METRICS	Captures additional innodb metrics.	false	Metrics
EXTENDED_MY_ISAM_METRICS	Captures additional MyISAM metrics.	false	Metrics
REMOTE_MONITORING	Enable multi-tenancy monitoring.	true	Metrics/Inventory
METRICS	Set to `true` to enable metrics-only collection.	false
INVENTORY	Set to `true` to enable inventory-only collection.	false

The values for these settings can be defined in several ways:

Add the value directly to the configuration file. This is the most common way.
Replace the values from environment variables using the {{}} notation. This requires infrastructure agent 1.14.0+. For more on this, see more on infrastructure agent passthrough environment variables.
Use secrets management to protect sensible information, such as passwords, so that it's not exposed in plain text on the configuration file. For more information, see secrets management.

Labels and custom attributes

You can also decorate your metrics with labels. Labels allow you to add key/value pair attributes to your metrics so that you can query, filter, or group your metrics.

Even though our default sample configuration file includes examples of labels, they're optional. You can remove, modify, or add new ones.

labels:
   env: production
   role: load_balancer

Example configurations

This is the basic configuration used to collect metrics and inventory from your localhost:

integrations:
  - name: nri-mysql
    env:
      HOSTNAME: localhost
      PORT: 3306
      USERNAME: mysql_user
      PASSWORD: mysql_password
      REMOTE_MONITORING: true
    interval: 30s
    labels:
      environment: production
    inventory_source: config/mysql

This configuration collects metrics every 30 seconds and inventory every 60 seconds:

integrations:
  - name: nri-mysql
    env:
      METRICS: true
      HOSTNAME: localhost
      PORT: 3306
      USERNAME: mysql_user
      PASSWORD: mysql_password
      REMOTE_MONITORING: true
    interval: 30s
    labels:
      environment: production

  - name: nri-mysql
    env:
      INVENTORY: true
      HOSTNAME: localhost
      PORT: 3306
      USERNAME: mysql_user
      PASSWORD: mysql_password
      REMOTE_MONITORING: true
    interval: 60s
    labels:
      environment: production
    inventory_source: config/mysql

Use to connect to MySQL with TLS. You can add TLS_INSECURE_SKIP_VERIFY : true to disable the server name verification:

integrations:
  - name: nri-mysql
    env:
      METRICS: true
      HOSTNAME: localhost
      PORT: 3306
      USERNAME: mysql_user
      PASSWORD: mysql_password
      USE_TLS: true
      REMOTE_MONITORING: true
    interval: 30s
    labels:
      environment: production

Use to connect to MySQL using a socket file. This is an alternative to a TCP Hostname/Port connection:

integrations:
  - name: nri-mysql
    env:
      METRICS: true
      USERNAME: mysql_user
      PASSWORD: mysql_password
      SOCKET: /var/run/mysql/mysql.sock
      REMOTE_MONITORING: true
    interval: 30s
    labels:
      environment: production

Use this to collect your metrics including extended, slave, innodb and my-isam metrics:

integrations:
  - name: nri-mysql
    env:
      METRICS: true
      HOSTNAME: localhost
      PORT: 3306
      USERNAME: mysql_user
      PASSWORD: mysql_password
      EXTENDED_METRICS: true
      EXTENDED_INNODB_METRICS: true
      EXTENDED_MY_ISAM_METRICS: true
      REMOTE_MONITORING: true
    interval: 30s
    labels:
      environment: production

Use this if you need to collect metrics from two different MySQL servers using the the same integration.

integrations:
  - name: nri-mysql
    env:
      METRICS: true
      HOSTNAME: mysql_host1
      PORT: 3306
      USERNAME: mysql1_user
      PASSWORD: mysql1_password
      REMOTE_MONITORING: true
    interval: 30s
    labels:
      environment: production

  - name: nri-mysql
    env:
      METRICS: true
      HOSTNAME: mysql_host2
      PORT: 3306
      USERNAME: mysql2_user
      PASSWORD: mysql2_password
      REMOTE_MONITORING: true
    interval: 30s
    labels:
      environment: production

Find and use data

Data from this service is reported to an integration dashboard.

Metrics are attached to the MysqlSample event type. You can query this data for troubleshooting purposes or to create custom charts and dashboards.

For more on how to find and use your data, see Understand integration data.

Metric data

The MySQL integration collects the following metrics:

Default metrics

These metrics are captured by default:

Name	Description
`cluster.slaveRunning`	Boolean. `1` if this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it is `0`. For metrics reported if enabled, see replication slave metrics.
`db.handlerRollbackPerSecond`	Rate of requests for a storage engine to perform a rollback operation, per second.
`db.innodb.bufferPoolPagesData`	Number of pages in the InnoDB buffer pool containing data.
`db.innodb.bufferPoolPagesFree`	Number of free pages in the InnoDB buffer pool.
`db.innodb.bufferPoolPagesTotal`	Total number of pages of the InnoDB buffer pool.
`db.innodb.dataReadBytesPerSecond`	Rate at which data is read from InnoDB tables in bytes per second.
`db.innodb.dataWrittenBytesPerSecond`	Rate at which data is written to InnoDB tables in bytes per second.
`db.innodb.logWaitsPerSecond`	Number of times that the log buffer was too small and a wait was required for it to be flushed before continuing, in waits per second.
`db.innodb.rowLockCurrentWaits`	Number of row locks currently being waited for by operations on InnoDB tables.
`db.innodb.rowLockTimeAvg`	Average time to acquire a row lock for InnoDB tables, in milliseconds.
`db.innodb.rowLockWaitsPerSecond`	Number of times operations on InnoDB tables had to wait for a row lock per second.
`db.openedTablesPerSecond`	Number of files that have been opened with `my_open()` (a mysys library function) per second. Parts of the server that open files without using this function do not increment the count.
`db.openFiles`	Number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes.
`db.openTables`	Number of tables that are open.
`db.qCacheFreeMemoryBytes`	Amount of free memory in bytes for the query cache.
`db.qCacheHitRatio`	Percentage of queries that are retrieved from the cache.
`db.qCacheNotCachedPerSecond`	Number of noncached queries (not cacheable, or not cached due to the `query_cache_type setting`) per second.
`db.qCacheUtilization`	Percentage of query cache memory that is being used.
`db.tablesLocksWaitedPerSecond`	Number of times per second that a request for a table lock could not be granted immediately and a wait was needed.
`net.abortedClientsPerSecond`	Number of connections per second that were aborted because the client died without closing the connection properly.
`net.abortedConnectsPerSecond`	Number of failed attempts to connect to the MySQL server, per second.
`net.bytesReceivedPerSecond`	Byte throughput received from all clients, per second.
`net.bytesSentPerSecond`	Byte throughput sent to all clients, per second.
`net.connectionErrorsMaxConnectionsPerSecond`	Rate per second at which connections were refused because the server `max_connections` limit was reached.
`net.connectionsPerSecond`	Number of connection attempts per second.
`net.maxUsedConnections`	Maximum number of connections that have been in use simultaneously since the server started.
`net.threadsConnected`	Number of currently open connections.
`net.threadsRunning`	Number of threads that are not sleeping.
`query.comCommitPerSecond`	Number of COMMIT statements executed per second.
`query.comDeletePerSecond`	Number of DELETE statements executed per second.
`query.comDeleteMultiPerSecond`	Number of DELETE statements that use the multiple-table syntax executed per second.
`query.comInsertPerSecond`	Number of INSERT statements executed per second.
`query.comInsertSelectPerSecond`	Number of INSERT SELECT statements executed per second.
`query.comReplaceSelectPerSecond`	Number of REPLACE SELECT statements executed per second.
`query.comRollbackPerSecond`	Number of ROLLBACK statements executed per second.
`query.comSelectPerSecond`	Number of SELECT statements executed per second.
`query.comUpdateMultiPerSecond`	Number of UPDATE statements that use the multiple-table syntax executed per second.
`query.comUpdatePerSecond`	Number of UPDATE statements executed per second.
`query.preparedStmtCountPerSecond`	Current number of prepared statements per second. (The maximum number of statements is given by the `max_prepared_stmt_count` system variable.)
`query.queriesPerSecond`	Total number of statements executed by the server per second, including statements executed within stored programs.
`query.questionsPerSecond`	Number of statements executed by the server per second, limited to only those sent by clients.
`query.slowQueriesPerSecond`	Number of queries per second that have taken more than `long_query_time` seconds. This counter increments regardless of whether the slow query log is enabled.

Extended metrics

Additional metrics captured when extended_metrics is enabled (set to 1 in the configuration file):

Name	Description
`db.createdTmpDiskTablesPerSecond`	Number of internal on-disk temporary tables created per second by the server while executing statements.
`db.createdTmpFilesPerSecond`	Number of temporary files created per second by mysqld.
`db.createdTmpTablesPerSecond`	Number of internal temporary tables created per second by the server while executing statements.
`db.handlerDeletePerSecond`	Number of times per second that rows have been deleted from tables.
`db.handlerReadFirstPerSecond`	Number of times per second the first entry in an index was read.
`db.handlerReadKeyPerSecond`	Number of requests per second to read a row based on a key.
`db.handlerReadRndNextPerSecond`	Number of requests per second to read the next row in the data file.
`db.handlerReadRndPerSecond`	Number of requests per second to read a row based on a fixed position.
`db.handlerUpdatePerSecond`	Number of requests per second to update a row in a table.
`db.handlerWritePerSecond`	Number of requests per second to insert a row in a table.
`db.maxExecutionTimeExceededPerSecond`	Number of SELECT statements per second for which the execution timeout was exceeded.
`db.qCacheFreeBlocks`	Number of free memory blocks in the query cache.
`db.qCacheHitsPerSecond`	Number of query cache hits per second.
`db.qCacheInserts`	Number of queries added to the query cache.
`db.qCacheLowmemPrunesPerSecond`	Number of queries per second that were deleted from the query cache because of low memory.
`db.qCacheQueriesInCachePerSecond`	Number of queries per second registered in the query cache.
`db.qCacheTotalBlocks`	Total number of blocks in the query cache.
`db.selectFullJoinPerSecond`	Number of joins that perform table scans because they do not use indexes, per second.
`db.selectFullJoinRangePerSecond`	Number of joins per second that used a range search on a reference table.
`db.selectRangeCheckPerSecond`	Number of joins per second without keys that check for key usage after each row.
`db.selectRangePerSecond`	Number of joins per second that used ranges on the first table.
`db.sortMergePassesPerSecond`	Number of merge passes that the sort algorithm has had to do, per second.
`db.sortRangePerSecond`	Number of sorts per second that were done using ranges.
`db.sortRowsPerSecond`	Number of sorted rows per second.
`db.sortScanPerSecond`	Number of sorts that were done by scanning the table, per second.
`db.tableOpenCacheHitsPerSecond`	Number of hits per second for open tables cache lookups.
`db.tableOpenCacheMissesPerSecond`	Number of misses per second for open tables cache lookups.
`db.tableOpenCacheOverflowsPerSecond`	Number of overflows per second for the open tables cache.
`db.threadCacheMissRate`	Percent of threads that need to be created to handle new connections because there are not enough threads available in the cache.
`db.threadsCached`	Number of threads in the thread cache.
`db.threadsCreatedPerSecond`	Number of threads per second created to handle connections.

Extended innodb metrics

Additional metrics captured when extended_innodb_metrics is enabled (set to 1 in the configuration file):

Name	Description
`db.innodb.bufferPoolPagesDirty`	Current number of dirty pages in the InnoDB buffer pool.
`db.innodb.bufferPoolPagesFlushedPerSecond`	Number of requests per second to flush pages from the InnoDB buffer pool.
`db.innodb.bufferPoolReadAheadEvictedPerSecond`	Number of pages per second read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.
`db.innodb.bufferPoolReadAheadPerSecond`	Number of pages per second read into the InnoDB buffer pool by the read-ahead background thread.
`db.innodb.bufferPoolReadAheadRndPerSecond`	Number of “random” read-aheads per second initiated by InnoDB. This happens when a query scans a large portion of a table but in random order.
`db.innodb.bufferPoolReadRequestsPerSecond`	Number of logical read requests per second.
`db.innodb.bufferPoolReadsPerSecond`	Number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk, per second.
`db.innodb.bufferPoolWaitFreePerSecond`	Number of times per second a read or write to InnoDB had to wait because there were not clean pages available in the buffer pool.
`db.innodb.bufferPoolWriteRequestsPerSecond`	Number of writes per second done to the InnoDB buffer pool.
`db.innodb.dataFsyncsPerSecond`	Number of `fsync()` operations per second.
`db.innodb.dataPendingFsyncs`	Current number of pending fsync() operations.
`db.innodb.dataPendingReads`	Current number of pending reads.
`db.innodb.dataPendingWrites`	Current number of pending writes.
`db.innodb.dataReadsPerSecond`	Number of data reads (OS file reads) per second.
`db.innodb.dataWritesPerSecond`	Number of data writes per second.
`db.innodb.logWriteRequestsPerSecond`	Number of write requests for the InnoDB redo log per second.
`db.innodb.logWritesPerSecond`	Number of physical writes per second to the InnoDB redo log file.
`db.innodb.numOpenFiles`	Number of files InnoDB currently holds open.
`db.innodb.osLogFsyncsPerSecond`	Number of `fsync()` writes per second done to the InnoDB redo log files.
`db.innodb.osLogPendingFsyncs`	Number of pending `fsync()` operations for the InnoDB redo log files.
`db.innodb.osLogPendingWrites`	Number of pending writes per second to the InnoDB redo log files.
`db.innodb.osLogWrittenBytesPerSecond` rate	Number of bytes written per second to the InnoDB redo log files.
`db.innodb.pagesCreatedPerSecond`	The number of pages created per second by operations on InnoDB tables.
`db.innodb.pagesReadPerSecond`	Number of pages read per second from the InnoDB buffer pool by operations on InnoDB tables.
`db.innodb.pagesWrittenPerSecond`	Number of pages written per second by operations on InnoDB tables.
`db.innodb.rowsDeletedPerSecond`	Number of rows deleted per second from InnoDB tables.
`db.innodb.rowsInsertedPerSecond`	Number of rows per second inserted into InnoDB tables.
`db.innodb.rowsReadPerSecond`	Number of rows per second read from InnoDB tables.
`db.innodb.rowsUpdatedPerSecond`	Number of rows per second updated in InnoDB tables.

Extended myisam metrics

Additional metrics captured when extended_myisam_metrics is enabled in the configuration file:

Name	Description
`db.myisam.keyBlocksNotFlushed`	Number of key blocks in the MyISAM key cache that have changed but have not yet been flushed to disk.
`db.myisam.keyCacheUtilization`	Percentage of the key cache that is being used.
`db.myisam.keyReadRequestsPerSecond`	Number of requests to read a key block from the MyISAM key cache, per second.
`db.myisam.keyReadsPerSecond`	Number of physical reads of a key block from disk into the MyISAM key cache, per second.
`db.myisam.keyWriteRequestsPerSecond`	Number of requests per second to write a key block to the MyISAM key cache.
`db.myisam.keyWritesPerSecond`	Number of physical writes of a key block from the MyISAM key cache to disk, per second.

Extended slave cluster metrics

Additional metrics captured when the extended metrics flag is enabled in the configuration file and the cluster.slaveRunning metric is returning a value of 1. Check the MySQL Documentation for more details.

Name	Description
`db.relayLogSpace`	Total combined number of bytes for all existing relay log files.
`cluster.lastIOErrno`	Error number of the most recent error that caused the I/O thread to stop.
`cluster.lastIOError`	Error message of the most recent error that caused the I/O thread to stop.
`cluster.lastSQLErrno`	Error number of the most recent error that caused the SQL thread to stop.
`cluster.lastSQLError`	Error message of the most recent error that caused the SQL thread to stop.
`cluster.slaveIORunning`	Status of whether the I/O thread is started and has connected successfully to the master. The values can be `Yes`, `No`, or `Connecting`.
`cluster.slaveSQLRunning`	Status of whether the SQL thread is started. The values can be `Yes` or `No`.
`cluster.secondsBehindMaster`	Difference in seconds between the slave’s clock time and the timestamp of the query when it was recorded in the master’s binary log. When the slave is not correctly connected to the master, this metric won’t be reported.
`cluster.masterLogFile`	Name of the master binary log file from which the I/O thread is currently reading.
`cluster.readMasterLogPos`	Position in the current master binary log file up to which the I/O thread has read.
`cluster.relayMasterLogFile`	Name of the master binary log file containing the most recent event executed by the SQL thread.
`cluster.execMasterLogPos`	Position in the current master binary log file to which the SQL thread has read and executed, marking the start of the next transaction or event to be processed.

Inventory

The MySQL integration captures the configuration parameters of the MySQL node returned by SHOW GLOBAL VARIABLES. The data is available on the Inventory page, under the config/mysql source.

System metadata

The MySQL integration collects the following metadata attributes about your MySQL system:

Name	Description
`software.edition`	`software.edition` takes the value of the MySQL `version_comment` variable.
`software.version`	The MySQL server version.
`cluster.nodeType`	Either `master` or `slave,` depending on the role of the MySQL node being monitored.

Source code

The MySQL integration is open source software. That means you can browse its source code and send improvements, or create your own fork and build it.

For more help

If you need more help, check out these support and learning resources:

Browse the Explorers Hub to get help from the community and join in discussions.
Find answers on our sites and learn how to use our support portal.
Run New Relic Diagnostics, our troubleshooting tool for Linux, Windows, and macOS.
Review New Relic's data security and licenses documentation.

Compatibility and requirements

Important

Quick start

Install and activate

ECS

Kubernetes

Linux

Windows

Configuration

Enabling your MySQL Server

Configure the integration

Important

MySQL Instance Settings

Labels and custom attributes

Example configurations

Basic configuration

Basic configuration with different metric/inventory intervals

Metrics-only with TLS connection

Metrics-only connecting over socket

Metrics-only with all extended metrics

Multi-instance monitoring

Find and use data

Metric data

Default metrics

Extended metrics

Extended innodb metrics

Extended myisam metrics

Extended slave cluster metrics

Inventory

System metadata

Source code

For more help

MySQL monitoring integration

Compatibility and requirements

Important

Quick start

Install and activate

Kubernetes

Linux

Windows

Configuration

Enabling your MySQL Server

Configure the integration

Important

MySQL Instance Settings

Labels and custom attributes

Example configurations

Basic configuration

Basic configuration with different metric/inventory intervals

Metrics-only with TLS connection

Metrics-only connecting over socket

Metrics-only with all extended metrics

Multi-instance monitoring

Find and use data

Metric data

Default metrics

Extended metrics

Extended innodb metrics

Extended myisam metrics

Extended slave cluster metrics

Inventory

System metadata

Source code

.css-3u3y73{fill:none;stroke:currentColor;stroke-width:2;stroke-linecap:round;stroke-linejoin:round;}.css-1bvd50p{width:1rem;height:1rem;fill:none;stroke:currentColor;stroke-width:2;stroke-linecap:round;stroke-linejoin:round;}For more help

For more help