In this article, we will talk about how to reset MySQL root password on Linux Debian, so stay tuned to find out everything you need to know.
Forgetting passwords is quite common and can happen to the best of us—even tech-savvy people who know their way around devices are not safe from this. If you forgot your root password, you can still gain access and reset your password. All you require is access to the server and sudo privileges.
This article shows how you can reset the password on the MySQL database.
Introduction to MySQL database
MySQL is one of the most widely used database servers with the following features:
- It is a free and open-source database written in C/ C++
- It provides cross-platform support.
- It is a relational database management system (RDBMS)
- It can be used as a component of Linux, Apache, MySQL, and PHP (LAMP) stack
- It has support for ANSI structured query language (SQL), store procedures, triggers, cursors, and transactions
How to Reset MySQL Root Password on Linux Debian 11.3
The root is the most privileged user in Linux-based systems. Losing a root password means you cannot perform high-privileged tasks that require a root password. A workaround can reset the root password without asking for the old password. In the steps below, we will discuss how we can change the root password of MySQL root.
Find the database version
The first step is to find the version of the MySQL database you are running. Run the following command on the terminal:
For this part, kindly take note of the database version.
Stop the database server
Then, stop the database server with the following command:
$sudo systemctl stop mysql
Restarting the database server without permission check
Now, we will restart the database server such that it starts without a permission check. This will provide access to the command line with root privileges without requiring a root password. To start the MySQL server without the grant tables, we will alter the systemd daemon configuration and pass some additional parameters to the server while starting.
To do this, you have to run the following command on the Terminal:
$sudo systemctl edit mysql
The above command will open the nano editor with a file opened to make changes to the default MySQL service start options. Add the following line to the file:
[Service] ExecStart= ExecStart=/usr/sbin/mysqld --skip grant-table --skip-networking
In the above lines, we will first clear the ExecStart variable, and then set the command with the parameters to skip grant tables and networking. By doing this, you will skip the loading of grant tables and networking capabilities.
Additionally, you can close the nano editor by pressing “Ctrl + X”. You will be asked to save the changes in the file. Press “Y” and then the “Enter” key to save the file.
Reload the systemd configuration and restart the MySQL server
Now, reload the systemd configuration to apply the changes we have made above. Do this by entering the following lines on the Terminal:
$sudo systemctl daemon-reload
Now, start the MySQL server with the following command:
$sudo systemctl start mysql
You might not see any output, but the MySQL server will be restarted.
Connect to MySQL server
With the previous steps, you can start the MySQL server without the grant tables and networking services enabled. Now, you can connect to the MySQL database with the following command:
$sudo mysql -u root
You will be provided the Shell prompt for the database.
Load grant table
Now that you have started and accessed the server, you can change the root password. Note that the database server is running right now with limited capabilities. You can connect without a password but can’t run any command that can alter the data. To reset the data, you must first load the grant tables.
You can pull this off by running the following command on the Terminal:
Change the MySQL root password
You can now change the root password. On the Terminal, type the following command to change it:
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password';
You have specified that MySQL will use its default authentication to authenticate the root user.
Note: Ensure the following things while providing a new password a) The password must be solid and secure such as using special characters b) The password must be kept in a safe place.
Exit MySQL prompt
Since your password has been changed, you may now, exit the MySQL prompt using the following command:
In the next step, we will restart the database in normal mode.
Reverting the database to normal settings
To start the database with normal settings, we will have to revert the earlier changes such that grant tables are loaded, and networking settings are enabled. Type the following command on the Terminal to do just that:
$sudo systemctl revert mysql
Then, apply the changes via reloading system configuration:
$sudo systemctl daemon-reload
You can now restart the service with the following command:
$sudo systemctl restart mysql
This will restart the database.
Check that the new password is working
You can now check if the new password is working by specifying the following command:
mysql -u root -p
When prompted for a new password, type the new password. You will be able to gain access to the database.
And that’s about it for this article. Earlier, we discussed how we could reset the root password on the MySQL database. It is quite possible even for a technical person to forget or lose his password, so we think it’s necessary that we address this issue in case it happens to you too.
If you have further questions regarding this topic, feel free to leave a comment down below.
If this guide helped you, please share it.