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

access-deniedMySQLmysql-error-1130permissions

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

Best Answer

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