When mysql server is hosted on a cloud environment like amazon aws/ec2 or rackspace, connecting to it using mysql GUI tools may not be possible without opening default mysql server port 3306. It is not recommended to open port 3306 on your production server for outside world for security reasons. One option for regular mysql operations is to connect to your remote cloud server using ssh and run command line utility mysql. This is not very user friendly option.
The second option is to use phpmyadmin and do admin stuff from web interface. This option is slightly better if your mysql password is strong and you use it over https. But this will require setting up secure web server setup. Also the web interface may have its own limitations as compared to a GUI client.
My favorite option is to use ssh port forwarding feature. Assuming you already have a pretty good ssh password or RSA/DSA based ssh login setup, this will not add to any additional security risk. Here are the steps to do it:
- ssh -i [PRIVATE_SSH_KEY] -L 3306:localhost:3306 [USER]@REMOTE_HOSTNAME
(Assuming your mysql server is also on REMOTE_HOSTNAME) - If you are using password based auth then simply use
ssh -L 3306:localhost:3306 [USER]@REMOTE_HOSTNAME - open your mysql GUI tool and use these settings (exact layout of ui may depend on your GUI client)
This technique can be used for other applications also where you need to connect to some specific port which is not open for remote connections.
