Categories
kubernetes

How to deploy MySQL using Persistent volume in Kubernetes

Last updated on August 3rd, 2022 at 08:24 am

Click to rate this tutorial!
[Total: 1 Average: 5]

In this tutorial we are going to deep dive in to deploying MySQL database using persistent volumes. Simple approach without much complexity.

Let us break this in to multiple steps for easier understanding

What are PODS?

POD represent single instance of a running process in your K8 cluster. PODs are ephemeral, If a POD running on a node fails it will get delete and all resources associated with the POD such as storage(volume) also get destroyed. In other words volume associated with a POD exist as long as the POD is up and running.

When the POD get destroyed even if an identical replacement is created, the related volume is also destroyed. This means that you will get a brand new POD with a new volume. Read more about pod lifecycle here.

Persistent volume(PV) is a storage concept within cluster provisioned using Storage Classes. PV’s are plugins that has a lifecycle independent of the POD. PersistentVolumeClaim (PVC) is a request for storage by a user. More details about PVC and PV

Using DigitalOcean

In this example we are using DigitalOcean managed Kubernetes service to deploy MySQL.

StatefulSet with Persistent Volume

For storage volumes to provide persistence StatefulSets can be used. Although individual Pods are still prone to failure, StatefulSet make it easier to match existing volumes to the new Pods when they get replaced.

Save the below YAML file as mysql_digital_ocean.yml . Update the MySQL password under env section according to your choice.

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: new-mysql-statefulset
spec:
  replicas: 1
  serviceName: mysql
  selector:
    matchLabels:
      app: new-mysql
  template:
    metadata:
      labels:
        app: new-mysql
    spec:
      terminationGracePeriodSeconds: 10
      containers:
        - name: mysql
          image: mysql:5.6
          ports:
            - name: tpc
              protocol: TCP
              containerPort: 3306
          env:
            - name: MYSQL_ROOT_PASSWORD
              value: password
          volumeMounts:
            - name: new-mysql-data-claim
              mountPath: /var/lib/mysql
  volumeClaimTemplates:
    - metadata:
        name: new-mysql-data-claim
      spec:
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 1Gi

Next step is to use kubectl to read the file and create StatefulSet, Read more on VolumeClaimTemplates

$ kubectl apply -f mysql_digital_ocean.yml
statefulset.apps/new-mysql-statefulset created
$ 

Once you get the above message after applying the YAML file, run the kubectl get command to see the status of Statefulset, Pods and PVC

$ kubectl get statefulset
NAME                    READY   AGE
new-mysql-statefulset   1/1     33s

$ kubectl get pods
NAME                                          READY   STATUS    RESTARTS   AGE
new-mysql-statefulset-0                       1/1     Running   0          36s

$ kubectl get pvc
NAME                                           STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS       AGE
new-mysql-data-claim-new-mysql-statefulset-0   Bound    pvc-7730911c-c30e-40a9-b3d7-4935cabd8865   1Gi        RWO            do-block-storage   50m
$ 

From the output we can see that it created a
StatefulSet named new-mysql-statefulset
Pod was created with name new-mysql-statefulset-0
PVC name new-mysql-data-claim-new-mysql-statefulset-0

Create MySQL Service

Now that we have the MySQL container in place let us create a service for MySQL so that external applications can connect to the container. Name the file as mysql_service.yml

apiVersion: v1
kind: Service
metadata:
  name: new-mysql-service
  labels:
    app: new-mysql-statefulset
spec:
  selector:
    app: new-mysql
  ports:
    - name: tcp
      protocol: TCP
      port: 3306
      targetPort: 3306

Once the file is created run this command

$ kubectl apply -f mysql_service.yml
service/new-mysql-service created
$

Run the describe pod command to see the details of the service we just created.

$ kubectl describe service/new-mysql-service
Name:              new-mysql-service
Namespace:         default
Labels:            app=new-mysql-statefulset
Annotations:       <none>
Selector:          app=new-mysql
Type:              ClusterIP
IP Family Policy:  SingleStack
IP Families:       IPv4
IP:                10.245.252.95
IPs:               10.245.252.95
Port:              tcp  3306/TCP
TargetPort:        3306/TCP
Endpoints:         10.244.0.22:3306
Session Affinity:  None
Events:            <none>
[email protected] Good_Working

