Metrics
The following metrics are submitted to Nixstats.

max_used_connections
open_files
open_tables
qcache_free_blocks
qcache_free_memory
qcache_total_blocks
slave_open_temp_tables
threads_cached
threads_connected
threads_running
uptime
aborted_clients
aborted_connects
binlog_cache_disk_use
binlog_cache_use
bytes_received
bytes_sent
com_delete
com_delete_multi
com_insert
com_insert_select
com_load
com_replace
com_replace_select
com_select
com_update
com_update_multi
connections
created_tmp_disk_tables
created_tmp_files
created_tmp_tables
key_reads
key_read_requests
key_writes
key_write_requests
max_used_connections
open_files
open_tables
opened_tables
qcache_free_blocks
qcache_free_memory
qcache_hits
qcache_inserts
qcache_lowmem_prunes
qcache_not_cached
qcache_queries_in_cache
qcache_total_blocks
questions
select_full_join
select_full_range_join
select_range
select_range_check
select_scan
slave_open_temp_tables
slave_retried_transactions
slow_launch_threads
slow_queries
sort_range
sort_rows
sort_scan
table_locks_immediate
table_locks_waited
threads_cached
threads_connected
threads_created
threads_running

Dependencies
MySQL-python

Run apt-get install python-mysqldb on debian based distro's or yum install MySQL-python to install the required plugin. Alternatively you can run pip install MySQL-python .

If you're running python3 you have to run apt-get install python3-mysqldb or pip3 install mysqlclient (mysql-devel is required on CentOS yum install mysql-devel)

Create a mysql user

Create a mysql user that has access to the performance_schema database:

sudo mysql -e "CREATE USER 'nixstats'@'localhost' IDENTIFIED BY 'safeandsecurepassword';"
sudo mysql -e "GRANT PROCESS ON *.* TO 'nixstats'@'localhost';"
sudo mysql -e "GRANT SELECT ON performance_schema.* TO 'nixstats'@'localhost';"

In case you want to enable monitoring of a replica you will have to run the following query as well:

sudo mysql -e "grant replication client on *.* to 'nixstats'@'localhost';"

Configure the plugin
To enable the plugin edit the nixstats configuration file. This file is usually located at /etc/nixstats.ini.

Add the following settings to the end of the file.

[mysql]
enabled=yes
username=nixstats
password=safeandsecurepassword
host=127.0.0.1
database=performance_schema
port=3306
;socket=null

Test to see if the plugin is working by running nixstatsagent --test mysql. It should show the following metrics:

mysql:
{
"aborted_clients": 0,
"aborted_connects": 0,
"binlog_cache_disk_use": 0,
"binlog_cache_use": 0,
"bytes_received": 0,
"bytes_sent": 0,
"com_delete": 0,
"com_delete_multi": 0,
"com_insert": 0,
"com_insert_select": 0,
"com_load": 0,
"com_replace": 0,
"com_replace_select": 0,
"com_select": 0,
"com_update": 0,
"com_update_multi": 0,
"connections": 0,
"created_tmp_disk_tables": 0,
"created_tmp_files": 0,
"created_tmp_tables": 0,
"key_read_requests": 0,
"key_reads": 0,
"key_write_requests": 0,
"key_writes": 0,
"max_used_connections": 2.0,
"open_files": 6.0,
"open_tables": 71.0,
"opened_tables": 0,
"qcache_free_blocks": 1.0,
"qcache_free_memory": 67091584.0,
"qcache_hits": 0,
"qcache_inserts": 0,
"qcache_lowmem_prunes": 0,
"qcache_not_cached": 0,
"qcache_queries_in_cache": 0,
"qcache_total_blocks": 1.0,
"questions": 0,
"select_full_join": 0,
"select_full_range_join": 0,
"select_range": 0,
"select_range_check": 0,
"select_scan": 0,
"slave_open_temp_tables": 0.0,
"slow_launch_threads": 0,
"slow_queries": 0,
"sort_range": 0,
"sort_rows": 0,
"sort_scan": 0,
"table_locks_immediate": 0,
"table_locks_waited": 0,
"threads_cached": 1.0,
"threads_connected": 1.0,
"threads_created": 0,
"threads_running": 1.0,
"uptime": 59949.0
}

Now you can restart the agent service nixstatsagent restart to start monitoring MySQL.

Published on: 06 / 09 / 2018