Losing the root password for a MySQL server can be a significant hurdle for managing databases. Fortunately, MySQL provides a way to reset the password. Below we will outline the steps to do this for MySQL 5.7 and later versions, as the process differs from earlier versions due to changes in the authentication method.
Pre-Step: Identify Your MySQL Version
Before proceeding, you should identify which version of MySQL you are using. You can do this by running:
mysql --version
Step 1: Stop the MySQL Service
First, you need to stop the running MySQL service:
sudo systemctl stop mysql
Step 2: Configure MySQL for Safe Mode
To change the root password, we’ll start MySQL in safe mode, which allows us to log in without a password.
Open the MySQL configuration file:
sudo vim /etc/mysql/my.cnf
Add the following lines at the end of the [mysqld]section:
[mysqld]
skip-grant-tables
Then restart the MySQL service:
sudo systemctl restart mysql
Step 3: Log in to MySQL
Now, you can log in to MySQL without a password:
mysql -u root
Step 4: Change the Root Password
Once logged in, execute the following:
use mysql;
Now, if you’re using MySQL 5.7 or above, you should update the authentication_string column. Note that the PASSWORD() function is removed in MySQL 5.7.6 and later, so you need to use a simple hash mechanism with ALTER USER.
First, check the user table:
SELECT user, authentication_string FROM mysql.user WHERE user = 'root';
To reset the password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
Replace ‘your_new_password’ with your new root password.
Afterwards, run:
FLUSH PRIVILEGES;
Step 5: Secure MySQL Again
Exit MySQL and restore the normal operation mode:
exit
Edit the MySQL configuration file again:
sudo vim /etc/mysql/my.cnf
Remove or comment out the skip-grant-tables line by adding a #in front of it:
# skip-grant-tables
Then restart the MySQL service:
sudo systemctl restart mysql
Step 6: Test the New Password
Finally, test the new password to ensure you can log in:
mysql -u root -p
You will be prompted to enter the new password. If everything was configured correctly, you should gain access to the MySQL server.
Conclusion
By following these steps, you can reset the root password for your MySQL server on Ubuntu. Always ensure you keep your passwords safe and use complex passwords to prevent unauthorized access.