MySQL Plugin

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