Mysql operator

MySQL Operator for Kubernetes manages MySQL InnoDB Cluster setups inside a Kubernetes Cluster. MySQL Operator for Kubernetes manages the full lifecycle with setup and maintenance including automating upgrades and backups.

Before install

helm repo add mysql-operator https://mysql.github.io/mysql-operator/
helm repo update

Install

helm install mysql-operator mysql-operator/mysql-operator --namespace mysql-system --create-namespace

Create mysql cluster

Operator Custom Resource Properties

Using kubectl

For creating a MySQL InnoDB Cluster, first create a secret with credentials for a MySQL root user used to perform administrative tasks in the cluster. For example:

kubectl create secret generic -n mysql-system mypwds \
  --from-literal=rootUser=root \
  --from-literal=rootHost=% \
  --from-literal=rootPassword="RandomPassword"

Define your MySQL InnoDB Cluster, which references the secret. For example:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
  namespace: mysql-system
spec:
  secretName: mypwds
  tlsUseSelfSigned: true
  instances: 3
  version: 8.4.4
  router:
    instances: 1
    version: 8.4.4
  datadirVolumeClaimTemplate:
    storageClassName: nfs-client
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 1Gi
  datadirPermissions:
    fsGroupChangePolicy: Always
    setRightsUsingInitContainer: True
  podSpec:
    resources:
      requests:
        memory: "512Mi"
        cpu: "500m"
      limits:
        memory: "512Mi"
        cpu: "500m"

If the pods mysql-cluster-* falls with errors, it may be a matter of pvc. Look at the privilege settings or use securityContext. For example:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: myclustec
  namespace: mysql-system
spec:
  secretName: mypwds
  tlsUseSelfSigned: true
  instances: 3
  version: 9.1.0
  router:
    instances: 1
    version: 9.1.0
  datadirVolumeClaimTemplate:
    storageClassName: nfs-sc
    accessModes: 
      - ReadWriteOnce
    resources:
      requests:
        storage: 10Gi
  datadirPermissions:
    fsGroupChangePolicy: Always
    setRightsUsingInitContainer: False
  podSpec:
    securityContext:
      runAsUser: 1001
      runAsGroup: 1001
      runAsNonRoot: true
    resources:
      requests:
        memory: "512Mi"
        cpu: "500m"
      limits:
        memory: "512Mi"
        cpu: "500m"

Connect with Port Forwarding

Optionally use port forwarding to create a redirection from your local machine to easily use a MySQL client such as MySQL Workbench. We'll use port 3306 for a read-write connection to the primary on port 6446:

kubectl port-forward service/mysql-cluster 3306 -n mysql-system

Forwarding from 127.0.0.1:3306 -> 6446
Forwarding from [::1]:3306 -> 6446

To test, open a second terminal using the MySQL command line or MySQL Shell with the InnoDB Cluster user's credentials:

mysql -h127.0.0.1 -uroot -p -e 'select @@hostname;'

To demonstrate the connection to a local MySQL instance:

+-----------------+
| @@hostname      |
+-----------------+
| mysql-cluster-0 |
+-----------------+

Metrics for prometheus

Use Prometheus exporter for MySQL server metrics.

Required Grants

Connect with Port Forwarding and create user for metrics

mysql -h127.0.0.1 -uroot -p

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

Create secret with password

Add metrics spec in instances and ServiceMonitor monitor

Unfortunately, you need to pass the labels value in order for prometheus stack to see it, so you need to make your own manifest with the ServiceMonitor.

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
  namespace: mysql-system
spec:
  secretName: mypwds
  tlsUseSelfSigned: true
  instances: 3
  version: 8.4.4
  router:
    instances: 1
    version: 8.4.4
  datadirVolumeClaimTemplate:
    storageClassName: nfs-sc
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 1Gi
  datadirPermissions:
    fsGroupChangePolicy: Always
    setRightsUsingInitContainer: True
  podSpec:
    resources:
      requests:
        memory: "512Mi"
        cpu: "500m"
      limits:
        memory: "512Mi"
        cpu: "500m"
  metrics:
    enable: true
    image: "prom/mysqld-exporter"
    monitor: false
    options:
      - "--mysqld.address=127.0.0.1:3306"
      - "--mysqld.username=exporter"
---
apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  name: mysql-cluster
  namespace: mysql-system
  labels:
    release: kube-prometheus-stack
spec:
  endpoints:
  - path: /metrics
    port: metrics
  selector:
    matchLabels:
      mysql.oracle.com/cluster: mysql-cluster