Setting Up MySQL on Kubernetes with Prometheus & Grafana Monitoring

In this tutorial, we'll walk through how to set up MySQL on a Kubernetes (K8s) cluster, along with integrating Prometheus and Grafana for monitoring. We'll use the Bitnami Helm charts for MySQL, Prometheus, and Grafana, and show you how to create a custom Grafana dashboard to monitor MySQL.

1. Create a Namespace for Your Lab

First, create a Kubernetes namespace where you’ll deploy your MySQL and monitoring components.

kubectl create namespace my-lab

To verify the creation of the namespace, run:

kubectl get namespaces

This should list my-lab as one of the namespaces in your cluster.

2. Install MySQL Using Helm

We'll use the Bitnami Helm chart to install MySQL in the my-lab namespace. If you haven't added the Bitnami chart repository yet, do so with the following command:

helm repo add bitnami https://charts.bitnami.com/bitnami

Now, install MySQL with this command:

helm upgrade --install mysql bitnami/mysql --namespace my-lab

To check the status and ensure MySQL is running, use:

kubectl get pods --namespace my-lab

Access the MySQL Pod

To connect to MySQL, first, you’ll need to retrieve the MySQL password from the Kubernetes secrets. Use this command to get the secret:

kubectl get secret -n my-lab

Then decode the mysql-root-password and mysql-password as follows:

kubectl get secret/mysql -oyaml -n my-lab

You can decode the password using base64:

echo <encoded-password> | base64 -d

Now, access the MySQL pod using kubectl exec:

kubectl exec -it mysql-0 --namespace my-lab -- /bin/bash

Once inside the pod, connect to MySQL using the root password:

mysql -u root -p

Enter the decoded root password when prompted.

Create a Database and Some Tables

Once inside MySQL, create a database and some tables to monitor:

create database my_db_1;

use my_db_1;

create table my_table_1 (a varchar(100));
create table my_table_2 (a varchar(100));
create table my_table_3 (a varchar(100));

insert into my_table_1 values('One');
insert into my_table_1 values('One-One');

select * from my_table_1;

3. Install Prometheus and Grafana

Next, we will install Prometheus and Grafana to monitor MySQL.

Install Prometheus and Grafana

First, add the Prometheus community Helm charts:

helm repo add prometheus-community https://prometheus-community.github.io/helm-charts
helm repo update

Create a grafana namespace:

kubectl create ns grafana

Now, install the Prometheus-Grafana stack using Helm:

helm upgrade --install grafana prometheus-community/kube-prometheus-stack --namespace grafana

You can verify the installation by checking the pods:

kubectl get pods -n grafana

Expose MySQL with LoadBalancer

To access MySQL from outside the Kubernetes cluster, we need to expose the MySQL service using a LoadBalancer. Edit the MySQL service:

kubectl edit svc/mysql -n my-lab

Change the service type from ClusterIP to LoadBalancer:

spec:
  type: LoadBalancer

Verify the external IP has been assigned:

kubectl get svc -n my-lab

If you're using Minikube, you may need to run minikube tunnel to expose the service externally:

minikube tunnel

Now, access MySQL externally using the IP assigned:

mysql -h <external-ip> -u root -p

4. Install Prometheus MySQL Exporter

We need to install the MySQL Exporter to collect MySQL metrics and expose them for Prometheus to scrape.

Create Exporter Configuration

Create a file mysql-exporter-values.yaml with your MySQL connection details:

mysql:
  db: ""
  host: "<mysql-external-ip>"
  user: "root"
  pass: "<mysql-root-password>"
  port: 3306
  protocol: ""

serviceMonitor:
  enabled: true
  additionalLabels:
    release: grafana

Now, install the MySQL exporter using Helm:

helm upgrade --install mysql-exporter prometheus-community/prometheus-mysql-exporter -f mysql-exporter-values.yaml

Port-Forward Prometheus MySQL Exporter

To forward the MySQL exporter metrics to your local machine for testing, use:

kubectl port-forward svc/mysql-exporter-prometheus-mysql-exporter 9104 -n my-lab

Verify the metrics are being exposed by visiting http://localhost:9104/metrics.

5. Connect Grafana to Prometheus

Now that Prometheus and the MySQL exporter are set up, let’s connect Grafana to Prometheus.

Port-Forward Grafana

To access the Grafana dashboard, run the following port-forward command:

kubectl port-forward svc/grafana 3000:80 -n grafana

This will make Grafana accessible at http://localhost:3000. The default login credentials are:

Username: admin
Password: prom-operator

Once logged in, add Prometheus as a data source in Grafana:

Go to Configuration > Data Sources.
Add Prometheus as a data source with the URL http://prometheus-operated:9090.

6. Import Grafana Dashboards

To monitor MySQL, we can import pre-configured dashboards from Grafana's dashboard repository.

Import Dashboards

Go to Dashboards > Import and enter the following dashboard IDs:

Dashboard ID 14057 (MySQL Overview)
Dashboard ID 7362 (MySQL Performance)

These dashboards will automatically load and show relevant MySQL metrics, such as connections, queries, and resource utilization.

7. Conclusion

You've successfully set up MySQL in Kubernetes and integrated Prometheus and Grafana for monitoring. You can now monitor your MySQL instance in real-time using Grafana dashboards.

Key Takeaways:

• MySQL is running on Kubernetes using the Bitnami Helm chart.
• Prometheus is used to scrape metrics from MySQL using the MySQL exporter.
• Grafana is configured to visualize the data collected by Prometheus.
• You can access MySQL externally using a LoadBalancer service.

With this setup, you can easily scale and monitor your database infrastructure on Kubernetes.

Comments

[Boopathi]

This is a comprehensive guide to setting up a MySQL cluster on Kubernetes and implementing monitoring with Prometheus and Grafana. The step-by-step instructions are clear and helpful, especially for beginners.