DO180 How to connect to mysql using a route?

[Bertrand_Polus]

In DO180, Chapter 6, Guided Exercice "Deploying a Database Server on OpenShift", we are creating a route to mysql service using the command "oc expose service mysql-openshift".

After that, we are using "oc port-forward" command and next, we connect to mysql server using localhost. (And it is working)

But if I try to connect directly to the route created above (with a command like "mysql -uuser1 -pmypa55 --protocol tcp -h ROUTE_URL") form the Workstation, it is not working. If I ping ROUTE_URL, it is working.

Why is it not possible to connect to mysql using the route? Is there something else that needs to be configured to be able to do it? (Like allowing port 3306 in some firewall configuration?)

[arulz · Accepted solution]

Routes support only a limited range of protocols: HTTP, HTTPS and websockets (in addition to TLS with SNI) and I'm not sure is possible to expose in any way mysql through routes

I think that in order to expose mysql out of the cluster you should use a load balancer service (not sure if it's possible in the labs) 

[ericbos]

The mysql database is not accessible through a web browser.

Normally you would have a frontend application talking to the mysql database, like for example the todo application or the Wordpress application.

[Bertrand_Polus]

I was not trying to access the database from the web browser, but by using mysql client and using the URL of the route. (like what is done when using port forwarding)

But, as arulz replied above, route is likely not supporting the protocol used by mysql client.

[MichaelShire]

You don't connect to a route for port-forwarding.  The oc cli creates a temporary "proxy" between your machine port and the pod.
oc port-forward <podname> -n <namespace> 3306:<remotePort>

Then on your local mysql client
mysql --host=localhost --user=myname --password mydb --port:3306

[Bertrand_Polus]

As written in the original question, I understand that doing an "oc port-forward" and connecting to the localhost is working.

What my question was is "Why is it not possible to connect directly mysql using the URL of a route?" (and this question has already been replied above)

[jeanchlopez]

Hi

to work around this you need to configure a NodePort for the mysql service and then expose that service.

This will ket you connect to your DB using mysql client

---
apiVersion: v1
kind: Service
metadata:
name: {xxxx}-mysql
labels:
app: {xxxx}
spec:
type: NodePort
ports:
- port: 3306
nodePort: 30036
selector:
app: {xxxx}
tier: mysql

---
apiVersion: route.openshift.io/v1
kind: Route
metadata:
annotations:
openshift.io/host.generated: "true"
labels:
app: {xxxx}
name: {xxxx}-mysql
namespace: {xxxx}
spec:
host: {xxxx}-mysql-{xxxx}.apps.{basedomain}
port:
targetPort: 3306
to:
kind: Service
name: {xxxx}-mysql
weight: 100
wildcardPolicy: None

mysql -h r{xxxx}-mysql-{xxxx}.apps.{basedomain} -P 30036 -u {username} -p

Note that the port you select for your node port has to be above 30000

I use this for testing and not sure how practical this would be in your case.