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.
Note to self, don’t do bone-head mistakes like leave empty passwords. Here is a good article that explains what is happening.
Sharing is caring!