How to Reset the MySQL Root Password in Ubuntu

1. Reset the Forgotten MySQL root Password in Ubuntu
2. Fix Error in Setting a MySQL root Password With mysql_secure_installation

This tutorial considers two scenarios where we need to reset the MySQL root password in Ubuntu. First, when we forget the root password and want to reset it; second, when we cannot set a root password with mysql_secure_installation.

The instructions given in this tutorial have been tested using the MySQL Version 8.0.29 and Ubuntu 20.04.4. Let’s see the solution for both scenarios below.

Reset the Forgotten MySQL root Password in Ubuntu

Keeping a strong password for a database is right, but remembering it can be difficult. What if you forget the MySQL root password?

Do not panic! Many of us face this issue, but there is a way to update it.

1. Check your MySQL version by using the following command

   $ mysql --version
   

2. Shut down the MySQL server.

   In this way, we can easily restart it in safe mode to reset the root password. You will have to enter the system’s root password, which is Ubuntu in our case.

3. To start the MySQL server without table grants, we use the following command to update the systemd configuration file to pass extra command line parameters to the MySQL server upon startup.

   sudo systemctl edit mysql
   

   As soon as you press Enter, it will open a file for you using the nano editor. We will use it to edit the service overrides of MySQL.

In this way, we can update MySQL’s default service parameters.

Add the following to the empty file that is just opened in the nano editor.

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --skip-grant-tables --skip-networking

Once you paste the content given above in the file, press Ctrl+X to exit the file, Y to save the updates that we just made, and Enter to confirm the file’s name.

Here, ExecStart clears all the default values first and then provides the systemd with a new startup command which includes different parameters. These parameters disable loading the networking and grant tables.

4. To apply the updates, execute the following command to reload the systemd configuration.

   sudo systemctl daemon-reload
   

5. Start the MySQL server using the following command; it will not produce any output, but you can check the MySQL server’s status.

   sudo systemctl start mysql
   

   This time networking and grant tables are not enabled.

6. We connect to the database as a root user.

sudo mysql -u root

7. Now, we can change the password, but before that, use the following command to tell the database server to reload the grant tables.

   mysql> FLUSH PRIVILEGES;
   

8. Execute the statement below to reset the MySQL root user’s password. Do not forget to replace the my_new_password with a strong password that you can easily remember.

   ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'my_new_password';
   

   Here, MySQL lets us use custom authentication. So, the statement given above ensures that MySQL will now use its default authentication to authenticate the MySQL root user via a new password.

   Bravo! the MySQL root user’s password is now changed. Type exit to quit the MySQL shell.

9. Revert the MySQL server to normal settings by executing the following command.

   sudo systemctl revert mysql
   

   We will see similar to the following output, which means the above statement has been executed successfully.

Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.

10. To apply the changes, reload the systemd configuration.

    sudo systemctl daemon-reload
    

11. Restart the MySQL Server as follows.

    sudo systemctl restart mysql
    

12. Now, execute the following statement to log in as MySQL root user with the new password.

    mysql -u root -p
    

    You will be asked the enter the MySQL root user’s password. Enter the new password and enjoy access to your database server.

Fix Error in Setting a MySQL root Password With mysql_secure_installation

Users who install the MySQL server for the first time may have met the following error that is generated while setting a MySQL root password with mysql_secure_installation.

Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

Don’t worry! Here is the solution for that.

1. Kill the process of mysql_secure_installation or close the terminal.

2. Use the following statement to log in to the MySQL server.

   sudo mysql
   

   You will be prompted to enter the system’s root password. Once you enter the password, you will be taken to the MySQL console.

3. Execute the following ALTER statement. Don’t forget to replace my_new_password with your password.

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'my_new_password';
   

   Further, type exit to quit the MySQL shell.

4. Use the statement below to log in with the new password.

   mysql -u root -p
   

Finally, you can log in to the MySQL server as a root user.