Manage remote databases from localhost with phpMyAdmin

Access and work on your remote database sever from localhost using phpMyAdmin. It only requires a few changes to the configuration file found in the phpMyAdmin folder.

Many developers including me use phpMyAdmin to do the database work needed for their projects and this is probably because of it's ease of use and being able to create the needed tables without having to write the SQL statements. I use it because it's fast and it provides all the features I need at this point.

Maintenance and debugging apps very often requires you to access and inspect the live database data and a very handy way to do that is to use a tool you probably already have instead of having to login though your hosting company control panel in many cases. With phpMyAdmin this is disabled by default, but it can easily enabled by modifying the phpMyAdmin configuration file that can be found in it's installation folder.

The file config.inc.php contains the configuration settings for your phpMyAdmin installation. It uses an array to store sets of config options for every server it can connect to and by default there is only one, your own machine, or localhost. In order to connect to another server, you would have to add another set of config options to the config array. A set of config options would look something similar to this:

$i++;
$cfg['Servers'][$i]['host']          = '';
$cfg['Servers'][$i]['port']          = '';
$cfg['Servers'][$i]['socket']        = '';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysql';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['auth_type']     = 'config';
$cfg['Servers'][$i]['user']          = 'username';
$cfg['Servers'][$i]['password']      = 'password';

Pay attention that the config array is called cfg and it's a multidimensional array and that all servers, have to be part of the $cfg["Servers"] inner array. The way this works is by using an incrementing variable $i that sets a different inner array for each server inside the $cfg["Servers"] array. For this to work you need to make sure each new set of config options starts with an incremented $i by using $i++.

TIP This might be a personal preference but when I edit config files, I add my own changes to the bottom of the file if I can, as this makes it easier to find later on.

$i++;
Incrementing variable for each server
$cfg['Servers'][$i]['host']
Server host name or IP
$cfg['Servers'][$i]['port']
MySQL port - leave blank for default port
$cfg['Servers'][$i]['socket']
Path to the socket - leave blank for default socket
$cfg['Servers'][$i]['connect_type']
How to connect to MySQL server ('tcp' or 'socket')
$cfg['Servers'][$i]['extension']
The php MySQL extension to use ('mysql' or 'mysqli')
$cfg['Servers'][$i]['compress']
Use compressed protocol for the MySQL connection (requires PHP >= 4.3.0)
$cfg['Servers'][$i]['auth_type']
Method of authentication
$cfg['Servers'][$i]['user']
Account username
$cfg['Servers'][$i]['password']
Account password

tip For more info on the config options, please visit the official documentation provided by phpMyAdmin.

Many hosting companies do not allow remote database access by default, so first check with your hosting company and enable this feature. In my case, the hosting company I use, allows me to define a list of IP addresses that are allowed to connect to the remote database server.

issue By using this setup to access your remote database, you need to know that it can become a security issue, if your machine is breached, as the credentials are stored in clear text in the config file. This makes your database as secure as your localhost machine.

Once you added your set of config options, access phpMyAdmin on your local machine and a dropdown menu will be available to you as part of the login screen (when username and password is required to access phpMyAdmin), to select the server you want to connect to.

If you want to switch servers while being logged in into one of them, just go to the home page of phpMyAdmin and the same dropdown will be available to choose a different server.

3 comments

U Free CodeCanyon Scripts 2013-03-05 12:19:06

We absolutely love your blog and find nearly all of your post's to be just what I'm looking for. Do you offer guest writers to write content available for you? I wouldn't mind writing a post or elaborating on many of the subjects you write in relation to here. Again, awesome site!

U Glenn 2013-03-05 05:04:17

This is a really good tip particularly to those new to the blogosphere. Short but very accurate info… Thanks for sharing this one. A must read post!

U Theron 2013-02-24 04:21:58

Howdy, I think your web site may be having browser compatibility issues. When I look at your blog in Safari, it looks fine however, if opening in Internet Explorer, it's got some overlapping issues. I merely wanted to give you a quick heads up! Other than that, fantastic website!