How to Change and Recover MYSQL Root Password
When using the MySQL open-source relational database, you can and should increase the security of the database by adding a strong password.
Should you ever lose your password or have someone steal your password, you may need to change or recover the MySQL root password to regain control of your account. In this article, we discuss changing your MySQL password in Windows and Linux.
1. Stop the MySQL Run Process
Before you can change the password, you need to stop the run process for your MySQL database.
How to Stop MySQL Run in Windows
To stop the run process for MySQL on a Windows server, start by ensuring you are logging in as an Administrator in Windows, rather than logging in as a Standard account.
You can check the type of account you are using easily. In the lower-left corner of the screen, click on the Start button. Then click your account name at the top of the left side of the popup menu. Click Change Account Settings in the next popup window. At the top of the Your Info window, you should see the type of account underneath your account name.
After ensuring you logged in as an Administrator, you can stop the run process. Press and hold the Windows key before pressing the R key to open the Run window.
Inside the Open text box of the Run window, type services.msc and click the OK button.
In the list of services that appears, scroll down until you find the MySQL service entry. Right-click on that entry. Then left-click on the Stop command.
How to Stop MySQL Run in Linux
Before stopping the MySQL run process in Linux, make sure you log in to the account with your user name and password. Do not log in as the root, which can cause problems with the process of changing the password.
Next, locate the process identification file that stores the Process ID number for the account. The Process ID file records and forwards commands that you issue. The Process ID file in MySQL will have a .pid extension. It may have mysqld or the hostname of the system in the main part of the filename.
The .pid file may exist in a number of different directories, including:
- The var, lib, and mysql subdirectories.
- The var, run, and mysql subdirectories.
- The user, local, mysql, and data subdirectories.
With the process identification file located, you now can stop (or kill) the run process (also called the mysqld process). In most versions of Linux, simply open the command line by pressing and holding the CTRL and ALT keys before pressing the T key.
At the prompt, type kill `cat /var/lib/mysql/mysqld.pid` and hit Enter. This command needs to use the right-slanted single quote mark, usually on the key above the Tab key on the keyboard, rather than the traditional left-slanted single quote mark. This mark should go before and after the command string after the kill command.
Additionally, you should substitute the subfolder path that you discovered when searching for your .pid file for the path listed in our example. Replace the actual file name of your .pid file for the file name listed in our example.
2. Create the New File Containing the Password
To change the password, users will need to create a new text file for the MySQL account that contains the command for changing the password, as well as the new password itself.
How to Create a New Password Command File in Windows
You will need a clean text editor to create the file in Windows. Using a word processing file, such as Microsoft Word, may place hidden formatting characters into the file, causing problems.
For Windows users, the built-in Notepad app is the easiest clean text editor to use. Click the Start button, followed by Windows Accessories. Then click Notepad to open the text editor.
At the first line of the text editor, enter the following text string: ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;
Instead of the “NewPassword” placeholder, enter the password you want to use. If you are trying to change the password on a system hosted on a network, you will want to replace localhost with the name of the host on the network. If you are changing the password on the local system only, leave localhost in the text string. Leave all of the single quote marks in the text string.
Next, save the file on the root directory of the C: drive. In Notepad, click the File menu, followed by the Save As command. When naming the file, give it a name that you can easily identify. Leave the .txt file name extension as part of the file name.
How to Create a New Password Command File in Linux
Open a text editor in Linux to create the file containing the new password. You can select from a wide range of Linux-compatible text editors that will provide clean text without adding hidden characters.
Then follow the same instructions we listed in the Windows section for creating the new password command file. The text string is the same in Linux and in Windows.
When saving the file, be sure to save it in the mysql-init folder in Linux.
3. Apply the New Password
Now you are ready to apply your new password by making the text file you just created active.
How to Apply the New Password in Windows
You will need to open a command prompt window in Windows to activate the new password command file. Press and hold the CTRL and SHIFT keys and press the Esc key to open the Task Manager.
In the Task Manager window, click the File menu, followed by Run New Task. In the Create New Task window, enter cmd.exe in the Open text box. Add a checkmark to the Create This Task With Administrative Privileges checkbox. Then click OK.
In the command prompt window, open the MySQL directory. On a fresh command line, enter: mysqld –init-file=C:\\FILENAME.txt
Replace FILENAME in our text string with the actual file name you used in Step 2. Press the Enter key.
Exit out of the command prompt window. You now should be able to log in to your MySQL account with the new password. After you verify that the new password is working, you can delete the password command file you created in Step 2.
How to Apply the New Password in Linux
Open your terminal window in Linux to start the MySQL server again and activate the new password. In the terminal window, enter: mysqld –init-file=/home/me/mysql-init &
Hit the Enter key. This text string starts MySQL and changes the password by activating the password command file. Then log in to your MySQL account using the new password. If it works properly, you can delete the password command text file you created in Step 2.
4. Recover the Root User Password
If you forget the root password for your MySQL account, you have the option of attempting to recover control of the account by resetting it to a new value. The developers for MySQL specifically created this series of steps to reset the root user password whenever you forget or need to swap a new password for the old one.
Log in As the Root User
At the command line for MySQL, use the mysql -u root -p command to log in as the root user. You normally would then type your password, but because you are trying to recover the password, you will not be entering the password.
Stop the MySQL Server
At the command line, use the mysql stop command to stop the server process. You may need to add the directory path before mysql in the command line to execute the command properly. If so, /etc/init.d/mysql stop is a common entry for most installations. You cannot recover the password until you stop the server.
Start the MySQL Server Without a Password
You now can restart the server using a command string that does not require you to enter a password. Use the command string of mysqld_safe –skip-grant-tables & to restart the process. You should receive a message that tells you the server is now running again.
Set the New MySQL Root User Password
Now you can connect to the MySQL server as the root user by entering mysql -u root at the command line. You should receive a message welcoming you to MySQL.
Then enter the commands to reset the password as follows.
use mysql;
update user set password=PASSWORD(“NEWPASSWORD”) where user=’root’;
flush privileges;
quit
Replace the NEWPASSWORD placeholder inside the quotation marks with the password you want to use. The final two lines reload the privileges for MySQL.
To test the new password, enter the same text string that you did in the Stop the MySQL Server section to stop the MySQL server. After you receive the message that notifies you about the server stopping, you can restart the server and test the new password. To restart the server, just enter the same text string again, but replace stop with start at the end of the text string.
Common Problems When Changing and Recovering a MySQL Root Password
Creating a Password That Is Too Weak
If you decide to change the password for the MySQL root because you have concerns over someone hacking the original password, it is important to select an extremely strong new password. Some tips for creating a strong password include:
- Password length: The more characters that are part of the password, the stronger it will be. Hackers will have a far more difficult time guessing or brute-force attacking a longer password. Requiring passwords of 12 characters provides a nice level of security. Using more characters is even better, though.
- Random characters: Although it can be difficult to remember a long password that contains unrelated characters, including at least some non-standard characters in the password, strengthens it significantly. Symbols, numbers, and uppercase characters all greatly strengthen the password.
To help you remember a long password, consider using an entire phrase, separating each word with an asterisk or an exclamation point. Some people may only use the first two or three characters in each word in the phrase, creating what seems to be a random set of characters.
Another suggestion is to use four unrelated words that are easy to remember, separated by symbols or numbers. Some people choose to create an acronym for something that is familiar to them, alternating uppercase and lowercase letters to strengthen the password. Others decide to purposely misspell words in a way that is easy to remember.
Administrators can create MySQL settings that force users to create passwords that meet a certain character length. Through password validation settings in the software, MySQL also will examine the strength of passwords based on some of the criteria listed above. It then will rank the password.
With these password validation settings active, if you try to create a password that is too weak, MySQL can kick the password back for another try.
Adjusting Password Management Settings
If you find that you are having to change your password more often than you would like in MySQL, it is possible that the password management settings in MySQL are too strict. You can reduce the password management processes or eliminate them entirely to force fewer password changes. Some of the password management options include:
- Password expiration: MySQL allows users to force a root password change after a certain amount of time expires, such as 90 or 180 days. You can set the password expiration to occur after a longer period of time, or you can set passwords to never expire.
- Password reuse: When changing passwords, MySQL may have a limitation on the reuse of a password you used in the past. To reduce the restrictive nature of password reuse, you can create settings that allow for the reuse of all past passwords. You also can reduce the time period before the system allows you to reuse a password.
Granting Access to the Password Table
The MySQL system table contains multiple types of information about user accounts, including passwords. The table containing the passwords is part of the MySQL system database, including a list of any password changes occurring in the user accounts.
You must guard this table by only allowing those with administrative accounts to gain access to the table. If someone else is able to access these passwords through the table, it could lead to a loss of account security. A hacker with access to this table could see every password, as well as past passwords.
