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
Top comments (3)
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 💚
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:
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.
Thank you for adding this info, I think that the readers that are not familiar with containers will have a treat reading it!