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

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

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

then

FLUSH PRIVILEGES;

done!

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

If you're trying đồ sộ connect with mysql -h -u -p and it returns this message with the IP address, then the MySQL server isn't able đồ sộ vì thế a reverse lookup on the client. This is critical because thats how it maps the MySQL client đồ sộ the grants.

Make sure you can vì thế 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 đồ sộ the line that begins with the bind-address directive. It should look lượt thích this:

    bind-address            = 0.0.0.0

Login đồ sộ 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 đồ sộ connect đồ sộ the database remotely with the following command.

    sudo ufw allow from remote_IP_address đồ sộ 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 đồ sộ login đồ sộ 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 badges111 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