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 lớn have a user with this kind of access open lớn 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 lớn insert a new entry with Host='%' and User='some_user', thinking that this allows you lớn 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 phàn nàn '%', it is used in preference lớn the new entry when connecting from localhost! The correct procedure is lớn insert a second entry with Host='localhost' and User='some_user', or lớn delete the entry with Host='localhost' and User=''. After deleting the entry, remember lớn issue a FLUSH PRIVILEGES statement lớn reload the grant tables. See also Section 5.4.4, “Access Control, Stage 1: Connection Verification”.

One has lớn 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 lớn connect lớn this MySQL server' even though I was using root. Here's how lớn 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 lớn open lớn 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 lớn change permissions on database 'dataentry'. Remember lớn change the IP Address, Port, and database name lớn your settings)

   mysql -u root -p
   Enter password: 
   mysql>GRANT ALL ON *.* lớn 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 lớn remote connect lớn 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 lớn 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 lớn grant access lớn 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 *.* lớn 'USER_NAME'@'%' Identified By 'YOUR_PASSWORD'; 

then

FLUSH PRIVILEGES;

done!

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

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

Make sure you can tự 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 lớn the line that begins with the bind-address directive. It should look lượt thích this:

    bind-address            = 0.0.0.0

Login lớn 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 lớn connect lớn the database remotely with the following command.

    sudo ufw allow from remote_IP_address lớn 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 lớn login lớn phpmyadmin with root trương mục , there goto mysql database and select user table , there edit root trương mục 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 trăng tròn, 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