K8S MySQL Statefulsets

Short note on using MySQL on K8S with NFS as PVs. I did it on GCP. You can set up NFS Server from my link below.

Argo CD, Traefik, Let’s Encrypt on GCP Part 1

After follow the setting of NFS Server from my link above, you have to set up the folder for MySQL.

Navigate to folder /mnt/nfs_share and create folder mysql, config, mysql0, mysql1

cd /mnt/nfs_share/
mkdir mysql
mkdir config
sudo chown 999:999 -r config
sudo chmod 777 config
cd mysql
mkdir mysql0
mkdir mysql1

Change owner/group in NFS folder to 999:999

sudo chown 999:999 -r mysql0
sudo chown 999:999 -r mysql1
sudo chmod 777 mysql0
sudo chmod 777 mysql1

For MySQL Stateful Sets. You Still needs to make pods replicate data by yourself. mysql-0 will be primary. This is a read/write node. mysql-1 will be read replica node.

Kubernetes StatefulSet - Examples & Best Practices

This note use MySQL Group Replication. My yaml files is base on following read.

Run a Replicated Stateful Application

Following are useful read.

MySQL Group Replication in Kubernetes

How To Configure MySQL Group Replication on Ubuntu 20.04 | DigitalOcean

Next, apply yml file to deploy MySQL StatefulSets. You must change NFS Server IP to yours. This is for example. It’s not for production, Especially for security.

mysql/mysql_statefulsets.yml · main · art Krisada / argocd-test · GitLab

I used Argo CD to deploy. The result is as follow.

Test by shell to mysql-0 pod. Then, create database. Insert test data.

exec kubectl exec -i -t -n mysql mysql-0 -c mysql -- sh -c "clear; (bash || ash || sh)" 

mysql -uroot -proot

CREATE DATABASE playground;
CREATE TABLE playground.equipment ( 
id INT NOT NULL AUTO_INCREMENT,
type VARCHAR(50),
quant INT,
color VARCHAR(25),
PRIMARY KEY(id)
);
INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");
SELECT * FROM playground.equipment;

Next shell to mysql-1. Then, select test data.

exec kubectl exec -i -t -n mysql mysql-1 -c mysql -- sh -c "clear; (bash || ash || sh)" 

mysql -uroot -proot

SELECT * FROM playground.equipment;

You will see the data that you inserted from mysql-0 pod.