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

December 12, 2023

Introduction

MySQL is a popular database management system for web application software. Like many web services, MySQL has an administrator-level or root password. The root password allows a user to perform all top-level functions in the database.

If you've never set a root password on your MySQL database, you should be able to connect to it. However, this is not a good security practice, as anyone can access your database.

If your database has a root password, but you lost track of it, this guide will help you reset a MySQL root password on Linux and Windows.

How to reset or change MySQL root password on Linux or Windows.

Prerequisites

  • An existing MySQL database.
  • Access to a Linux or Windows server running MySQL.
  • Administrator privileges.
  • A text editor.
  • Access to a command-line interface (or terminal).

How to Change MySQL User Root Password in Linux

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

Step 1: Log in as MySQL User

Log into your system as the user that you use to start and run the MySQL server. You can also log in as root, but in that case, you must use the --user=mysql -p option when starting the mysqld service later in step 5.

Starting the server as root creates root-owned files in the data directory, which can cause permission-related issues for future server startups.

Step 2: Stop the MySQL Server

Before changing the password, stop the MySQL server if it is active. Run the following command to stop the mysqld process:

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

Create a file in which you will specify the new password. Follow the steps below:

1. Create a new text file using your favorite text editor. In this tutorial, we will use nano. The syntax 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';
  • Replace the NewPassword with the password you want to use. Include the single-quote marks and the semicolon at the end. Make sure to use a strong and secure password.
  • The command works for the machine you're currently using. Replace localhost with the appropriate hostname if you're connecting to a different system.
Creating a password file for MySQL in Ubuntu.

3. 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 command applies the text-file password change. The output shows the process ID (PID) of the service that was started in the background.

Step 5: Restart the Server

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

sudo service mysql start

Wait until the process completes.

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.

Opening the Services app on Windows.

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

Stopping the MySQL service on Windows.

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';
  • Make sure to keep the quote marks and semicolon. Replace NewPassword with the password of your choice.
  • The localhost string makes the password change on your local system. If you’re changing the password on a system over the network, substitute localhost with the appropriate hostname.
Create a file with password command windows.

3. 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

After reading this guide, you should be ready to reset the root password on MySQL in Linux and Windows. Whether you forgot your password or just want to change it for security reasons, it is an easy task that improves your database security.

Next, learn the difference between MySQL and PostgreSQL, or follow our guide and get started with MySQL Workbench on Ubuntu.

Was this article helpful?
YesNo
Sofija Simic
Sofija Simic is an experienced Technical Writer. Alongside her educational background in teaching and writing, she has had a lifelong passion for information technology. She is committed to unscrambling confusing IT concepts and streamlining intricate software installations.
Next you should read
How To Remove Or Delete A MySQL User Account
April 16, 2024

This article covers the basics of using the DROP USER statement used to delete MySQL user account. Follow the step-by-step instructions and remove one or multiple accounts in a matter of seconds.
Read more
Install And Get Started With MySQL Workbench On Ubuntu 18.04
December 12, 2023

Workbench is a visual tool for managing MySQL databases . Its graphical interface allows administrators and database architects to model data, build and run SQL queries, and maintain a database.
Read more
How To Rename A MySQL Database
March 20, 2024

As an administrator, you may need to rename a database. However, for security, the command to rename a database directly was removed in MySQL 5.1.23. This guide will provide the instructions for renaming a MySQL database.
Read more
How To Import And Export MySQL Databases In Linux
March 11, 2024

MySQL can be used for something as simple as a product database, or as complex as a website. This guide will walk you through how to export the database and import it from a dump file in MySQL.
Read more