Skip to main content

MySQL collector

MySQL is an open-source relational database management system.

This module monitors one or more MySQL servers, depending on your configuration.

Requirements

Executed queries:

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

User Statistics query is MariaDB specific.

A user account should have the following permissions:

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

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.

Metrics

All metrics have "mysql." prefix.

  • userstats_* metrics need User Statistics plugin enabled. MariaDB and Percona MySQL only.

Labels per scope:

  • global: no labels.
  • connection: no labels.
  • user: user.
MetricScopeDimensionsUnits
netglobalin, outkilobits/s
queriesglobalqueries, questions, slow_queriesqueries/s
queries_typeglobalselect, delete, update, insert, replacequeries/s
handlersglobalcommit, delete, prepare, read_first, read_key, read_next, read_prev, read_rnd, read_rnd_next, rollback, savepoint, savepointrollback, update, writehandlers/s
table_open_cache_overflowsglobalopen_cacheoverflows/s
table_locksglobalimmediate, waitedlocks/s
join_issuesglobalfull_join, full_range_join, range, range_check, scanjoins/s
sort_issuesglobalmerge_passes, range, scanissues/s
tmpglobaldisk_tables, files, tablesevents/s
connectionsglobalall, abortedconnections/s
connections_activeglobalactive, limit, max_activeconnections
threadsglobalconnected, cached, runningthreads
threads_createdglobalcreatedthreads/s
thread_cache_missesglobalmissesmisses
innodb_ioglobalread, writeKiB/s
innodb_io_opsglobalreads, writes, fsyncsoperations/s
innodb_io_pending_opsglobalreads, writes, fsyncsoperations
innodb_logglobalwaits, write_requests, writesoperations/s
innodb_cur_row_lockglobalcurrent waitsoperations
innodb_rowsglobalinserted, read, updated, deletedoperations/s
innodb_buffer_pool_pagesglobaldata, dirty, free, misc, totalpages
innodb_buffer_pool_pages_flushedglobalflush_pagesrequests/s
innodb_buffer_pool_bytesglobaldata, dirtyMiB
innodb_buffer_pool_read_aheadglobalall, evictedpages/s
innodb_buffer_pool_read_ahead_rndglobalread-aheadoperations/s
innodb_buffer_pool_opsglobaldisk_reads, wait_freeoperations/s
innodb_os_logglobalfsyncs, writesoperations
innodb_os_log_fsync_writesglobalfsyncsoperations/s
innodb_os_log_ioglobalwriteKiB/s
innodb_deadlocksglobaldeadlocksoperations/s
filesglobalfilesfiles
files_rateglobalfilesfiles/s
connection_errorsglobalaccept, internal, max, peer_addr, select, tcpwraperrors/s
opened_tablesglobaltablestables/s
open_tablesglobalcache, tablestables
process_list_fetch_query_durationglobaldurationmilliseconds
process_list_queries_countglobalsystem, userqueries
process_list_longest_query_durationglobaldurationseconds
qcache_opsglobalhits, lowmem_prunes, inserts, not_cachedqueries/s
qcacheglobalqueriesqueries
qcache_freememglobalfreeMiB
qcache_memblocksglobalfree, totalblocks
galera_writesetsglobalrx, txwritesets/s
galera_bytesglobalrx, txKiB/s
galera_queueglobalrx, txwritesets
galera_conflictsglobalbf_aborts, cert_failstransactions
galera_flow_controlglobalpausedms
galera_cluster_statusglobalprimary, non_primary, disconnectedstatus
galera_cluster_stateglobalundefined, joining, donor, joined, synced, errorstate
galera_cluster_sizeglobalnodesnodes
galera_cluster_weightglobalweightweight
galera_connectedglobalconnectedboolean
galera_readyglobalreadyboolean
galera_open_transactionsglobalopentransactions
galera_thread_countglobalthreadsthreads
key_blocksglobalunused, used, not_flushedblocks
key_requestsglobalreads, writesrequests/s
key_disk_opsglobalreads, writesoperations/s
binlog_cacheglobaldisk, alltransactions/s
binlog_stmt_cacheglobaldisk, allstatements/s
slave_behindconnectionsecondsseconds
slave_statusconnectionsql_running, io_runningboolean
userstats_cpuuserusedpercentage
userstats_rowsuserread, sent, updated, inserted, deletedoperations/s
userstats_commandsuserselect, update, othercommands/s
userstats_denied_commandsuserdeniedcommands/s
userstats_created_transactionsusercommit, rollbacktransactions/s
userstats_binlog_writtenuserwrittenB/s
userstats_empty_queriesuseremptyqueries/s
userstats_connectionsusercreatedconnections/s
userstats_lost_connectionsuserlostconnections/s
userstats_denied_connectionsuserdeniedconnections/s

Configuration

Edit the go.d/mysql.conf configuration file using edit-config from the Netdata config directory, which is typically at /etc/netdata.

cd /etc/netdata # Replace this path with your Netdata config directory
sudo ./edit-config go.d/mysql.conf

DSN syntax in details.

jobs:
- name: local
dsn: '[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]'
# username:[email protected](address)/dbname?param=value
# user:[email protected]/dbname
# Examples:
# - name: local
# dsn: user:[email protected](/usr/local/var/mysql/mysql.sock)/
# - name: remote
# dsn: user:[email protected]/mydb?charset=utf8

For all available options see module configuration file.

Troubleshooting

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].

    cd /usr/libexec/netdata/plugins.d/
  • Switch to the netdata user.

    sudo -u netdata -s
  • Run the go.d.plugin to debug the collector:

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

Do you have any feedback for this page? If so, you can open a new issue on our netdata/learn repository.