How do i allow/prevent remote connection to MySQL?

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: