Resetting ROOT password in MySQL 5.6

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.

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

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

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

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.

FLUSH PRIVILEGES;

Now, indeed, we can change the root password.

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.

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.

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

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

There might arise a situation where you need to reset the root password for your MySQL database.  It can be because you forgot your password or you need to change the password for security reasons. 

Step 1: Stop the MySQL server

• Begin by checking if you’re logged in as an administrator.
• Press Win+R (hold the Windows/Super key, and press “r”.) Once the “Run” box appears type:

services.msc

• Click OK.
• Scroll down the list of services to find the MySQL service. Right-click that entry then left-clicks Stop.

Step 2: Launch a Text Editor

• Click on the menu and search for Notepad.
• Alternatively, you can use the path: Menu > Windows Accessories > Notepad.

Step 3: Create a New Text File with the Password Command

• Enter the following line 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 of your choice.

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

Consequently, 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.
• Then, click on 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

•  Navigate to the MySQL directory using the command prompt:

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

Accordingly, the command line will 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 choose a different name in Step 2, use the same name after the double slash.

Step 6: Clean 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 unique 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.

At this stage, you have successfully reset the root password for your MySQL database.

0) shut down service mysql56

1) go to C:\ProgramData\MySQL\MySQL Server 5.6, note that ProgramData is a hidden folder

[mysqld]

skip-grant-tables

3) start service mysql56

4) by right, you can access the database, run mysql

5) and use the query below to update the password

update mysql.user set password=PASSWORD('NEW PASSWORD') where user='root';

note: for newer version, use authentication_string instead of password

6) shut down the service again, remove the line skip-grant-tables save it, and start the service again. try to use the password you set to login.

---

0) stop the service

sudo /usr/local/mysql/support-files/mysql.server stop

1) skip grant table

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

once it’s running, don’t close it, and open a new terminal window

2) go into mysql terminal

/usr/local/mysql/bin/mysql -u root

3) update the password

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

for newer version like 5.7, use

UPDATE mysql.user SET authentication_string=PASSWORD('password') WHERE User='root';

4) run FLUSH PRIVILEGES;

5) run \q to quit

6) start the mysql server

sudo /usr/local/mysql/support-files/mysql.server start

I’m kind of stuck on what to do with this. There were several options I found between this site and the mysql site on how to resolve a forgotten root password, but I think I’ve now broken it.

1. I was unable to use the line command with the created reset text file that mysql.com recommended here

2. So I uninstalled WAMP which is what I had used to install it to begin with. That also failed because after reinstall I had a new version and I couldnt log into either of them. Apparently both versions were still there as uninstalling WAMP didnt uninstall MySQL.

3. So I tried the make-sure-everything-is-deleted steps here and rebooted and reinstalled WAMP. No go, won’t take default password.

4. Tried to run the command line command using the newest version and got this error.

asked Jul 21, 2013 at 23:05

This time around, the newer version of mysql let me log in with no password. I was able to get it fixed from the command line and get it reset to what I wanted. Tried that multiple times before, so I cannot say I understand what changed now, but it did.

answered Jul 21, 2013 at 23:21

2 silver badges11 bronze badges

Try to reset you password as described here

1. stop the mysql service (likely requires user to be admin/root)
2. write ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'; into new file and save it under C:\mysql-init.txt (for windows)
3. Open cmd.exe (with admin privilges) and enter commands (see below)

 C:\> cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
 C:\> mysqld --init-file=C:\\mysql-init.txt

It worked for me.

7 gold badges48 silver badges76 bronze badges

answered Feb 26, 2015 at 21:01

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.

• An existing MySQL database
• Access to a Linux or Windows server running MySQL
• Administrator privileges on the computer that hosts the MySQL database
• A text editor. Notepad is included by default in Window. Vim is installed by default in Linux.
• Access to a command-line interface (or terminal)

Step 1: Log in as the MySQL User

Step 2: Find the .pid File for the MySQL Service

The next step is to find the .pid file for the MySQL service.

Most systems store them in /var/lib/mysql/, /var/run/mysqld/, or /usr/local/mysql/data/ path. The filename usually starts with mysqld (or your system’s hostname) and ends with the .pid extension.

Step 3: Kill the mysqld Process

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

Replace mysql-data-directory/host_name.pid with the filename you found in the previous step. Ensure to specify the whole path to the file. Also, make sure to use the back-tick key (usually above the tab key) and not a single-quote mark in the beginning of the command.

Step 4: Create the Password File

1. Open your favorite text editor. In this example, we use vim:

sudo vim

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

Bear in mind to include the single-quote marks and the semicolon. Replace NewPassword with the password you want to use. Finally, make sure to use a strong secure password, like these examples.

The command will work for the machine you’re currently using. If you’re connecting to a different system, replace localhost with the appropriate hostname.

3. Save the file to home/me/mysql-init.

Step 5: Restart the MySQL Server and Apply the New Password

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

This launches 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 before the init command.)

Step 6: Cleaning Up

Lastly, log into your MySQL server using the root account, and verify the new password works. Then, delete the file you created in Step 4.

How to Reset MySQL Root Password in Windows

Step 1: Stop the MySQL server

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

services.msc

3. Click OK.

4. 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 on the menu and search for Notepad.

Alternatively, you can use the path: menu > Windows Accessories > Notepad.

Step 3: Create a New Text File with the Password Command

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

Make sure you keep the quote marks and semicolon. Replace NewPassword with the password of your choice.

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

Consequently, the localhost command makes the password change on your local system. If you’re changing the password on a system over the network, substitute the hostname for localhost.

Step 4: Open a Command Prompt

1. Press Ctrl+Shift+Esc.

2. Then, click on the File menu > Run new task.

4. Click OK.

Step 5: Restart the MySQL Server with Your New Config File

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

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

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

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

Step 6: Clean 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 unique 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:\mysql-init.txt file.

After reading this guide, you should be ready to reset the root password on MySQL in Linux and Windows. Granted, it is not too challenging, yet it is a secure way to change a sensitive password.