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

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 tướng have a user with this kind of access open to tướng 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 tướng insert a new entry with Host='%' and User='some_user', thinking that this allows you to tướng specify localhost to connect from the same machine. The reason that this does not work is that the mặc định privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than vãn '%', it is used in preference to tướng the new entry when connecting from localhost! The correct procedure is to tướng insert a second entry with Host='localhost' and User='some_user', or to tướng delete the entry with Host='localhost' and User=''. After deleting the entry, remember to tướng issue a FLUSH PRIVILEGES statement to tướng reload the grant tables. See also Section 5.4.4, “Access Control, Stage 1: Connection Verification”.

One has to tướng 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 tướng connect to tướng this MySQL server' even though I was using root. Here's how to tướng make sure that root has the correct permissions.

My setup:

• Ubuntu 14.04 LTS
• MySQL v5.5.37

Solution

1. Open up the tệp tin under etc/mysql/my.cnf

2. Check for:

   • port (by mặc định this is port = 3306)
   • bind-address (by mặc định this is bind-address = 127.0.0.1; if you want to tướng open to tướng 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 tướng change permissions on database 'dataentry'. Remember to tướng change the IP Address, Port, and database name to tướng your settings)

   mysql -u root -p
   Enter password: 
   mysql>GRANT ALL ON *.* to tướng 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 tướng remote connect to tướng your database. For example, I'm using MySQL Workbench and putting in 'Hostname:10.1.1.7', 'Port:3306', 'Username:root'

Just perform the following steps:

1. Connect to tướng 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
     

2. Create user

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

3. Grant permissions

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

4. Flush privileges

   FLUSH PRIVILEGES;
   

You need to tướng grant access to tướng 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

Simple way:

Grant All Privileges ON *.* to tướng 'USER_NAME'@'%' Identified By 'YOUR_PASSWORD'; 

then

FLUSH PRIVILEGES;

done!

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

If you're trying to tướng connect with mysql -h -u -p and it returns this message with the IP address, then the MySQL server isn't able to tướng bởi a reverse lookup on the client. This is critical because thats how it maps the MySQL client to tướng the grants.

Make sure you can bởi an nslookup 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 tệp tin ( <- The order here might matter).

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

This working for any future remote mysql connection !

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

Navigate to tướng the line that begins with the bind-address directive. It should look lượt thích this:

    bind-address            = 0.0.0.0

Login to tướng 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 tướng connect to tướng the database remotely with the following command.

    sudo ufw allow from remote_IP_address to tướng any port 3306

answered Oct 13, 2009 at 12:56

Pascal ThiventPascal Thivent

570k140 gold badges1.1k silver badges1.1k bronze badges

answered Mar 23, 2021 at 17:32

BabaNewBabaNew

9761 gold badge15 silver badges31 bronze badges

answered Sep 27, 2017 at 13:09

BabaNewBabaNew

9761 gold badge15 silver badges31 bronze badges

answered Oct 12, 2017 at 21:49

Erica KaneErica Kane

3,35229 silver badges38 bronze badges

answered Nov 21, 2019 at 18:29

Pratap SharmaPratap Sharma

2,7432 gold badges22 silver badges36 bronze badges

simple way is to tướng login to tướng phpmyadmin with root tài khoản , there goto mysql database and select user table , there edit root tài khoản and in host field add % wild thẻ . and then through ssh flush privileges

 FLUSH PRIVILEGES;

answered Oct 18, 2023 at 8:00

answered Nov 22, 2017 at 5:54

Alex RAlex R

11.8k18 gold badges112 silver badges198 bronze badges

answered May 19, 2018 at 6:35

Eran LeviEran Levi

9072 gold badges14 silver badges31 bronze badges

answered Dec 4, năm trước at 22:37

answered Jun 22, năm 2016 at 14:18

JanJan

3294 silver badges12 bronze badges

answered Jul 21, 2022 at 13:28

answered Mar đôi mươi, 2019 at 21:20

Abd AbughazalehAbd Abughazaleh

5,4695 gold badges56 silver badges59 bronze badges

answered May 8, 2020 at 9:59

AdrianAdrian

2,2915 gold badges42 silver badges79 bronze badges