This article shows you how to install MySQL with an Azure Files Storage backend on Azure Kubernetes Service (AKS). Azure Files Storage is a highly available and scalable file storage that provides network shares in the cloud. A major benefit of Azure Files over Azure Managed Disk is that multiple containers can access the same storage.

This article will walk you through the deployment step-by-step so that you can ensure your installation of MySQL with Azure Files Storage and AKS runs smoothly. We will discuss how to create the needed resources in Kubernetes. I.e. how you start the MySQL Docker Image as a deployment in the pod, configure it, provide it as a service and test it at the end.

Have fun!

Preparation

For the overview of these instructions, I use a separate .yaml file for each Kubernetes resource. Of course you can also put them all in the same file if you separate them with three dashes ---.

In principle, we need five different types of resources:

  • Storage Class
  • ConfigMap
  • PersistentVolumeClaim
  • Deployment
  • Service

Therefore, we first create the following five files: storageclass.yaml, configmap.yaml, pvc.yaml, deploy.yaml and ` ``service.yaml```. These will contain the complete resource description for k8s.

I use a new namespace test-ns for the test files. If you don’t already have one, you can create it like this:

kubectl create namespace test-ns

Storage Class

AKS comes with a few predefined StorageClasses. Including the azurefile-csi StorageClass. Normally it works perfectly. Unfortunately, we cannot use them with MySQL for the following reason.

The problem with azurefile-csi

MySQL tries to change the read and write permissions as well as the owner of the files. Also, MySQL tries to lure some files for InnoDB tables. The problem with this is that the AzureFiles file system does not store this information at all.

If you try with azurefile-csi, you might see errors like this in the logs:

2023-01-10T16:04:17.986024Z 0 [ERROR] [MY-012574] [InnoDB] Unable to lock ./#innodb_redo/#ib_redo0 error: 13
2023-01-10T16:04:17.991042Z 0 [ERROR] [MY-012894] [InnoDB] Unable to open './#innodb_redo/#ib_redo0' (error: 11).

It is also possible that the container does not start at all:

Back-off restarting failed container

Solution

As a workaround, we need to create our own StorageClass. To do this, we fill the corresponding file with the following content.

storageclass.yaml

apiVersion: storage.k8s.io/v1
metadata:
  name: mysql-azurefile
kind: StorageClass
mountOptions:
  - dir_mode=0777
  - file_mode=0777
  - uid=999
  - gid=999
  - mfsymlinks
  - nobrl
  - cache=strict
  - nosharesock
parameters:
  skuName: Standard_LRS
provisioner: file.csi.azure.com
reclaimPolicy: Delete
volumeBindingMode: Immediate

Owner and group must be set to 999, then MySQL is happy and doesn’t try to change anything. Important: Some versions may behave differently. With MySQL container 5.7.16 and 8.0 this worked with 999.

  • uid=999
  • gid=999

Next we tell the Azure File Storage to set the permissions to 0777. This gives the containers that integrate this storage the illusion that they have full rights.

  • dir_mode=0777
  • file_mode=0777

We can now roll out this file on our Kubernetes cluster.

kubectl apply -f storageclass.yaml

You should note that a storage class in Kubernetes is not tied to a namespace. If you already have “mysql-azurefile”, you have to change this name. You can check it with kubectl get storageclass.

Config Map

A ConfigMap is a Kubernetes resource that stores configuration data as key-value pairs. You can use them for configuring containers in a Kubernetes cluster. ConfigMaps allow you to inject environment variables, files, and other configurations into containers without packaging them directly into the image.

This allows us to later use the original MySQL image with our own configuration.

configmap.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysqld-cnf
data:
  mysqld.cnf: |
    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    # log-error      = /var/log/mysql/error.log
    # By default we only accept connections from localhost
    #bind-address   = 127.0.0.1
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    max_allowed_packet=500M

And roll out.

kubectl apply -f configmap.yaml -n test-ns

Persistent Volume Claim

A Persistent Volume Claim (PVC) is a request to the Kubernetes cluster to provision a Persistent Volume (PV). PVCs are used by Kubernetes pods to reserve memory for the data they need to keep even after they have restarted. They can also be used to make data accessible between different pods.

In our scenario we just have to make sure that we use the mysql-azurefile storage class defined above.

