MySQL on k8s Environment Deployment

1. Overview

MySQL is a relational database management system developed by Swedish MySQL AB Company and is a product of Oracle. MySQL is one of the most popular relational database management systems. In the application of WEB, MySQL is one of the best RDBMS (Relational Database Management System) applications. Here we will focus on the deployment of MySQL on k8s. The advantages of MySQL deployment on k8s are as follows:

Resource isolation
Dynamic elastic expansion
Environmental Consistency
Convenient operation and maintenance

Official documents: https://docs.oracle.com/en-us/iaas/mysql-database/doc/getting-started.html
I can also refer to this article for an introduction to MySQL principles: Introduction to MySQL principles

2. Start deployment (one master, two slaves)

1) Add Source

helm repo add bitnami https://charts.bitnami.com/bitnami
helm pull bitnami/mysql
tar -xf mysql-9.3.3.tgz

2) Modify configuration

Modify mysql/values.yaml

...

image:
  registry: myharbor.com
  repository: bigdata/mysql
  tag: 8.0.30-debian-11-r15

...

architecture: replication

...

primary:
  persistence:
    enabled: true
    size: 10Gi
    storageClass: "mysql-local-storage"
    # Directory needs to be created on the host ahead of time
    local:
    - name: mysql-0
      host: "local-168-182-110"
      path: "/opt/bigdata/servers/mysql/data/data1"
  service:
    type: NodePort
    nodePorts:
      mysql: "30306"

secondary:
  replicaCount: 2
  persistence:
    enabled: true
    size: 10Gi
    storageClass: "mysql-local-storage"
    # Directory needs to be created on the host ahead of time
    local:
    - name: mysql-1
      host: "local-168-182-111"
      path: "/opt/bigdata/servers/mysql/data/data1"
    - name: mysql-2
      host: "local-168-182-112"
      path: "/opt/bigdata/servers/mysql/data/data1"
  service:
    type: NodePort
    nodePorts:
      mysql: "30307"

...

metrics:
  ## @param metrics.enabled Start a side-car prometheus exporter
  ##
  enabled: true
  image:
    registry: myharbor.com
    repository: bigdata/mysqld-exporter
    tag: 0.14.0-debian-11-r33

Add mysql/templates/pv.yaml

{{- range .Values.primary.persistence.local }}
---
apiVersion: v1
kind: PersistentVolume
metadata:
  name: {{ .name }}
  labels:
    name: {{ .name }}
spec:
  storageClassName: {{ $.Values.primary.persistence.storageClass }}
  capacity:
    storage: {{ $.Values.primary.persistence.size }}
  accessModes:
    - ReadWriteOnce
  local:
    path: {{ .path }}
  nodeAffinity:
    required:
      nodeSelectorTerms:
        - matchExpressions:
            - key: kubernetes.io/hostname
              operator: In
              values:
                - {{ .host }}
---
{{- end }}

{{- range .Values.secondary.persistence.local }}
---
apiVersion: v1
kind: PersistentVolume
metadata:
  name: {{ .name }}
  labels:
    name: {{ .name }}
spec:
  storageClassName: {{ $.Values.secondary.persistence.storageClass }}
  capacity:
    storage: {{ $.Values.secondary.persistence.size }}
  accessModes:
    - ReadWriteOnce
  local:
    path: {{ .path }}
  nodeAffinity:
    required:
      nodeSelectorTerms:
        - matchExpressions:
            - key: kubernetes.io/hostname
              operator: In
              values:
                - {{ .host }}
---
{{- end }}

Add mysql/templates/storage-class.yaml

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: {{ .Values.primary.persistence.storageClass }}
provisioner: kubernetes.io/no-provisioner

3) Start installation

# Create a persistent directory
mkdir -p /opt/bigdata/servers/mysql/data/data1

# Prepare the mirror first
docker pull docker.io/bitnami/mysql:8.0.30-debian-11-r15
docker tag docker.io/bitnami/mysql:8.0.30-debian-11-r15 myharbor.com/bigdata/mysql:8.0.30-debian-11-r15
docker push myharbor.com/bigdata/mysql:8.0.30-debian-11-r15

