Setting Root Password on MySql when it’s Empty

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.