How to Reset the MySQL Root Password: Step-by-Step Guide

Accessing databases is key task for server management and web application maintenance.

MySQL is one of the most common and powerful database management systems (DBMS). As a database administrator, you manage critical information, and the MySQL root password plays a crucial role in ensuring the security and integrity of this data.

You may occasionally lose or forget the MySQL root password. This can happen for various reasons, from unforeseen circumstances to team changes or simple human error. In such cases, knowing how to reset the root password and regain access to the database is essential.

In this article, we will present methods and tools to help you reset the MySQL root password and restore full control over your database. We will cover various approaches, from using official MySQL tools like mysqladmin and mysqld_safe to editing configuration files and making system changes.

We will also discuss important precautions to help you secure your database and prevent the root password from being lost in the future. These include using strong passwords, limiting database access to only necessary users, and regularly backing up data to minimize risks.

So, if you need to reset the MySQL root password, below are detailed instructions and recommendations to help you regain access and resume managing your MySQL database.

MySQL Password Reset Methods

MySQL does not use PAM, and the SSH password will not work. The root password is set during the database server installation at the stage of forming the privilege tables.

If you receive the error ERROR 1045: Access denied for user 'root'@'localhost' instead of a greeting after an attempt to log in, you need to reset the root password.

To reset the root password in MySQL 8, you need to follow a simple procedure. This method is equally suitable for MySQL and MariaDB, as they are fully compatible and have the same commands.

In Ubuntu, you can reset the MySQL root password using the same methods as in Debian. A slight difference may occur for CentOS 7, which we will note in the corresponding section below.

How to Reset the MySQL Root Password Using skip-grant-tables

This is a fairly common method due to its simplicity. Here is a step-by-step plan for this operation:

Step 1: Stop the Database Service

service mysqld stop

Step 2: Start the Service Without Privilege Tables

mysqld --skip-grant-tables&

The ampersand at the end allows you to run the service in the background, so it does not occupy a separate terminal window.

Step 3: Connect to the Server

Since the privilege tables are not loaded, there is no need to enter a password:

mysql -u root

Step 4: Load the Privilege Tables

Each command to the database must end with a semicolon. If the query is not completed, MySQL will wait for the continuation of the command. You can simply add a semicolon if it was missed in the new line.

FLUSH PRIVILEGES;

The response should be: Query OK, 0 rows affected.

Step 5: Set a New Root Password

To set a new root password in MySQL, for Ubuntu and Debian, use:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';

If this query does not work, try:

UPDATE `mysql`.`user` SET `password`=password('PASSWORD') WHERE `user`='root';

Or:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('PASSWORD');

On CentOS 7, you can reset the MySQL root password with the command:

UPDATE `mysql`.`user` SET `authentication_string`=password('PASSWORD') WHERE `user`='root';

You will see the response: Query OK, 0 rows affected if it is successfully executed. 

The password can even be an empty value, but in this case, you will lose the ability to connect to the server by any other means except the command line. The password changes immediately after executing the command. You can check it, for example, by connecting through phpMyAdmin if no empty value was set.

Step 6: Exit MySQL

quit;

The response should be Bye, and you will return to the regular terminal.

Step 7: Start the Database Server in Normal Mode

service mysqld start

How to Reset the MySQL Root Password on Windows

Resetting the MySQL superuser password on Windows is similar to the procedure for resetting the root password for Linux systems. Some differences may only be in the process of finding and starting the database service itself. 

Here is a step-by-step guide for resetting the database superuser password for Windows OS.

Step 1: Open Command Prompt as Administrator

There is a difference depending on which database is installed. To check, run:

For MySQL:

sc qc "mysql"

For MariaDB:

sc qc "mariadb"

If the database is not installed, you will see the message that the service does not exist. Otherwise, you will see the basic information about MySQL or MariaDB.

Step 2: Navigate to the Database Service Executable

If the %PATH% variable is not set, go to the folder with the database service executable file. You can find the pathin the sc qc command output in the "BINARY_PATH_NAME". In our case, it is C:\localhost\mariadb\bin\.

cd C:\localhost\mariadb\bin\

Step 3: Stop the Running Service

For MySQL:

net stop mysql

For MariaDB:

net stop mariadb

Step 4: Open a New Command Prompt Window

Use the start command. Unlike Linux, in Windows, the ampersand at the end of the command does not work, and when the service starts, it will not allow further commands until the session ends (CTRL+C).

Step 5: Start the Server Without Privileges

You can use the same command for MySQL and MariaDB.

mysqld --skip-grant-tables

Step 6: In the Second Window, Enter:

mysql -u root -p

Enter the blank password, and you will see the standard greeting. Now you need to enter commands for the database server.

Step 7: Execute the Initial Command

FLUSH PRIVILEGES;

Without this initial command, you may receive an error when changing the password: "ERROR 1290: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement."

Step 8: Execute the Command to Change the Password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';

Note: If the command does not work, try the options from Step 5 of the instructions for Linux systems.

Close the command prompt window with the temporary database version running or press CTRL+C.

Step 9: Start the System Service

For MySQL:

net start mysql

For MariaDB:

net start mariadb

Conclusion

Resetting the MySQL root password is an important skill for database administrators. In this article, we reviewed various methods and tools for resetting the root password and regaining full control over your database.

In conclusion, here are some key points regarding database security:

• Use complex passwords that combine letters, numbers, and special characters, and change them regularly.

• Limit database access to only essential users to minimize the risk of unauthorized access.

• Regularly update the MySQL system to eliminate known vulnerabilities and receive the latest security patches.

• Regularly back up the database. This is your insurance in case of data loss or system failure. Develop a backup strategy, determine the frequency and methods for creating backups, and check their recoverability to ensure data restoration capability.

• Remember the importance of training and self-development in database management. Stay informed about the latest trends, new features, and MySQL tools. Use educational resources, attend seminars and conferences, and communicate with experienced professionals in this field. Constantly updating your knowledge and skills will help you manage the database effectively and solve emerging problems.

Resetting the MySQL root password is a critical task that requires attention and knowledge. However, with the studied methods and recommendations, you can handle this task and regain complete control over your MySQL database. Remember to ensure security, create backups, and continue learning, and your work with MySQL will be successful and secure.