How to Reset a MySQL Root Password

MySQL is a free, open-source, and widely used database management system. It is very popular for web application software. MySQL has a root password that allows a user to perform all database-related tasks.

If you are a system administrator responsible for managing a MySQL server, then you may often need to set or reset the MySQL root password – for example, if you forgot the MySQL root password or want to change the password for security reasons.

In this tutorial, we will show how to set, change, or reset the MySQL root password in Ubuntu 18.04.

Step 1 – Set the MySQL Password for the First Time

When you install the MySQL server for the first time, a MySQL root password is not set. You can connect to the MySQL shell without providing a password, but this is not recommended for security reasons.

To set the MySQL root password, connect to the MySQL shell with the following command:

mysql

Once connected, change the database to mysql using the following command:

use mysql;

Next, set the MySQL root password with the following command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secure-
password';

Next, flush the privileges and exit from the MySQL shell with the following command:

FLUSH PRIVILEGES;
EXIT;

Next, verify the MySQL by logging in with the root user:

mysql -u root -p

You will be prompted to enter the newly configured password to connect to the MySQL shell.

Step 2 – Change the MySQL Root Password

The easiest way to change the MySQL root password is with mysql_secure_installation script.

mysql_secure_installation

You will be asked to provide your current MySQL root password as shown below:

Securing the MySQL server deployment.
Enter password for user root:

Provide your MySQL root password and hit Enter. You should see the following output:

Select “No”

You will then be prompted to change the root password. Select YES.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y

Type Y and hit Enter to proceed. You will be asked to provide new password as shown below:

New password:
Re-enter new password:

Provide your new password, hit Enter, and complete the remaining steps as shown below:

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

You can also change the MySQL root password with the following command:

mysqladmin -u root -p password your-new-password

You will be asked to provide your current root password to change the password.

Step 3 – Recover Forgotten MySQL Root Password

If you forgot the MySQL root password, then you will need to recover it.

To recover the MySQL root password, first stop the MySQL service using the following command:

systemctl stop mysql

Next, create a mysqld directory and give proper permissions with the following command:

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

Next, start the MySQL service with the –skip-grant-tables:

mysqld_safe --skip-grant-tables &

This will allow you to connect the MySQL server without a password.

Next, log in to MySQL with the root user as shown below:

mysql -u root

After login, change the database to mysql as shown below:

use mysql;

Next, reset your root password with the following command:

UPDATE mysql.user SET authentication_string = PASSWORD('new-password') WHERE User = 
'root' AND Host = 'localhost';

Next, reload the privileges and exit from the MySQL shell with the following command:

FLUSH PRIVILEGES;
EXIT;

Next, stop the MySQL service with the following command:

mysqladmin -u root -p shutdown

You will be asked to provide your new password to stop the MySQL service.

Next, start the MySQL service again with the following command:

systemctl start mysql

Next, log in to MySQL with your new password as shown below:

mysql -u root -pnew-password

Once logged in, you should see the following output:

Conclusion

In the above guide, you learned how to set, update, and recover the MySQL root password on an Ubuntu 18.04 server. This guide should work with Ubuntu 18.04, Debian 10 and CentOS 8. If you are ready to try resetting your MySQL password, get started on dedicated server hosting from Atlantic.Net.