Connect to a Remote MySQL Server

Normally the default install of your MySQL only allows connection from same pc. Suppose the firewall is not an issue, MySQL should be able to accessed remotely, providing the privilege is given to a user from a special IP, otherwise you will get an error message like:

#1130 host ‘xxxx’ is not allowed to connect to this mysql server.

I saw a lot of articles talking about this issue, well here is how I understand this and what I have done to get past this issue.

When you check privileges of your server(the same applies to a particular database), you will see two columns ‘user name’ and ‘host’ this two must match at the same time for the user from that host to gain access.

So if you get: host ‘localhost’ is not allowed to connect… it has same reason as any remote host not being able to connect. As why ip address works and not the name, it is all because of DNS resolving, so it is not the issue that I am talking here, if DNS is all working (most of time it is), I can not see any reason why one is working and the other is not.

Ok, if the remote ip or dns name is not in the list, the only thing you need to do is to grant the user and ip the access of your server, through following mysql command:

mysql> GRANT ALL on *.* TO root@’dnsnameoripaddress’ IDENTIFIED BY ‘password’

Note that mysql entities are case-sensitive.

Even there is ‘root’ user from local pc, this remote ‘root’ is actually a different new user, and some have suggested using ‘UPDATE user’ I guess that will limit the current user from the changed ip address rather than creating a new user.

Tags:

This entry was posted on Wednesday, October 27th, 2010 at 1:54 am and is filed under PHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

*