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

I have just downloaded WAMP. I want to configure a password for the MySQL root user using MySQL console. No password has been set previously.

The following is the input

    mysql-> use mysql
    Database changed
    mysql-> UPDATE user
         -> SET Password=PASSWORD<'elephant7'>
         -> WHERE user='root';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user='root'' at line 3

This worked perfectly for me.

mysql> use mysql; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'my-password-here';

From the mysql documentation version: 8.0.18:

A superuser account 'root'@'localhost' is created. A password for the superuser is set and stored in the error log file. To reveal it, use the following command: shell> sudo grep 'temporary password' /var/log/mysqld.log Change the root password as soon as possible by logging in with the generated, temporary password and set a custom password for the superuser account:

shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

The following commands (modified after those found here) worked for me on my WSL install of Ubuntu after hours of trial and error:

sudo service mysql stop
sudo mysqld --skip-grant-tables &
mysql -u root mysql
UPDATE mysql.user SET authentication_string=null WHERE User='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password_here';
flush privileges;
exit;

Try this one. It may be helpful:

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

I hope it helps.

This is the only command that worked for me. (I got it from M 8.0 documentation)

ALTER USER 'root'@'*' IDENTIFIED WITH mysql_native_password BY 'YOURPASSWORD';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOURPASSWORD';

mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'my-password-here';

Try it once, it worked for me.

I have problems with set password too. And find answer at official site

SET PASSWORD FOR 'root'@'localhost' = 'your_password';

While using mysql version 8.0 + , use the following syntax to update root password after starting mysql daemon with --skip-grant-tables option

UPDATE user SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_password')

Try this:

UPDATE mysql.user SET password=password("elephant7") where user="root"

If you have ERROR 1064 (42000) or ERROR 1046 (3D000): No database selected in Mysql 5.7, you must specify the location of the user table, the location is mysql.table_name Then the code will work.

sudo mysql -u root -p

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

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.

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';