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.
Prerequisites to Reset MySQL Root Password in Linux & Windows
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
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
Step 4. Create MySQL Password File
To create the password file, open your preferred text editor and run:
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
--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.
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
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.
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).
The server should not be started as root without using –user.=mysql. Otherwise, you might face permission-related problems for future server startups. However, to solve it, change the ownership of the files to mysql or remove them.
Yes, it is. To make changes in the database and its related tables, a user needs to have appropriate access and privileges. We employ passwords in the login mode to secure our data records and restrict access to our system for other users.
All the steps of this tutorial are compatible with Windows 10 and are required to reset MySQL root password on Windows 10.
- Stop the MySQL server without applying the grant table authentication.
- Restart localhost.
- Restart the server by skipping the grant table options.
- Set your user account password for the MySQL database host using the UPDATE command statement.
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.