Open Source

Use MySQL GUI tools remotely and securely

Vincent Danen looks at MySQL GUI tools for database-driven Web applications. Here are his tips for getting the most of the GUI tools, even while connecting remotely, without compromising security.

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:

Host remotesql
  Hostname webserver.domain.com
  User joe
  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 user@localhost.localdomain 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!

About

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.

3 comments
ikaros
ikaros

I'm using putty in Windows so there is no ~/.ssh/config Any idea how to follow this tutorial?

hijyoti007
hijyoti007

SQLyog is much better than the official MySQL GUI tools. Try them and you wont be disappointed.

rberger
rberger

Great and helpful post. One bug it seems. At the end you said to remotely use the mysql command via tunnel: mysql -u root -p -h me.domain.com -P 13306 But it should not specify the -h me.domain.com for the remote host since you want it to connect to the localhost and get forwarded to the remote host over the ssh tunnel you created earlier. So it should be: mysql -u root -p -P 13306 Or if you wanted to connect to a particular database on the remote host thru the tunnel: mysql -u root -p -P 13306 database_name

Editor's Picks