Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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: configure workbench

create server instance

overview of workbench home

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!

share|improve this answer

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

share|improve this answer
    
You mean to say I could do it with GUI tools? –  user53864 May 29 '11 at 16:15
    
The gui tools will just see a regular mysql endpoint in your network. That is, the stunnel end point in your network. The remote mysql server will see connections coming from the remote stunnel end point so keep this in mind when you are adding users to mysql. I could post a sample stunnel conf that I use if you want. –  Lmwangi May 30 '11 at 6:21
    
Totally I'm still not sure what I've to do!!! –  user53864 May 31 '11 at 16:13
    
Your information is useful and I'm going to try this one day. Thanks a lot! –  user53864 Jun 9 '11 at 15:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.