MySQL is one of the most popular database management systems for web applications. MySQL has a root password or an admin-level password, just like many web services. The root passwords permit users to perform any top-level function in a database.
While setting up MySQL, it is recommended that you set up a root password for your database for security-related concerns. However, this does not mean that you cannot use MySQL without a root password. It is upon you to think about the task at hand and decide whether to set up a root password or not.
Resetting MySQL root password
There are lots of questions asked by MySQL users about “How to reset MySQL root password.” If you had set up a root password but unfortunately lost track of it, then this article tutorial will guide you through the process of resetting the root password both on Linux and Windows.
Prerequisites
- Existing MySQL database
- Admin privileges on the PC hosting the MySQL database
- Access to Windows or Linux server running MySQL
- Command-line interface or terminal access
- A text editor. (Don’t worry since Windows users have Notepad installed by default on their computers while Linux users have vim pre-installed).
Once you have all the mentioned prerequisites, let’s roll on and learn how to reset or change the MySQL root password.
We shall do this process on two fronts:
- How to reset MySQL root password in Linux
- How to reset MySQL root password in Windows
1. How to reset MySQL root password in Linux
Step 1: Log in to MySQL
When booting into your Linux operating system, it is essential to log in to the same user that usually runs MySQL. This is crucial to avoid errors such as creating files owned by the root user, which might cause damage to your system. However, you might as well log in as a root user once you start your MySQL server. To do this, ensure you start MySQL using the syntax below.
user=fosslinux
User = fosslinux
Step 2: Finding the .pid file used by the MySQL service
The next step is locating the .pid file. In most Linux systems, they are stored in this location:
/var/lib/mysql or /var/run/mysqld/ or /usr/local/mysql/data/.
Find the .pid file
Since not all Linux systems are the same, you can try out any of the paths mentioned above and see if you can locate the file. To quickly find the file, you can narrow your search to files starting with mysqld (or your hostname) and ending with .pid.
Note: Searching the file will simplify the process; hence we recommend you try it out.
Once you find the file, now proceed to the next step
Step 3: Killing the mysqld / .pid process
Before resetting or creating a new root password, we first have to stop the MySQL server. This can be done by opening the terminal and executing the command line below:
kill `cat /mysql-data-directory/host_name.pid`
kill the .pid process
Note: Replace the mysql-data-directory/host_name.pid with the filename we found in step 2. Also, it is vital to specify the entire file path to avoid getting errors.
Additional tip: Always ensure you use the back-tick key instead of a single-quote mark key. The back-tick key is located above the tab-key on most computers and before the one numkey.
Step 4: Creating a new root password file
Now it is time to open your favorite text editor. For our case, we shall use the Vim text editor. Open terminal using the shortcut CTRL + ALT + T, then run the command below:
sudo vim mysql-init
sudo vim mysql-init
Note: if you get an error after running the command above, execute this command to proceed with the other processes:
sudo apt install vim
install vim
Now add the line below in the file:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Sudo vim and create the new password file by adding the command into the vim editor
Note: When manually typing the line above into the terminal, remember to include all the semicolons and single-quote marks. Also, replace the “NewPassword” phrase with the new password you want to use. Finally, ensure you use a strong and secure password or refer here to learn how to generate a strong random password on Linux.
The above command will only work on the host machine you are using when running the command. However, if you plan to connect to a different system, replacing the localhost with the relevant or rather appropriate hostname is recommended for the command to work. If you fail to do so, you will encounter errors.
Once you have added the new file to your text editor, save it to:
home/mysql-init.
Mysql.init file in the home directory
Step 5: Restarting the MySQL Server and Applying the Newly created Password
It is time to apply the changes we made to our newly created password. First, we shall restart the MySQL server by executing the command below in the terminal:
mysqld --init-file=/home/mysql-init &
Restart MySQL server and apply the newly created password
This command will launch MySQL and apply the text-file password we created in the previous step. Depending on the method you use to start your server, you might be required to add other options such as –defaults-file before the init command.
After you apply the newly created password and restart the MySQL server, it is time to clean up. We shall do this by logging into your MySQL server as a root user and verifying that the password we created actually works. Once you confirm that the file works well, proceed and delete the file we created in step 4 above.
2. How to reset MySQL root password in Windows
If you are a Windows user and would love to learn how to reset your MySQL root password on Windows, read this article to the end.
Step 1: Stopping the MySQL server.
Before stopping the MySQL server, you must check to ensure you are logged in as an administrator.
- Press the Win + R keys on windows to launch the Run dialogue box the type:
services.msc
- Next click OK.
Run dialogue box
- Scroll down through the list of services and locate the MySQL service. Right-click on it and then select the Stop option as shown in the image below:
Step 2: Open your favorite text editor
For illustration purposes, we shall be using the default Notepad text editor. However, don’t freak out if you have a different text editor since the processes are similar.
- Now click on the Start menu and search for Notepad. Click on it to launch.
Alternatively, use this path to find Notepad: menu>Windows Accessories>Notepad.
Step 3: Creating a new Password text file using the password command
Copy and paste the syntax below into the text editor to avoid errors while manually typing:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Create a new text file
Like we did for Linux, ensure to keep the semicolon and the single quote marks and replace the NewPassword phrase with the new password you intend to use on your MySQL.
Click on the File option, then Save As to save the text file password onto the root of your hard drive. Save the file as mysql-init.txt.
Save the file as mysql-init.txt.
Consequently, the localhost command can only make password changes on your system. Therefore, if you intend on changing the password on a different system like over the network, then substitute the localhost with the appropriate hostname for the different system.
Step 4: Launch CMD (Command Prompt)
- Click on the Ctrl + Shift + Esc keys on your keyboard
- Select file menu, then Run a new task
- Type cmd.exe and select the Run as administrator checkbox
- Click OK to launch a command prompt with administrator privileges.
Create a new task and run cmd
Step 5: Restarting MySQL Server and Applying the new Config file
- Using the command prompt, navigate to the MySQL directory:
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
- Next, input the following command:
mysqld --init-file=C:\\mysql-init.txt
Navigate to MySQL server directory, restart MySQL server, and apply the new config file
Note: You should take into consideration that there are two backslashes before the mysql-init prompt. Besides, if you used a different name to the one we used for illustration in step 2 above, then you should use that name immediately after the two backslashes without spacing.
Once this is complete, you can now log into your MySQL as a root user using the newly created password.
Ensure you double-check to ascertain that the new password works flawlessly. After confirmation, proceed and do away with the mysql-init.txt file we stored in our C:\ directory.
Generic instructions for resetting MySQL root password
The preceding sections have provided in-depth instructions on resetting MySQL root passwords for Linux and Windows. Alternatively, when using any platform, you can use the MySQL client to reset the password. However, it is vital to note that this method is less secure compared to the preceding methods.
To use the MySQL client, follow the steps highlighted below:
1. The first step is to stop the MySQL server. After stopping, restart it with the option below:
--skip-grant-tables
This option will enable anyone to connect without a password but will all privileges, and it disables account-management statements such as ALTER USER and SET PASSWORD.
This option is deemed insecure since if the server starts with the –skip-grant-tables option, skip-networking is enabled by disabling remote connections.
2. Now connect to the MySQL server by running the command below:
mysql
Note: no password is required while restarting the MySQL server using MySQL client since the server started with the –skip-grant-tables.
3. Now, in the MySQL client, run the command below to tell the server to reload the grant tables. This will enable the account management statements that had been stopped earlier to work again:
mysql> FLUSH PRIVILEGES;
After running the command, run the ALTER USER command to change the password. Replace the MyNewPass, with the new password you intend to use.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Note: To change the root password for a different system, ensure to alter the localhost with the system’s hostname.
Now you can connect to your MySQL server as a root user using the password we created. Then, stop and restart the server normally without the –skip-grant-tables options and without enabling the skip-networking variable.
That’s all you have successfully reset your MySQL root password.
Conclusion
If you had previously assigned a root password to MySQL but forgot it, you can reset it by assigning a new password to your MySQL, as shown in this article.
Note: MySQL does not require a root password to connect. However, the downside of not assigning a root password to your MySQL is that it is insecure, and you might lose your valuables. Therefore, if you have never set a root password to your MySQL, we highly recommend that you follow the steps mentioned above to create one and secure your data and information.
