Getting anything running on Kubernetes is a bit of a challenge, but today I was working on deploying MySQL so I could migrate my Ghost blog from v4.x to v5.x. This means creating a mysql instance, a user (for ghost) and any other data that we need to run the deployment. The first thing that we need to do is add a secret which will define the root user password and the password for ghost user:

# secrets.yaml
apiVersion: v1
kind: Secret
metadata:
  name: mysql-secrets
type: Opaque
data:
  database__connection__password: {{ openssl rand -hex 20 | base64 }}
  database__user__password: {{ openssl rand -hex 20 | base64 }}

Next, we need a persistent volume store which will retain the databse data if the pod dies. Since I’m using Linode’s LKE, we can use linode’s block storage:

# volume.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi
  storageClassName: linode-block-storage-retain

Note above that we generate a random password for both of the users. Next, we need to create the deployment and service for the mysql instance. We choose mysql version 8 (since it’s the latest), and we attach the persistent volume store here.

# deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  strategy:
    type: Recreate
  replicas: 1
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - image: mysql:8
        name: mysql
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secrets
              key: database__connection__password
        - name: MYSQL_USER_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secrets
              key: database__user__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

Finally, we create the service:

# service.yaml
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  ports:
  - port: 3306
  selector:
    app: mysql

Assuming we put all of these yaml files in the same director (which I called mysql), we can now deploy the pods with:

kubectl apply -f ./mysql

Finally, we’ll add the user to the database. We can SSH to the created pods by using:

kubectl get pods
kubectl exec --stdin --tty $POD_NAME -- /bin/bash

We can load up mysql and create the user with:

mysql -p"$MYSQL_ROOT_PASSWORD" -e "CREATE USER 'user'@'%' IDENTIFIED BY '${MYSQL_USER_PASSWORD}';"

We’d also want to run any commands here to set up tables, or grant user access.

And now we’re done! We have a user of name user with password stored in the kubernetes secret database__user__password and the mysql database running at host: mysql.default.svc.cluster.local.