Reset or Change MySQL root user Password on Ubuntu Linux

MySQL

Today, in this article we will discuss How you can change / Reset / Set / Update MySQL root user’s password on Ubuntu Linux Server. Sometimes while playing with root user’s privileges to allow its access remotely may change its password. Or if you have forgotten the root user’s password. Then you can not access the MySQL DB without the password. In that case, you need to change/reset it’s MySQL password. Please follow these steps to reset/change the MySQL root user password.

Stop The MySQL Server


Start The MySQLd Configuration And Skip The Grant Table

This will start a new MySQL Server with no-password and it will ignore the Grant table rules. Now, This Temporary MySQL Server will have full control.

Login To MySQL As ‘root’ User

Update MySQL ‘root’ User Password

Now, this is the command that will update the password of root user. You need the change the NewPassword string with your desired password string.

Flush & Reload The Privileges

Now, after running the update command you need to tell MySQL Server to reload the grant tables so that account-management statements work with new privileges.

Note: on some versions, if password column doesn’t exist, you may need to try:
UPDATE user SET authentication_string=password(‘NewPassword’) WHERE user=’root’;

Exit From MySQL Client Window

Now, after flushing & reloading the privileges table. Run the exit command to exit from MySQL Client window.

Kill Temporary MySQL Server

Now, after updating the MySQL root user’s password you need to kill the temporary MySQL Server that you created in 2 step (Without Password).

Start MySQL Server

Now, at last, you need to start MySQL server with password & all Grant rules.

Note: This method is not regarded as the most secure way of resetting the password, however, it works.

Reference: https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

Leave a Reply