13

I have a strange issue on a web server (Windows Server 2012) with MySQL 5.7.16. I can't connect anymore to mysql server, I don't know why.

If I type mysql -uroot -ppassword appear an error 

ERROR 1130 <HY000>: Host '::1' is not allowed to connect to this MySQL server or
ERROR 1130 <HY000>: Host '127.0.0.1' is not allowed to connect to this MySQL server

I tried to use another user with all privileges and I've seen that in host there is only localhost (not 127.0.0.1 or ::1)

How can I login with root@localhost and not with root@127.0.0.1? It's very frustrating... Every account trying to use @127.0.0.1 or @::1 but there exist only localhost in host and I can't change it.

If I type mysql -uroot -ppassword I see
ERROR 1130 <HY000>: Host '127.0.0.1' is not allowed to connect to this MySQL server

Same if I type mysql -uroot -ppassword -h localhost or anything else

FloT
  • 745
  • 1
  • 7
  • 17
9overflow
  • 668
  • 1
  • 6
  • 15

6 Answers6

38

Ok i Fixed...

I've comment "skip_name_resolve" in my.ini and everything is back to work.. i really don't know why because this record was in my.ini also yesterday..last week.. last month..

9overflow
  • 668
  • 1
  • 6
  • 15
  • Same happened with me, it was accessible before commenting the entry, can someone explain what is the reason ? – Atul Chavan Feb 05 '18 at 07:16
  • the user, you are using to connect to mysql, probably is something like 'user'@'domainname', or with a default installation user, 'root'@'localhost'. Since `skip_name_resolve` is enabled `localhost` will not be equal to `127.0.0.1` when there is no 'root'@'127.0.0.1' user added, your connection will be rejected. – Cemal Oct 11 '21 at 17:02
10

The variable skip_name_resolve gives better performance because the server does not try to resolve the names of the connecting clients or look for them every time in the host name cache (even localhost is resolved/searched), but the manual states that config also limits the @localhost connections. The solution is to copy the @localhost users with @127.0.0.1, like this:

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password';
CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password';
FLUSH PRIVILEGES;

where ::1 is localhost for IPv6 addressing. This way we keep the root and local accounts limited to the local server. Using '%' open the potential clients to the world, and we don't want that. Disabling skip_name_resolve also requires the server having an accesible and fast DNS resolver to minimize latency.

I noted that I can connect with a local phpmyadmin even if the user has @localhost; this is because phpmyadmin connects thru a local unix socket, a special type of file used to communicate between processes, and does not need networking.

EDIT: As @Francisco R noted, the new root users also should have full access to all databases by issuing the following commands:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1'
FLUSH PRIVILEGES
Fjor
  • 320
  • 4
  • 6
7

I had the same message after a fresh installation with the no-install zip and solved it as follows. Perhaps this could have been a solution for your problem too:

  1. Stop the MySQL server or service.
  2. Open a Command Prompt window with administrative rights and go to the bin folder in the MySQL install directory.
  3. Start MySQL with skip-grants-table and don't forget your config file:

mysqld --defaults-file=[filename] --skip-grant-tables

  1. Open another Command Prompt window and go to the bin folder again.
  2. Now you can login:

mysql -u root -p

  1. Show the users with:

SELECT user, host FROM mysql.user;

  1. Verify there is one 'root' with host 'localhost'.
  2. Change the host:

UPDATE mysql.user SET host='%' WHERE user='root';

  1. Exit the mysql program and close the Command Prompt window.
  2. Type Ctrl-C in the other Command Prompt window to stop the server, then close the Command Prompt Window.
  3. Start MySQL as you normally would and verify that you can login.
felippe
  • 493
  • 6
  • 7
Joep
  • 146
  • 2
  • 6
1

Make sure that when you created the user you have specified % as the hostname, otherwise the user will only be able to connect from the localhost.

Fabricio
  • 532
  • 1
  • 6
  • 21
1

I came here looking for a solution using Local by flywheel for wordpress development to the same problem, BUT, in a linux machine.

Just if someone faces the same problem, the solution listed here works.

Just comment skip_name_resolve in the file conf/mysql/my.cnf.hbs under the file tree created by Local

Thanks!

Ian Sebastian
  • 390
  • 2
  • 8
0

Looks that you need to modify your hosts file. C:\Windows\System32\Drivers\etc\hosts

just add the line and save it, (to be able to edit and save you may need to open it as administrator)

127.0.0.1 localhost

Lena Kaplan
  • 756
  • 4
  • 13
  • I tried but nothing is changed. I add 127.0.0.1 localhost and ::1 localhost but still this error – 9overflow Jul 02 '17 at 13:16
  • I'd like to login to mysql with user root@localhost and add 127.0.0.1 / ::1 to host, but i can't – 9overflow Jul 02 '17 at 13:19
  • Sorry, i've made a mistake, the error is: ERROR 1130 : Host '::1' is not allowed to connect to this MySQL server or ERROR 1130 : Host '127.0.0.1' is not allowed to connect to this MySQL server – 9overflow Jul 02 '17 at 13:27
  • Please add some explanation to your answer such that others can learn from it - why should this help to resolve the problem? – Nico Haase May 17 '21 at 17:30