HomeAPI Reference
Guides
Back to 360 Monitoring

MySQL Plugin

Suggest Edits

This plugin monitors the performance of your MySQL server.

Reported Metrics

The plugin reports the following metrics from the MySQL server:

  • 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 the apt-get install python-mysqldb command on Debian or yum install MySQL-python to install the required plugin. Alternatively, you can run the pip install MySQL-python command.

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

Create a mysql user

If you are using Plesk, create a mysql user that has access to the performance_schema database with the following command, where safeandsecurepassword is the password you wish to assign to the user:

plesk db "CREATE USER 'agent360'@'127.0.0.1' IDENTIFIED BY 'safeandsecurepassword'"

Otherwise, create a mysql user that has access to the performance_schema database with the following command, where safeandsecurepassword is the password you wish to assign to the user:

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

In case you want to enable monitoring of a replica, you will have to run the following query as well (based on DB server):

For MariaDB 10.5 and higher:

sudo mysql -e "grant replica monitor on *.* to 'agent360'@'127.0.0.1';"

For all MySQL and MariaDB 10.4 and lower:

sudo mysql -e "GRANT REPLICATION CLIENT ON *.* TO 'agent360'@'localhost';"

Configure the plugin

To enable the plugin, edit the /etc/agent360-custom.ini file and add the following settings to the end of the file:

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

Test the plugin

To see if the plugin is working, run the agent360 --test mysql command. 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 with the service agent360 restart command.

Updated 6 months ago