MySQL

This document describes how to configure your Google Kubernetes Engine deployment so that you can use Google Cloud Managed Service for Prometheus to collect metrics from MySQL. This document shows you how to do the following:

Set up the exporter for MySQL to report metrics.
Configure a PodMonitoring resource for Managed Service for Prometheus to collect the exported metrics.
Access a dashboard in Cloud Monitoring to view the metrics.
Configure alerting rules to monitor the metrics.

These instructions apply only if you are using managed collection with Managed Service for Prometheus. If you are using self-deployed collection, then see the source repository for the MySQL exporter for installation information.

These instructions are provided as an example and are expected to work in most Kubernetes environments. If you are having trouble installing an application or exporter due to restrictive security or organizational policies, then we recommend you consult open-source documentation for support.

For information about MySQL, see MySQL.

Prerequisites

To collect metrics from MySQL by using Managed Service for Prometheus and managed collection, your deployment must meet the following requirements:

Your cluster must be running Google Kubernetes Engine version 1.21.4-gke.300 or later.
You must be running Managed Service for Prometheus with managed collection enabled. For more information, see Get started with managed collection.
To use dashboards available in Cloud Monitoring for the MySQL integration, you must use mysqld_exporter version v0.14.0 or later.
For more information about available dashboards, see View dashboards.

For information about creating a least-privileged user, see Required Grants.

Install the MySQL exporter

We recommend that you install the MySQL exporter, mysqld_exporter, as a sidecar to your MySQL workload. For information about using sidecars, see Extended applications on Kubernetes with multi-container pods.

To install mysqld_exporter as a sidecar to MySQL, modify your MySQL configuration as shown in the following example:

# Copyright 2022 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

+ apiVersion: v1
+ kind: ConfigMap
+ metadata:
+  name: mysql-exporter-config
+ data:
+   my.cnf: |
+     [client]
+     user=root
+     password=password
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: mysql
  selector:
    matchLabels:
+     app.kubernetes.io/name: mysql
  template:
    metadata:
      labels:
+       app.kubernetes.io/name: mysql
    spec:
      containers:
+     - name: exporter
+       image: prom/mysqld-exporter:v0.15.0
+       args:
+         - --config.my-cnf=/home/my.cnf
+       ports:
+       - containerPort: 9104
+         name: prometheus
+       volumeMounts:
+       - mountPath: /home/my.cnf
+         subPath: my.cnf
+         name: mysql-exporter-config
      - name: mysql
        image: mysql:5.7
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: password
        - name: MYSQL_USER
          value: sbtest
        - name: MYSQL_PASSWORD
          value: password
        - name: MYSQL_DATABASE
          value: sbtest
        ports:
        - containerPort: 3306
+     volumes:
+     - name: mysql-exporter-config
+       configMap:
+         name: mysql-exporter-config
+         items:
+         - key: my.cnf
+           path: my.cnf

You must add any lines preceded by the + symbol to your configuration.

Update the --config.my-cnf argument with a path to a configuration file with credentials that work with your MySQL instance.

To apply configuration changes from a local file, run the following command:

kubectl apply -n NAMESPACE_NAME -f FILE_NAME

You can also use Terraform to manage your configurations.

Define a PodMonitoring resource

For target discovery, the Managed Service for Prometheus Operator requires a PodMonitoring resource that corresponds to the MySQL exporter in the same namespace.

You can use the following PodMonitoring configuration:

# Copyright 2022 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

apiVersion: monitoring.googleapis.com/v1
kind: PodMonitoring
metadata:
  name: mysql
  labels:
    app.kubernetes.io/name: mysql
    app.kubernetes.io/part-of: google-cloud-managed-prometheus
spec:
  endpoints:
  - port: prometheus
    scheme: http
    interval: 30s
    path: /metrics
  selector:
    matchLabels:
      app.kubernetes.io/name: mysql

Ensure that the label selectors and the port match the selectors and port used in Install the MySQL exporter.

To apply configuration changes from a local file, run the following command:

kubectl apply -n NAMESPACE_NAME -f FILE_NAME

You can also use Terraform to manage your configurations.

Define rules and alerts

You can use the following Rules configuration to define alerts on your MySQL metrics:

# Copyright 2022 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

apiVersion: monitoring.googleapis.com/v1
kind: Rules
metadata:
  name: mysql-rules
  labels:
    app.kubernetes.io/component: rules
    app.kubernetes.io/name: mysql-rules
    app.kubernetes.io/part-of: google-cloud-managed-prometheus
spec:
  groups:
  - name: mysql
    interval: 30s
    rules:
    - alert: MySQLConnectionErrors
      annotations:
        description: |-
          MySQL connection errors
            VALUE = {{ $value }}
            LABELS: {{ $labels }}
        summary: MySQL connection errors (instance {{ $labels.instance }})
      expr: mysql_global_status_connection_errors_total > 0
      for: 5m
      labels:
        severity: warning

To apply configuration changes from a local file, run the following command:

kubectl apply -n NAMESPACE_NAME -f FILE_NAME

You can also use Terraform to manage your configurations.

For more information about applying rules to your cluster, see Managed rule evaluation and alerting.

You can adjust the alert thresholds to suit your application.

Verify the configuration

You can use Metrics Explorer to verify that you correctly configured the MySQL exporter. It might take one or two minutes for Cloud Monitoring to ingest your metrics.

To verify the metrics are ingested, do the following:

In the Google Cloud console, go to the Metrics explorer page:
Go to Metrics explorer

If you use the search bar to find this page, then select the result whose subheading is Monitoring.
In the toolbar of the query-builder pane, select the button whose name is either MQL or PromQL.
Verify that PromQL is selected in the Language toggle. The language toggle is in the same toolbar that lets you format your query.

Enter and run the following query:

up{job="mysql", cluster="CLUSTER_NAME", namespace="NAMESPACE_NAME"}

View dashboards

The Cloud Monitoring integration includes the MySQL Prometheus Overview dashboard. Dashboards are automatically installed when you configure the integration. You can also view static previews of dashboards without installing the integration.

To view an installed dashboard, do the following:

In the Google Cloud console, go to the Dashboards page:
Go to Dashboards

If you use the search bar to find this page, then select the result whose subheading is Monitoring.
Select the Dashboard List tab.
Choose the Integrations category.
Click the name of the dashboard, for example, MySQL Prometheus Overview.

To view a static preview of the dashboard, do the following:

In the Google Cloud console, go to the Integrations page:
Go to Integrations

If you use the search bar to find this page, then select the result whose subheading is Monitoring.
Click the Kubernetes Engine deployment-platform filter.
Locate the MySQL integration and click View Details.
Select the Dashboards tab.

Troubleshooting

For information about troubleshooting metric ingestion problems, see Problems with collection from exporters in Troubleshooting ingestion-side problems.