I was working on one of my servers when I realized that I had done a bone-head mistake. I left the root password blank in MySQl.
It was not as easy as I thought to fix the problem. There were a couple of things I didn’t account for, specifically that when you’re initially setting up MySQL on Ubuntu and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.
This means that if you try to reset the password like this:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_pass';
It will fail. If you search your database you’ll see it’s empty.
mysql> select * from mysql.user;
Scroll until you find the following columns:
| plugin | authentication_string
| auth_socket |
What is happening is you need to set a new password while switching from the auth_socket plugin to the mysql_native_password plugin.
Do this by leveraging the following syntax in your SQL statement: WITH mysql_native_password
Your updated command will look like this:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_pass';
Query OK, 0 rows affected (0.00 sec)
Run the same table query against the mysql.user database and you’ll see that the plugin is now updated and password set.
Here is a good article explaining what is happening when you leave empty passwords.