How to Reset the MySQL Root Password

MySQL is a popularly used database management system for web applications. It has a root password that gives users full control over the database. If you haven’t set a root password for your MySQL database, you might be able to create a secure one.

Therefore, resetting the MySQL root password is important for managing the database securely, especially if you forgot MySQL password. In this tutorial, we will show you step-by-step instructions for resetting the MySQL root password on Linux and Windows.

Prerequisites

Before proceeding with the MySQL root password reset process, make sure you fulfill the following requirements:

1. Administrative access to the system where MySQL is installed.
2. MySQL is installed on your operating system.
3. Basic knowledge of using the command line.

Resetting the MySQL Root Password in Linux

This guide assumes that you have installed MySQL on Ubuntu Linux system. You can reset the MySQL root password by following the steps below:

Step 1: Log into MySQL as a Root User

To reset a MySQL root password safely, it is recommended to log in as the user that runs the MySQL server instead of the root user.

It is important to note that, starting the server as root creates files owned by root in the data directory, which can lead to permission problems when starting the server in the future.

Step 2: Stop the MySQL Server

To reset the root password, it’s necessary to stop the MySQL server. You can do this by executing the below-given command:

sudo systemctl stop mysql

Alternatively, you can use the command below to kill all running instances of the MySQL server:

killall mysqld

Step 3: Create a Password File

Once you stop the MySQL server, create a new text file to temporarily store the new password:

sudo nano /home/[username]/mysql-init

Replace new_password with your desired password for the root user. After this, paste the following SQL query in the newly created file:

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

Step 4: Restart the MySQL Server

Now, start the MySQL server with the initialization file you just created:

sudo mysqld --init-file=/home/username/mysql-init &

Replace the username with your actual username. This command will restart the MySQL server:

Step 5: Login and Remove Temporary Files

Once the server restarts, you can log into MySQL using the new password:

mysql -u root -p

Finally, for security purposes, remove the temporary file created earlier with the following command:

sudo rm mysql-init

Resetting the MySQL Root Password in Windows

The process for MySQL password reset on a Windows operating system differs slightly from that of Linux. Let’s go through the below-listed steps to learn how to reset MySQL password on Windows:

Step 1: Login as Administrator

Ensure you are logged in as an Administrator on your Windows machine to have the necessary permissions.

Step 2: Stop the MySQL server

To stop the MySQL service, you need to access the Services application, which can be done by pressing Win+R and then typing the services.msc in the Run box:

Now locate MySQL in the list, right-click on it, and select Stop to halt the service:

Step 3: Create Password File

Open Notepad or any text editor to create a new file and add the following line to the file:

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

Replace new_password with your desired password and save the file in a known location on your system:

Step 4: Open Command Prompt

Next, open Command Prompt as Administrator. For this, search for cmd in the search results, and select Run as administrator:

Step 5: Add New Parameters and Restart Server

After this, type the following command in CMD to navigate to the MySQL installation directory, typically located in C:\Program Files\MySQL\MySQL Server <version>\bin:

cd "C:\Program Files\MySQL\MySQL Server <version>\bin"

Replace the version with the MySQL version number installed on your system:

After this, start MySQL with the initialization file you created earlier by running the following command:

mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini" --init-file=C:\\mysql-init.txt

After this, restart the MySQL service from the Services list. To do this, find the MySQL service, right-click on it, and select Start:

Step 6:  Login and Remove Temporary Files

After restarting the MySQL service, log in with the new password you set:

mysql -u root -p

After confirming that you can access MySQL, delete the initialization file for security reasons.

Conclusion

Resetting the MySQL root password is essential for securing your database. It prevents unauthorized access and ensures you maintain full control over your system. Key steps include stopping the MySQL server, creating a temporary file for the new password, restarting the server with the updated settings, and logging in using the new password. In this article, we discussed step-by-step instructions for resetting the MySQL root password on both Linux and Windows.

FAQ