Tips to use MySQL 8.0 on Kubernetes

MySQL 8.0 has many features updated from 5.x. They are totally awesome, however, some features might make some trouble in upgrading from 5.x to 8.0. Change of default authentication plugin is one of such troublesome features. Until 5.x, the default plugin is mysql_native_password. From 8.0, the default plugin is caching_sha2_password. This difference may cause database login failure because it requires a mysql client to support caching_sha2_password. In concrete, you may get a message as follows:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded

To solve this error, you have to set a parameter to configure authentication plugin before the server starting up. You can set the parameter by putting a config file including following lines:

[mysqld]
default-authentication-plugin=mysql_native_password

Then, how to set the parameter for mysql running on kubernetes? That is the main theme of this post. To put the config file into kubernetes mysql container, you can use ConfigMap.

The ConfigMap is as follows. As shown in code, you can describe the contents of mysql config directly into yaml of ConfigMap.

# config-map.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  ## name of ConfigMap. This will be referred from volume definition
  name: mysql-config
  labels:
    app: mysql
data:
  ## default_auth is the name of config. This will be referred from volume mount definition
  default_auth: |
    [mysqld]
    default_authentication_plugin= mysql_native_password

To put the default_auth data into k8s container as a config file, define volumes and volumeMounts in a mysql pod configuration.

# pod.yaml
kind: Pod
apiVersion: v1
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  containers:
    - name: mysql
      image: mysql:8.0
      imagePullPolicy: Always
      ports:
        - containerPort: 3306
          name: mysql
      env:
      - name: MYSQL_ROOT_PASSWORD # use only for debugging
        value: "root_password"
      - name: MYSQL_DATABASE
        value: "your_database"
      - name: MYSQL_USER
        value: "your_user"
      - name: MYSQL_PASSWORD
        value: "your_password"
      volumeMounts:
      - name: mysql-config-volume ## specify volume name
        mountPath: /etc/mysql/conf.d/default_auth.cnf ## path to mount file
        subPath: default_auth ## name of config
  volumes:
    - name: mysql-config-volume ## volume name
      configMap:
        name: mysql-config ## name of ConfigMap

OK! Then, let’s create these resources onto your k8s cluster, and check if the configuration of the default auth plugin is applied correctly.

$ kubectl apply -f config-map.yaml
$ kubectl apply -f pod.yaml

Check if a pod running up.

$ kubectl get pods

table
NAME      READY   STATUS    RESTARTS   AGE
mysql   1/1     Running   0          5s

Dive into the pod.

$ kubectl exec -it mysql /bin/bash

Check a value of the plugin setting.

root@mysql:/# mysql -u root -D mysql -proot_password

mysql > SELECT user, plugin FROM user WHERE user="your_user";

And you will get following result with table format.

| your_user | mysql_native_password |

Looks good☺️

It’s time to check if it is able to connect the mysql server from outside of the pod. For making easy to connect, create a Service for mysql. This enables pods in the same namespace to access the mysql server by a host name mysql.

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

$ kubectl apply -f service.yaml

To connect the server, create a mysql client pod.

# client-pod.yaml
kind: Pod
apiVersion: v1
metadata:
  name: mysql-client
  labels:
    name: mysql-client
spec:
  containers:
  - name: mysql-client
    image: ellerbrock/alpine-mysql-client
    command: ["sleep", "1000"]

$ kubectl apply -f client-pod.yaml

Confirm if the mysql-client pod running up, and dive into the pod.

$ kubectl exec -it mysql-client /bin/ash

Login to the mysql server.

~ $ mysql -h mysql -u your_user -D your_database -pyour_password

MySQL [your_database]> 

Complete! 🙌

All source codes shown in this post are published in my Github repository. If you are interested in, feel free to clone and try it out!

YoshiyukiKato / mysql8-k8s-example

example of using mysql8.0 on kubernetes

mysql8-k8s-example

Example codes used in my dev.to post.

usage

create all resources

./create

login to mysql server

$ kubectl exec -it mysql-client /bin/ash
$ mysql -h mysql -u your_user -D your_database -pyour_password

MySQL [your_database]> 

delete all resources

$ ./delete

View on GitHub

Comments

[Adrian B.G.]

I may be wrong but MariaDB does not have breaking changes like MySql and more features than the premium mysql, just saying that is another alternative 100% compatible with less ops pain.

As for K8s is not used for stateful services like SQL databases, in production ofc. It is awesome for staging and dev environments 💚

[Yoshiyuki Kato]

Thank you for kindly comment, Adrian!

I'm sorry for not so familiar with MariaDB, but as you said, I think it is able to use MariaDB as alternative to MySQL. (Note that they seem to have a few incompatibilities as shown in this page)

As you pointed out, this article is not for production but just for development. I understand that saving data in as safe place as possible is a highly important business-critial problem in production use.

I think there are two problems in different viewpoints:

1. Pod is not stateful
2. Storing data in cluster is not safe

The 1st one, it could be solved by using StatefulSet with persistent volume. But the 2nd one is more serious, it is a kind of disaster recovery problem. Using StatefulSet and persistent volume with heptio/ark might solve this problem, however, I think it is reasonable to use datastore outside of cluster such as full-managed services.

[Adrian B.G.]

Thank you for adding this info, I think that the readers that are not familiar with containers will have a treat reading it!