search

MaastrichtU-IDS / dsri-documentation

📖 Documentation for the Data Science Research Infrastructure at Maastricht University
https://dsri.maastrichtuniversity.nl
MIT License
21 stars 8 forks source link

Connect to MariaDB or MySQL-DB does not work from outside DSRI (SOLVED) #26

Closed LucDM closed 3 years ago

LucDM commented 3 years ago

Describe the problem

I am testing the database-applications from the catalog. I have instantiated a persistent mariadb pod and a Mysql8 pod. Both work in local mode and I can communicate pod to pod between the two using the mysql command line tool. I want to manage the databases from my laptop using MySQL Workbench or another tool such as DBeaver. My laptop is connected over the UM-VPN. I cannot make contact with either pod. The connection gives time-out errors, probably due to firewalling. It appears the VPN-zone is not seen as a trustworthy network to access SQLdatabases on the DSRI. That makes managing the databases a lot more difficult, such as transferring data in and out of the pod and using the graphical management tools from Workbench.

To Reproduce

  1. Instantiate 2 persistent database pods from the catalog (gets IP address in range 10.129.0.0 in my case)
  2. use the mysql command line tool and use it to connect to the other pod : mysql -h (ip-otherpod) -u root -p (and provide password)
  3. this works
  4. now establish this same from your pc connected via the UM VPN
  5. see "connection refused" or "time-out"
  6. I tried this using VPN and normal user and also beheerder3 level access : does not work.

URL to the documentation related to the problem

https://maastrichtu-ids.github.io/dsri-documentation/docs/deploy-database

In the green boxes, it specifically says you can communicate pod-to-pod. That is true. This works. But this implies that remote connections are not possible.

vemonet commented 3 years ago

Using port-forwarding should be a solution to this issue. We did not used it for database yet, but it works perfectly with forwarding JupyterLab UI

oc port-forward <pod> [<local_port>:]<remote_port>

Here is the most recent documentation from OpenShift to do port fowarding: https://docs.openshift.com/container-platform/4.6/nodes/containers/nodes-containers-port-forwarding.html

Here is an older article, but which specifically discuss forwarding of databases: https://www.openshift.com/blog/openshift-connecting-database-using-port-forwarding

LucDM commented 3 years ago

Thanks Vincent, that tip was good! I managed to get it working. As you suggested, this tip is also useful for other applications and port forwardings. Here is briefly how to do this on Windows:10

Prerequisites:

You must have the oc-client installed locally. You must be connected to the UM network directly or using VPN. The pod's SQL-app must have a working access account e.g. 'root' or 'someuser'

HowTo

  1. Open a cmd box (no need for administrator)

  2. start oc.exe and request the list of pods so you can determine the pod-id that you want to connect to

Result: you get a list of the pods in use C:\Program Files (x86)\Openshift>oc get pods NAME READY STATUS RESTARTS AGE mariadb-1-deploy 0/1 Completed 0 42h mariadb-1-j9n7q 1/1 Running 0 42h

  1. I want to connect to the running mariadb-pod so activate the port-forward: (I am using the standard port)

C:\Program Files (x86)\Openshift>oc port-forward mariadb-1-j9n7q 3306:3306

Result: IPv4 and IPv6 port forwarding activated C:\Program Files (x86)\Openshift>oc port-forward mariadb-1-j9n7q 3306:3306 Forwarding from 127.0.0.1:3306 -> 3306 Forwarding from [::1]:3306 -> 3306

  1. Leave the command box open so OC can keep handling the port-forwarding as long as you need the connection

  2. start your SQL management tool (I use MySQL-WorkBench or DBeaver) and create a connection using "localhost" as the hostname using the local port number (sql default as in this example is 3306 as specified as local-port in the OC port-forward command in step 3). Use of localhost is because your PC is intercepting the traffic and tunnels it to the pod using the OC forwarder.

  3. activate your connection in the SQL-tool

Result: messages in the OC cmd window that confirm tunneled traffic Handling connection for 3306 Handling connection for 3306 Handling connection for 3306

You now can login from your PC using your favorite SQL tool.

vemonet commented 3 years ago

Happy that it worked!

And thanks a lot for those detailed instructions @LucDM , we will add it to the database documentation, that will be really helpful for people who wants to deploy databases!

By the way, I just added a template to deploy a Virtuoso triplestore on the new OKD4 cluster (https://console-openshift-console.apps.dsri2.unimaas.nl), it can be easily started from the catalog (check the Template category and search for Virtuoso) and will store data in a persistent storage automatically