I've accidentally locked out root on MySQL

Question

I was playing around with MySQL on OS X and I removed all the 'root' users using DROP USER. I then added some of them back and did GRANT ALL on *.* to 'root'@'localhost';, and then logged out after verifying that yes indeed, I could log in and do a few privileged operations.

Unfortunately, one of the privileged operations I did not try was GRANT ALL on *.*. It turns out that you don't get the privilege to grant privileges with the GRANT ALL command. So now I'm stuck. I can't give anybody any privileges to do anything else.

At this point I don't actually have any data in MySQL. I could just wipe the install and do it over. But I would like to fix this without resorting to that extreme. How do I do that?

Answer 1

Put the following in a file:

GRANT ALL ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

The restart mysqld with the --init-file option specifying that file.