$%@!#& mysql root user has no permissions error 1044

Sometimes you just can't seem to win...well at least not for 3 hours or so...

Somehow my mysql root user lost all of it's permissions. I think it might be because I moved the database files over from another harddrive that I upgraded to a larger one at somepoint and then did a re-install of mysql and then just over wrote the data directory and started mysql backup without actually setting up the new mysql instance. All the version numbers and paths were the same so I figured it would be a go. Apparently not. Here is the error I kept getting no matter what I tried:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'

I tried every database and kept getting

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'blah'

DAMN!

anyway i found this stupid trick in the mysql user forums (note the date of the post!):

Posted by [name withheld] on July 30 2003 11:58pm [Delete] [Edit]
when you are simply trying to:
C:\mysql\bin>mysql -uroot -p mysql

and you get:

ERROR 1044: Access denied for user: '@127.0.0.1' to database 'mysql'

Here is what I do. The key is to supply your real ip address for the -h (host) parameter. On windows, from the command prompt type 'ipconfig' to see your ip address. Once you have that, do the following:

C:\mysql\bin>mysql -h 192.168.0.1 -u root -p mysql
Enter password: ****************

// then I explicitly add root@127.0.0.1 to the user table, so after this I can log in as you would expect

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'root-password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO root@127.0.0.1 IDENTIFIED BY 'root-password' WITH GRANT OPTION;

Why would mysql care if you are connecting from an externaly available ip address on your own computer I don't know. All i do know is that it wouldn't let me grant the root user permissions via the loopback address or via sockets.

This one was a doozy!