Deploying MySQL and phpMyAdmin with K3s Kubernetes

Posted July 13, 2024
Deploying MySQL and phpMyAdmin with K3s Kubernetes

This guide teaches you how to perfectly run a MySQL database with Kubernetes K3s cluster. K3s has been the lightweight Kubernetes distribution alternative. We will install it and discuss all the steps we need to get the MySQL Kubernetes cluster up and running. Along the guide.

We’ll create a phpMyAdmin cluster to create access to MySQL within K3s. We’ll learn:

  • Getting started with K3s.
  • How to create MySQL Kubernetes K3s deployments and Services.
  • Pesisitng MySQL data within the K3s cluster.
  • How to use phpMyAdmin with K3S and access a MySQL Kubernetes cluster.

Ready? Now dive in and spin up a K3s Kubernetes cluster for your MySQL and phpMyAdmin services.

Why K3s Kubernetes with MySQL and phpMyAdmin

Kubernetes itself is enough to get MySQL cluster ready. But in this case, Kubernetes gives you the option to other distributions like K3s. K3s will still use the same manifest as the classical Kubernetes.

In our case, K3s tend to be lightweight and more compact compared to the standard Kubernetes. This is great when running resource consuming services like MySQL. You get:

  • An optimized low-resource Kubernetes environment. We will be able to use minimal CPU and memory resources and still run a fully working K3s MySQL cluster.
  • K3s is fully compatible with standard Kubernetes APIs. This means any Kubernetes background remains the same.
  • It is to deploy a K3s MySQL cluster to Homelabs, Iots, and local developments.
  • K3s is easier to set up and you don’t need any additional tools the classical Kubernetes needs.

Prerequisites

To follow along with this guide, you only need to be familiar with Kubernetes and running the kubectl commands.

Please Note: This tutorial will only use K3s to create and deploy a single instance MySQL Kubernetes instance.

Installing K3s Kubernetes

In our previous guide, we covered how to install k3s. Please check How to Install K3s on Ubuntu to learn more.

You will need to run the following command to get K3s installed:

curl -sfL https://get.k3s.io | sh -

MySQL and phpMyAdmin with K3s Kubernetes

Make sure you confirm the K3s server is ready with this systemctl command that checks the status of a k3s Service :

sudo systemctl status k3s

MySQL and phpMyAdmin with K3s Kubernetes

However, due to permission issues, you might get a k3s.yaml/KUBECONFIG error. In such a case, run the following command and the error will be resolved:

curl -sfL https://get.k3s.io | K3S_KUBECONFIG_MODE="644" sh -s -

Creating MySQL Persistent Kubernetes Volume

The data just on your K3s MySQL server will change. The database will get updated with new database, new tables, and records and you develop your Apps. These entities must remain as so, even when you stop or restart a MySQL cluster.

Kubernetes will let you create PersistentVolume to let K3s add MySQL storage to a given host path. Here, you tell k3s the capacity of your storage and its host path.

Go to your working directory and create a pv.yaml file.

touch pv.yaml

Your K3s MySQL storage will be as follows:

apiVersion: v1
kind: PersistentVolume
# Metadata, PersistentVolume labels
metadata:
  # Name of the PersistentVolume
  name: mysqldb-pv-storage
  # Labels to categorize the PersistentVolume
  labels:
    # Label to indicate the type of storage
    type: local
# the PersistentVolume
spec:
  # Sorage class
  storageClassName: manual
  # The capacity of the PersistentVolume
  capacity:
    # The amount of storage available
    storage: 5Gi
  # Access modes for the volume
  accessModes:
    # The volume can be mounted 
    # as read-write by a single node
    - ReadWriteOnce
  # the path on the host
  # Where the volume is stored
  hostPath:
    # Path on the host machine
    path: "/mnt/k3s-data/mysql"

Use the command to get the volume ready:

kubectl apply -f pv.yaml
########OutPut###########
# persistentvolume/mysqldb-pv-storage created
###################

PersistentVolume is provisioned by an administrator. This means its resources are independent of a given pod (in this case MySQL). This means, that in MySQL pod (as a user), you will need to create a Persistent Volume Claim (PVC) to consume PV resources.

