HowTo: Use PhpMyAdmin to Remote Admin mySQL DBs via SSH Tunnel...

RemoteAccess
RemoteAccess

I know this has been done to death, but I'm going to consolidate several technical issues here into one article with a very specific purpose: Using Mac OS (Snow Leopard) running phpMyAdmin (locally) to remote-access and administrate mySQL server through a secure shell tunnel.

That's pretty esoteric.

While there are a lot of good tools out there, free ones to boot, for managing mySQL databases, phpMyAdmin remains one of my personal favorites.  So, if you don't have phpMyAdmin installed, we need to do that first.

I am assuming that you already have an up-and-running webserver on your mac.  Since most come so equipped out of the box, then there shouldn't be any significant issues.  However, you should be aware that you can encounter problems with the newer machines and the older software package installs.  For example, the new boxes are 64-bit architecture but MAMP installs a 32-bit version of tools.   As you start adding-on additional utilities, you're going to get that "huh?" look on your face as modules stop working.

Fortunately, the solution is simple:  mac ports.  Mac ports is a collection of unix-based software tools that have been ported over to work in the left-handed BSD environment.  It also installs the 64-bit versions of the tools you'll need.  Plus you can install other way-cool software, like MongoDB.  Since I've provided you with the links you need to get started, get started.

Uninstall MAMP if you've previously installed it and refer to the mac-ports page for downloading and installing mac ports.  (Tip:  Every Monday morning, I run the following commands from shell:

# sudo port selfupdate
# sudo port upgrade outdated

This ensures that all of my installed ports are current.  Which is really the nice thing about using mac ports.  It keeps all your installed packages current.

# sudo port list | grep -i phpmyadmin

will generate a list of port software with the string phpmyad in it.  You're looking for phpMyAdmin.  To install,

# sudo port install {packageName}

And let port spin-off and do it's thing.  Depending on what's happening on your machine, this may take a while.  So, while we're waiting on that to complete, let's talk about ssh and rsa keys.

ssh is the secure shell - it establishes a  secure (encrypted) communications channel via tcp/ip between your local machine and a remote host.  ssh is installed,by default, with Snow Leopard so you're good to go there.

What you will need on the remote host is an account.  Secure or not, you can't log into and access any machine without an account.  What the RSA key does (which I am not going to delve into rsa in this article), is authenticate your identity to the remote server using public/private key pairs.

Now, take an aside for a second and let's talk about tcp/ip and ports.

TCP/IP is a communications protocol.  That means that it defines the rules by which two systems introduce themselves and agree to talk to each other.   You can access a remote machine's command line insecurely by using an application such as telnet or rlogin.  However, everything that you transmit and receive between the two machines is done in clear-text.

