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.
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
It worked for me as well with xampp server…
Useful Information explained very nicely.
Thanks,
Sunil Chaudhary
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.
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']...
:You should check the host, username, and password in your configuration and make sure that they correspond…
Also, make sure php my admin’s config.inc.php does not have overriding settings for username and password in $cfg[‘Servers’]…:
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
Thank you alot! Works perfect for me.
You saved me today
Thanks
Thank You for taking the time to share!!!
Thank you so much, this was very helpful!!!!!!!!
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
Cool Man! Lost about 2 hours trying to find solution damn! :O
Thanks!
I was just missing number 2, thanks!