Reset mysql password on windows

Если у вас Linux, то смотрите статью «Как сбросить пароль root для MySQL или MariaDB».

Пароль пользователя root спрашивается во время установки СУБД. Если установка делалась вручную, то есть без инстолятора, как это описано, например, в этой статье, то пароль может быть не установлен вовсе.

Если вы используете какие-то готовые сборки, которые включают в себя MySQL/MariaDB, то обратитесь за паролем на официальные сайты этих сборок. Также попробуйте такие учётные данные:

• Пользователь: root
• Пароль: root

Если вы действительно забыли пароль MySQL/MariaDB и вам нужно сбросить пароль root в MySQL на Windows, то данная статья расскажет, как это сделать.

Шаг 1 — Определяем версию системы управления базой данных

Найдите, в какой папке у вас расположен файл mysqld.exe. При установке по данной инструкции, этот файл расположен в папке C:\Server\bin\mysql-8.0\bin\.

Откройте командную строку. Нам понадобятся права администратора, поэтому делаем следующее: нажмите Win+x и там выберите Windows PowerShell (администратор):

Теперь перейдите в командной строке в директорию с файлом mysqld.exe, для этого используйте команду вида:

cd путь\до\папки

Например, у меня это папка C:\Server\bin\mysql-8.0\bin\, тогда команда такая:

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

Нужно определить версию MySQL/MariaDB, для этого выполните команду:

.\mysql --version

Пример вывода:

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

Шаг 2 — Остановка сервера базы данных

Для изменения пароля root вы должны заранее отключить сервер базы данных. Для MySQL и MariaDB вы можете сделать это командой:

net stop mysql

После того, как сервер остановлен, вы вручную получите к нему доступ для сброса пароля рута.

Шаг 3 — Перезапуск сервера базы данных без проверки разрешений

Если вы запускаете MySQL и MariaDB без загрузки информации о привилегиях пользователя, она позволит вам без ввода пароля получить доступ к командной строке базы данных с привилегиями рута. Это позволит вам получить доступ к базе данных без знания парольной фразы. Чтобы это сделать, вам нужно не дать базе данных загрузить таблицы привилегий, которые содержат информацию о привилегиях пользователя. Поскольку это несёт риск безопасности, вы также должны избежать сетевой активности, чтобы не допустить подключения других клиентов.

Запустите базу данных без загрузки таблиц привилегий и без доступа к сети:

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

Программа НЕ должна завершить работу, то есть теперь в это окно командной строки ничего нельзя ввести.

Шаг 4 — Смена пароля рута

Теперь вы можете подключиться к базе данных как пользователь рут, у которого не спросят пароль.

Открываем новое окно командной строки, можно без прав администратора.

Опять переходим в нужную папку

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

И подключаемся к серверу MySQL/MariaDB

.\mysql -u root

Вы сразу же увидите приглашение оболочки базы данных. Приглашение командной строки MySQL:

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.

Теперь, когда у вас имеется рут доступ, вы можете изменить пароль рута.

Простым способом смены пароля рута для современных версий MySQL является использование запроса ALTER USER. Тем не менее эта команда не будет работать прямо сейчас, поскольку таблицы привилегий не загружены. Давайте скажем серверу баз данных перегрузить таблицы привилегий введя команду:

FLUSH PRIVILEGES;

Теперь действительно мы можем поменять пароль рута.

Для MySQL 5.7.6 и новее, а также для MariaDB 10.1.20 и новее используйте следующую команду:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'новый_пароль';

Для MySQL 5.7.5 и старее, а также для MariaDB 10.1.20 и старее используйте:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('новый_пароль');

Не забудьте поменять новый_пароль на выбранный вами новый пароль.

Примечание: если команда ALTER USER не работает, то это обычно является признаком более серьёзной проблемы. Тем не менее вместо этой вы можете попробовать UPDATE … SET для сброса root пароля:

UPDATE mysql.user SET authentication_string = PASSWORD('новый_пароль') WHERE User = 'root' AND Host = 'localhost';

После этого не забудьте перегрузить таблицы привилегий:

FLUSH PRIVILEGES;

В любом случае вы должны видеть подтверждение, что команда успешно выполнена. Вывод:

Query OK, 0 rows affected (0.02 sec)

