CODEWITHSUNDEEP
mysql
concept47
concept47

Reputation: 31726

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.

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.

Upvotes: 898

Views: 2662840

Answers (24)

countzero
countzero

Reputation: 51

THE REAL PROBLEM IS FINDING THE WSL2 IP ADDRESS TO USE AS HOST!

This is not '127.0.0.1'

To find the WSL2 IP address to reference as host see the following article.

Accessing network applications with WSL

This shows there is a command you can run from WSL2 shell to find it:

ip route show | grep -i default | awk '{ print $3}'

This will give you something like

172.30.96.1

After you install the MySql client:

sudo apt-get install mysql-client

You can connect with something like:

mysql -u username -h 172.30.96.1 -p

The -p flag requests password prompt.

This host IP will now work from your code, e.g. npm mysql2

Upvotes: 0

Amy C
Amy C

Reputation: 21

I solved this issue with my connection by using my public IP instead of my private IP - I created a new user but I thought my private IP was what MySQL wanted. No, it wanted the public IP! You can tell by looking at the error log: sudo tail -f /var/log/mysql/error.log - it'll tell you what IP address it wants. From there, I simply dropped the new user I created and recreated it using the public IP instead. After that, I was able to connect. Good luck!

Upvotes: 0

Yannick Motton
Yannick Motton

Reputation: 35971

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.

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”.

Upvotes: 1095

cryptic_hash
cryptic_hash

Reputation: 109

You need to allow users from other locations(x.x.x.x in your case) as well from where you are going to connect to the database.

All the above answers do seem correct in the wildcard(%) declaration for allowing hosts from all locations but that opens it to all hosts and hence opening a security risk. Its better to explicitly specify the host as follows:

CREATE USER 'username'@'x.x.x.x' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'x.x.x.x' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Replace x.x.x.x with the host ip that you are connecting from.

Upvotes: 3

Will
Will

Reputation: 11490

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'

Upvotes: 140

minhas23
minhas23

Reputation: 9671

Just perform the following steps:

  1. Connect to MySQL (via localhost)

    mysql -uroot -p

    • If the MySQL server is running in Kubernetes (K8s) and being accessed via a NodePort

      kubectl exec -it [pod-name] -- /bin/bash
mysql -uroot -p

  1. Create user

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

  2. Grant permissions

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

  3. Flush privileges

    FLUSH PRIVILEGES;

Upvotes: 102

Ali Tofigh
Ali Tofigh

Reputation: 141

1. From a terminal, connect you to your MySQL running container

docker exec -it your_container_name_or_id bash

2. In your container, connect you to the MySQL database

mysql -u your_user -p

enter your password to connect to database.

3. execute this SQL script to list all existing database users:

SELECT host, user FROM mysql.user;

The result will be some thing like below:

host user
127.0.0.1 root
::1 root
localhost mysql.sys
localhost root

you should add a new row:

host user
% root 
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

Upvotes: 1

BabaNew
BabaNew

Reputation: 976

If you are using MySQL WorkBench, you can achieve this easily:

  1. From the menu, select Server -> Users And Privileges enter image description here

  2. On the lower left, click on "Add account" enter image description here

  3. Fill the form with username, host matching (% means every host) and the password enter image description here

  4. Click on "Apply" on the lower right enter image description here

After this you are good to go. Then, if you want to refine your configuration, you can use the "Administrative Roles" tab to set the command that can be used by the user (SELECT, ALTER etc etc) and the "Schema privileges" tab to restrict the user interaction to specific schemas.

Upvotes: 8

user3437729
user3437729

Reputation: 29

I was also facing same issue, It resolved 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.

Upvotes: 2

soufiane ELAMMARI
soufiane ELAMMARI

Reputation: 1029

This working for any future remote mysql connection !

    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Navigate to the line that begins with the bind-address directive. It should look like this:

    bind-address            = 0.0.0.0

Login to your mysql as root terminal

    mysql -u root -p
    -- root password

    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;

    EXIT;

finally Grant that machine exclusive permission to connect to the database remotely with the following command.

    sudo ufw allow from remote_IP_address to any port 3306

Upvotes: 29

Adrian
Adrian

Reputation: 2291

If you have WAMP Server + Windows 10 and you are using it for development than Right Click on Wamp Icon => Wamp Settings => Check Allow Virtual Hosts other than 127* enter image description here

Upvotes: 0

Pratap Sharma
Pratap Sharma

Reputation: 2743

Well, nothing of the above answer worked for me. After a lot of research, I found a solution. Though I may be late this may help others in future.

Login to your SQL server from a terminal

 mysql -u root -p
 -- root password
GRANT ALL ON *.* to root@'XX.XXX.XXX.XX' IDENTIFIED BY 'password';

This should solve the permission issue.

Before solving error

After solving issue

Happy coding!!

Upvotes: 4

Abd Abughazaleh
Abd Abughazaleh

Reputation: 5495

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

this error because no password to the root , and this Maybe occurred with you when you trying to connect from outside .

Upvotes: 0

devugur
devugur

Reputation: 1499

Simple way: 

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

then

FLUSH PRIVILEGES;

done!

Upvotes: 33

Eran Levi
Eran Levi

Reputation: 909

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! enter image description here

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

I'm hope it was helpful to you too.

Upvotes: 2

Alex R
Alex R

Reputation: 11881

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.

Upvotes: 2

Erica Kane
Erica Kane

Reputation: 3352

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.)

Upvotes: 5

BabaNew
BabaNew

Reputation: 976

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

Click on User manager: enter image description here

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)

enter image description here

Upvotes: 7

Jan
Jan

Reputation: 329

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".

Upvotes: 1

user889030
user889030

Reputation: 4754

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;

Upvotes: 3

Aditya P Bhatt
Aditya P Bhatt

Reputation: 22071

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

Upvotes: 390

HimalayanCoder
HimalayanCoder

Reputation: 9850

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

Select Any Host for the desired username

enter image description here

Upvotes: 85

Dennis McLaughlin
Dennis McLaughlin

Reputation: 301

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.

Upvotes: 30

Pascal Thivent
Pascal Thivent

Reputation: 570285

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.

Upvotes: 18

Related Questions