Openshift: How to remote access MySQL?

Question

So I just finished setting up a JBoss application server gear on Openshift and I attached a MySQL and phpmyadmin cartridges. My question is if there is a way to remote access to the database server using an app like MySQL Workbench?

Cloe

essentially but from a remote instead – Cloe

I think its possible to set up mySQL to allow remote connections without a lot of difficulty. I saw a script years ago that could break into the database without a password however that was on localhost. I would think its possible to connect phpmyadmin using the remote ip address of the SQL server in the config – Bobinette

haha im not trying to get in trouble here. It should be legal with the right steps. – Cloe

Answer 1

You can use rhc port-forward to forward ports from your database to your local machine. Check out the tutorial here. The basic idea of port forwarding in this context is that you can forward ports on your local machine to ones on the gear. So in your case, you would forward some port on your local machine to the port mysql is listening on for connections in your gear. Then you would connect MySQL Workbench to the port on your local machine.

Dieldrin

do you a link on how to do that? I read some stuff before on it but i didnt understand it – Cloe

@Mtn_Wolf, see my edits. Let me know if you need more information. – Dieldrin

port-forwarding doesn't seems to be a permanent solution. I have a standalone application which needs to communicate to the database. How do I do that? – Tease

Port-forward IS a permament solution. – Cacology

Do I have to port-forward on my server or on my local machine? – Baram

Hi Denny. You should run the port-forward command on the machine you want the ports to be fowarded to. – Dieldrin

Answer 2

Use SSH and MysqlWorkbench

SSH commands: (next to rhc ssh myappname)

echo $OPENSHIFT_MYSQL_DB_HOST

echo $OPENSHIFT_MYSQL_DB_PORT

echo $OPENSHIFT_MYSQL_DB_USERNAME

echo $OPENSHIFT_MYSQL_DB_PASSWORD

MysqlWOrkbench

Create new connection:

Set connection name

set connection method: Standard TCP/IP over SSH

Fill SSH hostname = host of yor app, ex: "myappname-user.rhcloud.com"

SSH Username = UUID of your app

SSH Key file = On Windows: C:\Users\XXX.ssh\id_rsa On OS X: /Users/XXX/.ssh/

Click "Test connection" .... and use myqlworkbench

Hilaria

I persoanally prefer this over forwarding a port. Thanks! Also I was putting 127.0.01 as port, but echoing $OPENSHIFT_MYSQL_DB_HOST saved the day for me! PS: I've tried with Sequel Pro. – Ghastly

I can't believe this worked. Especially on Windows... amazing – Selfpreservation

Answer 3

The solution is explained here using Linux:

rhc port-forward -a app &

rhc app show -a <app>

mysql -u <user>  -h 127.0.0.1 -P 3307 -p

After entering your password, you are connected with remote mysql on Openshift.

Cacology

Answer 4

Login via SSH.

1. Login for SSH
2. export | grep MYSQL
3. View value to OPENSHIFT_MYSQL_DB_HOST and OPENSHIFT_MYSQL_DB_PORT

Example:

[xxxxxxx.rhcloud.com xxxxxxx]\>  export | grep MYSQL
declare -x OPENSHIFT_MYSQL_DB_GEAR_DNS="xxxxxxx-name.rhcloud.com"
declare -x OPENSHIFT_MYSQL_DB_GEAR_UUID="xxxxxxx"
declare -x OPENSHIFT_MYSQL_DB_HOST="xxxxxxx-name.rhcloud.com"
declare -x OPENSHIFT_MYSQL_DB_PASSWORD="nlxxxxxxx"
declare -x OPENSHIFT_MYSQL_DB_PORT="57176"
declare -x OPENSHIFT_MYSQL_DB_URL="mysql://adminxxxxxxx:nlqxxxxxxx-name.rhcloud.com:57176/"
declare -x OPENSHIFT_MYSQL_DB_USERNAME="adminxxxxxxx"

Active

Answer 5

To remotely access MySQL on Openshift, you can configure NodePort or LoadBalancer as the type on the Service instead of the default ClusterIP. This will allow external tcp access to your database. Note a nodeport will be defined in the 30000-32767 range by default which will map to the targetport.

Novanovaculite

The original question was about OpenShift 2, not OpenShift 3. – Spermous