Deploying MySQL on Kubernetes

Introduction

In the initial stages of this article, we’ll explore the challenges involved in setting up a functional database within a containerized environment. These challenges revolve around ensuring the database’s availability, state management, and redundancy. Despite these complexities, many developers find it advantageous to align their application stacks and data layers with the efficient deployment and automation principles offered by platforms like Kubernetes.

This article is dedicated to demonstrating the deployment of MySQL database instance on Kubernetes, leveraging the power of persistent volumes. This capability is particularly crucial for stateful applications, as it mitigates the inherent transient nature of Kubernetes pods.

Prerequisites

Before delving into the MySQL deployment process, you should have:

Kubernetes cluster in place, along with the installation of kubectl. You can download the script that installs all the necessary requirement from my Github page.

Please note that this tutorial focuses on deploying a single-instance MySQL database. For clustered stateful applications, you would need to create StatefulSet objects.

The process of deploying MySQL on Kubernetes involves creating several YAML files to define the following Kubernetes components:

• A Kubernetes secret for securely storing the database password.
• A Persistent Volume (PV) to allocate storage space for the database.
• A Persistent Volume Claim (PVC) responsible for claiming the allocated PV for the deployment.
• The actual database deployment.
• Setting up the Kubernetes Service for MySQL.

Step 1: Create Kubernetes Secret

Use a text editor: In the case, I will be using vi to create the Secret file.

vi mysql-secret.yaml

The file defines the secret. Enter the password for the root MySQL account in the stringData section of the YAML.

apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
type: kubernetes.io/basic-auth
stringData:
  password: @YourPassword$

Here’s a breakdown of each section:

apiVersion: v1: This line specifies the API version of the Kubernetes object being created.

kind: Secret: A Secret is used to store and manage sensitive information, such as passwords, API tokens, or any confidential data that should not be exposed in plain text.

metadata: This section contains metadata about the Secret, including its name.

name: mysql-secret: This name is how you will reference and access the Secret in other parts of your Kubernetes configuration.

type: kubernetes.io/basic-auth: This type indicates that the Secret is intended to store basic authentication credentials, typically used for usernames and passwords.

stringData: This section is where the actual sensitive data is stored in the Secret.

password: @YourPassword$: This represents the password you want to securely store.

Save the file and exit. Use Kubectl to apply the changes to the cluster.

kubectl apply -f mysql-secret.yaml

The system confirms the successful creation of the secret:

mysql-secret

Step 2: Create Persistent Volume and Volume Claim

Create the storage configuration file:

vi mysql-storage.yaml

This file consists of two parts:

• The first part defines the Persistent Volume. Customize the amount of allocated storage in spec.capacity.storage. In spec.hostPath specify the volume mount point.
• The second part of the file defines the PVC.

apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv-volume
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 20Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/mnt/data"
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 20Gi

Here’s a breakdown of the spec section under the kind: PersistentVolume

NB: You can customize the amount of allocated storage in spec.capacity.storage. In spec.hostPath specify the volume mount point.

spec: This section contains the specifications for the PersistentVolume.

storageClassName: manual: This specifies the storage class to be used for this PersistentVolume. A storage class is a way to define the storage provisioner and reclaim policy for the volume.

capacity: It specifies the storage capacity of the PersistentVolume.

storage: 5Gi: This sets the capacity of the PersistentVolume to 5gigabytes.

accessModes: This specifies the access modes for the PersistentVolume.

- ReadWriteOnce: This access mode allows the volume to be mounted as read-write by a single node at a time.
hostPath: This section defines the source of the storage.

path: “/mnt/data”: The PersistentVolume is created using storage located on the host machine at the path “/mnt/data.”

Save the file and exit.

Then, apply the storage configuration with kubectl.

kubectl apply -f mysql-storage.yaml

The system confirms the creation of the PV and the PVC.

pv and pvc created.

Step 3: Create MySQL Deployment

1. Create the deployment file. The deployment file defines the resources the MySQL deployment will use.

vi mysql-deployment.yaml

2. In the separate section of the file, define the service name and port.

The entire YAML should look like in the example below:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - image: mysql:latest
        name: mysql
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        ports:
        - containerPort: 3306
          name: mysql
        volumeMounts:
        - name: mysql-persistent-storage
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-persistent-storage
        persistentVolumeClaim:
          claimName: mysql-pv-claim
