How to change MySQL root password

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.

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’;

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

amin nahdy

Amin Nahdy, an aspiring software engineer and a computer geek by nature as well as an avid Ubuntu and open source user. He is interested in information technology especially Linux based ecosystem as well as Windows and MacOS. He loves to share and disseminate knowledge to others in a transparent and responsible way.

Leave a Reply

Close Menu