How to Deploy MySQL Server 5.7 to Kubernetes

Deploying MySQL Server 5.7 on Kubernetes

Overview

Learn how to run MySQL on Kuberbetes using this guide, which will show you to deploy MySQL 5.7 and MySQL 8.

You will be introduced to creating A Pod for your database server, as well as attaching persistent storage, configMaps, and exposing your database via a service resource.

Getting Started

Resource files used for this tutorial are available in Github. Use them as templates or to follow along.

Configuration

The base Docker containers for MySQL available from Dockerhub allow a number of configurations to be set via environment variables.

Secrets

Secrets should never be stored with the Docker images, nor should they be kept in clear text in a server configuration repository. They should always be stored somewhere secure.

Kubernetes has a resource type called Secret, which can be used to store sensitive information. In this example, a temporarily YAML file will be created and used to declaratively create a secret in Kubernetes.

When a secret is created in this manner, the secret’s key values must be stored as base64 encoded strings. To convert a string to base64 on OSX and most Linux distributions, use the base64 command.

echo -n "my-super-secret-password" | base64

bXktc3VwZXItc2VjcmV0LXBhc3N3b3JkCg==

The -n flag is added to the echo command to prevent newlines from being included in the base64 encode. When encoding the root password using this method, always use the -n flag with echo. Otherwise, your password may not be what you expect and you will find yourself loved out of the database server.

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

touch secret.yml

Add the following contents to it. We are setting a new key named ROOT_PASSWORD under the data key. The value of ROOT_PASSWORD is the base64 encoded password we generated earlier.

---
apiVersion: v1
kind: Secret
metadata:
  name: mysql-secrets
type: Opaque
data:
  ROOT_PASSWORD: c3VwZXItc2VjcmV0LXBhc3N3b3JkCg==

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

kubectl apply -f secret.yml

We can use the kubectl get secret command to list all secret resources added to the Kubernetes cluster. The information provide is basic, however.

kubectl get secret

NAME                  TYPE                                  DATA   AGE
 default-token-fhdgj   kubernetes.io/service-account-token   3      13m
 mysql-secrets         Opaque                                1      10m

We can see that the mysql-secrets resource has been created and that it has 1 data object, which is to save it has exactly one key-value pair. We can use the kubectl describe secret command to display additional information about the resource.

kubectl describe secret mysql-secrets

Name:         mysql-secrets
 Namespace:    default
 Labels:       
 Annotations:
 Type:         Opaque
 Data
 ROOT_PASSWORD:  22 bytes

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

Persistent Storage

Containers are ephemeral constructs. Any changes to the running container is lost when the container stops running.

For obvious reasons, this is not ideal for databases, as their is an expectation the data is persistent.

Kubenetes pods will not automatically attach a persistent volume store at runtime. A storage claim must be created, and then the pod must be configured to mount the claimed storage.

Creating a PersistentVolumeClaim Resource

PersistentVolumeClaim resources define the attributes of the storage volume, including the access mode, class, and size. The claim used in this example is very basic, as we are only defining the access mode and size.

The storage will be ReadWriteOnce, which means that it can be mounted to only one node and that node will have write access. It will also be 1GiB in size.

Create a new YAML file named persistentVolumeClaim.yml

touch persistentVolumeClaim.yml

Add the following contents to it.

---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-data-disk
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 1Gi

Now, create the storage claim by using the kubectl apply command.

kubectl apply -f persistentVolumeClaim.yml

Using the kubectl get PersistentStorageClaim command we can verify it was created successfully.

kubectl get persistentvolumeclaim mysql-data-disk

NAME              STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
 mysql-data-disk   Bound    pvc-e466493a-7ce2-11e9-baed-42010a800086   1Gi        RWO            standard       22m

To get more detailed information about the StorageVolumeClaim named mysql-data-disk, we use the kubectl describe storagevolumeclaim command.

kubectl describe persistentvolumeclaim mysql-data-disk

             {"apiVersion":"v1","kind":"PersistentVolumeClaim","metadata":{"annotations":{},"name":"mysql-data-disk","namespace":"default"},"spec":{"ac...            pv.kubernetes.io/bind-completed: yes            pv.kubernetes.io/bound-by-controller: yes            volume.beta.kubernetes.io/storage-provisioner: kubernetes.io/gce-pd
 Finalizers:    [kubernetes.io/pvc-protection]
 Capacity:      1Gi
 Access Modes:  RWO
 Events:
   Type       Reason                 Age   From                         Message
   ----       ------                 ----  ----                         -------
   Normal     ProvisioningSucceeded  23m   persistentvolume-controller  Successfully provisioned volume pvc-e466493a-7ce2-11e9-baed-42010a800086 using kubernetes.io/gce-pd
 Mounted By:  mysql-deployment-6b89d9cc44-v4qnz

Deploying MySQL 5.7 on Kubernetes

Creating the Deployment Resource

With the secrets and persistent storage in place, it is time to create the MySQL deployment. The deployment resource will create the MySQL POD and maintain its lifecycle. It can also be used to scale the Pods, however, doing so with MySQL is outside of the scope of this tutorial.

Create new YAML file called deployment.yml

touch deployment.yml

Add the following contents to it.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-deployment
  labels:
    app: mysql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
        - name: mysql
          image: mysql:5.7
          ports:
            - containerPort: 3306
          volumeMounts:
            - mountPath: "/var/lib/mysql"
              subPath: "mysql"
              name: mysql-data
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secrets
                  key: ROOT_PASSWORD
      volumes:
        - name: mysql-data
          persistentVolumeClaim:
            claimName: mysql-data-disk

Look closely at the yaml file. Notice how we are setting an environment variable using the ‘env’ key under the containers key. The environment variable’s name is MYSQL_ROOT_PASSWORD, which is the variable that is passed to the MySQL Docker image to set root’s password.

The value for the MYSQL_ROOT_PASSWORD variable is pulled from the secret resource created earlier in the tutorial named mysql-secrets.

Also, notice the volume that is being mounted to the /var/lib/mysql path. The volume being mounted is from the PersistentStorageClaim resource created earlier in the tutorial.

Create the deployment in Kubernetes using the kubectl apply command.

kubectl apply -f deployment.yml

Exposing MySQL through a Service Resource

Since the MySQL Pod is ephemeral, if you were to point an application to the pods IP address, access to the MySQL server would be lost when a new pod replaces the failed one. The new pod would is unlikely to receive the same IP address as the failed one, and that means the application will no longer be able to connect to the database.

A service resource is used to create a static IP address, and then serve traffic to any Pod attached to the service via labels.

In this example the MySQL service will not be exposed outside of the Kubernetes Cluster. Only other pods in the cluster will be able to access it.

Create a new file named service.yml

touch service.yml

Add the following contents to it.

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

Create the service resource using the kubectl apply command.

kubectl apply -f service.yml

Managing the Database using PhpMyAdmin

Deploying a PhpMyAdmin pod in Kubernetes for managing a MySQL Server pod will be covered in another tutorial. However, we mention it here as it is one solution that could be used to administering the server.

An official PhpMyAdmin Docker image is available from Dockerhub.