MySQL router in kubernetes as a service

Question

I want to deploy MySQL-router in Kubernetes working as a service.

My plan..

• Deploy MySQL-router inside k8 and expose MySQL-router as a service using LoadBalancer (MetalLB)
• Applications running inside k8 sees mysql-router service as its database.
• MySQL-router sends application data to outside InnoDB cluster.

I tried to deploy using:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-router
  namespace: mysql-router
spec:
  replicas: 1 
  selector:
    matchLabels:
      app: mysql-router
  template:
    metadata:
      labels:
        app: mysql-router
        version: v1
    spec:
      containers:
      - name: mysql-router
        image: mysql/mysql-router
        env:
        - name: MYSQL_HOST
          value: "192.168.123.130"
        - name: MYSQL_PORT
          value: "3306"
        - name: MYSQL_USER
          value: "root"
        - name: MYSQL_PASSWORD
          value: "root@123"
        imagePullPolicy: Always
        ports:
        - containerPort: 6446

192.168.123.130 is MySQL cluster Master IP.

apiVersion: v1
kind: Service
metadata:
  name: mysql-router-service
  namespace: mysql-router
  labels:
    app: mysql-router
spec:
  selector:
    app: mysql-router
  ports:
  - protocol: TCP
    port: 6446
  type: LoadBalancer
  loadBalancerIP: 192.168.123.123

When I check mysql-router container logs, I see something like this:

Waiting for mysql server 192.168.123.130 (0/12)
Waiting for mysql server 192.168.123.130 (1/12)
Waiting for mysql server 192.168.123.130 (2/12)
....

After setting my external MySQL cluster info in deployment, I get following errors:

Successfully contacted mysql server at 192.168.123.130. Checking for cluster state. Can not connect to database. Exiting.

I can not deploy mysql-router without specifying MYSQL_HOST. What am I missing here?

My ideal deployment

Answer 1

Of course you have to provide the MySQL Host. You could doing this with k8s DNS which setup with in the services.

MySQL Router is middleware that provides transparent routing between your application and any backend MySQL Servers. It can be used for a wide variety of use cases, such as providing high availability and scalability by effectively routing database traffic to appropriate backend MySQL Servers.

Examples

For examples below i use dynamic volume provisioning for data using openebs-hostpath And using StatefulSet for the MySQL Server.

Deployment

MySQL Router :

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-router
  namespace: mysql-router
spec:
  replicas: 1 
  selector:
    matchLabels:
      app: mysql-router
  template:
    metadata:
      labels:
        app: mysql-router
        version: v1
    spec:
      containers:
      - name: mysql-router
        image: mysql/mysql-router
        env:
        - name: MYSQL_HOST
          value: "mariadb-galera.galera-cluster"
        - name: MYSQL_PORT
          value: "3306"
        - name: MYSQL_USER
          value: "root"
        - name: MYSQL_PASSWORD
          value: "root@123"
        imagePullPolicy: Always
        ports:
        - containerPort: 3306

MySQL Server

apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: galera-cluster
  name: mariadb-galera
spec:
  podManagementPolicy: OrderedReady
  replicas: 1
  selector:
    matchLabels:
      app: mariadb-galera
  serviceName: mariadb-galera
  template:
    metadata:
      labels:
        app: mariadb-galera
    spec:
      restartPolicy: Always
      securityContext:
        fsGroup: 1001
        runAsUser: 1001
      containers:
      - command:
        - bash
        - -ec
        - |
          # Bootstrap from the indicated node
          NODE_ID="${MY_POD_NAME#"mariadb-galera-"}"
          if [[ "$NODE_ID" -eq "0" ]]; then
              export MARIADB_GALERA_CLUSTER_BOOTSTRAP=yes
              export MARIADB_GALERA_FORCE_SAFETOBOOTSTRAP=no
          fi
          exec /opt/bitnami/scripts/mariadb-galera/entrypoint.sh /opt/bitnami/scripts/mariadb-galera/run.sh
        env:
        - name: MY_POD_NAME
          valueFrom:
            fieldRef:
              apiVersion: v1
              fieldPath: metadata.name
        - name: BITNAMI_DEBUG
          value: "false"
        - name: MARIADB_GALERA_CLUSTER_NAME
          value: galera
        - name: MARIADB_GALERA_CLUSTER_ADDRESS
          value: gcomm://mariadb-galera.galera-cluster
        - name: MARIADB_ROOT_PASSWORD
          value: root@123
        - name: MARIADB_DATABASE
          value: my_database
        - name: MARIADB_GALERA_MARIABACKUP_USER
          value: mariabackup
        - name: MARIADB_GALERA_MARIABACKUP_PASSWORD
          value: root@123
        - name: MARIADB_ENABLE_LDAP
          value: "no"
        - name: MARIADB_ENABLE_TLS
          value: "no"
        image: docker.io/bitnami/mariadb-galera:10.4.13-debian-10-r23
        imagePullPolicy: IfNotPresent
        livenessProbe:
          exec:
            command:
            - bash
            - -ec
            - |
              exec mysqladmin status -uroot -p$MARIADB_ROOT_PASSWORD
          failureThreshold: 3
          initialDelaySeconds: 120
          periodSeconds: 10
          successThreshold: 1
          timeoutSeconds: 1
        name: mariadb-galera
        ports:
        - containerPort: 3306
          name: mysql
          protocol: TCP
        - containerPort: 4567
          name: galera
          protocol: TCP
        - containerPort: 4568
          name: ist
          protocol: TCP
        - containerPort: 4444
          name: sst
          protocol: TCP
        readinessProbe:
          exec:
            command:
            - bash
            - -ec
            - |
              exec mysqladmin status -uroot -p$MARIADB_ROOT_PASSWORD
          failureThreshold: 3
          initialDelaySeconds: 30
          periodSeconds: 10
          successThreshold: 1
          timeoutSeconds: 1
        volumeMounts:
        - mountPath: /opt/bitnami/mariadb/.bootstrap
          name: previous-boot
        - mountPath: /bitnami/mariadb
          name: data
        - mountPath: /opt/bitnami/mariadb/conf
          name: mariadb-galera-config
      volumes:
      - emptyDir: {}
        name: previous-boot
      - configMap:
          defaultMode: 420
          name: my.cnf
        name: mariadb-galera-config
  volumeClaimTemplates:
  - apiVersion: v1
    metadata:
      name: data
    spec:
      storageClassName: openebs-hostpath
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 20Gi

