Xtykutl

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

My error message was similar and said 'Host XXX is not allowed to connect to this MySQL server' even though I was using root. Here's how to make sure that root has the correct permissions.

My setup:

• Ubuntu 14.04 LTS


• MySQL v5.5.37

Solution

1. Open up the file under 'etc/mysql/my.cnf'


2. 
   

   Check for:

   
   
   
   
   
   • port (by default this is 'port = 3306')
   
   
   • bind-address (by default this is 'bind-address = 127.0.0.1'; if you want to open to all then just comment out this line. For my example, I'll say the actual server is on 10.1.1.7)
   
   
   
   


3. 
   

   Now access the MySQL Database on your actual server (say your remote address is 123.123.123.123 at port 3306 as user 'root' and I want to change permissions on database 'dataentry'. Remember to change the IP Address, Port, and database name to your settings)

   
   
   

   mysql -u root -p
   
   Enter password: <enter password>
   
   mysql>GRANT ALL ON *.* to root@'123.123.123.123' IDENTIFIED BY 'put-your-password';
   
   mysql>FLUSH PRIVILEGES;
   
   mysql>exit
   
   

   
   



4. sudo service mysqld restart



5. You should now be able to remote connect to your database. For example, I'm using MySQL Workbench and putting in 'Hostname:10.1.1.7', 'Port:3306', 'Username:root'

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

Just perform the following steps:

1) Connect to mysql

mysql -uroot -p

2) Create user

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

3) Grant permissions

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

4) Flush priviledges

FLUSH PRIVILEGES;

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.

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.

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;

Just use the interface provided by MySql's GUI Tool (SQLyog):

Click on User manager:

Now, if you want to grant access FOR ANY OTHER REMOTE PC, just make sure that, just like in the underneath picture, the Host field value is % (which is the wildcard)

If this is a recent mysql install, then before changing anything else, try simply to execute this command and then try again:

flush privileges;

This alone fixes the issue for me on Ubuntu 16.04, mysql 5.7.20. YMMV.

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

If you happen to be running on Windows; A simple solution is to run the MySQL server instance configuration wizard. It is in your MYSQL group in the start menu. On the second from last screen click the box that says "allow root access from remote machines".

Most of the answers here show you creating users with two host values: one for localhost, and one for %.

Please note that except for a built-in localhost user like root, you don't need to do this. If you simply want to make a new user that can log in from anywhere, you can use

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';

GRANT <whatever privileges are appropriate> ON <relevant tables> TO myuser;

and it will work just fine. (As others have mentioned, it's a terrible idea to grant administrative privileges to a user from any domain.)

Just find a better way to do that from your hosting control panel (I'm using DirectAdmin here)

simply go to the target server DB in your control panel, in my case:
MySQL management -> select your DB -> you will find: "Access Hosts", simply add your remote host here and its working now!

I guess there is a similar option on other C.panels like plesk, etc..

I'm hope it was helpful to you too.

On the off chance that someone facing this issue is experiencing it from within SQLyog, this happened:

I had connected to the remote database (from within SQLyog) and worked for some hours. Afterwards I left the system for some minutes, then came back to continue my work - ERROR 1130 ! Nothing I tried worked; Restarting SQLyog didn't fix it. Then I restarted the system - it still didn't work.

So I tried connecting from the terminal - it worked. Then retried it on SQLyog ... and it worked. I can't explain it other than 'random computer quirkiness', but I think it might help someone.

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.

Problem: root@localhost is unable to connect to a fresh installation of mysql-community-server on openSUSE 42.2-1.150.x86_64.
Mysql refuses connections - period.

Solution:

$ ls -l /var/lib/mysql/mysql/user.*

-rw-rw---- 1 mysql mysql     0 Apr 29 19:44 /var/lib/mysql/mysql/user.MYD

-rw-rw---- 1 mysql mysql  1024 Apr 29 19:44 /var/lib/mysql/mysql/user.MYI

-rw-rw---- 1 mysql mysql 10684 Apr 29 19:44 /var/lib/mysql/mysql/user.frm

File user.MYD has 0 size (really ?!).
I copied all 3 files from another working system.

$ /usr/sbin/rcmysql stop

$ cd /var/lib/mysql/mysql/

$ scp root@othersytem:/var/lib/mysql/mysql/user.* ./

$ /usr/sbin/rcmysql start

$ cd -

$ mysql -u root -p

I was able to log in. Then, it was just a matter of re-applying all schema privileges.
Also, if you disabled IPv6, re-enable it temporary so that root@::1 account can also work.

This answer might help someone...

All these answers didnt help, then I realised I forgot to check one crucial thing.. The port :)

I have mysql running in a docker container running on a different port. I was pointing to my host machine on port 3306, which I have a mysql server running on. My container exposes the server on port 33060. So all this time, i was looking at the wrong server! doh!

Simple way:

Grant All Privileges ON *.* to 'USER_NAME'@'%' Identified By 'YOUR_PASSWORD'; 

then

FLUSH PRIVILEGES;

done!

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