Introduction:
Losing or forgetting the MySQL root password can be a stressful situation, but don't worry; there's a way to regain access to your MySQL database in Ubuntu. In this article, I will walk you through a step-by-step guide on how to reset the MySQL root password on your Ubuntu system. We will use a method that involves stopping the MySQL service, creating a temporary configuration file, and setting a new password for the root user.
Step 1: Stop the MySQL Service
The first step is to stop the MySQL service to ensure we can safely proceed with the password reset process. Open your terminal and run the following command:
sudo service mysql stop
Step 2: Create a Temporary Configuration File
Next, we'll create a temporary configuration file to start the MySQL service without password authentication. This will allow us to reset the root password. Run the following command to create the file:
sudo nano /etc/mysql/mysql.conf.d/reset-mysql-password.cnf
Step 3: Add Configuration to the File
In the nano editor, add the following lines to the reset-mysql-password.cnf file.
[mysqld]
skip-grant-tables
To save the changes and exit the editor, press CTRL + X, then type Y, and finally, press Enter.
Step 4: Start MySQL Service in Safe Mode
With the temporary configuration file in place, start the MySQL service in safe mode:
sudo mysqld_safe --skip-grant-tables &
Step 5: Connect to MySQL Without Password
Now, we can connect to the MySQL server without entering a password. Open a new terminal window and run the following command:
mysql -u root
Step 6: Set a New Password for Root User
Once you are connected to the MySQL prompt, it's time to set a new password for the root user. Execute the following SQL commands:
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOUR_NEW_PASSWORD';
Replace 'YOUR_NEW_PASSWORD' with your desired password. The FLUSH PRIVILEGES; command ensures that the changes take effect immediately.
If you get the following error while using weak password
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
You can fix this like below, if you want to use weak password(Not recommended)
SHOW VARIABLES LIKE 'validate_password%';
SET GLOBAL validate_password.policy=LOW;
Now again run the password update sql:
Step 7: Exit MySQL and Remove the Configuration File
After setting the new password, exit the MySQL prompt:
exit;
Remove the temporary configuration file:
sudo rm /etc/mysql/mysql.conf.d/reset-mysql-password.cnf
Step 8: Restart MySQL Service
Finally, restart the MySQL service to apply the changes:
sudo service mysql restart
Congratulations! You have successfully reset the MySQL root password on your Ubuntu system. Now you can access your MySQL database using the new password you set. Always make sure to keep your MySQL root password secure .
Top comments (1)
Thank you so much !