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

.

. The default login credentials are:

Username: admin

Password: prom-operator

.

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.