]> MySQL Connection failure. 🌐:aligrant.com

MySQL Connection failure.

Alastair Grant | Wednesday 21 September 2005

I run a MYSQL daemon on my PC at work, for tinkering and general messing about with stuff. Although today when I tried to logon I couldn't. Producing this error:

ERROR 1130 HY000 Host 'localhost' is not allowed to connect to this MySQL server

Which was great fun. It didn't matter how many different combinations of username, hostnames and passwords I tried. No luck.

A quick search on Google resulted in numerous people with the problem, but no fix. So I had to figure it out the hard way. Luckily it's not that hard. This is what you need to do.

Stop mysqld and restart it with the switch '--skip-grant-tables'. This will mean the server will bypass authentication when clients connect. You will now find that you can logon to MySQL in the normal way.

I found if I did a select on mysql.user, nothing was returned. Which was the cause of the problem. Alas the GRANT option doesn't work when the server is started without grant tables.

What you need to do is manually insert a user record. This script will insert a user record for "root" with the password of "password" (make sure you change it after).

INSERT INTO user VALUES ('%', 'root', PASSWORD('password'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','',DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

You then need to shutdown the MySQL daemon, but the password will have taken affect, so quit MySQL and run this command:

mysqladmin -u root -p shutdown

Enter "password" when prompted.

Hey presto, you now have root back in place and you can set about using GRANT again after starting up MySQL in the normal fashion.

Breaking from the voyeuristic norms of the Internet, any comments can be made in private by contacting me.