How to Reset MySQL Root Password
MySQL is an open-source, Oracle-backed relational database management system that uses Structured Query Language. It can be run on several platforms, including Linux, Windows, and Unix. As an open-source software, MySQL is free to use, and its source code can be modified to suit your needs.
Managing MySQL from the command line can be a complicated task for beginners. MySQL has a default admin or root password that allows you to perform several tasks. However, you may need to reset or change the MySQL root user password for security reasons or if you have forgotten the password.
This guide provides step-by-step instructions on how to reset or set the MySQL root password on Linux.
Change the MySQL Root Password
If you already know your MySQL root password and want to change it for security reasons, the simplest and easiest way to do so is by using the mysql_secure_installation script.
To begin, open the command-line terminal and run the script as shown below:
mysql_secure_installation
You will be prompted to provide your current MySQL root password, as shown below:
Securing the MySQL server deployment. Enter password for user root:
Enter your MySQL root password and press Enter. You will then be asked if you want 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 then be prompted 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 then be prompted 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 then be asked to set a new password as shown below:
New password: Re-enter new password:
Provide your new password and press Enter. You will then be asked to confirm the 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 then be asked if you want 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 then be asked if you want to disallow remote root login 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 then be asked if you want 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. Finally, you will be asked if you want to reload the privilege tables 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 logging into the MySQL shell:
mysql -u root -p
You will be prompted to provide the MySQL root password.
You can use the mysqladmin command-line utility to change the MySQL root password. Use the following command:
mysqladmin -u root -p password your-new-password
You will need to provide your current root password to change the password.
Set the MySQL Password for the First Time
By default, the MySQL root password is not set when you install the MySQL server on a fresh server. You can log in to MySQL without providing any password, which 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 log in to the MySQL shell with the following command:
mysql
After connecting to the MySQL shell, switch to the mysql database with 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';
Then, flush the privileges and exit from the MySQL shell with the following commands:
mysql> FLUSH PRIVILEGES;
mysql> EXIT;
Now, log in to MySQL with the 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 logged in, you should see 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, you may forget the MySQL root password and need to recover it to access the MySQL shell.
Before resetting the MySQL root password, you need to stop the MySQL service.
You can stop the MySQL service easily with the following command:
systemctl stop mysql
Next, you need to disable the database from loading the grant tables and skip networking to prevent other clients from connecting.
First, create a mysqld directory and give it proper permissions 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 using the following command:
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';
Then, exit from the MySQL shell with the following command:
mysql> EXIT;
Next, stop the MySQL service with the following command:
killall mysqld
Then, start the MySQL service again with the following command:
systemctl start mysql
Finally, log in to MySQL with your new password using the following command:
mysql -u root -p
You will be prompted to enter your new password.
Enter password:
Once logged in, 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 methods. I hope this helps you reset your MySQL root password.