ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server

Question

Why oh why can I not connect to mysql?

mysql -u root -ptest101 -h xxx.xxx.xxx.xxx
ERROR 1130 (HY000): Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server

In my.cnf I have the below

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0

I also ran the below...

'UPDATE mysql.user SET Password = PASSWORD('test101') WHERE User = 'root';
FLUSH PRIVILEGES;

I can access on the host machine using mysql -u root -ptest101 but not using mysql -u root -ptest101 -h xxx.xxx.xxx.xxx

Wow...why is this happening? I am n ubuntj 12.04

mysql> SELECT host FROM mysql.user WHERE User = 'root';
+---------------------------------------------+
| host                                        |
+---------------------------------------------+
| %                                           |
| 127.0.0.1                                   |
| ::1                                         | |
| localhost                                   |
+---------------------------------------------+
5 rows in set (0.00 sec)

Remember that mysql accounts aren't just usernames. they're `user@host` pairs, where either of the pair can be a wildcard. You might be using the right username, but did you grant the right host privileges as well? — Marc B, Sep 30 '13 at 18:47

score 413 · Accepted Answer · edited May 24 '16 at 17:28

413

Your root account, and this statement applies to any account, may only have been added with localhost access (which is recommended).

You can check this with:

SELECT host FROM mysql.user WHERE User = 'root';

If you only see results with localhost and 127.0.0.1, you cannot connect from an external source. If you see other IP addresses, but not the one you're connecting from - that's also an indication.

You will need to add the IP address of each system that you want to grant access to, and then grant privileges:

CREATE USER 'root'@'ip_address' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip_address';

If you see %, well then, there's another problem altogether as that is "any remote source". If however you do want any/all systems to connect via root, use the % wildcard to grant access:

CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

Finally, reload the permissions, and you should be able to have remote access:

FLUSH PRIVILEGES;

edited May 24 '16 at 17:28

Eric Wanchic

2,046
1
23
26

answered Sep 30 '13 at 18:37

newfurniturey

37,556
9
94
102

I did what was suggested. See that outuo above. I still get same error. I cant connect. – Tampa Sep 30 '13 at 19:16
5

This post worked http://bucktownbell.com/?p=653 – Tampa Oct 04 '13 at 07:26
3

That sql statement "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'" need a ";" after it.... – bronze man Nov 11 '13 at 08:26
2

and now it just complains that `Access denied for user` – user3338098 Nov 04 '15 at 17:34
6

and after this you should do `SET PASSWORD FOR 'root'@'%' = PASSWORD('MyNewPass');` mysql docs only say to update `'root'@'localhost'` to "change the root password" which deceives people into believing they have changed the root password when they haven't. – user3338098 Nov 04 '15 at 18:22
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; Is wrong, replace the % with * – JazzCat Jan 09 '16 at 17:12
1

@JazzCat `%` is correct as documented at http://dev.mysql.com/doc/refman/5.7/en/request-access.html – user3338098 Jul 08 '16 at 15:55
If you get access denied for user look here.. http://stackoverflow.com/a/16004129/4301119 – Balraj Singh Bains Feb 05 '17 at 11:52
This comment worked. I also had the problem where my root user was not having rights to connect from any host or my specific host. Thanks. – IndikaM Feb 10 '17 at 23:55
This worked for mysql 8 as well – Shobi Aug 21 '19 at 16:27
Change the bind address to 0.0.0.0 and followed by CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; worked like magic. I'm running two ec2 instances incase if anyone runs into trouble with that and remember to run with public IP. You can use nmap to confirm ports are exposed to outside world, just to be on the safer side. – Anand MP May 29 '20 at 06:09
Why is this much hard? Do I need to add hosts for all users one by one? – mehdi mohammadi Apr 19 '21 at 13:32
this works for me using Docker-Compose with mysql:8.0.32 & phpmyadmin:latest with this command `CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';`. Thank you so much!! – Triet Nguyen Apr 07 '23 at 11:24

score 29 · Answer 2 · edited Oct 03 '17 at 15:48

29

Following two steps worked perfectly fine for me:

Comment out the bind address from the file /etc/mysql/my.cnf:

#bind-address = 127.0.0.1
Run following query in phpMyAdmin:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; FLUSH PRIVILEGES;

edited Oct 03 '17 at 15:48

Tim S. Van Haren

8,861
2
30
34

answered Oct 11 '14 at 09:56

Ravi Garg

542
4
5

9

+ `sudo service mysql restart` – sromku Nov 05 '15 at 08:07
3

Removing the bind will make the server open on ALL IP's... this is a security issue! – Alex Frenkel Nov 09 '18 at 17:31
Works for me. Thanks man. – Syafiqur__ Sep 01 '20 at 06:38
@AlexFrenkel But dont we have password authentication via privileges? How can you otherwise access it from a dynamic ip for debugging purpose. I can change it later for production – Joel G Mathew Jul 21 '21 at 22:32

score 22 · Answer 3 · edited Apr 28 '16 at 08:14

22

$mysql -u root --host=127.0.0.1 -p

mysql>use mysql

mysql>GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'redhat@123';

mysql>FLUSH PRIVILEGES;

mysql> SELECT host FROM mysql.user WHERE User = 'root';

edited Apr 28 '16 at 08:14

dur

15,689
25
79
125

answered Apr 28 '16 at 06:26

siripurapu venkatarao

351
2
5

sudo service mysql restart need to run after this thanks – Adnan Zaheer Feb 07 '21 at 11:58

score 2 · Answer 4 · answered Aug 05 '15 at 12:48

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'passWord'; Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON . TO 'name'@'%'; Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)

mysql>

Make sure you have your name and % the right way round
Makes sure you have added your port 3306 to any firewall you may be running (although this will give a different error message)

hope this helps someone...

score -1 · Answer 5 · answered Sep 16 '16 at 06:09

-1

For those who are able to access cpanel, there is a simpler way getting around it.

log in cpanel => "Remote MySQL" under DATABASES section:
Add the IPs / hostname which you are accessing from
done!!!

answered Sep 16 '16 at 06:09

peterpan1215

17
2

cpanel is a third party gui tool. – Joel G Mathew Jul 21 '21 at 22:32

score -7 · Answer 6 · answered Jul 22 '14 at 11:03

-7

Go to PhpMyAdmin, click on desired database, go to Privilages tab and create new user "remote", and give him all privilages and in host field set "Any host" option(%).

answered Jul 22 '14 at 11:03

TomoMiha

1,218
1
14
12

score -13 · Answer 7 · edited Sep 19 '15 at 06:16

-13

there an easy way to fix this error

just replace the files on the folder : C:\xampp\mysql\data\mysql

with the files on : C:\xampp\mysql\backup\mysql

edited Sep 19 '15 at 06:16

Sruit A.Suk

7,073
7
61
71

answered Sep 18 '15 at 16:55

salim333

9

ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server

7 Answers7

Linked

Related