MySQL root password change

Question

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
FLUSH PRIVILEGES;

Have a look at this from the MySQL reference manual:

First log in to MySQL:

mysql -u root -p

Then at the mysql prompt, run:

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

Then

FLUSH PRIVILEGES;

Look at this page for more information: Resetting the Root Password: Unix Systems

UPDATE:

For some versions of mysql, the password column is no longer available and you'll get this error:

ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

In this case, use ALTER USER as shown in the answer below.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

You can find Resetting the Root Password in the MySQL documentation.

This is the updated answer for WAMP v3.0.6 and up.

In the MySQL command-line client, phpMyAdmin or any MySQL GUI:

UPDATE mysql.user
SET authentication_string=PASSWORD('MyNewPass')
WHERE user='root';

FLUSH PRIVILEGES;

In MySQL version 5.7.x there is no more password field in the MySQL table. It was replaced with authentication_string. (This is for the terminal/CLI.)

In the MySQL command-line client, phpMyAdmin or any MySQL GUI:

UPDATE mysql.user SET authentication_string=PASSWORD('MyNewPass') WHERE user='root';

FLUSH PRIVILEGES;

Please follow the below steps.

1. sudo service mysql stop
2. sudo mysqld_safe --skip-grant-tables
3. sudo service mysql start
4. sudo mysql -u root
5. use mysql;
6. show tables;
7. describe user;
8. update user set authentication_string=password('1111') where user='root';
9. FLUSH PRIVILEGES;

Log in with password "1111".

I searched around as well and probably some answers do fit for some situations,

my situation is Mysql 5.7 on a Ubuntu 18.04.2 LTS system:

(get root privileges)

$ sudo bash

(set up password for root db user + implement security in steps)

# mysql_secure_installation

(give access to the root user via password in stead of socket)

(+ edit: apparently you need to set the password again?)

(don't set it to 'mySecretPassword'!!!)

# mysql -u root

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> set password for 'root'@'localhost' = PASSWORD('mySecretPassword'); 
mysql> FLUSH PRIVILEGES;
mysql> exit;

# service mysql restart

Many thanks to zetacu (and erich) for this excellent answer (after searching a couple of hours...)

Enjoy :-D

S.

Edit (2020):

This method doesn't work anymore, see this question for future reference...

I found it! I forgot to hash the password when I changed it. I used this query to solve my problem:

update user set password=PASSWORD('NEW PASSWORD') where user='root';

I forgot the PASSWORD('NEW PASSWORD') and just put in the new password in plain text.