Keep an eye of the attribute name Endpoints, it says
Endpoints: 10.244.0.22:3306 .

Run the get pod command with -o wide option to see our mysql pod ip address and make sure that the Endpoint above matches.

$ kubectl get pods -o wide
NAME                                          READY   STATUS    RESTARTS   AGE     IP            NODE                   NOMINATED NODE   READINESS GATES
new-mysql-statefulset-0                       1/1     Running   0          10m     10.244.0.22

Let us check the MySQL service details

$ kubectl get services
NAME  TYPE  CLUSTER-IP   EXTERNAL-IP   PORT(S)      AGE
new-mysql-service    ClusterIP  10.245.252.95   <none>           3306/TCP       3d18h
$ 

Cluster IP is 10.245.252.95, you can connect to the MySQL database using this IP and port number is 3306. Database connection details configured within your application has to be updated using the above details.

Using a service for your MySQL database ensures that your application is not dependent on the POD lifecycle and associated IP addresses. All you have to worry about is the MySQL ClusterIP service IP address as shown above.

Test MySQL connectivity

Now that you have your MySQL service up and running let us connect to MySQL running on Kubernetes by

1] Creating a database and table
2] Insert data to the table

Before we create a database we need to connect to the pod.

$ kubectl get pods

Using the get pods command we will get the name of the pod. In this case it will be new-mysql-statefulset-0

Now use kubectl exec command to connect to and then issue the mysql command to connect to the MySQL server inside the pod as shown. Password will be “password” (From mysql_digital_ocean.yml )

$ kubectl -it exec new-mysql-statefulset-0 -- /bin/bash
[email protected]:/# mysql -u root -p

Once inside the mysql prompt, run these

mysql> create database test_mistonline;
Query OK, 1 row affected (0.01 sec)

mysql> use test_mistonline;
Database changed

mysql> CREATE TABLE kube_first (ID int,Name varchar(255) ,Address varchar(255),City varchar(255));
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO kube_first (ID,Name,Address,City) VALUES ('1','Steve','1st Street', 'Seattle');
Query OK, 1 row affected (0.01 sec)

mysql> select * from kube_first;
+------+-------+------------+---------+
| ID   | Name  | Address    | City    |
+------+-------+------------+---------+
|    1 | Steve | 1st Street | Seattle |
+------+-------+------------+---------+
1 row in set (0.00 sec)

mysql>

As you can see above we created a database and a table, then inserted some data in to the table inside the POD.

Test MySQL Data Persistence

This is the final step in our deployment to test whether the data we created above persist if the POD is destroyed. Let’s do that now. We are deleting the pod using the kubectl delete command.

$ kubectl get pods
NAME                                          READY   STATUS    RESTARTS   AGE
new-mysql-statefulset-0                       1/1     Running   0          3d18h


$ kubectl delete pod/new-mysql-statefulset-0
pod "new-mysql-statefulset-0" deleted

$ kubectl get pods
NAME                                          READY   STATUS              RESTARTS   AGE
new-mysql-statefulset-0                       0/1     ContainerCreating   0          3s

$ kubectl get pods
NAME                                          READY   STATUS    RESTARTS   AGE
new-mysql-statefulset-0                       1/1     Running   0          39s

As you can see the pod got deleted and then recreated automatically. Next step is to check whether the database and table we created before the POD got destroyed are still available.

$ kubectl -it exec new-mysql-statefulset-0 -- /bin/bash

[email protected]:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| #mysql50#lost+found |
| mysql               |
| performance_schema  |
| test_mistonline     |
+---------------------+
5 rows in set (0.00 sec)

mysql> select * from test_mistonline.kube_first;
+------+-------+------------+---------+
| ID   | Name  | Address    | City    |
+------+-------+------------+---------+
|    1 | Steve | 1st Street | Seattle |
+------+-------+------------+---------+
1 row in set (0.00 sec)

mysql>

I logged in to the newly created pod and boom I do have the data inside my MySQL table intact. Awesome!!

Hope this tutorial gave you insight on how data persistence works in Kubernetes when you have a database pod running.

Click to rate this tutorial!
[Total: 1 Average: 5]

Leave a Reply

Your email address will not be published.