github.com/netdata/go.d.plugin@v0.58.1/modules/mysql/integrations/mysql.md

<!--startmeta
custom_edit_url: "https://github.com/netdata/go.d.plugin/edit/master/modules/mysql/integrations/mysql.md"
meta_yaml: "https://github.com/netdata/go.d.plugin/edit/master/modules/mysql/metadata.yaml"
sidebar_label: "MySQL"
learn_status: "Published"
learn_rel_path: "Data Collection/Databases"
most_popular: True
message: "DO NOT EDIT THIS FILE DIRECTLY, IT IS GENERATED BY THE COLLECTOR'S metadata.yaml FILE"
endmeta-->

# MySQL


<img src="https://netdata.cloud/img/mysql.svg" width="150"/>


Plugin: go.d.plugin
Module: mysql

<img src="https://img.shields.io/badge/maintained%20by-Netdata-%2300ab44" />

## Overview

This collector monitors the health and performance of MySQL servers and collects general statistics, replication and user metrics.


It connects to the MySQL instance via a TCP or UNIX socket and executes the following commands:

Executed queries:

- `SELECT VERSION();`
- `SHOW GLOBAL STATUS;`
- `SHOW GLOBAL VARIABLES;`
- `SHOW SLAVE STATUS;` or `SHOW ALL SLAVES STATUS;` (MariaDBv10.2+) or `SHOW REPLICA STATUS;` (MySQL 8.0.22+)
- `SHOW USER_STATISTICS;` (MariaDBv10.1.1+)
- `SELECT TIME,USER FROM INFORMATION_SCHEMA.PROCESSLIST;`


This collector is supported on all platforms.

This collector supports collecting metrics from multiple instances of this integration, including remote instances.


### Default Behavior

#### Auto-Detection

By default, it detects instances running on localhost by trying to connect as root and netdata using known MySQL TCP and UNIX sockets:

- /var/run/mysqld/mysqld.sock
- /var/run/mysqld/mysql.sock
- /var/lib/mysql/mysql.sock
- /tmp/mysql.sock
- 127.0.0.1:3306
- "[::1]:3306"


#### Limits

The default configuration for this integration does not impose any limits on data collection.

#### Performance Impact

The default configuration for this integration is not expected to impose a significant performance impact on the system.


## Metrics

Metrics grouped by *scope*.

The scope defines the instance that the metric belongs to. An instance is uniquely identified by a set of labels.



### Per MariaDB instance

These metrics refer to the entire monitored application.

This scope has no labels.

Metrics:

