How to Deploy MySQL Statefulset in Kubernetes

Reading Time: 4 minutes

This blog aims to explain each of the components required to deploy MySQL statefulset cluster on Kubernetes.

While deploying the MySQL on Kubernetes, what object type should be used and why? Deployments or StatefulSets?

The answer is StatefulSet. Let’s discuss!

StatefulSet is the Kubernetes object used to manage stateful applications.It is preferred over deployments as it provides guarantees about the ordering and uniqueness of these Pods i.e. the management of volumes is better with stateful sets.

Why do We Need MySQL Statefulset?


MySQL is going to be a stateful application i.e. it stores data (like tables, users) inside a volume. If the data is stored in pod ephemeral storage, then the data will get erased once the pod restarts.

Also, MySQL may have to be scaled to more than one pod in caseload increases.

All these operations have to be done in such a way that data consistency is maintained across all the pods like mysql-0, mysql-1.

Prerequisites:

  • A Kubernetes Cluster (I will be using minikube single node cluster on my local system)

Secret

Secrets in Kubernetes are the objects used for supplying sensitive information to containers.They are like ConfigMaps with the difference that data is store in a base 64 encoded format.

For the security of our MySQL cluster, it is wise to restrict access to the database with a password. We will use Secrets to mount our desired passwords to the containers.

In this tutorial, we use base64 encoded to store ‘MYSQL_ROOT_PASSWORD’. For example:

$ echo -n "password" | base64
cGFzc3dvcmQ==

Keeping note of the outputted value and create a new file named secret.yml

$ vi secret.yml
apiVersion: v1
kind: Secret
metadata: 
    name: mysecret
type: Opaque
data:
   ROOT_PASSWORD: cGFzc3dvcmQ=

Now run the kubectl apply command to create the secret in Kubernetes.

$ kubectl apply -f secret.yml 
secret/mysecret created

We can use the kubectl describe secret command to display additional information about the resource.

$ kubectl describe secret/mysecret
Name:         mysecret
Namespace:    default
Labels:       <none>
Annotations:  <none>

Type:  Opaque

Data
====
ROOT_PASSWORD:  8 bytes

Under data we can see the key we defined in the secret.yml file, however, we do not see the actual value. We only know that the value is 8 bytes in length. We wouldn’t expect to see the value as wouldn’t be very secure if we could.

Services

In Kubernetes services are the objects that pods use to communicate with each other. Services of type ClusterIP are usually used for inter-pod communication.

Normal Kubernetes services act as load balancers and follow round-robin logic to distribute loads. Headless services don’t act like load balancers.

In the case of MySQL servers, we require a headless service because it is a requirement for the MySQL statefulset.

Why do we need MySQL Headless Service?

As you know, headless svc does not work as a load balancer and is used to address a group of pods together. There is another use case for headless services.

We can use it to get the address of individual pods. Let’s take an e.g. to understand this. We have two pods running as part of the Mysql Statefulset.

Pod name Pod Address
mysql-0  172.17.0.3
mysql-1  172.17.0.8

and a headless svc “mysql” svc is pointed to these pods.

If you do a nslookup from a pod running inside the same namespace of your cluster, you’ll be able to get the address of the above pods through the headless svc.

MySQL uses port number 3306 by default.
3306 port number is used by MySQL protocol to connect with the MySQL clients and utilities such as ‘mysqldump’. It is a Transmission Control Protocol.

In general, port 3306 shouldn’t be opened since it could make the server vulnerable to attack. If the user needs to connect to the database remotely, there are many other secure options, instead of opening the port 3306.

Create the Service from the following service.yml configuration file:

apiVersion: v1
kind: Service
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  clusterIP: None
  selector:
    app: mysql
  ports:
    - name: tcp
      protocol: TCP
      port: 3306
$ kubectl apply -f service.yml 
service/mysql created

StatefulSet

Finally, create the StatefulSet from the following statefulset.yml configuration file:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  replicas: 2
  serviceName: mysql
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      terminationGracePeriodSeconds: 10
      containers:
        - name: mysql
          image: mysql:5.6
          ports:
            - name: tpc
              protocol: TCP
              containerPort: 3306
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom: 
               secretKeyRef: 
                key: ROOT_PASSWORD
                name: mysecret
          volumeMounts:
            - name: data
              mountPath: /var/lib/mysql
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        storageClassName: standard
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 1Gi
$ kubectl apply -f statefulset.yml 

Verifying the replica set deployment and accessing the replica set

If we execute the commands: kubectl get sts and kubectl get pods -l app=mysql , we see the cluster deployed.

Login to the MySQL pod and Verify:

kubectl exec -it mysql-0 -- mysql -u root -p

We have now logged into the MySQL database.

Create Sample Tables and Data:

If you want to scale the replica you can use the following command:

$ kubectl scale sts mysql --replicas 3

Conclusion:

That’s all folks! We have covered the detailed guide on deploying the MySQL statefulset on Kubernetes.In this blog we have setup a 2 node MySql cluster.We also looked how to scale the number of node.That’s all for this blog .If you want to setup Mongodb cluster also, you can refer the following blog:

References:

https://kubernetes.io/docs/concepts/workloads/controllers/statefulset/

Written by 

Jubair Ahmad is a Software Intern at Knoldus.Inc.He loves learning new technology and also have interest in playing cricket.

Leave a Reply