If you have lost or forgotten the root password to your MySQL database and if you can access the server then you will be able to reset the password if you have a sudo user account.
In this short tutorial, you will learn how to change MySQL password for root.
To reset the root password, the commands might differ depending on the version of the database. To check your current MySQL version, invoke the command below in you terminal :
mysql –version
You might want to check here how it should look like.
Read: How to Install MySQL Workbench on Ubuntu 22.04
Access to MySQL database with root privileges
Now in order to change or reset the root password, you would need to shutdown the database server first using the following command:
sudo systemctl stop mysql
You will then be able to access it manually.
In order to be allowed to access the MySQL database command line or terminal with root privileges without the need to provide a password, you would have to run the MySQL database without loading any information about user privileges (stored in grant tables).
Read: How to solve MySQL error: Access denied for user root@localhost;
This can be achieved by instructing the MySQL database not to load grant tables. The command below will perform this task (in the background as per the & at the end):
sudo mysqld_safe –skip-grant-tables &
You should now be able to connect to MySQL database as root user without the need for a password prompt.
mysql -u root
Modifying the root password
With your current root access, you will now be able to modify the root password. You first have to invoke the command :
FLUSH PRIVILEGES
For recent versions of MySQL down to MySQL 5.7.6, run the following command (change or reset mysql root password Ubuntu-based):
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘your_new_password’; [mysql change root password]
Read: Quick Guide to Installing PostgreSQL on Ubuntu 22.04
For versions older than (and including) MySQL 5.7.5, issue the command below:
SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘your_new_password’);
Note that on some versions, you may want to run the two commands below:
UPDATE user SET authentication_string=password(‘your_new_password’) WHERE user=’root’;
FLUSH PRIVILEGES
Now you would need to exit and restart the service using the command :
sudo systemctl start mysql
If you like the content, we would appreciate your support by buying us a coffee. Thank you so much for your visit and support.
