How to reset MySQL 5.7 root password in Ubuntu 16.04

Did you forgot the root password of MySQL in your server? Did you reinstalled MySQL in your server, typed wrong the password and now you're unable to access your MySQL server? Whatever the reasons is, is pretty important to know how to reset the root password in your MySQL server in case that something like the mentioned cases happen.

In this tutorial we'll show how to reset the root password of your MySQL server in Ubuntu 16.04.

1. Stop active MySQL service

As first step, you need to stop any running instance of MySQL that is active:

sudo service mysql stop

This will allow you to start the mysql service in safe mode.

2. Start MySQL in safe mode

If you are sure that there's not any MySQL process already running in the background, you can start the safe version of MySQL with mysqld_safe. However, it will be executed with the skip-grant-tables argument:

mysqld_safe --skip-grant-tables &

This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, if the server is started with the --skip-grant-tables option, it enables --skip-networking automatically to prevent remote connections.

3. Access as root user

Now that mysql is running without any security verification locally, you need to login as root with the following command in the MySQL CLI:

mysql -u root mysql

4. Change password

Now that you have access to the mysql CLI, you will be able to run queries without a problem, including the one that changes the password of Root. As first, change of database to the mysql one with the following query:

use mysql;

Then, in MySQL 5.7 you can change the password of an user with the following query, as you can see, the user in this case will be root:

update user set authentication_string=password('YOUR-NEW-PASSWORD') where user='root';

Warning: in case that you face the following exception after running the previous query:

You can solve this error by running the following query first:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOUR-NEW-PASSWORD';

Then, run the update query again and it will work again (more information about this issue here). And that's it! You will be able now to login to mysql as root with the new password in the next step.

5. Stop safe MySQL and start normal service

Stop the safe version of MySQL with the following command:

sudo service mysql stop

And start the normal service again with:

service mysql start

Once the service starts correctly, access to the MySQL CLI with the following command:

mysql -u root -p

This will prompt for the password that you set previously to the root user and that's it!

Happy coding !