MySQL is a database that is synonymous with ease-of-use, and most database-driven Web applications use it as their database of choice. Because of this, MySQL is used on many Web servers. While the MySQL command line tool is useful, unless you know all the ins and outs of SQL syntax, it can be time-consuming to get things done. It is for this reason that tools like phpMyAdmin have become so popular.
The MySQL developers have also come out with their own GUI tools that can be used to connect to local and remote MySQL databases. These tools include the MySQL Administrator, MySQL Query Browser, and MySQL Workbench—all are great graphical tools to ease the manipulation and creation of MySQL databases.
The problem with using these tools to manipulate a remote database is that they often require you to make MySQL listen on a network interface; most MySQL administrators choose to have MySQL listen only to the localhost, or to a socket, disallowing remote connections. This is good security practice; however, it doesn't allow you to make use of these GUI tools remotely without a little bit of effort. Using ssh to tunnel the connection works wonders here. Not only does it allow for strong authentication and encryption, but it doesn't compromise the design of having MySQL listen for local connections only.
To begin with, MySQL must be listening to the localhost (by default it will only listen for connections on a local socket), which can be accomplished by telling MySQL to allow network connections then restarting the server. MySQL should be configured to only listen for connections on the loopback, or local, network interface.
Next, edit the ~/.ssh/config file on the host you wish to make the connections from, and insert the following:
LocalForward *:13306 localhost:3306
This will connect to webserver.domain.com as user joe and forward port 13306 on the local system to port 3306 on webserver.domain.com (the standard MySQL port). Note that we're not binding the forwarded port to the local interface on the local machine, but to all interfaces; this means we can connect to me.domain.com port 13306 (assuming me.domain.com is the name of the local workstation), instead of connecting to localhost port 13306 (this is important, because the GUI tools will try to use a socket to connect to the localhost, which we do not want). Now you can make the initial connection by executing:
$ ssh -f -N remotesql
Finally, fire up the MySQL Administrator and tell it to connect to me.domain.com on port 13306 with your credentials (be it as a user account or as root). If you get an "access denied" error, check permissions on the remote database. You may have to give permissions to email@example.com in order to properly connect.
Using this, you will be able to connect to the database over a secure connection across the internet, by any system on the local network (unless firewall rules prevent it on the local machine), so you can also use the MySQL command line tool locally to work on a remote database by using:
$ mysql -u root -p -h me.domain.com -P 13306
Delivered each Tuesday, TechRepublic's free Linux NetNote provides tips, articles, and other resources to help you hone your Linux skills. Automatically sign up today!
Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.