Introduction

MySQL is a database management system. It’s operated under the Open Source software model and has become a very popular software package 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 without a password. This is not a good idea though, because it means anyone can access your database.

If your database has a root password, but it’s been lost, this guide will help you reset a new password in both Windows and Linux.

Prerequisites

  • An existing MySQL database
  • Administrator privileges on the computer that hosts the MySQL database

Tools/Software

  • A text editor
    • Notepad is included by default in Windows
    • Vi is included by default in Linux
    • Or substitute your preferred text editor
  • Access to a command-line interface (or terminal)

Reset MySQL Password in Windows

Step 1: Stop the MySQL server

Make sure you’re logged in as an administrator.

Press Win+R (hold the Windows / Super key, and press the letter “r”.)  In the “Run” box that appears type:

services.msc

Click OK.

Scroll down the list of services to find the MySQL service.  Right-click that entry, then left-click Stop.

Step 2: Launch a text editor

Click the menu and search for “Notepad”.  Or, you can click the menu > Windows Accessories > Notepad.

Step 3: Create a new text file with the password command

Enter the following into the text editor:

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

Make sure you keep the quote marks and semicolon.  Replace NewPassword with the password you want to use.

Use the File > Save As menu to save the file to the root (C:\ ) of your hard drive.  Type a filename that makes sense, such as mysql-init.txt.

The ‘localhost’ command will make the password change on your local system.  If you’re trying to change the password on a system over the network, substitute the hostname for ‘localhost’.

Step 4: Open a command prompt

Press Ctrl-Shift-Esc.  Click the File menu > Run new task.  Type cmd.exe, and check the box to run as administrator.  Click OK.

Step 5: Restart the MySQL server with your new config file

Type the following at the command prompt:

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

The command prompt should change to show that you’re working in this folder.

Enter the following:

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

Note that there are two slashes after the C: prompt.  Also, if you chose a different name in Step 2, use that name after the double slash.

Step 6: Cleaning up

Now, you can log into your MySQL server as root using the new password.  Double-check to make sure it works.  If you have special configuration options (such as launching MySQL with a –defaults-file option), go ahead and do so.

Once MySQL launches and you’ve confirmed the password change, delete the C:\myswl-init.txt file.

Reset MySQL Root Password in Linux

Step 1: Log in as the MySQL user

When you boot into your Linux installation, make sure you’re logged in as the same user that normally runs MySQL.

You can log in as root, but when you start the MySQL server, make sure you start it with the –user=mysqloption.

If you don’t, the system may create files owned by the root user, which can create problems.

Step 2: Find the .pid file for the MySQL service

The location depends on your distribution of Linux, and a few other variables.  Many systems have them in /var/lib/mysql/, /var/run/mysqld/, or /usr/local/mysql/data/.  The filename usually starts with mysqld or your system’s hostname, and uses the .pid extension.

Step 3: Kill the MySQLD process

Open a command line, and run the following:

kill `cat /mysql-data-directory/host_name.pid`

Use the filename you found in the previous step in place of mysql-data-directory/host_name.pid.  Make sure to specify the whole path to the file.  Also, use the back-tick key (usually above the tab key) instead of a single-quote mark.

Step 4: Create the password file

Open your favorite text editor and type:

sudo vim

into your command line, and hit Enter.

In the text editor, type in the following:

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

Make sure you include the single-quote marks and the semicolon. Switch out NewPassword for the password you want. This command works for the machine you’re working on.  If you’re connecting to a different system, replace localhostwith the correct hostname.

Save the file to home/me/mysql-init

Substitute your user account for /home/me, and make sure you’re saving the file to a password-protected user account so others can’t open the file. Since your .txt file has a copy of the password in a plain, unencrypted text, you don’t want it falling into the wrong hands!

Step 5: Restart the MySQL server and apply the new password

In the terminal window, type:

mysqld --init-file=/home/me/mysql-init &

This will launch MySQL, and apply the text-file password change.  Depending on how you start your server, you may need to add other options (such as –defaults-file.)

Step 6: Cleaning up

Log into your MySQL server using the root account, and make sure the new password works. Then, delete the file you created in Step 4.

Conclusion

After reading this guide, you should be ready to reset the root password in MySQL. It is not too challenging, and a secure way to change a sensitive password.