Skip to main content
Version: Self Hosted Turbo

Monitor MySQL DB on Instance

Prerequisites

Install sfAgent  to start monitoring MySQL database running on instances using MySQL plugin.

note

The MySQL plugin has been tested on Ubuntu 16.04 and 18.04, as well as on CentOS 7, using MySQL versions 5.7 and 8.

Get Started 

To get started with MySQL database monitoring, create a user and grant permissions to collect data from the MySQL database.

Create New User

For MySQL versions 5.6 and 5.7, create username and password using the following command:

 create user 'username'@'%' identified by 'uniquepassword';

For MySQL versions 8.0 and greater, create username and password using the following command:

create user 'username'@'%' identified with mysql_native_password by 'uniquepassword';
note

Use the username and password created in this section while setting up the access permission and configuration.

Set Access Permissions

Use the following command to set access permission. 

grant select on performance_schema.* to 'username'@'%';
note

By default, the root user has the above mentioned permission.

Configuration 

Add the below-mentioned configuration to the config.yaml which is located at the following path /opt/sfagent/ directory .

note

Root user access is required to enable database replication.

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: <password>
        port: 3306 
        user: <username>
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 

Enable Logs

General Logs

  1. Execute the following command and locate the mysql.conf.d/mysqld.cnf file.

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

    Example output:
    /etc/my.cnf /etc/mysql/my.cnf ~/my.cnf

  2. Execute the below-mentioned command in the  mysql.conf.d/mysqld.cnf file to enable general logs.

    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 
  3. Simultaneously, log into Mysql  DB with the root user id and execute the below commands.

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

    Example general log format:

    Once the logs are enabled, My SQL general logs will be generated in the below format.

    2023-05-04 11:52:10.470 IST > LOG:  duration: 1006.577 ms  statement:    select pg_sleep(1);

Slow Query Logs

  1. In the mysqld.cnf file, modify and configure the variables as shown below: 

    slow_query_log= 1
    slow_query_log_file=/var/log/mysql/mysql-slow.log 
  2. Simultaneously, log into Mysql  DB with the root user id and execute the below commands:

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

    Example of slow query log format:

    # Time: 2023-05-04T06:47:52.143598Z
    # User@Host: petclinic[petclinic] @ localhost []  Id:    12
    # Query_time: 20.023072  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
    SET timestamp=1683182852;
    select sleep(20);
note

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

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

Enable Replication (optional)

Prerequisites

  1. Make sure that the replication is enabled on the database.

  2. Execute the following queries on the slave using the username provided in the config.yaml file to check the replication status.

    show slave status;
    select * from replication_connection_status;

Create New User and Set Access Permission

  1. To enable the replication, create a replication user using the following commands. The below given commands should be executed on the master.

    CREATE USER 'replica_user'@'slave_server_ip' IDENTIFIED WITH   mysql_native_password BY 'password';
  2. Execute the below-mentioned command to grant permission to the replication user.

    GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica_user'@'slave_server_ip';

View Metrics and Logs

  1. Go to the Application tab in the SnappyFlow and navigate to your Project > Application > Dashboard.


    note

    Once the MySQL configuration settings are done, the MySQL plugin will be automatically detected within the Metrics section. However, if the plugin is not detected, you can import template = MySQL or MySQL_Replication to view the corresponding metrics.

  2. MySQL database Metrics and Slow Query Logs are displayed in the Metrics section of the dashboard.

  1. MySQL database General Logs are displayed in the Log Management section of the dashboard.
  1. To access the unprocessed data gathered from the plugins, navigate to the Browse data section and select the following data: Index, Instance, Plugin, and Document Type.

Template Details

TemplatePluginDocument Type
MySQLmysqldatabaseDetails, serverDetails, and tableDetails
MySQL_ReplicationmysqldatabaseDetails, serverDetails, tableDetails, masterReplicationDetails, and slaveReplicationDetails