Tips to use MySQL 8.0 on Kubernetes
Tags: kubernetes, mysql
Published: 2018-12-27 05:56:54

---
title: Tips to use MySQL 8.0 on Kubernetes
published: true
description: A small tips to use MySQL 8.0 on Kubernetes
tags: kubernetes, MySQL
---

[MySQL 8.0](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/) 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:

```my.cnf
[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`.


```yaml
# 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.


```yaml
# 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.

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

Check if a pod running up.

```shell
$ kubectl get pods

table
NAME      READY   STATUS    RESTARTS   AGE
mysql   1/1     Running   0          5s
```
Dive into the pod.

```shell
$ kubectl exec -it mysql /bin/bash
```
Check a value of the plugin setting.

```shell
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`.

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

```shell
$ kubectl apply -f service.yaml
```

To connect the server, create a mysql client pod.

```yaml
# 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"]
```

```shell
$ kubectl apply -f client-pod.yaml
```

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

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

Login to the mysql server.


```shell
~ $ 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!

{% github YoshiyukiKato/mysql8-k8s-example %}