Skip to main content

Monitoring MySQL databases running on Windows

Overview

The MYSQL Metric plugin collects metrics for number of user connections and more.

Metrics plugin

Collects metric data organized in following documentType under metrics index: 

  • serverDetails
  • databaseDetails
  • tableDetails
  • masterReplicationDetails
  • slaveReplicationDetails

Prerequisites

  • MySQL 5.6 or above

Logger plugin

collects general logs and slow query logs. General logs are sent to log index whereas slow queries are sent to metrics index under documentType:mysqlSlowQueryLogs

Set access permissions

Username used for DB access should have appropriate permissions 

grant select on information_schema.*to 'username' identified by 'password';
grant select on performance_schema.*to 'username' identified by 'password';
note

Root user has these permissions by default 

Enable Replication(optional)

To collect the replication details replication has to be enabled.

Execute the following queries on the slave using the login of the user provided in the config.yaml file:

1)"show slave status"

2)"select * from replication_connection_status"

If the user is able to execute these queries then the replication details can be collected.

Commands to create a replication user if you want to enable replication:

the commands to be executed on the source or master,

CREATE USER 'replica_user'@'slave_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica_user'@'slave_server_ip';

Configuration Settings

Add the plugin configuration in config.yaml file under C:\Program Files (x86)\Sfagent\ directory as follows to enable this plugin

- name: mysql
enabled: true
interval: 300
config:
port: 3306
host: 127.0.0.1
user: xxxx
password: xxxx
documentsTypes:
- databaseDetails
- serverDetails
- tableDetails
- masterReplicationDetails #optional to be enabled when replication is setup
- slaveReplicationDetails #optional to be enabled when replication is setup

For help with plugins, please reach out to support@snappyflow.io

Viewing data and dashboards   

  • Data generated by plugin can be viewed inbrowse data page inside the respective application underplugin=mysql  and documentType= serverDetails
  • Dashboard for this data can be instantiated by Importing dashboard templateMySQL to the application dashboard