Выходим из сессии:

exit;

Пароль изменён, вы можете остановить запущенный вручную экземпляр сервера базы данных и перезапустить его как это было раньше.

Шаг 5 — Обычный перезапуск сервера базы данных

Для начала, остановите экземпляр сервера базы данных, который вы запустили вручную на Шаге 3. Для этого перейдите в окно с запущенной mysqld и нажмите Ctrl+c.

Затем перезапустите сервис обычным образом:

net start mysql

Теперь вы можете подтвердить, что новый пароль работает, запустите:

.\mysql -u root -p

Эта команда должна вызвать приглашение в который нужно ввести новый пароль. Введите его, вы должны получить доступ к интерфейсу командной строки базы данных, как это обычно и происходит.

Заключение

Теперь вы восстановили административный доступ к серверу MySQL или MariaDB. Убедитесь, что новый пароль рута, который вы выбрали, безопасный и храните его в надёжном месте.

Ошибка «—shared-memory, or —named-pipe should be configured on NT OS»

Если при запуске mysqld вы столкнулись со следующей ошибкой:

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

то вам необходимо к команде запуска mysqld добавить флаг —shared-memory.

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. 

In this article, we will look into the process of resetting the MySQL password using Windows cmd. To do so follow the below steps:

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.

Last Updated :
28 Dec, 2022

Like Article

Save Article

MySQL is an open-source Relational Database Management System that includes cutting-edge features, management tools, and technical support. Obviously, this most popular database has a root password. if you have forgotten your root password, join us with this article to learn How to Reset MySQL Root Password in Linux & Windows. To continue performing your top-level functions and tasks, you need to know your root password.

Although the server does not require a password for connecting as root if you have not set a root password on your MySQL database, it is not secure to not assign a root password. You can just bookmark this tutorial to not worry about forgetting your MySQL root password.

To let this tutorial works correctly, provide the options below.

• Access to a Linux VPS or Windows Server running MySQL.
• An existing MySQL database.
• Administrator privileges on the machine hosting the MySQL database.
• A text editor. (Depending on working on Windows or Linux, Notepad and Vim will be your assistants by default).
• Access to a command-line/Terminal interface

Reset/Change MySQL Root Password in Linux [Step by step]

Let’s go through the required steps of this part to learn how to reset/change MySQL Root Password in Linux. To change the password for the MySQL ‘root’@’localhost’ account on Unix, use the steps below. Modify the instructions to use a new hostname section if you want to alter the root account’s password.

Step 1. Log in as MySQL User

The instructions presuppose that you launch the MySQL server using the Unix login account you typically use to do so. You should log in as mysql before following the steps, for instance, if you run the server using that login account. You can also log in as root, but doing so requires that you start mysqld with the --user=mysql option.

Step 2. Locate the .pid file

Find the .pid File for the MySQL Service. It contains the process ID for the server. Your distribution, hostname, and configuration will all affect the precise name and placement of this file. /var/lib/mysql, /var/run/mysqld, and /usr/local/mysql/data are typical locations. In most cases, the file name ends in.pid and either starts with mysqld or the hostname of your system.

Step 3. Stop the mysqld Server Process

Stop the MySQL server if it is running. Open a command line and run the command below to kill the mysqld process:

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

When using the cat command, use backticks rather than forward quote marks. These lead to the kill command substituting the output of cat.

Step 4. Create MySQL Password File

To create the password file, open your preferred text editor and run:

sudo vim

Now, add the following line in the file to create a text file containing the password-assignment statement on a single line.

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

Keep in mind that the password should be changed to the one you want to use. Try to use a strong password. Also, be aware that the machine you’re using right now will be able to execute the command. Replace localhost with the correct hostname if you’re connecting to a different machine. Finally, save the file to home/me/mysql-init.

Note: Do not save the file anywhere that other users can access it because it contains the password. Make that the file has rights that allow mysql to view it if you are not logged in as mysql, the user that the server runs as.

Step 5. Start the MySQL Server

Set the init_file system variable to the desired name before starting the MySQL server and apply the changes to the password.

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

At server startup, the server runs the code contained in the file specified by the init_file system variable, updating the password for the root@localhost account. Depending on how you typically start your server, more choices could be required. For instance, before the init_file argument, --defaults-file can be required. Delete /home/me/mysql-init after the server has successfully started. (Step 4)

