There will be times when you are setting up several development environments and you forget mysql root password.

In this guide I will give you steps on how to reset/ change MySQL root password if you have ssh access to your server(Ubuntu).

SSH into your server and in server terminal.

  1. Stop the MySQL Server: sudo service mysql stop
  2. Start mysqld configuration: sudo mysqld --skip-grant-tables &
  3. Login to MySQL as root: mysql -u root mysql
  4. In the below commands replace NEWPASSWORD with your new password:

For MySQL < 8.0

UPDATE mysql.user SET Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
FLUSH PRIVILEGES;

If your MySQL uses new auth plugin, you will need to use: update user set plugin="mysql_native_password" where User='root'; before flushing privileges.

Note: on some versions, if password column doesn’t exist, you may want to try:
UPDATE user SET authentication_string=password('NEWPASSWORD') WHERE user='root';

Note: This method is not regarded as the most secure way of resetting the password, however, it works.

For MySQL >= 8.0

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';
FLUSH PRIVILEGES;

5. Stop password less mysql process and restart normal mysql process

sudo killall -9 mysqld and then start normal process: sudo service mysql start