Connecting to a remote MySQL database via SSH tunnel with local phpMyAdmin

I got tired of working with unfamiliar and slow database tools and wanted to use my old favourite tool, phpMyAdmin on my local machine to connect to a remote database, that I can only access via an SSH tunnel. First, you need to create the SSH tunnel, of course

ssh -fNL 3307:localhost:3306 user@host

This makes the tunnel to user@host to port 3307, which you can then access as if it is local in phpMyAdmin. Next you must set up phpMyAdmin to show a dropdown to multiple servers:

  • Open config.inc.php in your phpmyadmin installation folder.
  • Create a $hosts array that contain all the info between all the servers that are different. For now, we will only specify a few items. Adapt to your liking.
$hosts = array(
     'local' => array(
          'verbose' => 'Verbose display name',
          'user' => 'blah',
          'password' => 'blah',
          'port' => 3306,
          'host' => '127.0.0.1',
          'auth_type' => 'http'
     ),
     'remote1' => array(
          'user' => 'etc'
     ),
     'etc' => array(
          'user' => 'etc'
     )
);

With the various server configs defined, you can simply edit your config.inc.php file as follows:

$i = 0;
foreach ($hosts as $k => $v) {
    $i++;
    $cfg['Servers'][$i]['verbose'] =$v['verbose'];
    $cfg['Servers'][$i]['port'] =$v['port'];
    $cfg['Servers'][$i]['user'] =$v['user'];
    $cfg['Servers'][$i]['password'] =$v['password'];
    $cfg['Servers'][$i]['host'] =$v['host'];
    $cfg['Servers'][$i]['auth_type'] =$v['auth_type'];
    $cfg['Servers'][$i]['favorite'] ='pma__favorite';
}

Simple?

Leave a Reply

Your email address will not be published. Required fields are marked *