Accessing Remote Databases Using Local PhpMyAdmin

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

10 thoughts on “Accessing Remote Databases Using Local PhpMyAdmin”

  1. Thanks… worked like a charm for me.

    In my case the config file was at the following location:
    C:\wamp\apps\phpmyadmin4.1.14\config.inc.php

    Pranav

  2. Error
    MySQL said: Documentation

    Cannot connect: invalid settings.
    phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in your configuration and make sure that they correspond to the information given by the administrator of the MySQL server.

    1. You should check the host, username and password in your configuration and make sure that they correspond…

      Also make sure phpmyadmin’s config.inc.php dooes not have overriding settings for username and password in $cfg['Servers']...:

  3. It doesnt work for me. When i try to login i get an error message like:
    mysqli_real_connect(): (HY000/1045): Access denied for user ‘xxxxxxxxxx’@’myipadress’ (using password: YES)

    What could it be? I will connect to another mysql server from my local PC/Mac.

    Please help

  4. Thank you for taking the time to share this. It was extremely helpful for me and almost certainly for a lot of other people. Thank you, we’re grateful :)

Leave a Reply to Jenny Cancel reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>