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

Question

I accidentally changed the "host" value for the root user inside of the user table for mysql.

I need to change it back to localhost because I changed it to arancillary2125 (my other machines DNS name) but I cannot access mysql (from PHPMYADMIN or command prompt) due to "localhost" not having permissions any more.

I am running windows XP // apache //

I have tried the following solutions but they didn't work:

https://serverfault.com/questions/92870/1130-host-localhost-is-not-allowed-to-connect-to-this-mysql-server

Solution 1

Whoops.

This is on your local machine, I assume? At Start->Run, enter 'services.msc' and locate the MySQL service. Stop the service.

Edit:

Find your my.ini file (usually in C:\Windows or C:\mysql, etc). If you don't have one, create one. It's just a plain ASCII file.

Alter the file, add this in the [mysqld] section: skip-grant-tables.

On the command line, issue the command net start MySQL and wait a moment, the MySQL service should start.

Still on the command line, issue the command mysql -u root and hit "ENTER". You should be logged into MySQL as the 'root' user. Carefully change your grants and then logout. Stop the MySQL service / server. Re-edit your my.ini file and remove / comment out the skip-grant-tables line, and again start the server. Try to login as root again.

Just tested this on my Win XP Pro box.

This essentially has the effect of bypassing all the grant tables and thus doesn't bother to lookup whether you're supposed to access it from that machine or not.

MySQL reference manual : --skip-grant-tables option

Solution 2

So I had this problem aswell, happened all of a sudden with #1130 - Host ‘localhost’ is not allowed to connect to this MySQL server.

After searching and searching I can tell you that bdl solution is the way forward, once you can log back into the server you can change the permissions about and remove the line skip-grant-tables under your my.ini mysql config file.

I would have just upvoted bdl's post but im too new on the website to do that yet.

Solution 3

Recreate arancillary2125 (if even on an old desk top) and then access your DB that way.

Comments

[Raph]

When i try this, I open up the prompt, put in my password and it closes right after I do so.

[Mitulát báti]

I got this when I try to connect to the mysql with mysql -u root in cmd: ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server. I tried a lot of articles on the internet but still got this problem. I reinstalled every mysql thing but this message I got everywhere... :(((