The new password should now allow you to connect to the MySQL server as root. Restart the server normally after stopping it.

Reset MySQL Root Password in Windows

The MySQL ‘root’@’localhost’ account’s password can be changed on Windows by using the steps below. Modify the instructions to use a new hostname section if you want to alter the root account’s password. Log in as Administrator to your system and follow the below steps to reset MySQL Root Password in Windows.

Step 1. Stop the MySQL Server

To stop the MySQL Server if it is running, first open the box, press the “Windows + R” key and type the below phrase, and click Ok.

services.msc

Now, find “MySQL” and select the “Stop” service option.

Step 2. Launch Notepad as Administrator

In this step, run the text editor “Notepad” with the “administrator” privileges. To do this, search for Notepad on the menu or use the path below:

menu > Windows Accessories > Notepad.

Step 3. Create a New Text File

Into your text editor, type the following line and save the Notepad file.

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

Do not forget to replace NewPassword with the password of your choice.

To save the file to the C: drive at the top of your hard drive, use the File > Save As menu. Decide on a filename, like mysql-init.txt.

The localhost command as a result updates the password on your local system. Change localhost to the hostname if you’re changing the password on a system over the network.

Step 4. Open Command Prompt and Restart MySQL Server

Run the Windows terminal “Command Prompt” as administrator as you see below.

To change the directory to where MySQL is installed, use the “cd” command:

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

the text file containing the new password should then be initialized with its destination path before running the “mysqld” command to reset the password. So, enter the following

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

Now, the user password must be reset successfully, and you can log into your MySQL server as root using the new password.

Note: Use the alternate filename you selected in Step 2 after the double slash.

Step 5. MySQL Reset Password Verification

Run the following command for verification:

mysql -u root -p

When you confirmed the password change, again delete the C:\mysql-init.txt file once MySQL launches. You’re all done. You reviewed the best and easiest procedure to reset MySQL root password on Windows.

How to Reset MySQL Root Password in Non-Unix/Windows Platforms

Password reset methods are provided in the sections above for Windows, Unix, and Unix-like systems. As an alternative (though less secure), you can reset the password using the MySQL client on any platform by following the below steps:

1. If necessary, stop the MySQL server and restart it with the --skip-grant-tables option. This disables account-management lines like ALTER USER and SET PASSWORD and allows anyone to connect with full rights without a password. If the server is launched using the –-skip-grant-tables option, it also turns off remote connections by activating skip_networking because this is unsafe.

2. Using the mysql client, connect to the MySQL server; Because the server was started using the command --skip-grant-tables, there is no need for a password.

$> mysql

3. Tell the server to reload the grant tables in the mysql client for account-management commands to function:

mysql> FLUSH PRIVILEGES;

Then modify the password for the ‘root'@'localhost‘ account. The password should be changed to the one you want to use. Modify the instructions to use a new hostname section if you want to alter the root account’s password.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

The new password should now allow you to connect to the MySQL server as root. The server should be stopped and restarted normally (without the --skip-grant-tables option or the skip_networking system variable enabled).

FAQ

Conclusion

In this article, you learned How to Reset MySQL Root Password in Linux & Windows. Now, you know the best and easiest way to reset and change MySQL root password. As you reviewed, the methods were not that difficult but are so handy to achieve your server security.

If you follow the above steps properly then you can smoothly Reset MySQL Root Password in Linux & Windows without any errors but do not hesitate to contact us if you encounter any problems. Our technical support team will try their best to solve your problems.

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.

Prerequisites

• 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

When you boot into your Linux installation, make sure you’re logged in as the same user that normally runs MySQL. Although you can log in as root, once you start the MySQL server, make sure you start it with the --user=mysql option.

Otherwise, the system may create files owned by the root user, which can cause problems.

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

Before you create a new root password, stop the MySQL server. To kill the mysqld process, open a command line, and run the following:

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

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

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

To apply the changes to the password, restart the MySQL server by running the following command in the terminal:

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

1. Begin by checking if you’re logged in as an administrator.

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

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

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.

3. Type cmd.exe, and check the box to run as administrator.

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"

2. Then, 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 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.

Conclusion

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.

Reset MySQL 8.0 root Password in Windows