A better alternative for using phpMyAdmin

Almost every week I run into at least one production site that has a phpMyAdmin installed in the document root of the site, or as a separate vhost on the server. While this used to be pretty required in 2005 to make changes to the database in production, now in 2015 we have better ways to do that.

The problem

The reason phpMyAdmin is installed on the website is that the MySQL server only listens on localhost via a UNIX socket, or on the loopback interface via tcp. That way it’s impossible to connect to it from a remote address.

The bad thing about this is that we have an extra web application on our site we need to take care of. These phpMyAdmin installs are often never updated and might contain security issues that allow attackers to gain access to you production database.

A better alternative: SSH tunnels

If your server is reachable via SSH (even via a VPN connection) we can use a better method: SSH tunnels.

How this works is pretty simple:

  • We connect to our server via an SSH connection
  • Over this SSH connection we set up a tunnel with a port forward that allows a SQL client on our own computer to use the remote database as if it was a local connection

This might sound complicated, but there are a lot of SQL clients available that do this SSH tunneling for you. Below is a screenshot from Sequel Pro for OSX:

![Sequel Pro for Mac OSX][sequel-pro] [sequel-pro]: /wp-content/uploads/2015/10/Screenshot-2015-10-08-10.10.42.png

You can see 2 things here:

  • the MySQL connection (which always connects to
  • the SSH connection (which is your normal SSH login)

Once this connection has been setup the SQL client works just as it would on a local connection.

SQL clients that support tunneling

These are the clients I use on a daily basis:

  • OSX: Sequel Pro
  • Linux, Windows & OSX: MySQL Workbench
  • And ofcourse using the mysql commandline program in an SSH connection (mostly via drush sql-cli when it’s a Drupal website)
comments powered by Disqus