How to reset MySQL or MariaDB root password on Windows

The root password is set during the installation of the DBMS. If the installation was done manually, that is, without an installer, as described, for example, in this article, then the password may not be set at all.

If you use any ready-made assemblies that include MySQL/MariaDB, then ask for the password on the official sites of these assemblies. Also try these credentials:

• User: root
• Password: root

If you really forgot the MySQL/MariaDB password and you need to reset the root password in MySQL on Windows, then this article will tell you how to do it.

Step 1 – Determine the version of the database management system

Find in which folder your mysqld.exe file is located. When installed according to this turorial, this file is located in the C:\Server\bin\mysql-8.0\bin\ folder.

Open a command prompt. We need administrator rights, so we do the following: click Win+x and select Windows PowerShell (administrator) there:

Now go to the directory with the mysqld.exe file on the command line, for this, use a command of the form:

cd path \to\folders

For example, I have this in the C:\Server\bin\mysql-8.0\bin\ folder, then the command is:

cd C:\Server\bin\mysql-8.0\bin\

You need to determine the version of MySQL/MariaDB, to do this, run the command:

.\mysql --version

Output Example:

C:\Server\bin\mysql-8.0\bin\mysqld.exe Ver 8.0.19 for Win64 on x86_64 (MySQL Community Server - GPL)

Step 2 – Stop the Database Server

To change the root password, you must stop the database server in advance. For MySQL and MariaDB, you can do this with the command:

net stop mysql

After the server is stopped, you will start it manually to reset the root password.

Step 3 – Restarting the database server without checking permissions

If you start MySQL and MariaDB without loading user privilege information, it will allow you to access the database command line with root privileges without entering a password. This will allow you to access the database without knowing the passphrase. To do this, you need to prevent the database from loading privilege tables that contain user privilege information. Since this carries a security risk, you should also avoid network activity in order to prevent other clients from connecting.

Run the DBMS without loading privilege tables and without network access:

.\mysqld --skip-grant-tables --skip-networking --shared-memory

The program should NOT shut down, that is, now nothing can be entered into this command line window.

Step 4 – Change the root password

Now you can connect to the database as a root user who will not be asked for a password.

Open a new command prompt window, without administrator privileges.

Again, go to the desired folder

cd C:\Server\bin\mysql-8.0\bin\

And connect to the MySQL/MariaDB server

.\mysql -u root

You will immediately see a database shell prompt. MySQL command line prompt:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Now that you have root access, you can change the root password.

An easy way to change the root password for modern versions of MySQL is to use the ALTER USER query. However, this command will not work right now because privilege tables are not loaded. Let's tell the database server to reload the privilege tables by typing:

FLUSH PRIVILEGES;

Now, indeed, we can change the root password.

For MySQL 5.7.6 and later, and also for MariaDB 10.1.20 and later, use the following command:

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

For MySQL 5.7.5 and older, as well as for MariaDB 10.1.20 and older, use:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Remember to change the new_password to your new password.

Note: if the ALTER USER command does not work, then this is usually a sign of a more serious problem. However, instead of this, you can try UPDATE … SET to reset the root password:

UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';

After that, do not forget to reload the privilege tables:

FLUSH PRIVILEGES;

In any case, you should see confirmation that the command completed successfully. Output:

Query OK, 0 rows affected (0.02 sec)

Exit the session:

exit;

The password has been changed, you can stop the manually started instance of the database server and restart it as it was before.

Step 5 – Regular restart of the database server

To begin, stop the database server instance that you manually started in Step 3. To do this, go to the window with mysqld running and press Ctrl+c.

Then restart the service as usual:

net start mysql

Now you can confirm that the new password works, run:

.\mysql -u root -p

This command should prompt you to enter a new password. Enter it, you should access the database command line interface, as is usually the case.

Conclusion

You have now restored administrative access to the MySQL or MariaDB server. Make sure that the new root password that you selected is safe and keep it in a safe place.

The error “--shared-memory, or --named-pipe should be configured on NT OS”

If you encounter the following error when starting mysqld:

[ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS

then you need to add the --shared-memory flag to the mysqld start command.