pvc.yaml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: azurefile-pvc
spec:
  accessModes:
    - ReadWriteMany
  storageClassName: mysql-azurefile
  resources:
    requests:
      storage: 10Gi

Instead of “Storage: 10Gi” (~10 gigabytes) you can of course enter any other size. According to the documentation, the maximum limit is currently 5 TiB or 100 TiB with the feature for large file shares.

Thanks to the ReadWriteMany access mode, it is possible for several pods to connect to the storage at the same time.

Activate.

kubectl apply -f pvc.yaml -n test-ns

Deployment

A deployment serves to provision a desired number of pods that run a specific application or service. It is also possible to specify a deployment that provides a specific version of an application or service. You can also use deployments to update an existing application or service.

Here we define which container we want to use. I use the label “prj: mysqltest” here to be able to connect it to the service after.

deploy.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: batchest-mysql
  labels:
    prj: mysqltest
spec:
  selector:
    matchLabels:
      prj: mysqltest
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        prj: mysqltest
    spec:
      securityContext:
        runAsUser: 999
        runAsGroup: 999
      containers:
        - image: mysql:8.0
          resources:
            requests:
              memory: "250Mi"
              cpu: "100m"
            limits:
              memory: "400Mi"
              cpu: "1000m"
          name: mysql
          # Fuer 5.7 willst du evtl diese args verwenden:
          #args:
          #           - "--ignore-db-dir"
          #           - "lost+found"
          env:
            - name: MYSQL_DATABASE
              value: mysql_db_name # creates a database called mysql_db_name
            # Fuer 5.7 willst du evtl MYSQL_USER auf root setzen:
            # - name: MYSQL_USER 
            #   value: root
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysqlsecret
                  key: MYSQL_DB_PASSWORD
          ports:
            - containerPort: 3306
              name: mysql
          volumeMounts:
            - mountPath: /etc/mysql/conf.d
              readOnly: true
              name: mysqld-cnf
            - name: azurefile-pv
              mountPath: /var/lib/mysql
              subPath: sql
      volumes:
        - name: mysqld-cnf
          configMap:
            name: mysqld-cnf 
            items:
              - key: mysqld.cnf
                path: meinsql.cnf
        - name: azurefile-pv
          persistentVolumeClaim:
            claimName: azurefile-pvc

Instead of the mysql:8.0 image you can of course also use the still widespread version 5.7. You also need to check and possibly increase the RAM/CPU limits.

Note that I include 2 volumes here: one for the ConfigMap mysqld-cnf and one for the persistent volume claim azurefile-pvc.

However, it doesn’t work that way yet. I didn’t want to write the password directly into the configuration. That’s why I say in deployment that the password is in a secret. Of course, you also have to generate this secret:

kubectl create secret generic mysqlsecret --from-literal=MYSQL_DB_PASSWORD=ABC123 -n test-ns

This configuration sets up the MySQL user root with the password ABC123.

The pod is started by the deployment after the following command.

kubectl apply -f deploy.yaml -n test-ns

Services

A service in Kubernetes is a part of the network infrastructure that can be viewed as the entry and exit point for client requests. First, it allows the pods within the cluster to communicate with each other and provides a unified, logical address at which multiple pods can be reached. On the other hand, a service also allows access to pods from the external network if you configure it accordingly.

In this example, I’m using an internal service because I don’t want my MySQL server to be reachable from outside the cluster.

service.yaml

apiVersion: v1
kind: Service
metadata:
  name: mysqlservice
  labels:
    prj: mysqltest
spec:
  ports:
    - port: 3306
  selector:
    prj: mysqltest
  clusterIP: None

kubectl apply -f service.yaml -n test-ns

So our container can be reached under the name mysqlservice.

Final test

Here’s a quick test to see if everything works.

  1. List Pods
kubectl get pods -n test-ns

NAME                            READY   STATUS    RESTARTS   AGE
batchest-mysql-858cb8f7-l9tm4   1/1     Running   0          5m
  1. Connect to Pod
kubectl exec -it batchest-mysql-858cb8f7-l9tm4 -n test-ns -- /bin/sh

sh-4.4$
  1. Login to MySQL
mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>


Could I help? Buy me a drink ! 💙