Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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;
share|improve this answer
    
but i am already have a password to the root user its in my connection string instead of xxxx, and i did GRANT all privileges –  Maged E William Mar 27 at 15:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.