How to Change or Reset the MySQL Root Password on Windows and Linux

Overview

A well-liked database management system for online application software is MySQL. MySQL features a root or administrator-level password, just like a lot of web services. A user can access all top-level database functions with just the root password.

You ought to be able to connect to your MySQL database if you haven’t changed the root password. Nevertheless, since anyone may access your database, this is not a recommended security procedure.

This tutorial will assist you in resetting the MySQL root password on Windows and Linux if you have forgotten the password for your database.

Required conditions

a MySQL database that is already in place.
access to a MySQL server running on Windows or Linux.

rights as an administrator.
an editor of texts.
having access to a terminal or command-line interface.

How to Change MySQL User Root Password in Linux

For Linux users, modify your MySQL root password by following the instructions below. In this lesson, Ubuntu 22 will be used.

Follow the steps outlined below to change your MySQL root password in Linux. For this tutorial, we will use Ubuntu 22.

First, sign in as a MySQL user.
As the user that you use to launch and manage the MySQL server, log into your computer. You can also log in as root, but then you’ll need to start the mysqld service later in step 5 with the –user=mysql -p option.

Root-owned files are created in the data directory when the server is started as root, which may result in permission-related problems when the server is started again.

Step2 – Stop the MySQL server
Stop the MySQL server if it’s running before making any changes to the password. To terminate the MySQL process, issue the following command:

sudo service mysql stop

The command stops the MySQL server. Alternatively, run the following command to kill all running instances of the MySQL server:

killall mysqld

Step 3: Create a Password File

Make a file where you will enter the new password. As stated below, take action:

Using your preferred text editor, create a new text file. We’ll be using nano in this course. The way it is written is

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

Replace [username] with your actual username on your system.

2. Next, add the following line to the file:

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

Enter the password you wish to use in place of the NewPassword. Add the semicolon at the end and the single quote marks. Use a strong, secure password, please.
On the computer you are currently using, the command is functional. If you’re connecting to a different system, replace localhost with the correct hostname.

Creating a password file for MySQL in Ubuntu.

Save the file with Ctrl+O and exit the editor with Ctrl+X. If you are not logged in with the user account that you use for running the server, set the file permissions to allow the MySQL user to read the file.

Step 4: Apply the Changes

To apply the password change, use the following syntax:

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

Make sure to replace [username] with your actual username on your system. For example:

Changing the MySQL root password in Linux.

The text-file password is changed by the command. The process ID (PID) of the service that was launched in the background is displayed in the output.

Step 5: Restart the Server

Restart the MySQL server after changing the root password to apply the changes. Enter the command below:

Hold off till the procedure is over.

Step 6: Log in and Clean Up

Log into your MySQL server using the root account and verify the new password works.

Logging into the MySQL server on Ubuntu.

If everything works, you can delete the file you created in Step 3.

How to Reset MySQL Root Password in Windows

The steps below show how to reset the MySQL root password in Windows. In this tutorial, we will use Windows 11, but the process works for Windows 10 as well.

Step 1: Log in as Administrator

Log in to the Windows machine as an administrator. Administrative access ensures that you have the necessary rights to modify system-level settings.

Step 2: Stop MySQL server

1. Press Win+R (hold the Windows/Super key, and press r).

2. In the Run box, type services.msc and press Enter.

3. Scroll down the list of services to find the MySQL service. Right-click the entry and select Stop.

Step 3: Create Password File

The password file is a .txt document containing the new password you want to use. Follow the steps below:

1. Press the Windows key and search for Notepad. Press Enter to open the app.

2. Add the following line to the text file:

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

Don’t forget to include the semicolon and quote marks. Change NewPassword to any password you like.

On your local machine, the password is changed by the localhost string. When modifying a system’s password via a network, replace localhost with the correct hostname.

Create a file with password command windows.

Save the file to the root of your hard drive (C: ). The filename should be mysql-init.txt.

Step 4: Open Command Prompt

1. Press the Windows key and type cmd.

2. From the search results, select the Run as administrator option to open Command Prompt as administrator.

Running Command Prompt as an administrator.

Step 5: Add New Parameters and Restart Server

1. Navigate to the MySQL directory using the command prompt:

cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"

If you have a different MySQL version, make sure to use that version in the command.

2. Depending on how you installed MySQL, there are two ways to restart the server with the new parameters:

If you installed MySQL using the ZIP archive

Run the following command

mysqld --init-file=C:\\mysql-init.txt

Note that there are two slashes after the C: prompt.

If you installed MySQL using the MySQL Installation Wizard

You must specify a --defaults-file option in the command, followed by the path to the configuration file. Find the file path by right-clicking the MySQL service in the services list and selecting Properties:

Obtaining the MySQL file path for --defaults-file option.

The file path is listed in the Path to executable section. Copy the file path and use it in the command below, but make sure to use double slashes (\\) instead of single (\) in the path:

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

3. Restart the MySQL service from the Services list. Right-click the MySQL service and select Start.

Step 6: Clean up

Now, log into your MySQL server as root using the new password. Once MySQL launches and you’ve confirmed the password change, delete the C:\mysql-init.txt file.

Conclusion

You ought to be prepared to change the root password on MySQL on Linux and Windows after reading this tutorial. Changing your password is a simple task that enhances database security, whether you’ve forgotten it or you wish to do it for security reasons.