How to Reset the MySQL Root Password

Introduction

Before we begin talking about how to reset MySQL root password, let’s briefly understand - What is MySQL ?

MySQL is a fully-managed database service for web applications. MySQL root password refers to the login credential required to login to the administrative super-user account which allows you to perform all the top-level functions in the database.

If you won't set the root password you will be able to connect directly with the database which is not a good practice. Connecting to the database without any password is a security threat as it will expose your database to malicious users.

In this tutorial, you will reset the MySQL root password. We will also address a few FAQs on how to reset the MySQL root password.

Advantages of MySQL

1. Scalability: MySQL easily handles growing data volumes and user queries, making it suitable for large-scale applications.
2. High Performance: Its optimized architecture and indexing techniques ensure excellent speed and responsiveness for complex queries.
3. Flexibility: MySQL supports various data types, replication, and clustering, offering flexible options for building robust and scalable database solutions.
4. Open Source: MySQL's open-source nature allows for community contributions, ensuring rapid development, frequent updates, and extensive community support.
5. Reliability: With a proven track record, MySQL ensures data integrity, transactional support, and high availability, ideal for mission-critical applications.

Step 1 - Identify the Server version

You would be running commands based on the MySQL/MariaDB server version running on your system.

You can use the following command to check the version

mysql --version

You will get the following output:

Output

mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

For MariaDB:

Output

mysql  Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Make sure you have made a mental note of the server version.

Resetting MySQL or MariaDB Root Password

Once you have identified the server version, you can proceed to reset the password for MySQL/MariaDB:

Step 1 - Stop the MySQL/MariaDB Service

In order to change the root password, you need to stop the MySQL/MariaDB service with the help of the given command:

sudo systemctl stop mysql

Step 2 - Start the MySQL/MariaDB server without loading the grant tables

After that, use the following command to start the server without loading the grant tables:

sudo mysqld_safe --skip-grant-tables &

The ampersand & at the end will enable the command to run in the background. You can hence, continue to use the shell.

Anyone can connect to the database with all privileges granted without a password, with the help of the --skip-grant-tables option.

Step 3 - Login to the MySQL Shell

You can now connect to the database as a root user.

mysql -u root

Step 4 - Set a New Root Password

In case your system is running MySQL 5.7.6 and later or MariaDB 10.1.20 and later, run the following command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;

Modify the user table directly if the ALTER USER statement doesn't work.

UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

In case your system is running MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier, run the following command:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSH PRIVILEGES;

The Output will resemble the one given below in both cases:

Output

Query OK, 0 rows affected (0.00 sec)

Step 5 - Stop and Start the Database Server Normally

Once the root password has been set up, you need to stop the server and restart it normally.

mysqladmin -u root -p shutdown

You will then be prompted to enter the new root password.

For MySQL, use the following command to start the server normally:

sudo systemctl start mysql

For MariaDB use the below-mentioned command:

sudo systemctl start mariadb

Step 6 - Verify the Password

Verify whether the new password has been applied correctly, use the following command:

mysql -u root -p

You shall then be prompted to enter the new root password. Once you enter the password, you'll be logged in to the database server.

FAQs to Reset the MySQL Root Password

Conclusion

We hope this detailed guide helped you understand how to reset the MySQL root password. To learn more about MySQL, check out the official MySQL site. 

If you have any queries, please leave a comment below and we’ll be happy to respond to them for sure.