How do i allow/prevent remote connection to MySQL?
Posted by qnaguru on December 4, 2008
If you are not able to connect to MySQL(typically the error message will be ‘Access Denied’) from a remote host, you need to look into these:
Have you granted permission to connect from remote host ?
mysql> grant all on *.* to ‘<mysqluser>’@'<remoteHostName>’;
The above grants <mysqluser> to connect from <remoteHostName> to any database(*.*);
What does the above grant SQL actually do ?
It adds/updates the ‘user’ table in default mysql database (although you always see 0 rows affected upon running the grant sql!).
You can see this here (login as root):
mysql> use mysql;
mysql> select host, user, password from user;
In case this still does not work for you, the problem is most likely that you do not have the correct ‘host’, ‘user’ values sitting in that ‘user’ table.
try giving <remoteIPAddress> in place of <remoteHostName> while running the grant SQL.
Other things to look at:
See the following files:
/etc/hosts, /etc/hosts.allow, /etc/hosts.deny – check if the remote IP/Host has been explicitly denied access.
Check if IPTables has a rule to block remote host:
# iptables -L
