inanzzz | Using Kubernetes StatefulSet to persist MySQL data

In Kubernetes, if you want to to provide persistence for your pods, you can use a StatefulSet. Unlike a Deployment, a StatefulSet holds on to the identity for each of its Pods which is ideal for stateful apps. A StatefulSet requires a Headless Service to be responsible for Pods' identity.

It tends to be used for single-instance apps as the underlying PersistentVolume can only be mounted to one Pod. You can use it if you don't mind data loss in environments like development, test or qa. Avoid using it in production where it makes sense. Err towards cloud based storage instead in production.

Configuration

apiVersion: v1
kind: Namespace
metadata:
  name: storage

---

apiVersion: v1
kind: Service
metadata:
  name: mysql
  namespace: storage
  labels:
    app: mysql
spec:
  clusterIP: None
  selector:
    app: mysql
  ports:
    - name: tcp
      protocol: TCP
      port: 3306

---

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  namespace: storage
spec:
  replicas: 1
  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
              value: password
          volumeMounts:
            - name: data
              mountPath: /var/lib/mysql
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        storageClassName: standard
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 1Gi

Setup

Run $ kubectl apply -f mysql.yaml command to setup configuration above and the result should look like below.

$ kubectl -n storage get all
NAME          READY   STATUS    RESTARTS   AGE
pod/mysql-0   1/1     Running   0          11s

NAME            TYPE        CLUSTER-IP   EXTERNAL-IP   PORT(S)    AGE
service/mysql   ClusterIP   None                 3306/TCP   11s

NAME                     READY   AGE
statefulset.apps/mysql   1/1     11s

Test

Run $ kubectl -n storage port-forward service/mysql 3306:3306 command to expose MySQL service to local environment. I created a database in MySQL and added three records to it. It looks like below.

$ kubectl -n storage exec -it pod/mysql-0 -c mysql bash

root@mysql-0:/# mysql -uroot -ppassword

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| inanzzz            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE inanzzz;
Database changed

mysql> SHOW TABLES;
+-------------------+
| Tables_in_inanzzz |
+-------------------+
| users             |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

If you delete pod, service and statefulset, your data still be retained. However, if you delete namespace, it will be gone.