Skip to main content

MySQL on Instances

Overview#

MySQL on instances is monitored using sfAgent configured with MySQL plugin 

Metrics plugin#

Collects metric data organized in following documentType under metrics index: 

  • serverDetails 
  • databaseDetails
  • tableDetails

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 

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          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 in browse data page inside the respective application under plugin=mysql  and documentType= serverDetails 

  • Dashboard for this data can be instantiated by Importing dashboard template MySQL to the application dashboard