---
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  ports:
  - port: 3306
  selector:
    app: mysql

Let’s understand the components of the YAML file:

Deployment (apiVersion: apps/v1, kind: Deployment):

metadata: This section specifies the metadata for the Deployment.

name: mysql: The name “mysql” is assigned to this Deployment.
spec: This section describes the specifications for the Deployment.

selector: It specifies how the Deployment selects which Pods to manage.

matchLabels: This section defines the labels that the Deployment uses to select Pods to manage.

app: mysql: The Deployment selects Pods with the label “app” equal to “mysql.”
strategy: It defines the update strategy for the Deployment. In this case, the type is “Recreate,” meaning the existing Pods are terminated and new ones are created during updates.

template: This section describes the Pod template for the Deployment.

containers: This is an array of containers running in the Pod. In this case, there is one container.

image: mysql:latest: It specifies the Docker image to use for the container, which is the latest version of MySQL.

name: mysql: The name of the container is “mysql.”

env: This section defines environment variables for the container.

name: MYSQL_ROOT_PASSWORD: An environment variable named “MYSQL_ROOT_PASSWORD.”

valueFrom: This indicates that the value of the environment variable should be obtained from a Secret.

secretKeyRef: It specifies that the value comes from a Secret named “mysql-secret,” with the key “password.” This is where the MySQL root password is stored securely.
ports: It specifies the ports to open in the container.

containerPort: 3306: Port 3306 is opened for MySQL connections.
volumeMounts: This section defines where to mount volumes in the container.

name: mysql-persistent-storage: It specifies the volume name.

mountPath: /var/lib/mysql: The path within the container where the volume will be mounted. This is typically the location where MySQL stores its data.

volumes: This section specifies the volumes to be used in the Pod.

name: mysql-persistent-storage: The name of the volume matches the one specified in volumeMounts.

persistentVolumeClaim: It references a PersistentVolumeClaim (PVC) named “mysql-pv-claim.” The PVC provides storage resources for the Pod.

Service (apiVersion: v1, kind: Service):

ports: This section defines the ports to be exposed by the Service.

port: 3306: Port 3306 is exposed, which corresponds to the port that MySQL uses for database connections.
selector: It selects the Pods to forward network traffic to.

app: mysql: The Service forwards traffic to Pods with the label “app” equal to “mysql.” These are the Pods managed by the “mysql” Deployment.

Save the file and exit. Create the deployment by applying the file with kubectl:

kubectl apply -f mysql-deployment.yaml

The system confirms the successful creation of both the deployment and the service.

mysql-deployment

Access Your MySQL Instance

To access the MySQL instance, access the pod created by the deployment.

1. List the pods:

kubectl get pod

MySQL pod is running.

2. Find the MySQL pod and copy its name by selecting it and pressing Ctrl+Shift+C:

3. Get a shell for the pod by executing the following command:

kubectl exec --stdin --tty mysql-74f8bf98c5-bl8vv -- /bin/bash

The pod shell replaces the main shell:

bash shell

4. Type the following command to access the MySQL shell:

mysql -p

5. When prompted, enter the password you defined in the Kubernetes secret.

The MySQL shell appears.

MySQL shell

6. Enter the SQL command to show the database.

SQL command

Update Your MySQL Deployment

Edit the relevant YAML file to update any part of the deployment. Apply the changes with:

kubectl apply -f [filename]

Nevertheless, please consider the following two constraints:

1. This specific deployment is intended for a solitary MySQL instance, implying that it cannot be expanded to multiple Pods; it operates exclusively with one Pod.
2. This deployment lacks support for rolling updates, necessitating the constant configuration of spec.strategy.type as “Recreate.”

Delete Your MySQL Instance

If you intend to remove the entire deployment, use kubectl to delete each of the Kubernetes objects related to it:

kubectl delete deployment,svc mysql
kubectl delete pvc mysql-pv-claim
kubectl delete pv mysql-pv-volume
kubectl delete secret mysql-secret

This series of commands delete the deployment, the service, PV, PVC, and the secret you created. The system confirms the successful deletion:

Deleted Kubernetes Object

Conclusion

After completing this tutorial, you should be able to deploy a single MySQL instance on Kubernetes.

If you found this guideline helpful, click on the clap button or you can drop a comment.

Follow for more interesting stories.