In MySQL, the root user’s password is set to blank by default, and it’ll let you login even if you type anything in the field. However, if you’ve set a password to log in as root in MySQL and somehow forgot (we all did that at some point), there’s no way for you to work because the root is the ultimate user.
This writeup is primarily for users troubled with a forgotten root password in MySQL and would include the following.
Let’s get into the prerequisites section.
Prerequisites: Check MySQL Version
MySQL has been in rounds for decades; a set of commands supports each version and checking its version should be your top priority.
$ mysql -V
In our case, 8.0.31 is the version of MySQL, as seen above.
How to Reset MySQL Root Password?
In MySQL, the password reset process is quite tricky, and the following steps are carried out.
Step 1: Stop MySQL
The first step to changing the root password involves stopping the MySQL server, which is done using this command.
$ sudo systemctl stop mysql
Step 2: Starting MySQL Server by Skipping the Grant Tables
Again, start MySQL Service using this command to begin resetting the root password.
$ sudo mysqld_safe --skip-grant-tables &
In the above-executed command, the “–skip-grant-tables” is used to login into MySQL Shell with all the privileges granted without using any password. While the ampersand (&) means the command will run in the background, which allows you to get back to the terminal.
Step 3: Login as the root user
Following the process mentioned above and using the following command, users can log in to MySQL using this command.
$ sudo mysql -u root
As you can see in the above image, the terminal didn’t prompt you to enter the password; however, if you don’t have sudo permissions, you will need to enter the user password. Now we’ll change the password for the root user using MySQL Shell.
Step 4: Set the Password
Setting a password for the root user in MySQL is a must because, without it, your databases which are full of data, could be on the brink of leaking to do that, this command is used.
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('asd');
See the error in the image above. This is why we asked to check the version because the above command can only work on versions 5.7.5 or older. Users with newer versions are required to use this command.
> update user set authentication_string='' where User='root';
The above command removes any password and sets it to default which is blank.
However, as seen below, you can also use it to set a password for the root user.
> update user set authentication_string='' where User='root';
For some users, the above command may not work, so we’d recommend using this command.
> SET PASSWORD = 'New-Password-To-Set';
Step 5: Restart MySQL Service
Now that you’ve set a new password for the root user, restart the MySQL Service to avoid complications.
$ sudo systemctl restart mysql
Step 6: Verify the New Password
It is time to verify whether the new password is working; use this command to log in as root with the new password.
$ sudo mysql -u root -p
As seen in the image above, we first tried to log in to MySQL as root with an incorrect password which didn’t let us, but when we used the correct password, then it worked.
Conclusion
Forgetting the password for the root user may take you into a dimension of MySQL where you would feel powerless because if you haven’t set any other super-user, you’d be in trouble. The detailed explanation in this article will let you reset the root password in MySQL on Linux.
