How to Reset MySQL Root Password

MySQL is an open-source Oracle-backed relational database management system based on Structured Query Language. It is open-source which means you can use MySQL without paying a dime. It also allows you to change its source code to suit your needs. It can be run on several platforms including, Linux, Windows and Unix.

Working with MySQL and managing it from the command-line is very complicated task for any beginner user. By default, MySQL has an admin or root password that allows you to perform several tasks. In some cases, you may need to set or change the MySQL root user password. This can happen when you want to change the MySQL root password for security reasons or you've forgotten the password.

In this guide, we will explain how to reset or set the MySQL root password on Linux.

Table Of Contents

• Change the MySQL root Password
• Set the MySQL Password First Time
• Reset a Forgotten MySQL Root Password
• Conclusion

Change the MySQL root Password

If you already know your MySQL root password and want to change it for security reasons then the simple and easiest way to change it using the mysql_secure_installation script.

First, open the command-line terminal and run the script as shown below:

mysql_secure_installation

You will need 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 press Enter. You will be asked to change the root password as shown below:

VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component?

Type Y and press Enter to proceed. You will be asked to choose a password policy as shown below:

There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:

Type 0 for LOW and press Enter. You will be asked to change the password as shown below:

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

Type Y and press Enter. You will be asked to set new password as shown below:

New password: Re-enter new password:

Provide your new password and press Enter. You will be asked to continue with provided password as shown below:

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

Type Y and press Enter. You will be asked to remove anonymous users as shown below:

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y

Type Y and press Enter. You will be asked to disallow root login remotely as shown below:

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y

Type Y and press Enter. You will be asked to remove a test database as shown below:

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y

Type Y and press Enter. You will be asked to reload the privilege as shown below:

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

Type Y and press Enter to finish the process.

You can now verify your new MySQL password by login to MySQL shell:

mysql -u root -p

You will be asked to provide a MySQL root password.

You can also use the mysqladmin command-line utility to change the MySQL root password as shown below:

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

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

Set the MySQL Password First Time

By default, MySQL root password is not set when you install the MySQL server on the fresh server. You can login to MySQL without providing any password. So it is very dangerous for security reasons. It is always a good idea to set the MySQL root password after installing it.

First, open the command-line terminal and login to the MySQL shell with the following command:

mysql

After connected to the MySQL shell, switch the database to the mysql using the following command:

mysql> use mysql;

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

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

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

mysql> FLUSH PRIVILEGES; mysql> EXIT;

Now, login to MySQL with a new password using the following command:

mysql -u root -p

You will be prompted to enter your new password.

Enter password:

Provide your password and press Enter. Once login, you should get the following output:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.22-0ubuntu0.20.04.3 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Reset a Forgotten MySQL Root Password

In some situations when you forgot the MySQL root password then you may need to recover it to access the MySQL shell.

Before resetting the MySQL root password, you will need to stop the MySQL service.

You can stop the MySQL service easily with the following command:

systemctl stop mysql

Next, you will need to disable the database from loading the grant tables. You will also need to skip networking to prevent other clients from connecting.

First, create a mysqld directory and give proper permission with the following command:

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

You can start the MySQL service without loading the grant tables or enabling networking as shown below:

mysqld --skip-grant-tables --user=mysql &

You can now connect to your MySQL server without providing a root password:

mysql

Once connected to the MySQL shell, reload the privileges with the following command:

mysql> FLUSH PRIVILEGES;

Next, reset your MySQL root password using the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Your-Secure-P@ssw0rd';

Next, exit from the MySQL shell with the following command:

mysql> EXIT;

Next, stop the MySQL service with the following command:

killall mysqld

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

systemctl start mysql

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

mysql -u root -p

You will be asked to provide your new password as shown below:

Enter password:

Once login, you should see the following output:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Conclusion

In this guide, you learned how to set and reset the MySQL root password using different ways. I hope this will helps you to reset your MySQL root password.