Ubuntu MySQL GUI logins with certificate/key

Question

I will connect to MySQL on ubuntu cloud server everyday with Mysql Administrator and Mysql Query Browser GUI tools and I feel very slow in responding. I know it is possible to connect with ssh entering the below command in the terminal and then using the Mysql GUI tools.

ssh -L 3307:127.0.0.1:3306 mysql-server-ip

But this is not easy for everybody to run the above command in the terminal and then using the gui tools.

The above ssh command is also good and I felt as if I'm working on local databases but it would be best and easy for everybody if it had an option to specify that ssh command in the mysql gui tools itself.

Moreover I'm thinking to remove ip based restrictions as I'm using iptables rules only to allow mysql and other ports for the specific IPs on cloud server.

Is there any best and easy way of connecting to mysql like using certificate along with mysql gui tools?.

Finally what I'm expecting is, with Mysql GUI tools(Administrator & Query Browser):

• I want to easily connect to mysql with some certificate/key
• I want to remove ip based restriction so that whoever having key/certificate could connect to mysql

Anybody has any idea?. Thank you!

Answer 1

Simply I could do this with the help of SSH and MySQL Workbench. The Workbench downloads are available for variety of ubuntu versions in this link. To avoid ip based restrictions, should open ssh port for everyone and disable the ssh password login and instead use ssh-key logins. Only those who are allowed in the firewall can connect to the mysql but having the ssh private key can connect to mysql with the help of MySQL Workbench irrespective of the location, firewall rules, ip addresses and so on via ssh.

Take a look at these links:

http://dev.mysql.com/doc/workbench/en/wb-new-server-instance-wizard.html

http://dev.mysql.com/doc/workbench/en/wb-manage-server-instance-dialog.html

Below are some screenshots showing options to connect to mysql with Workbench:

To avoid ip based restriction with MySQL Query Brower & Aministrator, I don't know other than ssh port forwarding.

I didn't try your answer, I'll try it one day. It takes me more time to understand. Thank you!

Answer 2

I had the same problem recently. I couldn't find support for SSL in the MySQLgui tools. I also failed to find a MySQL proxy with SSL support that I could install in my network in the folowing way i.e.

Me/Anyone else in my net---------->MySQL Proxy host in my network------->MySQL over TLS-------->MySQL remote endpoint.

My eventual solution was to use stunnel and have:

Me/Anyone else in my net----------->Stunnel host in my net:3306-------->MYSQL in a TLS tunnel----->Stunnel in remote net:3307----->MySQL remote endpoint

The remote stunnel end could also be the mysql server. You can use different ports to fanout to different remote MySQL servers.

Update: This is what I have working for me:

Client end

local-stunnel# cat /etc/stunnel/stunnel.conf |grep -v ';'


sslVersion = SSLv3

chroot = /var/lib/stunnel4/
setuid = stunnel4
setgid = stunnel4
pid = /stunnel4.pid

socket = l:TCP_NODELAY=1
socket = r:TCP_NODELAY=1



debug = 2
output = /var/log/stunnel4/stunnel.log

client = yes


[mysqlc]
accept  = 13306
connect = remote.example.net:3307

Remote/server end:

remote-stunnel# cat /etc/stunnel/stunnel.conf |grep -v ';'

cert = /etc/pki/tls/certs/stunnel.pem

chroot = /var/run/stunnel/
setuid = nobody
setgid = nobody
pid = /stunnel.pid

socket = l:TCP_NODELAY=1
socket = r:TCP_NODELAY=1



debug = 3
output = /var/log/stunnel/stunnel.log



[mysqls]
accept  = 3307
connect = 3306

A certificate and key should be generated and con'cat'ed to a file (stunnel.pem). Mine looks like this

# cat /etc/pki/tls/certs/stunnel.pem 
-----BEGIN RSA PRIVATE KEY-----
MII.....
....
-----END RSA PRIVATE KEY-----

-----BEGIN CERTIFICATE-----
MIID....
....
-----END CERTIFICATE-----

So my tunnel looks like:

ME------Plaintext MySQL----->local-stunnel:13306-----TLS--->remote-stunnel:3307---Plaintext MySQL--->127.0.0.1:3306

Of course you can change the connect = 3306 on the remote end point to otherhost:3306