This is a particularly useful method for gaining access to your remote MySQL databases, such as those held on a web hosting account where the MySQL port may not be open. You can use this method to gain access to other services too (SMTP, IMAP, FTP), but in this post I'll explain how I use it in combination with MySQL Query Browser to administrate my DBs with a GUI. You need to have SSH access to your remote server (normally over port 22) for this to work. My instructions are for Ubuntu but it's easily transferred to other distros, Mac OS X, and Windows (just download an SSH client).
Run sudo apt-get install ssh if it isn't installed already, which
will install several SSH connectivity tools (more info here). Query
Browser is an excellent tool with which to run queries, updates, create
views and stored procedures, and loads more besides. Run
sudo apt-get install mysql-query-browser. Now to create the SSH
tunnel by using port forwarding; here's how I access a MySQL instance on
my local network:
james@james-laptop:~$ ssh -L 3307:localhost:3306 root@192.168.1.211 root@192.168.1.211's password:
Essentially this forwards all traffic on port 3307 on the local machine (james-laptop) to port 3306 on 192.168.1.211. The general format is ssh -L <localport>:<host>:<hostport>. Note that in my example I used localhost, but this is resolved after the connection has been made to 192.168.1.211 and so it refers to that IP address.
Effectively we can now access port 3306 (the default MySQL port) on 192.168.1.211 via port 3307 on james-laptop even though port 22 (the SSH port) is the only port open on 192.168.1.211. Keep the connection open (i.e. don't close the terminal) and open Query Browser. In the connection dialogue set the hostname to 127.0.0.1 and the port to 3307 and enter a username/password as required. Hit connect and you should see a graphical representation of your database(s). Note that in *nix OSs (including Mac OS X) you must use 127.0.0.1 rather than 'localhost' or the connection will be attempted via a named pipe rather than TCP.