Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

This should be dead simple, but I *cannot* get it to work for the life of me.
I'm just trying to connect remotely to my MySQL server.

- Connecting as:
```sh
mysql -u root -h localhost -p
```
- works fine, but trying:
```sh
mysql -u root -h 'any ip address here' -p
```
- fails with the error:

> ERROR 1130 (00000): Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

In the `mysql.user` table, there is exactly the same entry for user 'root' with host 'localhost' as another with host '%'.

I'm at my wits' end and have no idea how to proceed.
Any ideas are welcome.

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute
a `FLUSH PRIVILEGES` statement to tell the server to reload the grant tables.

PS: I wouldn't recommend to allow **any** host to connect for **any** user (especially not the `root` use). If you are using mysql for a client/server application, prefer a subnet address. If you are using mysql with a web server or application server, use specific IPs.

Possibly a security precaution. You could try adding a new administrator account:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
-> WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
-> WITH GRANT OPTION;

Although as Pascal and others have noted it's not a great idea to have a user with this kind of access open to any IP. If you need an administrative user, use root, and leave it on localhost. For any other action specify exactly the privileges you need and limit the accessibility of the user as Pascal has suggest below.

Edit:

From the MySQL FAQ:

> If you cannot figure out why you get
> Access denied, remove from the user
> table all entries that have Host
> values containing wildcards (entries
> that contain '%' or '_' characters). A
> very common error is to insert a new
> entry with Host='%' and
> User='some_user', thinking that this
> allows you to specify localhost to
> connect from the same machine. The
> reason that this does not work is that
> the default privileges include an
> entry with Host='localhost' and
> User=''. Because that entry has a Host
> value 'localhost' that is more
> specific than '%', it is used in
> preference to the new entry when
> connecting from localhost! The correct
> procedure is to insert a second entry
> with Host='localhost' and
> User='some_user', or to delete the
> entry with Host='localhost' and
> User=''. After deleting the entry,
> remember to issue a FLUSH PRIVILEGES
> statement to reload the grant tables.
> See also Section 5.4.4, “Access
> Control, Stage 1: Connection
> Verification”.

The message `*Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MySQL server` is a reply from the MySQL server to the MySQL client. Notice how its returning the IP address and not the hostname.

If you're trying to connect with `mysql -h<hostname> -u<somebody> -p` and it returns this message with the IP address, then the MySQL server isn't able to do a reverse lookup on the client. This is critical because thats how it maps the MySQL client to the grants.

Make sure you can do an `nslookup <mysqlclient>` FROM the MySQL server. If that doesn't work, then there's no entry in the DNS server. Alternatively, you can put an entry in the MySQL server's HOSTS file (`<ipaddress> <fullyqualifiedhostname> <hostname>` <- The order here might matter).

An entry in my server's host file allowing a reverse lookup of the MySQL client solved this very problem.

You need to grant access to the user from any hostname.

This is how you add new privilege from phpmyadmin

Goto Privileges > Add a new User

![enter image description here][1]

Select **Any Host** for the desired username

![enter image description here][2]


[1]:

[2]:

Well what you can do is just open mysql.cfg file and you have to change Bind-address to this

**bind-address = 127.0.0.1**

and then Restart mysql and you will able to connect that server to this.

Look this you can have idea form that.


[this is real sol][1]


[1]:

[To see links please register here]

One has to create a `new MySQL User` and assign privileges as below in `Query prompt` via phpMyAdmin or command prompt:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Once done with all four queries, it should connect with `username / password`

if you are trying to execute mysql query withouth defining connectionstring, you will get this error.

Probably you forgat to define connection string before execution. have you check this out?
(sorry for bad english)

All of the answers here didn't work in my case so I guest this may help other users in the future. This can also be a problem in our code, not just in MySQL alone.

If you are using `VB.NET`

Instead of this code:

Dim server As String = My.Settings.DB_Server
Dim username As String = My.Settings.DB_Username
Dim password As String = My.Settings.DB_Password
Dim database As String = My.Settings.DB_Database

MysqlConn.ConnectionString = "server=" & server & ";" _
& "user id=" & username & ";" _
& "password=" & password & ";" _
& "database=" & database

MysqlConn = New MySqlConnection()


You need to move `MysqlConn = New MySqlConnection()` on the first line. So it would be like this


MysqlConn = New MySqlConnection()

Dim server As String = My.Settings.DB_Server
Dim username As String = My.Settings.DB_Username
Dim password As String = My.Settings.DB_Password
Dim database As String = My.Settings.DB_Database

MysqlConn.ConnectionString = "server=" & server & ";" _
& "user id=" & username & ";" _
& "password=" & password & ";" _
& "database=" & database

simple way is to login to phpmyadmin with root account , there goto mysql database and select user table , there edit root account and in host field add % wild card . and then through ssh flush privileges

FLUSH PRIVILEGES;