# mysqld-exporter
docker pull docker.io/bitnami/mysqld-exporter:0.14.0-debian-11-r33
docker tag docker.io/bitnami/mysqld-exporter:0.14.0-debian-11-r33 myharbor.com/bigdata/mysqld-exporter:0.14.0-debian-11-r33
docker push myharbor.com/bigdata/mysqld-exporter:0.14.0-debian-11-r33

# Start Installation
helm install mysql ./mysql -n mysql --create-namespace

NOTES

NAME: mysql
LAST DEPLOYED: Mon Sep 19 23:57:18 2022
NAMESPACE: mysql
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
CHART NAME: mysql
CHART VERSION: 9.3.3
APP VERSION: 8.0.30

** Please be patient while the chart is being deployed **

Tip:

  Watch the deployment status using the command: kubectl get pods -w --namespace mysql

Services:

  echo Primary: mysql-primary.mysql.svc.cluster.local:3306
  echo Secondary: mysql-secondary.mysql.svc.cluster.local:3306

Execute the following to get the administrator credentials:

  echo Username: root
  MYSQL_ROOT_PASSWORD=$(kubectl get secret --namespace mysql mysql -o jsonpath="{.data.mysql-root-password}" | base64 -d)

To connect to your database:

  1. Run a pod that you can use as a client:

      kubectl run mysql-client --rm --tty -i --restart='Never' --image  myharbor.com/bigdata/mysql:8.0.30-debian-11-r15 --namespace mysql --env MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD --command -- bash

  2. To connect to primary service (read/write):

      mysql -h mysql-primary.mysql.svc.cluster.local -uroot -p"$MYSQL_ROOT_PASSWORD"

  3. To connect to secondary service (read-only):

      mysql -h mysql-secondary.mysql.svc.cluster.local -uroot -p"$MYSQL_ROOT_PASSWORD"



To access the MySQL Prometheus metrics from outside the cluster execute the following commands:

    kubectl port-forward --namespace mysql svc/mysql-metrics 9104:9104 &
    curl http://127.0.0.1:9104/metrics

View pod status

kubectl get pods,svc -n mysql -owide

4) Test Verification

The slave is read-only. Simple read and write tests, pod hangs, pulls up normally, and so on. This test validation is simple. There is no step-by-step presentation.

5) Prometheus monitoring

Prometheus: https://prometheus.k8s.local/targets?search=mysql

Data collection can be viewed through commands

kubectl get --raw http://10.244.0.74:9104/metrics
kubectl get --raw http://10.244.1.125:9104/metrics
kubectl get --raw http://10.244.2.178:9104/metrics

Grafana: https://grafana.k8s.local/
Account: admin, password obtained by following command

kubectl get secret --namespace grafana grafana -o jsonpath="{.data.admin-password}" | base64 --decode ; echo

Import grafana template, cluster resource monitoring: 7362
Official module download address: https://grafana.com/grafana/dashboards/

6) Uninstall

helm uninstall mysql -n mysql 

kubectl delete pod -n mysql `kubectl get pod -n mysql |awk 'NR>1{print $1}'` --force
kubectl patch ns mysql -p '{"metadata":{"finalizers":null}}'
kubectl delete ns mysql --force

There is only a master implementation of mysql, not a highly available implementation. There is currently no highly available implementation of mysql ok k8s officially. Although there are also highly available implementation schemes on the internet, our company does not really use them. Therefore, we do not evaluate the highly available schemes on the internet. Interested partners can try them. In fact, MySQL is generally only stored as metadata in the big data field, but hangs up as master, which has little impact. The high-availability implementation of mysql on k8s is also being studied. If there is a high-availability of mysql on k8s that actually falls into production, share it again later. mysql on k8s environment deployment is here first, if you have questions, please leave a message for me~

Posted by digibrain on Thu, 22 Sep 2022 22:45:33 +0530

Programs Team