MySQL router kubernetes deployment

How to setup MySQL router in Kubernetes cluster?

Continuing from previous post - MySQL router high availability for InnoDB cluster, in situations where application is deployed in kubernets it is better to incorporate mysql router to K8s itself. Inorder to deploy Mysql router in kubernetes two services are required along with the mysql router deployment.

MySQL Router deployment

• Router deployment should listen on two ports for read write and read only connections.
• hostAliases are used to define the backend innodb cluster database servers.
• MySQL router docker image is used with variables - MYSQL_HOST, MYSQL_PORT, MYSQL_USER and MYSQL_PASSWORD.
• MYSQL_HOST variables should point to MySQL service defined in next section.

mysql_router_deployment.yaml

apiVersion: apps/v1 kind: Deployment metadata: name: mysql-router namespace: default spec: replicas: 1 selector: matchLabels: app: mysql-router template: metadata: labels: app: mysql-router version: v1 spec: hostAliases: - ip: "192.168.8.110" hostnames: - "inno1" - ip: "192.168.8.111" hostnames: - "inno2" - ip: "192.168.8.112" hostnames: - "inno3" containers: - name: mysql-router image: mysql/mysql-router env: - name: MYSQL_HOST value: "mysql-service" - name: MYSQL_PORT value: "3306" - name: MYSQL_USER value: "rutpod" - name: MYSQL_PASSWORD value: "abc123##A" imagePullPolicy: Always ports: - name: rw protocol: TCP containerPort: 6446 - name: ro protocol: TCP containerPort: 6447

MySQL Service

This service allows routers to access the MySQL innodb database on port 3306.

mysql_service.yaml

apiVersion: v1 kind: Service metadata: name: mysql-service spec: ports: - name: mysql port: 3306 protocol: TCP targetPort: 3306 sessionAffinity: ClientIP type: ClusterIP --- apiVersion: v1 kind: Endpoints metadata: name: mysql-service subsets: - addresses: - ip: 192.168.8.110 - ip: 192.168.8.111 - ip: 192.168.8.112 ports: - name: mysql port: 3306 protocol: TCP

Router Service

• This service allows application services to use MySQL database via routers.
• Expose two ports, 6446 read write and 6447 read only.

router_service.yaml

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

Testing the connectivity

The qrouter setup can be easily validated deploying mysql client in kubernetes.
kubectl run mysql-client --image=mysql:5.7 -it --rm --restart=Never -- /bin/bash From the client container run below mysql commands,
Check read write connection,mysql -u rutpod -p -h mysql-router-service -P 6446 - rw
read only connection,mysql -u rutpod -p -h mysql-router-service -P 6447 - rw