How to find out the MySQL root password

Question

thanks to @thusharaK I could reset the root password without knowing the old password.

On ubuntu I did the following:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking

Then run mysql in a new terminal:

mysql -u root

And run the following queries to change the password:

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

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is 'authentication_string'.

Quit the mysql safe mode and start mysql service by:

mysqladmin shutdown
sudo service mysql start

You can't view the hashed password; the only thing you can do is reset it!

Stop MySQL:

sudo service mysql stop

or

$ sudo /usr/local/mysql/support-files/mysql.server stop

Start it in safe mode:

$ sudo mysqld_safe --skip-grant-tables

(above line is the whole command)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

$ mysql -u root

mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

MySQL 5.7 and over:

mysql> use mysql; 
mysql> update user set authentication_string=password('password') where user='root'; 

Start MySQL:

sudo mysql start

or

sudo /usr/local/mysql/support-files/mysql.server start

Your new password is 'password'.

MySQL 5.7 and above saves root in MySQL log file.

Please try this:

sudo grep 'temporary password' /var/log/mysqld.log

one thing that tripped me up on a new install of mySQL and wonder why I couldn't get the default password to work and why even the reset methods where not working. well turns out that on Ubuntu 18 the most recent version of mysql server does not use password auth at all for the root user by default. So this means it doesn't matter what you set it to, it won't let you use it. it's expecting you to login from a privileged socket. so

mysql -u root -p

will not work at all, even if you are using the correct password!!! it will deny access no matter what you put in.

Instead you need to use

sudo mysql

that will work with out any password. then once you in you need type in

 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password you want to use';

then log out and now the bloody thing will finally accept your password