Connecting MySQL Exporter to AOM for Monitoring Metrics

Scenario

MySQL Exporter collects MySQL database metrics. Core database metrics collected through Exporter are used for alarm reporting and dashboard display. Currently, Exporter supports MySQL 5.6 or later. If the MySQL version is earlier than 5.6, some metrics may fail to be collected.

Constraints

You are advised to use CCE for unified Exporter management.

Prerequisites

A CCE cluster has been created and MySQL has been installed.
Your service has been connected for Prometheus monitoring and a CCE cluster has also been connected. For details, see Prometheus Instance for CCE.
You have uploaded the mysql_exporter image to SoftWare Repository for Container (SWR). For details, see Uploading an Image Through a Container Engine Client.

Database Authorization

Log in to the cluster and run the following command to log in to the MySQL database as user root:
```
kubectl exec -it ${mysql_podname} bash
mysql -u root -p
```
Figure 1 Logging in to the database

Run the following command to grant permissions to the database:

CREATE USER 'exporter'@'x.x.x.x(hostip)' IDENTIFIED BY 'xxxx(password)' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'x.x.x.x(hostip)';

Check whether the authorization is successful.

Run the following SQL statement to check whether Exporter data exists. If Exporter data exists, the authorization is successful. In the following, host indicates the IP address of the node where MySQL is located.
```
select user,host from mysql.user;
```
Figure 2 SQL statement

Deploying MySQL Exporter in a CCE Cluster

Log in to the CCE console.
Click the connected cluster. The cluster management page is displayed.

Perform the following operations to deploy Exporter:

Use Secret to manage MySQL connection strings.
In the navigation pane, choose ConfigMaps and Secrets. In the upper right corner, click Create from YAML and enter the following YAML file.

The password has been encrypted using Opaque. For details about how to configure a secret, see Creating a Secret.
```
apiVersion: v1
kind: Secret
metadata:
    name: mysql-secret
    namespace: default
type: Opaque
stringData:
    datasource: "user:password@tcp(ip:port)/" # MySQL connection string, which needs to be encrypted.
```

Deploy MySQL Exporter.

In the navigation pane, choose Workloads. In the upper right corner, click Create Workload. Then select the Deployment workload and select a desired namespace to deploy MySQL Exporter.

The following shows the YAML used to deploy Exporter. For parameters, see mysql-exporter.

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    k8s-app: mysql-exporter # Change the name based on service requirements. You are advised to add the MySQL instance information, for example, ckafka-2vrgx9fd-mysql-exporter.
  name: mysql-exporter # Change the name based on service requirements. You are advised to add the MySQL instance information, for example, ckafka-2vrgx9fd-mysql-exporter.
  namespace: default # Must be the same as the namespace of MySQL.
spec:
  replicas: 1
  selector:
    matchLabels:
      k8s-app: mysql-exporter # Change the name based on service requirements. You are advised to add the MySQL instance information, for example, ckafka-2vrgx9fd-mysql-exporter.
  template:
    metadata:
      labels:
        k8s-app: mysql-exporter # Change the name based on service requirements. You are advised to add the MySQL instance information, for example, ckafka-2vrgx9fd-mysql-exporter.
    spec:
      containers:
      - env:
        - name: DATA_SOURCE_NAME
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: datasource 
        image: swr.cn-north-4.myhuaweicloud.com/aom-exporter/mysqld-exporter:v0.12.1
        imagePullPolicy: IfNotPresent
        name: mysql-exporter
        ports:
        - containerPort: 9104
          name: metric-port 
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
    dnsPolicy: ClusterFirst
    imagePullSecrets:
    - name: default-secret
    restartPolicy: Always
    schedulerName: default-scheduler
    securityContext: {}
    terminationGracePeriodSeconds: 30
---
apiVersion: v1
kind: Service
metadata:
  name: mysql-exporter
spec:
  type: NodePort
  selector:
    k8s-app: mysql-exporter
  ports:
    - protocol: TCP
      nodePort: 30337
      port: 9104
      targetPort: 9104

Check whether MySQL Exporter is successfully deployed.
1. On the Deployments tab page, click the Deployment created in 3.b. In the pod list, choose More > View Logs in the Operation column. The Exporter is successfully started and its access address is exposed.
1. Run the following commands to check whether MySQL Exporter is successfully deployed. If metric data is returned, it is successfully deployed. Perform verification using one of the following methods:
  - Log in to a cluster node and run either of the following commands:
```
curl http://{Cluster IP address}:9104/metrics
```
```
curl http://{Private IP address of any node in the cluster}:30337/metrics
```
  - In the instance list, choose More > Remote Login in the Operation column and run the following command:
```
curl http://localhost:9104/metric
```
  - Access http://{Public IP address of any node in the cluster}:30337/metrics.
    Figure 3 Access address

Configuring a CCE Cluster Metric Collection Rule

Add PodMonitor to configure a Prometheus collection rule for monitoring the service data of applications deployed in the CCE cluster.

Log in to the AOM 2.0 console.
In the navigation pane on the left, choose Prometheus Monitoring > Instances.
In the instance list, click a Prometheus instance for CCE.
In the navigation pane on the left, choose Metric Management. On the Settings tab page, click PodMonitor.

Click Add PodMonitor. In the displayed dialog box, set parameters and click OK.

In the following example, metrics are collected every 30s. Therefore, you can check the reported metrics on the AOM page about 30s later.

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: mysql-exporter
  namespace: default
spec:
  namespaceSelector: # Select the namespace where the target Exporter pod is located.
    matchNames:
      - default # Namespace where Exporter is located.
  podMetricsEndpoints:
  - interval: 30s
    path: /metrics # Enter the path corresponding to Prometheus Exporter. Default: /metrics.
    port: metric-port # Enter the name of ports in the YAML file corresponding to Prometheus Exporter.
  selector: # Enter the label of the target Exporter pod.
    matchLabels:
      k8s-app: mysql-exporter

Verifying that CCE Cluster Metrics Can Be Reported to AOM

Log in to the AOM 2.0 console.
In the navigation pane on the left, choose Prometheus Monitoring > Instances.
Click the Prometheus instance connected to the CCE cluster. The instance details page is displayed.
On the Metrics tab page of the Metric Management page, select your target cluster.
Select job {namespace}/mysql-exporter to query custom metrics starting with mysql.

Setting a Dashboard and Alarm Rule on AOM

By setting a dashboard, you can monitor CCE cluster data on the same screen. By setting an alarm rule, you can detect cluster faults and implement warning in a timely manner.

Setting a dashboard
1. Log in to the AOM 2.0 console.
2. In the navigation pane, choose Dashboard > Dashboard. On the displayed page, click Add Dashboard to add a dashboard. For details, see Creating a Dashboard.
3. On the Dashboard page, select a Prometheus instance for CCE and click Add Graph. For details, see Adding a Graph to a Dashboard.
Setting an alarm rule
1. Log in to the AOM 2.0 console.
2. In the navigation pane, choose Alarm Center > Alarm Rules.
3. On the Prometheus Monitoring tab page, click Create Alarm Rule to create an alarm rule. For details, see Creating a Metric Alarm Rule.