Skip to main content

MySQL on Instances

Overview

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

Pre-requisites 

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 
log-error=/var/log/mysqld.log 
pid-file=/var/run/mysqld/mysqld.pid 
general_log_file=/var/log/mysql/mysql.log 
general_log=1 

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
slow_query_log_file=/var/log/mysql/mysql-slow.log 

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

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

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

metrics:
  plugins:
    - name: mysql 
      enabled: true 
      interval: 60 
      config:
        documentsTypes:
          - databaseDetails 
          - serverDetails 
          - tableDetails 
          - masterReplicationDetails  #optional to be enabled when replication is setup
          - slaveReplicationDetails  #optional to be enabled when replication is setup
        host: 127.0.0.1 
        password: USERad@123$ 
        port: 3306 
        user: root 
logging:
  plugins:
    - name: mysql-error 
      enabled: true 
      config:
        log_level:
          - 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 
      config:
        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 
      config:
        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