In this demo we are setting up InnDB Cluster on Kubernetes, we will use a StatefulSets and NFS as storage. This demo was created on Oracle Cloud (OCI) but vanilla Kubernetes and NFS was used so should work for any cloud or on-prem.
Setup a NFS Server for your persistent volumes, howto here If you are using a public cloud provider you can most likely use dynamic storage options for handling of PV.
In bellow example I have a NFS Server on IP: 10.0.0.50 This NFS exposes folders:
- /var/nfs/pv0
- /var/nfs/pv1
- /var/nfs/pv2
You can look at configuration for kubernetes in yamls folder.
First we are creating three persistent volumes (pv0-pv2) for our InnoDB Cluster nodes. We are specifying that this volume can only be accessed by one node (ReadWriteOnce) We are also specifying that we will use our NFS server for storage. More information on PV here.
After we have created the persistent volumes we will create the StatefulSet. StatefulSets is a way in Kubernetes to manage stateful applications First we create services for our cluster nodes to expose them on the network. Next we configure our StatefulSet, we want to have three replicas (three InnoDB Cluster nodes) that we are starting in parallel. We also use the simplified way by defining a volume claim template (volumeClaimTemplates) that will claim the three previously created volumes.
kubectl create -f yamls/02-mysql-pv.yaml
kubectl get pv (short for kubectl get persistentvolumes)
(should be in STATUS Available)
Namespaces in k8s are like comparments in OCI, lets create a unique namespace for our cluster.
kubectl create namespace mysql-cluster
kubectl get namespaces
Set default namespace for comming commands to mysql-cluster:
kubectl config set-context --current --namespace=mysql-cluster
kubectl create -f yamls/02-mysql-innodb-cluster-manual.yaml
kubectl get pv,pvc
watch kubectl get all -o wide
(or kubectl get all -o wide -n mysql-cluster)
(or kubectl get all -o wide --all-namespaces)
If there are problems look at logs (mysqld is started direcly + error log is set to stderr in our docker image):
kubectl logs mysql-innodb-cluster-0
or prev failed pods by running:
kubectl logs -p mysql-innodb-cluster-1
Look at configuration for the pod:
kubectl describe pod mysql-innodb-cluster-1
kubectl exec -it mysql-innodb-cluster-0 -- mysql -uroot -p_MySQL2020_
kubectl exec -it mysql-innodb-cluster-0 -- mysqlsh -uroot -p_MySQL2020_ -S/var/run/mysqld/mysqlx.sock
kubectl exec -it mysql-innodb-cluster-0 -- mysql -uroot -p_MySQL2020_ -e"SET SQL_LOG_BIN=0; CREATE USER 'idcAdmin'@'%' IDENTIFIED BY '
idcAdmin'; GRANT ALL ON *.* TO 'idcAdmin'@'%' WI
TH GRANT OPTION";
kubectl exec -it mysql-innodb-cluster-1 -- mysql -uroot -p_MySQL2020_ -e"SET SQL_LOG_BIN=0; CREATE USER 'idcAdmin'@'%' IDENTIFIED BY '
idcAdmin'; GRANT ALL ON *.* TO 'idcAdmin'@'%' WI
TH GRANT OPTION";
kubectl exec -it mysql-innodb-cluster-2 -- mysql -uroot -p_MySQL2020_ -e"SET SQL_LOG_BIN=0; CREATE USER 'idcAdmin'@'%' IDENTIFIED BY '
idcAdmin'; GRANT ALL ON *.* TO 'idcAdmin'@'%' WI
TH GRANT OPTION";
Login to shell:
kubectl exec -it mysql-innodb-cluster-0 -- mysqlsh -uidcAdmin -pidcAdmin -S/var/run/mysqld/mysqlx.sock
and then configure the instances:
dba.configureInstance('idcAdmin@mysql-innodb-cluster-0:3306',{password:'idcAdmin',interactive:false,restart:true});
dba.configureInstance('idcAdmin@mysql-innodb-cluster-1:3306',{password:'idcAdmin',interactive:false,restart:true});
dba.configureInstance('idcAdmin@mysql-innodb-cluster-2:3306',{password:'idcAdmin',interactive:false,restart:true});
You see error below when running "dba.configureInstance" ERROR: Remote restart of MySQL server failed: MySQL Error 3707 (HY000): Restart server failed (mysqld is not managed by supervisor proc ess).
This is due some limitation running "restart" comand in MySQL for our docker container, we are working on solving this. Please restart MySQL manually to enable new settings, easiest to scale down + scale up again like:
kubectl scale statefulset --replicas=0 mysql-innodb-cluster
Look at: watch kubectl get all -o wide
during the scale up/down.
kubectl scale statefulset --replicas=3 mysql-innodb-cluster
Login to shell again:
kubectl exec -it mysql-innodb-cluster-0 -- mysqlsh -uidcAdmin -pidcAdmin -S/var/run/mysqld/mysqlx.sock
and run:
cluster=dba.createCluster("mycluster",{exitStateAction:'OFFLINE_MODE',autoRejoinTries:'20',consistency:'BEFORE_ON_PRIMARY_FAILOVER'});
cluster.status()
cluster.addInstance('idcAdmin@mysql-innodb-cluster-1:3306',{password:'idcAdmin',recoveryMethod:'clone'});
cluster.addInstance('idcAdmin@mysql-innodb-cluster-2:3306',{password:'idcAdmin',recoveryMethod:'clone'});
cluster.status()
Done, you should now have a running InnoDB Cluster using StatefulSets on Kubernetes.
Look at cluster status, login to mysql shell:
kubectl exec -it mysql-innodb-cluster-1 -- mysqlsh -uidcAdmin -pidcAdmin -S/var/run/mysqld/mysqlx.sock
And look at cluster status:
cluster=dba.getCluster()
cluster.status()
Also look at pods watch kubectl get all -o wide -n mysql-cluster
Kill the pod that is primary (RW) (mysql-innodb-cluster-0 most likely)
kubectl delete pod mysql-innodb-cluster-0
You should now see that one pod is restarted and that the "old" primary (RW) will join after restart as seconday (RO).
kubectl delete -f yamls/02-mysql-innodb-cluster-manual.yaml
kubectl delete pvc mysql-persistent-storage-mysql-innodb-cluster-0
kubectl delete pvc mysql-persistent-storage-mysql-innodb-cluster-1
kubectl delete pvc mysql-persistent-storage-mysql-innodb-cluster-2
kubectl delete -f yamls/02-mysql-pv.yaml
Make sure all is deleted:
kubectl get pv,pv
kubectl get all -o wide
Remember to also empty out the datadir on NFS between tests:
sudo rm -fr /var/nfs/pv[0,1,2]/*
ls /var/nfs/pv[0,1,2]/
- More information around InnoDB Cluster here
- Whenever deploying new stuff look at: watch kubectl get all -o wide
- Good training on Kubernetes: https://www.youtube.com/user/wenkatn/playlists
- Good training on Kubernetes: https://github.com/justmeandopensource