Can’t access MySQL because there are no records in mysql.user? This is how you fix that.

Posted on by

I don’t know if it is a problem with the OSX distro of MySQL 5.1.54, but when I installed it recently I found I couldn’t login at all. Nor could I set or reset the root password. The underlying problem was that there were no records in the users table…yeah, exactly, WTF?

I found this out because I eventually got to the end of the MySQL reference for resetting the root password where it tells you to perform a lobotomy on your mysqld and start it up using the –skip-grant-tables option. Nothing else had worked, and when I finally did then get in and SELECT * FROM mysql.user, I got no results, nadda.

The reference manual for resetting permissions starts by telling you to update the mysql.user table and set the password for the root user – but that doesn’t work if you’ve got no users in there. What to do? Well, you need to get all down and dirty and bypass CREATE USER and GRANT PRIVILEGES and simply INSERT a new user and their privileges into the mysql.user table. Simple really, and also described in the reference for adding users.

481 thoughts on “Can’t access MySQL because there are no records in mysql.user? This is how you fix that.”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>