remote connection to mysql

Question

have two machine PC#1 with db mysql and appache and PC#2 with a c# form try to connect to this db

here is my connection string used in PC#2: SERVER=My_public_ip;PORT=3306;DATABASE = my_test; UID = root; PASSWORD = xxxx;"

when i try to connect locally from PC#1 it work good:

SERVER=localhost;DATABASE = my_test; UID = root; PASSWORD = xxxx;"

i did make my root user remote enabled like that:

`GRANT ALL ON *.* to '%'@'%' WITH GRANT OPTION;`

and all i get is an Event viewer: unable to connect to any of the specified MySQL host

now i think it has nothing to do with firewall aftr turn them off on both pc and i still get the same error message, but i can't figure it out

Answer 1

You need to create the root user with a password

SET sql_log_bin = 0;
GRANT ALL ON *.* to root@'%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;

I would make it a little more secure. Perhaps by doing the following:

• use a username other than root (like remote_user)
• instead of %, user the netblock of the IP of PC2 (such as 10.20.30.%)

Therefore, you run this grant command on PC1 instead

SET sql_log_bin = 0;
GRANT ALL ON *.* to remote_user@'10.20.30.%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;

Note : SET sql_log_bin = 0; prevents recording the GRANT into the binary logs.

Give it a Try !!!

In your question, you said you ran this

GRANT ALL ON *.* to '%'@'%' WITH GRANT OPTION;

Wildcard characters are not allowed for the user. In fact, the MySQL Documentation on GRANT says

MySQL does not support wildcards in user names. To refer to an anonymous user, specify an account with an empty user name with the GRANT statement:

Your grant should say

GRANT ALL ON *.* to root@'%' WITH GRANT OPTION;

or

GRANT ALL ON *.* to remote_user@'10.20.30.%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;