SSH is a secure shell application that uses TCP/IP to establish an encrypted (via our RSA keys) session to a remote server and all information that is sent and received is encrypted, sent, received,  and decrypted using the public/private key pairs.  There are other schemes for encryption that you can use, such as DSA.  Whatever, the point is that if someone was listening to the SSH port (which is, by default, port #22), then they couldn't really understand your traffic since it will all look like random characters.

But, wait!  You say...what if it's some big gubbmint agency that's listening to my port with these huge supercomputers that do nothing by crack little dweeb encryption routines like mine?  Very possible, says I.  That's why we're going to change the default port over which SSH communicates.  Use a non-standard port in your networking configuration (yup, that's another article, too...) to move your ssh port to something way non-standard.  Doesn't really matter what as long as you follow the guidelines outlined in the wiki article I linked for you.

So, we should have working at this point:

  • a rsa-generated public/private key pair
  • an account on the remote machine that gives us shell access
  • web-server environment installed and working locally
  • phpMyAdmin installed locally

To test that you've successfully installed phpMyAdmin, you'll need to know where the package was installed and set your browser to that location:  http://localhost/phpMyAdmin is what I've established.  If you want to install the software in an non-standard location, you can set-up an Apache virtual host to alias the url to any location on your local machine:

By default, as part of the installation process, phpMyAdmin makes the following entry in your apache httpd.conf file:

[cc lang='text' ]

Alias /phpMyAdmin "/opt/local/www/phpmadmin"
Options Indexes MultiViews AllowOverride None
Order allow,deny
Allow from all

When you're able to get phpMyAdmin up and running on your local machine and, presumably, connected to your localhost's mysql server, you're going to need to do one last edit to your phpMyAdmin configuration file.

Before you can do that, however, you need to know one last bit of information about the mysql port on the remote host.

The way most admins configure their machines is that they disallow the standard port for mysql (#3306) by shutting off access to the port to any external user.  Only the localhost users may access the mysql database via port #3306.   If we want to access the database, we can only do so through our ssh tunnel.

The way we do that is to establish the ssh tunnel and we alias a local port on our machine that re-directs all traffic to that port, through the tunnel, to the destination port on the remote machine.  (Take a second to think about that.)  Additionally, the ssh tunnel takes all responses on the remote host and tunnels them back to our local host.

We need to define the local ports, and the remote servers that we'll map to, in the configuration file for phpMyAdmin.   You can add a near-infinite number of remote servers (at least up to a large int?) but for this, we're just going to add one.  Pull up your phpMyAdmin configuration file: config.inc.php and look for the lines/section:

/** First Server */
$++;

/** Authentication Type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';

/** Server Parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['auth_type'] = 'config';

// Authentication Method (config, httpd or cookie based)?
$cfg['Servers'][$i]['user'] = 'root'; // MYSQL user
$cfg['Servers'][$i]['password'] = 'root'; // MYSQL password

/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/* rajk - for blobstreaming */
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

And, after that, add the configuration section for each one of your server(s):

/* adding remote support for q/a connection */ 
$i++;
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3307';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'http';
$cfg['Servers'][$i]['AllowNoPassword'] = FALSE;

This establishes a listening port on the local machine at port 3307 for the phpMyAdmin application.  Please also don't forget the $i++ at the beginning of each block of servers that you add.

The only thing left to do is to ssh in to the remote box:

# ssh -C -p {port#} -L 3307:localhost:3306 {username}@{remote_machine_name}

You'll be prompted for your password (for the {remote_machine_name}) and once you successfully enter it, you'll be logged-into the remote system with what looks like a normal shell account.  This connection must be established before you attempt to connect to the remote sql server with whatever tool you're going to use.

remotehost
remotehost

When you start phpMyAdmin locally, and you go to the localhost root screen, you should now see a "server" option that will list all of the additional server's you've configured in the phpMyAdmin file.

Clicking on one of those servers will re-direct all your phpMyAdmin traffic out over the network, through the secure-shell tunnel to the remote host, and back again to the phpMyAdmin LAMP application executing in your local environment.

In other words, even though your URL tag will show you as working locally, you're really working on remote systems.  You break it, you've bought it.

Ok, so, to summarize, we've introduced you to mac ports (if you already didn't know about this way-cool tool) and talked about how you should upgrade all of your LAMP tools to 64-bit based applications so that they play well with each other.  We also talked about how to get the latest copy of phpMyAdmin and have it automagically installed for you -- as well as maintained regularly for as long as you can remember to run the mac ports update commands.

We talked about the SSH protocol and public/private key encryption - and believe me, I'll cover a lot more on this topic at a later date, and how it's used to create secure tcp/ip connections to remote hosts.  We also mentioned how to use the ssh tool to create secure tunnels through which we can run other applications, like phpMyAdmin, which is really a LAMP-stack web-based application that is served locally on your port 80, but accesses the database remotely on your local port 3308 (for example) which is sent out over the tunnel port (say, 50000), and then access the remote machines localhost standard port (for mySQL of 3306) before reverse tunneling everything back to you.

How cool is that?