PVC will request access to the hostPath (/mnt/k3s-data/mysql) to read/write. In such a case, if MySQL pod will ever need storage to preserve its data, it requests that storage from PVC. Kubernetes (K3s) will finally bind the PVC to PV and the pod can then mount the PVC as a volume to use the storage.

touch pvc.yaml
apiVersion: v1
# kind object of PersistentVolumeClaim
kind: PersistentVolumeClaim
# PersistentVolumeClaim Metadata
metadata:
  name: mysqldb-pv-claim-storage
# PersistentVolumeClaim
spec:
  # The storage class to use
  storageClassName: manual
  # Define the access modes
  accessModes:
    # The volume can be mounted as read-write
    - ReadWriteOnce
  # resources request
  resources:
    requests:
      # Request 5Gi of storage
      storage: 5Gi

You will need to apply this PVC with this command:

kubectl apply -f pvc.yaml
########OutPut###########
# persistentvolume/mysqldb-pv-storage created
###################

Confirm if these two volume claims are ready using these commands:

$ kubectl get pv
NAME                 CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                              STORAGECLASS   VOLUMEATTRIBUTESCLASS   REASON   AGE
mysqldb-pv-storage   5Gi        RWO            Retain           Bound    default/mysqldb-pv-claim-storage   manual         <unset>                          12m
$ kubectl get pvc
NAME                       STATUS   VOLUME               CAPACITY   ACCESS MODES   STORAGECLASS   VOLUMEATTRIBUTESCLASS   AGE
mysqldb-pv-claim-storage   Bound    mysqldb-pv-storage   5Gi        RWO            manual         <unset>                 13s

Creating a MySQL Deployment using K3s Kubernetes

A deployment is Kubernetes manifest to create pods and run MySQL server. K3s will use Deployment to Manage the lifecycle of the MySQL cluster.

You will create a manifest file as a Kubernetes Deployment. This deployment will act as a higher-level abstraction to manage the MySQL deployment. It does so by creating and managing a set of replica Pods.

The same deployment will get assigned the MySQL storage from the above PVC via the custom hostname set on your local machine.

Othe this Kubernetes k3s MySQL deployment will be:

  • Setting up MySQL variables
  • Adding MySQL root passwords
  • Adding a MySQL user
  • Initializing a new database.

Create this file to get the file ready:

touch mysqldb-deployment.yaml

Your MySQL manifest should look as follows:


apiVersion: apps/v1
# Deployment kind
kind: Deployment
# Deployment Metadata 
metadata:
  name: mysqldb-deployment
spec:
  # Selector to match pods with the label "app: mysqldb-deployment"
  selector:
    matchLabels:
      app: mysqldb-deployment
  # Strategy for updating pods
  # Recreate means to delete existing pods before creating new ones
  strategy:
    type: Recreate
  # pods Deployment Template
  template:
    # pods labels Metadata 
    metadata:
      labels:
        app: mysqldb-deployment
    spec:
      # The containers in the pod
      # MySQL
      containers:
        - image: mysql:8.0  # MySQL Docker image 
          name: mysqldb-deployment  # container Name
          env:  # Environment variables for the container
            - name: MYSQL_ROOT_PASSWORD
              value: root_pass  # MySQL Root password
            - name: MYSQL_DATABASE
              value: test_db  # Name of the database to create
            - name: MYSQL_USER
              value: test_user  # database user
            - name: MYSQL_PASSWORD
              value: user_pass  # Password for the database user
          ports:
            - containerPort: 3306  # MySQL container Port
              name: mysql  # Name of the port
          volumeMounts:
          # mount volumes
            - name: mysqldb-k3s-storage
            # Path inside the container where the volume will be mounted
              mountPath: /var/lib/mysql  
      volumes:
        - name: mysqldb-k3s-storage  #  volume name 
          persistentVolumeClaim:
            claimName: mysqldb-pv-claim-storage  # PersistentVolumeClaim to use

Let’s break down this code example:

  • The whole manifest deploy pod for K3s MySQL cluster.
  • Kubernetes API will create a MySQL container using the MySQL base image.
  • You’ll set the environment variables that MySQL will initiate when Kubernetes creates the pods. These are created under the env section.
  • MySQL uses port 3306. The point remains as default. Ensure this port is open and can be allocated.