Services

MySQL Router Service

apiVersion: v1
kind: Service
metadata:
  name: mysql-router-service
  namespace: mysql-router
  labels:
    app: mysql-router
spec:
  selector:
    app: mysql-router
  ports:
  - protocol: TCP
    port: 3306
  type: LoadBalancer
  loadBalancerIP: 192.168.123.123

MySQL Service

apiVersion: v1
kind: Service
metadata:
  namespace: galera-cluster
  name: mariadb-galera
  labels:
    app: mariadb-galera
spec:
  type: ClusterIP
  ports:
  - name: mysql
    port: 3306
  selector:
    app: mariadb-galera
---
apiVersion: v1
kind: Service
metadata:
  namespace: galera-cluster
  name: mariadb-galera-headless
  labels:
    app: mariadb-galera
spec:
  type: ClusterIP
  ports:
  - name: galera
    port: 4567
  - name: ist
    port: 4568
  - name: sst
    port: 4444
  selector:
    app: mariadb-galera

Answer 2

What you need its #1 communication from App1-x to Mysql router and #2 a VIP/LB from MysqlRoutere to external mysql instances.

Well start with #2 configuration of Mysql instances VIP. You will need a service without selector.

apiVersion: v1
kind: Service
metadata:
  name: mysql-service
spec:
  ports:
  - name: mysql
    port: 3306
    protocol: TCP
    targetPort: 3306
  sessionAffinity: None
  type: ClusterIP
---
apiVersion: v1
kind: Endpoints
metadata:
  name: mysql-service
subsets:
- addresses:
  - ip: 192.168.123.130
  - ip: 192.168.123.131
  - ip: 192.168.123.132
  ports:
  - name: mysql
    port: 3306
    protocol: TCP

You don't need LoadBalancer cuz you will connect only inside cluster. So, use ClusterIp instead.

#1 Create MysqlRouter deployment.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-router
  namespace: mysql-router
spec:
  replicas: 1 
  selector:
    matchLabels:
      app: mysql-router
  template:
    metadata:
      labels:
        app: mysql-router
        version: v1
    spec:
      containers:
      - name: mysql-router
        image: mysql/mysql-router
        env:
        - name: MYSQL_HOST
          value: "mysql-service"
        - name: MYSQL_PORT
          value: "3306"
        - name: MYSQL_USER
          value: "root"
        - name: MYSQL_PASSWORD
          value: "root@123"
        imagePullPolicy: Always
        ports:
        - containerPort: 6446

To connect to external MySQL instances trough VIP/ClusterIP use mysql-service service and if deployment and service is in same namespace use mysql-service as hostname or put there a CLusterIP from kubectl get service mysql-service

apiVersion: v1
kind: Service
metadata:
  name: mysql-router-service
  namespace: mysql-router
  labels:
    app: mysql-router
spec:
  selector:
    app: mysql-router
  ports:
  - name: mysql
    port: 6446
    protocol: TCP
    targetPort: 6446
  type: ClusterIP

You can connect within kubernetes cluster to mysql-router-service hostname in same namespace and outside namespace to mysql-router-service.namespace.svc or outside kubernetes cluster use NodePort or LoadBalancer.