Mysql – Problem remotely connecting to MySQL database

MySQLNetworkremote

Good afternoon everyone.

I have 3 servers on 2 different networks, all of which run MySQL.

I can use mysql -u<user> -p -h<ip-addr> from server 1 to server 2 and back without a problem.

When I try to use that command from server 3 (which is on a differnet network) to server 1 I get the following:-

$ mysql -u<user> -p -h<hostIP>
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '<hostIP>' (146)
$

I know the same command works from server 2 to server 1 with the same password. My initial thought was that there was a network block between server 3 and server 1 but if that were the case then server 3 would not get a password prompt.

I have also looked at the users table in server 1 and the user has the same password for local connections and network connections (which is why it works from server 2 to server 1).

Is there another port other than 3306 that is used as part of the connection after that password prompt? Or, failing that, what am I missing?

Many thanks,

David

Best Answer

There could be multiple reasons for this but to start troubleshooting as it looks like the issue with permissions/grants.

1.Please check and assign proper grants on server 1 :

GRANT ALL PRIVILEGES ON db.* TO 'username'@'server3_ip' IDENTIFIED BY 'Password';

flush privileges;

All is not compulsory in above query.

2.Double Check Bind parameter in in My.cnf .

bind-address 0.0.0.0

Hope this helps else we can debug further.

Related Question