All HowTo's MySQL & MariaDB

Remotely access MySQL on an OpenShift server

OpenShift doesn’t allow remote access to MySQL gear. You have to use IPTables to work around this limitation. The good news is that it’s a simple process. This article is a walk-through showing how i’ve done it.

First we need to get the application ID of the OpenShift application we’re working with. In my case i have three applications on this OpenShift server with MySQL gears.

The following command “rhc app-show myapp” lists the details of my “myapp” application. I’ve cut the output of the command to keep the content focused.

[root@OpenShift ~]# rhc app-show myapp
myapp @ http://myapp-form.apps.example.com/ (uuid: 586c58cdb3468bdcb00000b5)
----------------------------------------------------------------------------------------------
  Domain:     form
  Created:    Jan 04 12:37 PM
  Gears:      1 (defaults to small)
  Git URL:    ssh://[email protected]/~/git/myapp.git/
  SSH:        [email protected]
  Deployment: auto (on git push)

The ID i need is “586c58cdb3468bdcb00000b5” as you can see in the above output. Keep that ID noted down for later.

The next step is to see what MySQL processes are running. We use the “netstat -nlp | grep mysql” command for this.

[root@OpenShift ~]# netstat -nlp | grep mysql
tcp        0      0 127.6.31.130:3306           0.0.0.0:*                   LISTEN      14860/mysqld        
tcp        0      0 127.8.119.130:3306          0.0.0.0:*                   LISTEN      31176/mysqld        
tcp        0      0 127.9.167.2:3306            0.0.0.0:*                   LISTEN      25470/mysqld        
unix  2      [ ACC ]     STREAM     LISTENING     426800215 31176/mysqld        /var/lib/openshift/55079d14b3468b9a12000002/mysql//socket/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     425237167 25470/mysqld        /var/lib/openshift/586c58cdb3468bdcb00000b5/mysql//socket/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     426853850 14860/mysqld        /var/lib/openshift/557a207bb3468ba87b000001/mysql//socket/mysql.sock

The above output has two lines bolded. The bottom line is clearly the one we’re interested in because it has the application ID mentioned in it. Also notice the process ID, “25470”. The process ID is also mentioned in the top bolded line. We now have a match. The IP address we’re interested in is “127.9.167.2”.

The following is my sample “/etc/sysconfig/iptables” file. I’ve added a port forward that you can copy. Note that i’ve decided to use port “3307” for my port forward. In other words, when accessing this MySQL server remotely, i’d have to specify the port as being “3307”.

*nat
:PREROUTING ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:POSTROUTING ACCEPT [0:0]
-A POSTROUTING -o eth+ -j MASQUERADE
-A PREROUTING -i eth0 -p tcp --dport 3307 -j DNAT --to-destination 127.9.167.2:3306
COMMIT

*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -s 10.0.0.0/8 -j ACCEPT
-A INPUT -s 172.16.0.0/16 -j ACCEPT
:rhc-app-comm - [0:0]
-A INPUT -m tcp -p tcp --dport 35531:65535 -m state --state NEW -j ACCEPT
-A INPUT -j rhc-app-comm
-A OUTPUT -j rhc-app-comm
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

Test it with the following command from a remote system.

[me@workstation1 ~]$ mysql -h broker.apps.example.com -u myuser -p myapp -P 3307

I hope this has been a time saver.

Leave a Reply

Your email address will not be published. Required fields are marked *