Use this command to deploy MySQL with K3s:

kubectl apply -f mysqldb-deployment.yaml
########OutPut###########
# kubectl apply -f mysqldb-deployment.yaml
###################

Give Kubernetes a few minutes to provision your MySQL pod and use the following command to confirm is the container is ready:

$ kubectl get pods
########OutPut###########
NAME                                 READY   STATUS    RESTARTS   AGE
mysqldb-deployment-d48f879cf-jkb5t   1/1     Running   0          91s

Running K3s MySQL Pod with Kubernetes Service

A Kubernetes service exposes a container so you can use it. Once the Pod has allocated port 3306 to MySQL, Kubernetes will then need an access point using the Kubernetes service.

And create something simple as such:

touch mysqldb-service.yaml
# the API version 
# service object
apiVersion: v1
kind: Service
# Metadata for the Service
metadata:
  name: mysqldb-service
spec:
  # the ports exposed by the Service
  ports:
    # The port that the Service will expose
    - port: 3306
  # Selector to match pods with the deployment label
  # app: mysqldb-deployment
  selector:
    app: mysqldb-deployment
  # Set up a headless service with cluster IP
  clusterIP: None
kubectl apply -f mysqldb-service.yaml
########OutPut###########
# service/mysqldb-service created
###################

Running the K3s Kubernetes Container-persistent-storagester with MySQL

Everything is now to you will need to execute these commands as such:

  • Run the deployment:
kubectl apply -f mysqldb-deployment.yaml
  • Expose the deployment with a Kubernetes service:
kubectl apply -f mysqldb-service.yaml

Give Kubernetes time to set everything up and use the following command to confirm if the MySQL pod is ready:

kubectl get pods
NAME                                 READY   STATUS    RESTARTS   AGE
mysqldb-deployment-d48f879cf-jkb5t   1/1     Running   0          6m28s

Note: In this case, the pod name will be mysqldb-deployment-d48f879cf-jkb5t To access the database, un an interactive Kubernetes shell to access the above pod as such:

kubectl exec --stdin --tty pod-name -- /bin/bash
## For exmaple#
kubectl exec --stdin --tty mysqldb-deployment-d48f879cf-jkb5t -- /bin/bash

Access the Mysql Shell with this command:

mysql -p
$ kubectl exec --stdin --tty mysqldb-deployment-d48f879cf-jkb5t -- /bin/bash
bash-5.1# 
bash-5.1# mysql -p
Enter password: 

The Next step is to add the MySQL password as added in the MYSQL_ROOT_PASSWORD. Here, the password will be root_pass, or whatever value you added as such:

bash-5.1# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.38 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

You now have access to the MySQL database. You can run a command to access the database. For this case, K3s will have initialized MySQL with a test_db. You’ll use:

SHOW DATABASEs;

Command to check such:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.02 sec)

You can now use this shel to interact with your database. However, this must be the longest way to access MySQL.

phpMyAdmin is a nice tool to get graphical access to MySQL servers. Let’s now use K3s and Kubernetes and access MySQL with phpMyAdmin.

Accessing MySQL with phpMyAdmin on Kubernetes

Just like MySQL, you will use a Kubernetes manifest to create a K3s deployment.

However, in this case, the phpMyAdmin must be in communication with the MySQL pods. You will need to set environment variables with:

  • The hostname and the container name of the above created MySQL service, mysqldb-service.
  • The port opened to listed MySQL connections, 3306 and
  • MYSQL_ROOT_PASSWORD to login to phpMyAdmin UI.

Go ahead and create a new file:

touch phpmyadmin-deployment.yaml

Add MySQL phpMyAdmin deployment as follows:

# Define the API version for the deployment object
apiVersion: apps/v1
# Specify the kind of object being created, in this case, a Deployment
kind: Deployment
# Metadata for the Deployment, including the name and labels
metadata:
  # Name of the Deployment
  name: mysql-phpmyadmin-ui
  # Labels to categorize the Deployment
  labels:
    app: mysql-phpmyadmin-ui
