Mysql – Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user

mysql

MySQL 5.1.31 running on Windows XP.

From the local MySQL server (192.168.233.142) I can connect as root as follows:

>mysql --host=192.168.233.142 --user=root --password=redacted

From a remote machine (192.168.233.163), I can see that the mysql port is open:

# telnet 192.168.233.142 3306
Trying 192.168.233.142...
Connected to 192.168.233.142 (192.168.233.142).

But when trying to connect to mysql from the remote machine, I receive:

# mysql --host=192.168.233.142 --user=root --password=redacted
ERROR 1045 (28000): Access denied for user 'root'@'192.168.233.163' (using password: YES)

I have only 2 entries in mysql.user:

Host         User     Password
--------------------------------------
localhost    root     *blahblahblah
%            root     [same as above]

What more do I need to do to enable remote access?

EDIT

As suggested by Paulo below, I tried replacing the mysql.user entry for % with an IP specific entry, so my user table now looks like this:

Host             User     Password
------------------------------------------
localhost        root     *blahblahblah
192.168.233.163  root     [same as above]

I then restarted the machine, but the problem persists.

Best Solution

You have to put this as root:

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD' with grant option;

;

where IP is the IP you want to allow access, USERNAME is the user you use to connect, and PASSWORD is the relevant password.

If you want to allow access from any IP just put % instead of your IP

and then you only have to put

FLUSH PRIVILEGES;

Or restart mysql server and that's it.

Related Question