Testing MySQL Router Deployments on Kubernetes with InnoDB ClusterSet

Testing MySQL Router Deployments on Kubernetes

Once your MySQL Router is deployed and configured to handle read/write routing between InnoDB ClusterSet primary and secondary clusters, the next critical step is verifying that the routing behaves as expected.

In this post, we’ll walk through a simple Kubernetes-native testing strategy using a minimal Python application to validate Router behavior across ports 6446 (RW) and 6447 (RO).

The Goal

We want to:

• Write to MySQL via Router port 6446 every 2 seconds.
• Read via Router port 6447 every 2 seconds.
• Log RW operation metadata to a dedicated table (rw_log).
• Print RO query responses in the pod logs.

This lets us monitor which router handled the request and from which pod, helping validate traffic direction and router/cluster availability in a live environment.

RW Logging Schema

CREATE TABLE rw_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    connected_host VARCHAR(255),
    pod_host VARCHAR(255),
    router_role VARCHAR(10)
);

This will track connection source (connected_host), calling pod (pod_host), and whether it's a read or write request.

Kubernetes Secrets

apiVersion: v1
kind: Secret
metadata:
  name: mysql-router-secret
  namespace: mysql-router
type: Opaque
stringData:
  MYSQL_USER: conntest
  MYSQL_PASSWORD: examplepass

ConfigMap – The Python App

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-logger-script
  namespace: mysql-router
data:
  connect_loop.py: |
    import os
    import time
    import mysql.connector
    import socket
    from datetime import datetime

    def connect_db(role):
        host = os.environ['MYSQL_RW_HOST'] if role == 'RW' else os.environ['MYSQL_RO_HOST']
        port = int(os.environ['MYSQL_RW_PORT']) if role == 'RW' else int(os.environ['MYSQL_RO_PORT'])
        conn = mysql.connector.connect(
            host=host,
            port=port,
            user=os.environ['MYSQL_USER'],
            password=os.environ['MYSQL_PASSWORD'],
            database='test'
        )
        return conn

    def write_log():
        conn = connect_db('RW')
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO rw_log (connected_host, pod_host, router_role) VALUES (%s, %s, %s)",
            (conn.server_host, socket.gethostname(), 'RW')
        )
        conn.commit()
        cursor.close()
        conn.close()

    def read_log():
        conn = connect_db('RO')
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM rw_log")
        result = cursor.fetchone()
        print(f"[RO] Row Count: {result[0]}")
        cursor.close()
        conn.close()

    while True:
        try:
            write_log()
        except Exception as e:
            print(f"[RW ERROR] {e}")
        try:
            read_log()
        except Exception as e:
            print(f"[RO ERROR] {e}")
        time.sleep(2)

Kubernetes Deployment

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-router-test
  namespace: mysql-router
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql-router-test
  template:
    metadata:
      labels:
        app: mysql-router-test
    spec:
      containers:
      - name: logger
        image: python:3.9
        command: ["python"]
        args: ["/app/connect_loop.py"]
        env:
        - name: MYSQL_RW_HOST
          value: "mysql-router.mysql-router.svc.cluster.local"
        - name: MYSQL_RW_PORT
          value: "6446"
        - name: MYSQL_RO_HOST
          value: "mysql-router.mysql-router.svc.cluster.local"
        - name: MYSQL_RO_PORT
          value: "6447"
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: mysql-router-secret
              key: MYSQL_USER
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-router-secret
              key: MYSQL_PASSWORD
        volumeMounts:
        - name: app-code
          mountPath: /app
      volumes:
      - name: app-code
        configMap:
          name: mysql-logger-script

Observing Results

kubectl logs deploy/mysql-router-test -n mysql-router

You should see alternating logs like:

[RW] Inserted into rw_log via mysql-router1
[RO] Row Count: 23
[RW] Inserted into rw_log via mysql-router2
[RO] Row Count: 24

Meanwhile, query the rw_log table from MySQL to confirm inserts are correctly registered:

SELECT * FROM rw_log ORDER BY created_at DESC LIMIT 5;

Wrap-Up

This test setup provides a quick and effective way to validate your MySQL Router deployment under InnoDB ClusterSet architecture. It ensures:

• Routing through RW/RO ports behaves as expected.
• Failovers and redirection can be tracked.
• Kubernetes-native validation is streamlined.