| Metric | Dimensions | Unit | MySQL | MariaDB | Percona |
|:------|:----------|:----|:---:|:---:|:---:|
| mysql.net | in, out | kilobits/s | • | • | • |
| mysql.queries | queries, questions, slow_queries | queries/s | • | • | • |
| mysql.queries_type | select, delete, update, insert, replace | queries/s | • | • | • |
| mysql.handlers | commit, delete, prepare, read_first, read_key, read_next, read_prev, read_rnd, read_rnd_next, rollback, savepoint, savepointrollback, update, write | handlers/s | • | • | • |
| mysql.table_open_cache_overflows | open_cache | overflows/s | • | • | • |
| mysql.table_locks | immediate, waited | locks/s | • | • | • |
| mysql.join_issues | full_join, full_range_join, range, range_check, scan | joins/s | • | • | • |
| mysql.sort_issues | merge_passes, range, scan | issues/s | • | • | • |
| mysql.tmp | disk_tables, files, tables | events/s | • | • | • |
| mysql.connections | all, aborted | connections/s | • | • | • |
| mysql.connections_active | active, limit, max_active | connections | • | • | • |
| mysql.threads | connected, cached, running | threads | • | • | • |
| mysql.threads_created | created | threads/s | • | • | • |
| mysql.thread_cache_misses | misses | misses | • | • | • |
| mysql.innodb_io | read, write | KiB/s | • | • | • |
| mysql.innodb_io_ops | reads, writes, fsyncs | operations/s | • | • | • |
| mysql.innodb_io_pending_ops | reads, writes, fsyncs | operations | • | • | • |
| mysql.innodb_log | waits, write_requests, writes | operations/s | • | • | • |
| mysql.innodb_cur_row_lock | current waits | operations | • | • | • |
| mysql.innodb_rows | inserted, read, updated, deleted | operations/s | • | • | • |
| mysql.innodb_buffer_pool_pages | data, dirty, free, misc, total | pages | • | • | • |
| mysql.innodb_buffer_pool_pages_flushed | flush_pages | requests/s | • | • | • |
| mysql.innodb_buffer_pool_bytes | data, dirty | MiB | • | • | • |
| mysql.innodb_buffer_pool_read_ahead | all, evicted | pages/s | • | • | • |
| mysql.innodb_buffer_pool_read_ahead_rnd | read-ahead | operations/s | • | • | • |
| mysql.innodb_buffer_pool_ops | disk_reads, wait_free | operations/s | • | • | • |
| mysql.innodb_os_log | fsyncs, writes | operations | • | • | • |
| mysql.innodb_os_log_fsync_writes | fsyncs | operations/s | • | • | • |
| mysql.innodb_os_log_io | write | KiB/s | • | • | • |
| mysql.innodb_deadlocks | deadlocks | operations/s | • | • | • |
| mysql.files | files | files | • | • | • |
| mysql.files_rate | files | files/s | • | • | • |
| mysql.connection_errors | accept, internal, max, peer_addr, select, tcpwrap | errors/s | • | • | • |
| mysql.opened_tables | tables | tables/s | • | • | • |
| mysql.open_tables | cache, tables | tables | • | • | • |
| mysql.process_list_fetch_query_duration | duration | milliseconds | • | • | • |
| mysql.process_list_queries_count | system, user | queries | • | • | • |
| mysql.process_list_longest_query_duration | duration | seconds | • | • | • |
| mysql.qcache_ops | hits, lowmem_prunes, inserts, not_cached | queries/s | • | • | • |
| mysql.qcache | queries | queries | • | • | • |
| mysql.qcache_freemem | free | MiB | • | • | • |
| mysql.qcache_memblocks | free, total | blocks | • | • | • |
| mysql.galera_writesets | rx, tx | writesets/s | • | • | • |
| mysql.galera_bytes | rx, tx | KiB/s | • | • | • |
| mysql.galera_queue | rx, tx | writesets | • | • | • |
| mysql.galera_conflicts | bf_aborts, cert_fails | transactions | • | • | • |
| mysql.galera_flow_control | paused | ms | • | • | • |
| mysql.galera_cluster_status | primary, non_primary, disconnected | status | • | • | • |
| mysql.galera_cluster_state | undefined, joining, donor, joined, synced, error | state | • | • | • |
| mysql.galera_cluster_size | nodes | nodes | • | • | • |
| mysql.galera_cluster_weight | weight | weight | • | • | • |
| mysql.galera_connected | connected | boolean | • | • | • |
| mysql.galera_ready | ready | boolean | • | • | • |
| mysql.galera_open_transactions | open | transactions | • | • | • |
| mysql.galera_thread_count | threads | threads | • | • | • |
| mysql.key_blocks | unused, used, not_flushed | blocks | • | • | • |
| mysql.key_requests | reads, writes | requests/s | • | • | • |
| mysql.key_disk_ops | reads, writes | operations/s | • | • | • |
| mysql.binlog_cache | disk, all | transactions/s | • | • | • |
| mysql.binlog_stmt_cache | disk, all | statements/s | • | • | • |

### Per connection

These metrics refer to the replication connection.

This scope has no labels.

Metrics:

| Metric | Dimensions | Unit | MySQL | MariaDB | Percona |
|:------|:----------|:----|:---:|:---:|:---:|
| mysql.slave_behind | seconds | seconds | • | • | • |
| mysql.slave_status | sql_running, io_running | boolean | • | • | • |

### Per user

These metrics refer to the MySQL user.

Labels:

| Label      | Description     |
|:-----------|:----------------|
| user | username |

Metrics:

| Metric | Dimensions | Unit | MySQL | MariaDB | Percona |
|:------|:----------|:----|:---:|:---:|:---:|
| mysql.userstats_cpu | used | percentage |   | • | • |
| mysql.userstats_rows | read, sent, updated, inserted, deleted | operations/s |   | • | • |
| mysql.userstats_commands | select, update, other | commands/s |   | • | • |
| mysql.userstats_denied_commands | denied | commands/s |   | • | • |
| mysql.userstats_created_transactions | commit, rollback | transactions/s |   | • | • |
| mysql.userstats_binlog_written | written | B/s |   | • | • |
| mysql.userstats_empty_queries | empty | queries/s |   | • | • |
| mysql.userstats_connections | created | connections/s |   | • | • |
| mysql.userstats_lost_connections | lost | connections/s |   | • | • |
| mysql.userstats_denied_connections | denied | connections/s |   | • | • |



## Alerts


The following alerts are available:

