Can phpMyAdmin be used to connect to a remote database?

Question

I recently read this tutorial, which explains how one can easily reconfigure a local installation of phpMyAdmin to connect to a remote database. The functionality seems to be supported, but not enabled by default. I followed the tutorial, but when I attempt to connect to the remote host, the validation fails.

Someone explained to me in the comments to a related question that MySQL login takes the format of username@IPAddress. The remote database expects username@remotemachineIP, while phpMyAdmin sends username@mylocalIP.

If he's right about that, then that could be my problem. This is confusing though, because the article doesn't mention it.

---

1. Can I really connect to the remote server (hosted on an Amazon EC2) with phpMyAdmin?

2. Could this be the real issue in my connection attempt?

3. If so, how can I solve it?

Answer 1

phpMyAdmin can connect to a remote database just by setting it up with the host, user and password of the remote machine. As long as the MySQL port is open in the host, the MySQL running there is configured for allowing network connections, and your user has permission for being connected remotely.

From the machine running phpMyAdmin or any other machine (not the MySQL server), run the command line client to test if connection is possible:

$ mysql -h<ip> -u<user> -p<password> <database>

Where ip is the MySQL server IP, database is the name of your database and user and password are the credentials of your user in MySQL. This should give you the mysql client prompt on success. Or a proper description of the error.

UPDATE

MySQL server is usually configure for listen to a local socket only. It doesn't bind ti 3306 TCP port by default. In order to configure it properly you have to edit server's configuration file (usually /etc/my.cnf)

[mysqld]
bind-address            = 10.0.0.5

You have to replace the IP in this example with your server's IP address. And you have to check that option "skip-networking" is not activated. Because that would prevent MySQL binding the IP configured.

After rebooting you can check whether MySQL is listening with this command in a server's shell:

# netstat -nap | grep mysqld
tcp        0      0 10.0.0.5:3306                0.0.0.0:*                   LISTEN      3547/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     13554  3547/mysqld         /var/lib/mysql/mysql.sock

You have to see a line like the first output here. The 3306 number is the default MySQL port and you have to open it for incoming TCP connections, if you have a firewall protecting the server.

After this you should be able to connect to MySQL. But yet you may have problems loging in if your permissions are not properly set. MySQL users have user name and host in the form user@host, thus if you create a user my_user@localhost and you try to login from anything different of localhost, access will be denied. Let's think your client is running over a machine with IP address 10.0.0.33 and you want to give permission to access a database. Your user must be my_user@10.0.0.33 or my_user@% being the second one an unrestricted user for connecting from any machine. Which is better to avoid if you know beforehand the IP of your client.

Answer 2

You have to allow mysql user to log in from remote machine. For that purpose, on mysql server you have to run the following command: *GRANT ALL PRIVILEGES ON . TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD'*

More over you have to disable IP binding on mysql server by editing configuration file