How to Deploy MySQL on Kubernetes

Introduction

Kubernetes is a robust solution for managing stateful applications, with many features that help maintain application availability and integrity.

Deploying a MySQL database on Kubernetes can streamline an application's scalability. However, ensuring a database's availability and state in a containerized environment can be demanding.

This article will show you how to deploy a MySQL database instance on Kubernetes, including how to create persistent volumes.

Prerequisites

• A Kubernetes cluster with kubectl installed.
• Administrative access to your system via the command line/terminal.

Deploy MySQL on Kubernetes

To successfully deploy a MySQL instance on Kubernetes, create three YAML files to define the essential Kubernetes objects:

• mysql-secret.yaml. Defines the Kubernetes secret for storing the database password.
• mysql-storage.yaml. Contains the Persistent Volume (PV) definition for the database storage space allocation and a Persistent Volume Claim (PVC) to claim the PV for the deployment.
• mysql-deployment.yaml. The deployment itself and the Kubernetes Service.

Follow the steps in the sections below to see how to deploy MySQL on Kubernetes.

Step 1: Create Kubernetes Secret

Create a Secret file that stores the database password. Follow the steps below:

1. Open a text editor such as nano to create the Secret file:

nano mysql-secret.yaml

2. Use the following example as a reference for the Secret file syntax:

apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
type: kubernetes.io/basic-auth
stringData:
  password: [password]

Enter the password for the root MySQL account in the stringData.password section of the YAML.

3. Save the file and exit.

4. Use the following kubectl command to apply the changes to the cluster.

kubectl apply -f mysql-secret.yaml

The system confirms the successful creation of the secret.

Step 2: Create Persistent Volume and Volume Claim

Use a single file to create the complete storage configuration for the MySQL deployment. It must include the persistent volume (PV) and the persistent volume claim (PVC). See the steps below:

1. Create the storage configuration file:

nano mysql-storage.yaml

2. Add the data in the following format:

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

This file consists of two parts:

• Persistent Volume. Customize the amount of allocated storage in spec.capacity.storage. In spec.hostPath specify the volume mount point.
• Persistent Volume Claim. Adjust accessModes based on how the volume should be accessed (e.g., ReadWriteOnce, ReadWriteMany). The spec.resources.requests.storage should match the spec.capacity.storage defined for the PV.

3. Save the file and exit nano.

4. Apply the storage configuration with kubectl:

kubectl apply -f mysql-storage.yaml

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

Step 3: Create MySQL Deployment and Service

The final file defines the deployment and Kubernetes Service:

1. Create the deployment file:

nano mysql-deployment.yaml

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

2. Define the Deployment and Service in separate sections. Add the data in the following format:

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:5.6
        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

This file contains two parts:

• Deployment. Define the MySQL container image, set environment variables, and configure volume mounts. Adjust image, env (name should match from Step 1), and volumeMounts (name should match from Step 2).
• Service. Expose the MySQL deployment to other services or external clients. The port should match the port used by the MySQL container.

3. Save the file and exit.

4. Create the deployment by applying the file with kubectl:

kubectl apply -f mysql-deployment.yaml

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

Working with MySQL on Kubernetes

Once the MySQL instance is deployed, you can access, update, and delete the MySQL instance and deployment. The sections below show how to work with MySQL on Kubernetes.

Access Your MySQL Instance

To access the MySQL instance, connect to the pod created by the deployment:

1. List the pods:

kubectl get pod

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 [pod_name] -- /bin/bash

The pod shell replaces the main 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.

Update MySQL Deployment

Edit the relevant YAML file to update any part of the deployment. Use this command to apply the changes:

kubectl apply -f [file_name]

However, bear in mind the following two limitations:

• This particular deployment is for single-instance MySQL deployment. It means that the deployment cannot be scaled as it works on exactly one pod.
• This deployment does not support rolling updates. Therefore, the spec.strategy.type must always be set to Recreate.

Delete Your MySQL Instance

To remove the entire deployment, use kubectl to delete each related Kubernetes object:

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. The system confirms the successful deletion.

Conclusion

This guide showed how to deploy a single MySQL instance on Kubernetes. Additionally, the article also provided instructions on how to access, update, and delete a MySQL instance on Kubernetes.

For an exhaustive list of important MySQL commands, see this MySQL Commands Cheat Sheet.