Host 'xxx.xx.xxx.xxx' is not allowed to co

Question

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

mysql -u root -h localhost -p  

works fine, but trying

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.

Answer 1

I was also facing the same issue. I resolved it in 2 min for me I just white list ip through cpanel

Suppose you are trying to connect database of server B from server A. Go to Server B Cpanel->Remote MySQL-> enter Server A IP Address and That's it.

Answer 2

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.

Answer 3

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

Answer 4

This working for DirectAdmin;

1. Go to your DirectAdmin.
2. Go to your MySQL Management.
3. Select your database.
4. Under your Accesse Host tab, there is a field. You should fill this field by xxx.xx.xxx.xx.
5. Click on Add Host.

Finished. Now you can access to this DB by your your_database_username & your_database_password.
So Simple!

Answer 5

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

Answer 6

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

Select Any Host for the desired username