#1130 - Host 'localhost' is not allowed to connect to this MySQL server - After running an Acunetix scan

Question

I know this type of question has been asked lots of times, but none answer my problem. Please read carefully.

I was running my website on localhost using Wamp server normally. When today i decided to run an Acunetix scan for vulnerabilities on my localhost server.

Acunetix sent tons of commands to the mysql table in short period of time ( since it's localhost the commands went fast ) which cause my mysql server to crash with the error:

#1130 - Host 'localhost' is not allowed to connect to this MySQL server 

What I've already tried:

Running mysql through mysqld --skip-grant-tables I had access to mysql while on that, so I tried running

DROP USER 'root'@'127.0.0.1'; GRANT ALL PRIVILEGES ON . TO 'root'@'%';

But I got the error:

mysql> DROP USER 'root'@'127.0.0.1'; GRANT ALL PRIVILEGES ON . TO 'root'@'%';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables opt
ion so it cannot execute this statement
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'TO 'r
oot'@'%'' at line 1

I admit I do am a mysql noob but I did my homeworks and searched google, but was unable to find the solution.

Any help ?

I managed ti fix the issue by reinstalling wamp server and fully uninstalling, even with the mysql.

Oversweet

Answer 1

When your server is running with --skip-grant-tables, you disable all MySQL security so you're not allowed GRANT commands. What you can do, is access the "mysql" database and directly SELECT and UPDATE the rows you need to change. In your case it will be the user table.

Allocution

Hey, thanks for your answer. Since Phpmyadmin is not working, can you please give me the query for that ? What should I update ? – Oversweet

If I understand correctly your need, you should try this: use mysql; update user set Host='%' where User='root' and Host='127.0.0.1'; flush privileges; but I'm not sure whether the "flush privileges" will work; if not, just restart your MySQL server after the "update" statement. – Allocution

Maybe it would be easier to recreate your privilege tables, see dev.mysql.com/doc/refman/5.5/en/mysql-install-db-problems.html – Allocution

Well I managed to fix it by reinstalling wampserver. Thanks a lot mate. – Oversweet

@MarcDelisle, If access to grant is not allowed, how could access to the underlying mysql tables be allowed? Isn't that totally oxymoronic? – Bigmouth

@Bigmouth From dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html : "Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges." – Allocution

@MarcDelisle, Yes I mean what's the point of disabling the grant command since the underlying access is allowed? – Bigmouth

@Bigmouth A good question for the MySQL team. Maybe they want to make you realize that you are running without any privilege system active. – Allocution

@MarcDelisle, If that's the case you would get a warning instead of an access deny. – Bigmouth

@Bigmouth well you might want to tell that to the MySQL team. – Allocution

Answer 2

A simple Illustrated solution!

Footworn

this started happening after updating windows OS. And, followed above steps to resolve. – Sandstone

Why does this work? – Bihar

@Bihar this option is never recommended in production, but it is useful locally, it can also be used to reset root password. What it actually does is that it disables the access control and users from other network can connect, which means it is insecure. – Footworn

Answer 3

I had the same issue.

You can add skip-grant-tables to the my.ini file for the [mysqld] tag, under # The MySQL server, and restart the mysql server.

You can now open phpmyadmin and go to the user table in the mysql database. Make sure that the value of the password attribute is empty and that the value of host attribute is localhost. I was facing the second error. PHPMyAdmin was trying to connect with host value as 'localhost' and the table contained the value 127.0.0.1.

Remove skip-grant-tables from my.ini and restart the mysql server. This should work.

Orvalorvan

Answer 4

     GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';

http://dev.mysql.com/doc/refman/5.1/en/adding-users.html

EDIT.

this because your mysql server is on read_only option. to turn it off

connect to the server with root user:

mysql-h localhost-u root-p

and run the command:

mysql> set GLOBAL read_only = false;

mysql> FLUSH TABLES WITH READ LOCK;
mysql> UNLOCK TABLES;

EDIT2;

you must have stop the server and run this

mysqld_safe --skip-grant-tables due to root pwd chg

so stop the server and start it normal with a start

Merino

I also can't use that. I got the error:ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables opt ion so it cannot execute this statement – Oversweet

Thanks for your quick replies. But unfortunately It's still not working. This is what I done:pastebin.com/aqFjcrzX – Oversweet

C:\wamp\bin\mysql\mysql5.5.24\bin>mysqld_safe 'mysqld_safe' is not recognized as an internal or external command, operable program or batch file. – Oversweet

use this mysqld --skip-grant-tables instead – Merino

be sure to stop mysql before running this code , and then start it again – Merino

That's what I've been using the whole time. I only have access to the mysql database when my server is running with mysqld --skip-grant-tables. I did stopped the mysql. – Oversweet

Thanks mate for helping me. I managed to fix it by reinstalling WampServer. – Oversweet

Most of answers are without reading the question. If you can't login in mysql then where to execute query? In my friend's computer? – Usk

Answer 5

1. Click on config show in image
2. Click my.ini show in image
3. add skip-grant-tables after [mysqld] and save show in image. (*edited spelling issue here)
4. Now restart mySQL. that's all

Masbate

Answer 6

I solved the issue by reinstalling the server. The new configuration (my.ini) does not contain the --skip-grant-tables option and I am able to connect to the server as expected.

Don't forget to backup your databases before you do the new installation.

Trypanosomiasis