Skip to main content

MySQL on Kubernetes

MySQL running in Kubernetes can be monitored in SnappyFlow using two approaches:

MySQL monitoring with sfKubeAgent#

In this approach, sfKubeAgent is run as a side-car inside MySQL pod. The example below shows the config-map for sfKubeAgent container, config-map for MySQL container and pod yaml.

Config map for MySQL#

apiVersion: v1  kind: ConfigMap  metadata:    name: mysql-configmap  data:    mysql.cnf: |      [mysqld]      show_compatibility_56 = On      query_cache_type = 1      query_cache_size = 16M      query_cache_limit = 1M      general_log_file = /var/log/mysql/mysql.log      general_log = 1      slow_query_log = 1      slow_query_log_file = /var/log/mysql/mysql-slow.log 

Config map for MySQL sfKubeAgent#

apiVersion: v1kind: ConfigMapmetadata:  name: mysql-sfkubeagent-configmapdata:  config.yaml: |-    key: <enter profile key>     metrics:         plugins:         - name: mysql           enabled: true           interval: 30           config:             host: "127.0.0.1"             password: "<enter password>"             user: "root"             documentsTypes:             - databaseDetails             - serverDetails             - tableDetails     logging:         plugins:         - name: mysql-general           enabled: true           config:             log_path: "/var/log/mysql/mysql.log, /var/log/mysql.log, /var/log/mysqld.log"         - name: mysql-error           enabled: true           config:             log_level:             - error             - warn             - note             log_path: "/var/log/mysql/error.log, /var/log/mysql/mysql-error.log, /var/log/mysqld.err"         - name: mysql-slowquery           enabled: true           config:             log_path: "/var/lib/mysql/ip-*slow.log, /var/log/mysql/mysql-slow.log"

MySQL Pod YAML#

kind: PodapiVersion: v1metadata:  name: mysql-pod  labels:    snappyflow/appname: <app_name>    snappyflow/projectname: <project_name>spec:  containers:    - name: mysql-container      image: "mysql:5.7.14"      imagePullPolicy: IfNotPresent      ports:        - name: tcp          containerPort: 3306      env:        - name: MYSQL_ROOT_PASSWORD          value: <enter password>        - name: MYSQL_ROOT_USER          value: root      volumeMounts:        - name: varlog          mountPath: /var/log/mysql        - name: configmap-mysql          mountPath: /etc/mysql/conf.d    # Snappyflow's sfkubeagent container    - name: sfagent-container      image: snappyflowml/sfagent:latest      imagePullPolicy: Always      command:        - /app/sfagent        - -enable-console-log      env:        - name: APP_NAME          value: <app_name>        - name: PROJECT_NAME          value: <project_name>      volumeMounts:        - name: configmap-sfkubeagent-mysql          mountPath: /opt/sfagent/config.yaml          subPath: config.yaml        - name: varlog          mountPath: /var/log/mysql  volumes:    - name: configmap-mysql      configMap:        name: mysql-configmap    - name: configmap-sfkubeagent-mysql      configMap:        name: mysql-sfkubeagent-configmap    - name: varlog      emptyDir: {}

Viewing data and dashboards#

  • Metric data generated by plugin can be viewed in browse data page inside the respective application under metrics section with plugin=mysql and documentType= serverDetails, databaseDetails, tableDetails. Data from slow query logs can be found in metrics section with plugin=mysql-slowquery and documentType=SlowQueryLogs
  • Dashboard for this data can be instantiated by Importing dashboard template MySQL to the application dashboard

MySQL monitoring with Prometheus#

Refer to Prometheus Exporter overview to understand how SnappyFlow monitors using Prometheus exporters.

Pre-requisites#

Prometheus exporter is deployed as a side-car in the application container and the exporter port is accessible to sfPod

Configurations#

MySQL Service YAML#

apiVersion: v1 kind: Service metadata:   labels:    snappyflow/appname: <app_name>    snappyflow/projectname: <project_name>    snappyflow/component: mysql   name: mysql-prom-service spec:   ports:   - name: mysql     port: 3306     protocol: TCP     targetPort: mysql   - name: mysql-exporter     port: 9104     protocol: TCP     targetPort: mysql-exporter   selector:     app: <app_name>   sessionAffinity: None   type: ClusterIP

MySQL Pod YAML#

kind: Pod apiVersion: v1 metadata:  name: mysql-prometheus-pod  labels:   snappyflow/appname: <app-name>   snappyflow/projectname: <project-name>   snappyflow/component: mysql spec:  containers:  - name: mysql-container    image: "mysql:5.7.14"    imagePullPolicy: IfNotPresent    ports:    - name: tcp      containerPort: 3306    env:    - name: MYSQL_ROOT_PASSWORD      value: <password>    - name: MYSQL_ROOT_USER      value: root    volumeMounts:      - name: varlog        mountPath: /var/log/mysql      - name: configmap-mysql        mountPath: /etc/mysql/conf.d  # Prometheus exporter  - name: mysql-exporter    image: prom/mysqld-exporter:v0.10.0    imagePullPolicy: Always    ports:    - name: mysql-exporter      containerPort: 9104    command:      - sh      - -c      - DATA_SOURCE_NAME="root:$MYSQL_ROOT_PASSWORD@(localhost:3306)/" /bin/mysqld_exporter    env:      - name: MYSQL_ROOT_PASSWORD        value: <password>  volumes:  - name: configmap-mysql    configMap:      name: mysql-configmap  - name: varlog    emptyDir: {}

Viewing data and dashboards#

  • Data generated by plugin can be viewed in browse data page inside the respective application under plugin=kube-prom-mysql and documentType= serverDetails, tabledetails . Data from slow query logs can be found in metrics section with plugin=mysql-slowquery and documentType=mysqlSlowQueryLogs
  • Dashboard for this data can be instantiated by Importing dashboard template MySQL_Kube_Prom to the application dashboard