ERROR 1064 (42000): You have an error in your SQL syntax; Want to configure a password as root being the user

I was using MySQL 8 and non of the above worked for me.

This is what I had to do:

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

You can use:

SET PASSWORD FOR 'root' = PASSWORD('elephant7');

or, in latest versions:

SET PASSWORD FOR root = 'elephant7' 

You can also use:

UPDATE user SET password=password('elephant7') WHERE user='root';

but in Mysql 5.7 the field password is no more there, and you have to use:

UPDATE user SET authentication_string=password('elephant7') WHERE user='root';

Regards

On MySQL 8.0.15 (maybe earlier than this too): the PASSWORD() function does not work anymore, so you have to do:

Make sure you have stopped MySQL first (Go to: 'System Preferences' >> 'MySQL' and stop MySQL).

Run the server in safe mode with privilege bypass:

sudo mysqld_safe --skip-grant-tables
mysql -u root
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

Then

mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

Finally, start your MySQL again.

Enlighten by @OlatunjiYso in this GitHub issue.