Skip to main content

MySQL on Instances


MySQL on instances is monitored using sfAgent configured with MySQL plugin .

MySQL plugin has been tested on ubuntu (16.04 and 18.04) and centos 7 with MySQL versions 5.7 and 8.

Metrics plugin

Collects metric data organized in following documentType under metrics index: 

  • serverDetails
  • databaseDetails
  • tableDetails
  • masterReplicationDetails
  • slaveReplicationDetails

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


Enable MySQL configurations

Logging needs to be configured in the mysql.conf.d/mysqld.cnf file. In the configuration file uncomment and configure the variables shown below: 

show_compatibility_56 = On     #neeeded for metrics 

This file can be located by executing the command as shown below: 

mysqld --verbose --help | grep -A 1 "Default options"

E.g. output is /etc/my.cnf /etc/mysql/my.cnf ~/my.cnf. User needs to check each of the files for the configuration

Alternatively, login to mysql with root user and execute below commands 

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file= '/path/filename';

Enable Slow Query Logs  

In mysqld.cnf file, uncomment and configure the variables shown below: 

slow_query_log= 1

Or, login to mysql with root user and execute below commands 

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time =100;
SET GLOBAL slow_query_log_file = '/path/filename';

By Default /var/log/mysql directory is not present in centos, so we must create and provide ownership of that directory as mysql 

chown -R mysql:mysql /var/log/mysql 

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';

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';


Refer to sfAgent section for steps to install and automatically generate plugin configurations. User can also manually add the configuration shown below to config.yaml under /opt/sfagent/ directory

    - name: mysql 
      enabled: true 
      interval: 60 
          - databaseDetails 
          - serverDetails 
          - tableDetails 
          - masterReplicationDetails  #optional to be enabled when replication is setup
          - slaveReplicationDetails  #optional to be enabled when replication is setup
        password: USERad@123$ 
        port: 3306 
        user: root 
    - name: mysql-error 
      enabled: true 
          - error 
          - warning 
          - note 
        log_path: /var/log/mysql/error.log, /var/log/mysql/mysql-error.log, /var/log/mysqld.err, /var/log/mysqld.log 
    - name: mysql-general 
      enabled: true 
        log_path: /var/log/mysql/mysql.log , /var/log/mysql.log, /var/log/mysqld.log, /var/lib/mysql/ip-*.log
    - name: mysql-slowquery 
      enabled: true 
        log_path: /var/lib/mysql/ip-*slow.log, /var/log/mysql/mysql-slow.log 

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