Resetting the root password of MySQL can be a crucial process, whether you’ve forgotten your root credentials or need to update the password for security reasons. This guide provides a step-by-step walkthrough on how to reset the root password for MySQL in a secure and efficient manner. Let’s dive in.
- Step 1: Check the MySQL Version
Before making any changes, it’s helpful to verify the version of MySQL you’re running. Run the following command: mysql -v
This will display the current version of MySQL installed on your system. Knowing your version will help you determine if there are any specific version-related commands or syntax to follow. - Step 2: Stop the MySQL Process
To make changes to the MySQL root user and reset the password, you’ll need to stop the
mysqld
process. Use the following command: sudo systemctl stop mysql.service
Stopping the MySQL service ensures that no other users or processes can interfere while you perform administrative actions. - Step 3: Check the Status of MySQL
To confirm that MySQL has stopped successfully, check its status with: sudo systemctl status mysql.service
You should see that the MySQL service is inactive or stopped. - Step 4: Skip Networking and Grant Tables
For the next steps, you need to start MySQL in a mode that skips networking and bypasses the privilege checks: sudo systemctl set-environment MYSQLD_OPTS=”–skip-networking –skip-grant-tables”
This command starts MySQL with special settings that allow you to connect without checking the privileges, enabling password recovery. - Step 5: Start the MySQL Service Again
Start MySQL with the modified environment settings: sudo systemctl start mysql.service
- Step 6: Confirm MySQL Status
Check that MySQL is running with the new configuration: sudo systemctl status mysql.service
Ensure it is active so that you can proceed with password recovery. - Step 7: Log in to MySQL
Now, log in to MySQL as the root user without requiring a password: sudo mysql -u root
Since you’ve skipped the grant tables, you’ll be able to access the MySQL prompt directly. - Step 8: Flush Privileges
After logging in, the first step is to flush the privileges. This action ensures any privilege changes are reloaded: flush privileges;
- Step 9: Change the Root Password
Change the root password using the following command:
ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘your_strong_password_add_here’;
FLUSH PRIVILEGES;
exit;
Replace'your_strong_password_add_here'
with a secure password of your choice. Make sure to use a strong and unique password to ensure the security of your MySQL server. - Step 10: Exit the MySQL Command Line
After setting the password, exit the MySQL command line: quit;
- Step 11: Restore Database Normal Settings
It’s important to restore the normal settings of the MySQL service. First, unset the environment variables: sudo systemctl unset-environment MYSQLD_OPTS
Then revert any MySQL-specific configurations: sudo systemctl revert mysql - Step 12: Kill All MySQL Services
To ensure that all instances of MySQL are properly restarted, kill all active MySQL services: sudo killall -u mysql
- Step 13: Restart the MySQL Service
With the environment back to normal, restart MySQL: sudo systemctl restart mysql.service
This step will restart MySQL, applying all the changes you’ve made, including the new root password. - Step 14: Log in to MySQL With the New Password
Finally, test that your changes have taken effect by logging in with the new password: sudo mysql -u root -p
You’ll be prompted to enter the password. Enter the new root password you set earlier.
Conclusion
Resetting the root password for MySQL can seem like a daunting task, but by following these steps, you can complete the process in a secure and systematic way. Always ensure that you follow best practices, such as using a strong password and keeping it secure. With the MySQL root password reset, you’ll regain control of your MySQL server and maintain its security.