Home/ Questions/Q 6895

Querify Question Shop: Explore Expert Solutions and Unique Q&A Merchandise Latest Questions

Author
  • 60k
Asked: 2024-11-27T10:46:08+00:00

Resetting MySQL Root Password in Ubuntu: A Step-by-Step Guide

  • 60k

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 &
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Remove the temporary configuration file: 

sudo rm /etc/mysql/mysql.conf.d/reset-mysql-password.cnf
Enter fullscreen mode Exit fullscreen mode

Step 8: Restart MySQL Service
Finally, restart the MySQL service to apply the changes: 

sudo service mysql restart
Enter fullscreen mode Exit fullscreen mode

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 .

mysqlmysqlpasswordresetubuntuwebdev
Share

Leave an answer

You must login to add an answer.

Need An Account,