Accessing Remote Databases Using Local PhpMyAdmin

Posted: 2015-11-08 23:35:15

Accessing and managing a remote MySQL database from a “localhost” phpMyAdmin is very simple.

Requirements

Four things are required –

1. The remote MySQL server must be listening on a publicly accessible IP address (usually my.ini has MySQL configured to listen on 127.0.0.1 – which will not see outside connections).

# The MySQL server
[mysqld]
bind-address	= 0.0.0.0
port		= 3306

Note that some cloud-based VM providers might also require you to connect public:3306 to private:3306 (i.e., “endpoints” on Azure) in their Control Panel.

2. The remote MySQL user-name account has to have its ‘Host:’ field set to either “%” (which means that any IP can connect) or to your public IP address.

Note that this account also has to have at least the minimum set of permissions (‘SELECT‘ vs. ‘ALL‘) granted on the database(s) you need access to…
To create a user:

CREATE USER 'user-name'@'%' IDENTIFIED BY 'user-password';

To grant the user all permission on a specific database-name:

GRANT ALL PRIVILEGES ON `database-name`.* TO 'user-name'@'%';

Or to grant the user all permission on ALL databases:

GRANT ALL PRIVILEGES ON *.* TO 'user-name'@'%';

3. The remote server’s firewall should be configured to allow inbound and outbound port 3306 TCP connections.

4. The local computer needs to have a fully working phpMyAdmin environment installed (such as that provided by WAMP-Developer Pro).

Connecting phpMyAdmin to Remote Server

Edit phpMyAdmin’s configuration file (\WampDeveloper\Tools\phpMyAdmin\config.inc.php), and at the end of it, before the ending ?> line, add in –

/* Remote Server */
$i++;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['host'] = 'myinstance.123456789012.us-east-1.rds.amazonaws.com';
$cfg['Servers'][$i]['verbose'] = 'Remote Server Name';
$cfg['Servers'][$i]['user'] = '**********';
$cfg['Servers'][$i]['password'] = '**********'; 
$cfg['Servers'][$i]['hide_db'] = '^(mysql|performance_schema|innodb|information_schema)$';

Update for the host (address of remote server), server name (can be anything – used as display name), and the MySQL’s account user + password info. Do not modify anything else in the config.inc.php file. Save file.

Afterwards –

Access the localhost phpMyAdmin:

http://127.0.0.1/phpmyadmin/

Login with user:

root

Select from the “Current Server” drop-down:

Remote Server Name

phpMyAdmin will do the rest, using the provided info to establish a connection to the remote host/server, and manage the remote database(s) as if they where local.

phpMyAdmin Remote Server

phpMyAdmin Remote Database