Have you ever needed to manage your remote MySQL databases, and ended up settling on the less-than-ideal setup of having an instance of phpMyAdmin on the same server as the MySQL server? Well, I am about to make your day. I am going to show you how to manage any number of remote MySQL databases from your local instance of phpMyAdmin; without compromising on security.

This tutorial assumes you've already got phpMyAdmin up and running locally. If you need help with that, there are plenty of articles floating around to guide you.

SSH Tunnel

It is not absolutely necessary, but I suggest setting up Passwordless SSH between your local machine and the remote server. This will allow you to run the SSH tunnel as a background process.

We will be using SSH to set up a connection to our remote host, through which all requests to a specific port locally will be forwarded to a specific port on the remote machine. This is where the magic happens:

ssh -fNL 3307:localhost:3306 root@REMOTE_HOST

Replace REMOTE_HOST with the IP address (or host name) of the remote server.

Let's break that command down a bit:

  • -f Requests ssh to go to background just before command execution. Useful for having the SSH tunnel run in the background. If you are using a password to connect to the remote server, you'll want to remove this argument.
  • -N Do not execute a remote command. This is useful for just forwarding ports.
  • -L Specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side.

To verify that the SSH tunnel was started successfully, run the following command:

ps aux | grep ssh

You should see the ssh command you entered earlier in this list of processes.

Configure phpMyAdmin

Now that we've got the SSH tunnel running in the background, we can configure phpMyAdmin to connect to the remote machine over the tunnel. Edit the following configuration file:

sudo vim /etc/phpmyadmin/config.inc.php

Add the following to the end of the file:

# Add the following after all the existing server configurations:
$cfg['Servers'][$i]['verbose']       = 'Local';
$cfg['Servers'][$i]['host']          = 'localhost';
$cfg['Servers'][$i]['port']          = '3306';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysqli';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['auth_type']     = 'cookie';
$i++;

$cfg['Servers'][$i]['verbose']       = 'Remote Server 1';// Change this to whatever you like.
$cfg['Servers'][$i]['host']          = '127.0.0.1';
$cfg['Servers'][$i]['port']          = '3307';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysqli';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['auth_type']     = 'cookie';
$i++;

Save the file. Now, open up your browser and point it at your local phpMyAdmin instance. If you modified the phpMyAdmin config file correctly, you should now see a select drop down at the login screen. This will list both of the servers: "Local" and "Remote Server 1". You can add any number of servers to phpMyAdmin in this way.

Now, finally, the moment of truth. Try logging in with a valid MySQL user on your remote server. If all is well, you should be logged in to the account and should see all the databases to which that user has access.