Skip to main content

MySQL in 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: v1
kind: ConfigMap
metadata:
name: mysql-sfkubeagent-configmap
data:
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: Pod
apiVersion: v1
metadata:
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