Estimated reading time: 5 minutes
Resetting the MySQL root password can seem daunting, but it’s straightforward if you follow the proper steps. Whether you’re using Windows, Unix, or a Unix-like/Linux system, here’s a guide to help you through the process.
Table of contents
Resetting Password on Linux, Unix or Unix-like Systems
Step 1: Log in as the MySQL User
Login to the Linux/Unix user that MySQL runs under (e.g., mysql). You can log in as root as well. However, if you are running these steps as root, you should use the --user=mysql -p option when starting the mysqld service later.
Step 2: Stop the MySQL Server
If you are running MySQL as a service, use the service stop command to stop the MySQL server:
sudo service mysql stop You can also kill the MySQL process using its PID. Locate the MySQL PID file (usually in /var/lib/mysql/) and stop the server with:
kill `cat /mysql-data-directory/host_name.pid` Alternatively, you can use the killall command to kill all the instances of the mysqld process on the server:
killall mysqld Step 3: Create a Password-Reset File
Create a plaintext file in which you will specify the new password for the MySQL root user. Follow the steps below:
i) Create a text file using your favourite text editor. For our example, we are using the vi editor:
vi /home/[username]/mysql-init Replace the username with the username of the user you are logged into, for example: mysql.
ii) Add the password-assignment statement on a single line. Make sure to include the single-quote marks and the semicolon at the end.
Note: The password-assignment statement in our example resets the password for the MySQL root user on localhost. If you want to reset the password for a different host, replace the hostname in the statement accordingly.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'; Replace the password with the password that you want to use.
iii) Save the file. If you are not logged in with the user account you use to run the MySQL server, set the file permissions to allow the MySQL user to read the file.
Step 4: Start MySQL with the Init File
Pass the init file to the mysqld process, and start the server.
sudo mysqld --init-file=/home/[username]/mysql-init &The server executes the file’s contents defined by the init_file system variable at startup, changing the 'root'@'localhost' account’s password. You should see the output with the PID of the started background process.
Step 5: Restart the Server
Once the MySQL server is up with the init_file, login to the server and verify the changes. Then, stop the process and restart MySQL as you usually do.
kill `cat /mysql-data-directory/host_name.pid`
sudo service mysql restart Step 6: Clean Up
After the server has started successfully, delete /home/me/mysql-init file.
Resetting MySQL Root User Password on Windows
The steps below will guide you in resetting the MySQL root user password on a Windows machine.
Step 1: Log in as Administrator
Log in to the Windows machine as an administrator. Ensure you have administrative privileges.
Step 2: Stop MySQL Server
If you are running the MySQL server as a service:
i) Hit Win+R on your keyboard to open the Run prompt.
ii) Type services.msc in the Run prompt and press Enter.
iii) Find the MySQL service in the list and stop it.
If your MySQL server is not running as a service, you must use the Task Manager to force it to stop.
Step 3: Create a Password-Reset File
i) Like we did for Linux, Unix and Unix-like systems above, create a password-reset file. For our example, we are storing this file under C:\mysql_init.txt.
ii) Add the password-assignment statement on a single line. Make sure to include the single-quote marks and the semicolon at the end.
Note: The password-assignment statement in our example resets the password for the MySQL root user on localhost. If you want to reset the password for a different host, replace the hostname in the statement accordingly.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'; Replace the password with the password that you want to use.
Step 4: Start MySQL with Init File
i) Open the command prompt as administrator.
ii) Navigate to the MySQL directory using the command prompt:
cd "C:\Program Files\MySQL\MySQL Server 8.4\bin"Note: The MySQL directory may be different on your system depending on the MySQL server version you are running. Modify this command according to your MySQL installation directory.
Step 5: Start the MySQL Server
Depending on how you installed the MySQL server, you can start it from the command line using the following steps.
If you installed MySQL using the zip archive:
Start the MySQL server with the init_file system variable set to the name of the password reset file.
Note: Notice that the backslash in the option value is doubled.
C:\> mysqld --init-file=C:\\mysql_init.txtIf you installed MySQL using the MySQL Installation Wizard:
If you installed the MySQL server using the MySQL Installation Wizard, you must specify a --defaults-file option. Start the MySQL server using:
C:\> mysqld
--defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 8.4\\my.ini"
--init-file=C:\\mysql_init.txt Note: You can find the correct defaults-file in the properties of the service. Open the list of services: Type services.msc in the Run prompt. Right-click on the MySQL service, and click on Properties. The Path to executable field contains the --defaults-file setting.
Step 6: Clean Up and restart
After MySQL starts, delete the C:\mysql_init.txt file. Restart MySQL normally.
Platform-independent option to reset MySQL root password
If you prefer a platform-independent method:
Stop the MySQL Server and restart it with the --skip-grant-tables option to disable password requirements.
This allows anyone to connect without a password and with all privileges. It disables account-management statements such as ALTER USER and SET PASSWORD.
Now, since account management is disabled, you can directly log in to MySQL without a password:
$> mysqlOnce you are logged in, run the FLUSH PRIVILEGES statement to load the privileges on the server, and enable account-management statements.
mysql> FLUSH PRIVILEGES;Now, you can execute the password reset command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';Finally, Restart MySQL Normally: Restart MySQL without the --skip-grant-tables option.
Conclusion
Following these steps, you can securely reset the MySQL root password and regain access to your database. For more detailed instructions, refer to the MySQL documentation.
