How to reset MySQL password on Ubuntu or Linux?

Hey Folks! Most of the times while doing MySQL server installations or LAMP installations on AWS or any other cloud based Ubuntu or linux hosting platforms we encounter that the screen asking for MySQL root password didn’t appear and somewhat got skipped. This can be sometimes very much painful to fix and get your website functional and running well.

So, what to do in this case?

The answer is simple… if the system has skipped the MySQL password screen or in any way it got skipped and the installation took place successfully… you will be glad to know that the MySQL installation has been done with username: root and password: <blank i.e. no password>

To Check MySQL version:

SSH: mysql –version

So, using SSH or Putty on Windows.. you can login to the MySQL server using the following commands:

SSH: mysql -u root <hit enter>

Or,

SSH: sudo mysql -u root <hit enter>

or,

SSH: sudo mysql -u root -p <hit enter>

and, you will successfully login without being asked for any password and this happens because their is no password set for the root user.

Now, comes.. how I can reset this password?

So, once you have logged into the MySQL using the above steps. You can use the following commands to reset the password.

mysql> FLUSH PRIVILEGES;

MySQL version 5.7.6 or newer

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘new_password’;

MySQL version 5.7.5 or lower

mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘new_password’);

In case, the above steps do not work.. you can try the following approach to fix the problem.

mysql> UPDATE mysql.user SET authentication_string = PASSWORD(‘new_password’) WHERE User = ‘root’ AND Host = ‘localhost’;

//reload the grant tables
mysql> FLUSH PRIVILEGES;

This must have resolved your concerns and if not the problem needs a different approach to fix. So, lets go with that second approach now.

How to install MySQL version 5.7 on an Ubuntu 18.04 server?

SSH: sudo apt update

SSH: sudo apt install mysql-server

SSH: sudo mysql_secure_installation

Configuring MySQL

SSH: sudo mysql_secure_installation

This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options.

The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the strength of your MySQL password.

Regardless of your choice, the next prompt will be to set a password for the MySQL root user. Enter and then confirm a secure password of your choice.

From there, you can press Y and then ENTER to accept the defaults for all the subsequent questions.

This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MySQL immediately respects the changes you have made.

Login to MySQL server

SSH: sudo mysql

Check whether the authentication password is set for the user or not…

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Update the password with a strong password…

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

Make sure you are using your own password instead of ‘password’.

mysql> FLUSH PRIVILEGES;

mysql> exit;

And, try to login without a password this time… you will notice that the password has been updated.

I hope this was useful for you.

Thank you!!