| Alert name  | On metric | Description |
|:------------|:----------|:------------|
| [ mysql_10s_slow_queries ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.queries | number of slow queries in the last 10 seconds |
| [ mysql_10s_table_locks_immediate ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.table_locks | number of table immediate locks in the last 10 seconds |
| [ mysql_10s_table_locks_waited ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.table_locks | number of table waited locks in the last 10 seconds |
| [ mysql_10s_waited_locks_ratio ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.table_locks | ratio of waited table locks over the last 10 seconds |
| [ mysql_connections ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.connections_active | client connections utilization |
| [ mysql_replication ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.slave_status | replication status (0: stopped, 1: working) |
| [ mysql_replication_lag ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.slave_behind | difference between the timestamp of the latest transaction processed by the SQL thread and the timestamp of the same transaction when it was processed on the master |
| [ mysql_galera_cluster_size_max_2m ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.galera_cluster_size | maximum galera cluster size in the last 2 minutes starting one minute ago |
| [ mysql_galera_cluster_size ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.galera_cluster_size | current galera cluster size, compared to the maximum size in the last 2 minutes |
| [ mysql_galera_cluster_state_warn ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.galera_cluster_state | galera node state is either Donor/Desynced or Joined |
| [ mysql_galera_cluster_state_crit ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.galera_cluster_state | galera node state is either Undefined or Joining or Error |
| [ mysql_galera_cluster_status ](https://github.com/netdata/netdata/blob/master/health/health.d/mysql.conf) | mysql.galera_cluster_status | galera node is part of a nonoperational component. This occurs in cases of multiple membership changes that result in a loss of Quorum or in cases of split-brain situations. |


## Setup

### Prerequisites

#### Create netdata user

A user account should have the
following [permissions](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html):

- [`USAGE`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_usage)
- [`REPLICATION CLIENT`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-client)
- [`PROCESS`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_process)

To create the `netdata` user with these permissions, execute the following in the MySQL shell:

```mysql
CREATE USER 'netdata'@'localhost';
GRANT USAGE, REPLICATION CLIENT, PROCESS ON *.* TO 'netdata'@'localhost';
FLUSH PRIVILEGES;
```

The `netdata` user will have the ability to connect to the MySQL server on localhost without a password. It will only
be able to gather statistics without being able to alter or affect operations in any way.



### Configuration

#### File

The configuration file name for this integration is `go.d/mysql.conf`.


You can edit the configuration file using the `edit-config` script from the
Netdata [config directory](https://github.com/netdata/netdata/blob/master/docs/configure/nodes.md#the-netdata-config-directory).

```bash
cd /etc/netdata 2>/dev/null || cd /opt/netdata/etc/netdata
sudo ./edit-config go.d/mysql.conf
```
#### Options

The following options can be defined globally: update_every, autodetection_retry.


<details><summary>Config options</summary>

| Name | Description | Default | Required |
|:----|:-----------|:-------|:--------:|
| update_every | Data collection frequency. | 5 | no |
| autodetection_retry | Recheck interval in seconds. Zero means no recheck will be scheduled. | 0 | no |
| dsn | MySQL server DSN (Data Source Name). See [DSN syntax](https://github.com/go-sql-driver/mysql#dsn-data-source-name). | root@tcp(localhost:3306)/ | yes |
| my.cnf | Specifies the my.cnf file to read the connection settings from the [client] section. |  | no |
| timeout | Query timeout in seconds. | 1 | no |

</details>

#### Examples

##### TCP socket

An example configuration.

<details><summary>Config</summary>

```yaml
jobs:
  - name: local
    dsn: netdata@tcp(127.0.0.1:3306)/

```
</details>

##### Unix socket

An example configuration.

<details><summary>Config</summary>

```yaml
jobs:
  - name: local
    dsn: netdata@unix(/var/lib/mysql/mysql.sock)/

```
</details>

##### Connection with password

An example configuration.

<details><summary>Config</summary>

```yaml
jobs:
  - name: local
    dsn: netconfig:password@tcp(127.0.0.1:3306)/

```
</details>

##### my.cnf

An example configuration.

<details><summary>Config</summary>

```yaml
jobs:
  - name: local
    my.cnf: '/etc/my.cnf'

```
</details>

##### Multi-instance

> **Note**: When you define multiple jobs, their names must be unique.

Local and remote instances.


<details><summary>Config</summary>

```yaml
jobs:
  - name: local
    dsn: netdata@tcp(127.0.0.1:3306)/

  - name: remote
    dsn: netconfig:password@tcp(203.0.113.0:3306)/

```
</details>



## Troubleshooting

### Debug Mode

To troubleshoot issues with the `mysql` collector, run the `go.d.plugin` with the debug option enabled. The output
should give you clues as to why the collector isn't working.

- Navigate to the `plugins.d` directory, usually at `/usr/libexec/netdata/plugins.d/`. If that's not the case on
  your system, open `netdata.conf` and look for the `plugins` setting under `[directories]`.

  ```bash
  cd /usr/libexec/netdata/plugins.d/
  ```

- Switch to the `netdata` user.

  ```bash
  sudo -u netdata -s
  ```

- Run the `go.d.plugin` to debug the collector:

  ```bash
  ./go.d.plugin -d -m mysql
  ```