# Specification of the Deployment
spec:
  # Number of pod replicas to run
  replicas: 1
  # Selector to match pods
  # label is "app: mysql-phpmyadmin-ui"
  selector:
    matchLabels:
      app: mysql-phpmyadmin-ui
  # Deployment Template for the pod
  template:
    # pods Metadata
    metadata:
      labels:
        app: mysql-phpmyadmin-ui
    spec:
      containers:
        # name the container
        - name: mysql-phpmyadmin-ui
          # Docker image as phpMyAdmin
          image: phpmyadmin/phpmyadmin
          # Define the ports for the container
          ports:
            # phpMyAdmin Port container listens on
            - containerPort: 80
          # container Environment variables
          env:
            # Hostname of the MySQL server
            # (As named in MySQL deployment)
            - name: PMA_HOST
              value: mysqldb-service
            # Port of the MySQL server
            - name: PMA_PORT
              value: "3306"
            # Root password for MySQL
            # (As added in MySQL deployment)
            - name: MYSQL_ROOT_PASSWORD
              value: root_pass

Deploy this cluster to K3s using this command:

kubectl apply -f phpmyadmin-deployment.yaml

########OutPut###########
# deployment.apps/mysql-phpmyadmin-ui created
###################

The pods should be ready:

$ kubectl get pods
NAME                                   READY   STATUS    RESTARTS   AGE
mysql-phpmyadmin-ui-77cfd87b5c-vx7sc   1/1     Running   0          69s
mysqldb-deployment-d48f879cf-jkb5t     1/1     Running   0          46m

To access phpMyAdmin on the web, you’ll use NodePort which maps to the pod’s port 80. Create a new service file:

touch phpmyadmin-service.yaml

Update the file and let the service access the mysql-phpmyadmin-ui container as follows:

apiVersion: v1
# kind of object
# a Service
kind: Service
# Service Metadata 
metadata:
  # Name of the Service
  name: mysql-phpmyadmin-service
# Specification of the Service
spec:
  # type of Service
  # NodePort
  type: NodePort
  # Selector to match pods
  # Label "app: mysql-phpmyadmin-ui"
  selector:
    app: mysql-phpmyadmin-ui
  # ports exposed
  ports:
    # The protocol OF THIS Service
    - protocol: TCP
      # Port the Service will expose
      port: 80
      # Target port on the pod
      # Service will forward traffic here
      targetPort: 80
kubectl apply -f phpmyadmin-service.yaml

########OutPut###########
# service/mysql-phpmyadmin-service created
###################

Let’s check the created services.

kubectl get svc

You will get:

$ kubectl get svc
NAME                       TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)        AGE
kubernetes                 ClusterIP   10.43.0.1      <none>        443/TCP        2d5h
mysql-phpmyadmin-service   NodePort    10.43.55.170   <none>        80:31058/TCP   43s
mysqldb-service            ClusterIP   None           <none>        3306/TCP       44m

The main focus here is the mysql-phpmyadmin-service. You will need to check its PORT(S) Based on this example, I have 80:31058/TCP. This means the phpMyAdmin will get accessed on http://localhost:31058/ on the web:

MySQL and phpMyAdmin with K3s Kubernetes

You will use the values to log in:

 - name: MYSQL_USER
              value: test_user  # database user
 - name: MYSQL_PASSWORD
              value: user_pass  # Password for the database user

Enter:

  • The test_user as username.
  • Password will be the value of MYSQL_ROOT_PASSWORD which is user_pass

MySQL and phpMyAdmin with K3s Kubernetes

Delete all MySQL K3s Resources

To delete all the resources used in this guide, use these commands:

kubectl delete -f pv.yaml
kubectl delete -f pvc.yaml
kubectl delete -f mysqldb-deployment.yaml
kubectl delete -f mysqldb-service.yaml
kubectl delete -f phpmyadmin-deployment.yaml
kubectl delete -f phpmyadmin-service.yaml

Conclusion

You have learned how to perfectly create and run a MySQL database with Kubernetes K3s cluster. You learned:

  • How to create MySQL Kubernetes K3s deployments and Services.
  • Pesisitng MySQL data within the K3s cluster.
  • How to use phpMyAdmin with K3S and access a MySQL Kubernetes cluster.
Deploying MySQL and phpMyAdmin with K3s Kubernetes

Written By:

Joseph Chege