General info

I routing traffic by different port numbers

You are right, the reason for that is that connection to Mysql is done via TCP. That is why it is definitely not possible to have two simultaneous connections to two servers on the same IP:port.

Unlike HTTP, the TCP don't have headers that allows distinguishing the host the traffic shall be routed to. However, still there are at least two ways to achieve the functionality you'd like to achieve :) I'll describe that later.

I want to know if hostname based routing is possible or not I don't want separate load balancer for each mysql service.

K8s allows a few methods for service to be reachable outside the cluster (namely hostNetwork, hostPort, NodePort , LoadBalancer, Ingress )

The LoadBalancer is the simplest way to serve traffic on LoadBalancerIP:port ; however, due to TCP nature of connection you'll have to use one LoadBalancer per one mysql instance.

kind: Service 
apiVersion: v1 
metadata: 
  name: mysql 
spec: 
  type: LoadBalancer 
  ports: 
    - port: 3306 
  selector: 
    name: my-mysql

The NodePort looks good, but it allows you connecting only when you know port (which can be tedious work for clients)

Proposed solutions

• External IPs

If there are external IPs that route to one or more cluster nodes, Kubernetes Services can be exposed on those externalIPs. Traffic that ingresses into the cluster with the external IP (as destination IP), on the Service port, will be routed to one of the Service endpoints. externalIPs are not managed by Kubernetes and are the responsibility of the cluster administrator.

In the Service spec, externalIPs can be specified along with any of the ServiceTypes. In the example below, mysql-1 can be accessed by clients on 1.2.3.4:3306 (externalIP:port) and mysql-2 can be accessed by clients on 4.3.2.1:3306

$ cat stereo-mysql-3306.yaml 
apiVersion: v1
kind: Service
metadata:
  name: mysql-1234-inst-1
spec:
  selector:
    app: mysql-prod
  ports:
    - name: mysql
      protocol: TCP
      port: 3306
      targetPort: 3306
  externalIPs:
    - 1.2.3.4 
---
apiVersion: v1
kind: Service
metadata:
  name: mysql-4321-inst-1
spec:
  selector:
    app: mysql-repl
  ports:
    - name: mysql
      protocol: TCP
      port: 3306
      targetPort: 3306
  externalIPs:
    - 4.3.2.1

Note: you need to have 1.2.3.4 and 4.3.2.1 assigned to your Nodes (and resolve mysqlServerA / mysqlserverB at mydomain.comto these IPs as well). I've tested that solution on my GKE cluster and it works :).

With that config all the requests for mysqlServerA.mydomain.com:3306 that resolves to 1.2.3.4 are going to be routed to the Endpoints for service mysql-1234-inst-1 with the app: mysql-prod selector, and mysqlServerA.mydomain.com:3306 will be served by app: mysql-repl.

Of course it is possible to split that config for 2 namespaces (one namespace - one mysql - one service per one namespace).

• ClusterIP+OpenVPN

Taking into consideration that your mysql pods have ClusterIPs, it is possible to spawn additional VPN pod in cluster and connect to mysqls through it.

As a result, you can establish VPN connection and have access to all the cluster resources. That is very limited solution which requires establishing the VPN connection for anyone who needs access to mysql.

Good practice is to add a bastion server on top of that solution. That server will be responsible for providing access to cluster services via VPN.

Hope that helps.