Search at Knowledgebase
How to reset the root password of MySQL on Linux

Objective

Knowing the MySQL root password of your server can come in handy in several cases such as accessing the database server console to manually create databases, create users and associate them with the databases, grant privileges to database users on specific databases, etc.
If you are unsure of the MySQL root user password, then the below steps will guide you on how to reset the root password safely. Resetting/changing the database server root password occasionally is also an important security factor, which helps protect against unauthorized intrusion attempts.
The below steps can be followed to reset the root password for MySQL

MySQL

How to Change MySQL User 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. 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.

Your FREE Trial Starts Here!
Contact our team for application of dedicated server service!
Register as a member to enjoy exclusive benefits now!
Your FREE Trial Starts here!
Contact our team for application of dedicated server service!
Register as a member to enjoy exclusive benefits now!
Telegram Skype