Run a StatefulSet MySQL on k8s cluster

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql
  labels:
    app: mysql
    app.kubernetes.io/name: mysql
data:
  primary.cnf: |
    # Apply this config only on the primary.
    [mysqld]
    log-bin
    max_allowed_packet=128M
    skip-name-resolve    
  replica.cnf: |
    # Apply this config only on replicas.
    [mysqld]
    super-read-only
    max_allowed_packet=128M
    skip-name-resolve    

k create -f configmap.yml

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-configmap
#  namespace: ...
data:
  init.sql: |
    CREATE USER '$MYSQL_USER'@'%' IDENTIFIED BY '$MYSQL_PASSWORD';
    GRANT ALL PRIVILEGES ON '$MYSQL_DATABASE'.* TO '$MYSQL_USER'@'%';
    FLUSH PRIVILEGES;

    USE '$MYSQL_DATABASE';    

k create -f configmap-conf-db.yml

echo -n testuser | base64 
dGVzdHVzZXI=

echo -n testuserpass | base64 
dGVzdHVzZXJwYXNz

echo -n testdb | base64 
dGVzdGRi

echo -n rootpass | base64 
cm9vdHBhc3M=

apiVersion: v1
kind: Secret
metadata:
  name: mysql-cred
#  namespace: ...
type: Opaque
data:
  username: dGVzdHVzZXI=
  password: dGVzdHVzZXJwYXNz
  dbname: dGVzdGRi
  rootpass: cm9vdHBhc3M=

k create -f secrets.yml

# Headless service for stable DNS entries of StatefulSet members.
apiVersion: v1
kind: Service
metadata:
  name: mysql
  labels:
    app: mysql
    app.kubernetes.io/name: mysql
spec:
  ports:
  - name: mysql
    port: 3306
  clusterIP: None
  selector:
    app: mysql
---
# Client service for connecting to any MySQL instance for reads.
# For writes, you must instead connect to the primary: mysql-0.mysql.
apiVersion: v1
kind: Service
metadata:
  name: mysql-read
  labels:
    app: mysql
    app.kubernetes.io/name: mysql
    readonly: "true"
spec:
  ports:
  - name: mysql
    port: 3306
  selector:
    app: mysql

k create -f services.yml

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
      app: mysql
      app.kubernetes.io/name: mysql
  serviceName: mysql
  replicas: 3
  template:
    metadata:
      labels:
        app: mysql
        app.kubernetes.io/name: mysql
    spec:
      initContainers:
      - name: init-mysql
        image: mysql:9.0.1
        command:
        - bash
        - "-c"
        - |
          set -ex
          # Generate mysql server-id from pod ordinal index.
          [[ $HOSTNAME =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          echo [mysqld] > /mnt/conf.d/server-id.cnf
          # Add an offset to avoid reserved server-id=0 value.
          echo server-id=$((100 + $ordinal)) >> /mnt/conf.d/server-id.cnf
          # Copy appropriate conf.d files from config-map to emptyDir.
          if [[ $ordinal -eq 0 ]]; then
            cp /mnt/config-map/primary.cnf /mnt/conf.d/
          else
            cp /mnt/config-map/replica.cnf /mnt/conf.d/
          fi                    
        volumeMounts:
        - name: conf
          mountPath: /mnt/conf.d
        - name: config-map
          mountPath: /mnt/config-map
      containers:
      - name: mysql
        image: mysql:9.0.1
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-cred
              key: rootpass
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: mysql-cred
              key: username
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-cred
              key: password
        - name: MYSQL_DATABASE
          valueFrom:
            secretKeyRef:
              name: mysql-cred
              key: dbname
        ports:
        - name: mysql
          containerPort: 3306
        resources:
          requests:
            cpu: 100m
            memory: 256Mi
          limits:
            cpu: 200m
            memory: 512Mi
        readinessProbe:
          exec:
            command: ["mysqladmin", "ping", "-h", "localhost"] # todo: mysql -h127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD -e'SELECT 1' --> Using a password on the command line interface can be insecure.
          initialDelaySeconds: 30
          periodSeconds: 2
          timeoutSeconds: 1
        livenessProbe:
          exec:
            command: ["mysqladmin", "ping", "-h", "localhost"]
          initialDelaySeconds: 30
          periodSeconds: 10
          timeoutSeconds: 5
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
        - name: mysql-config
          mountPath: /docker-entrypoint-initdb.d
      volumes:
      - name: conf
        emptyDir: {}
      - name: config-map
        configMap:
          name: mysql
      - name: mysql-config
        configMap:
          name: mysql-configmap
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 256Mi

k create -f statefulset.yml

mysql -u root -p
mysql -u testuser -p

> show databases;