Search code examples
mysqlpasswordsroot

MySQL 5.7.20 can't set a root password

After a fresh mysql 5.7 installation i have a problem with root pw.

I'm trying start mysql with mysqld_safe --skip-grant-tables & and go in to mysql mysql -u root

And try to set a password with

root@localhost [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> ALTER USER 'root@localhost' IDENTIFIED BY 'password';

And im getting this error;

ERROR 1396 (HY000): Operation ALTER USER failed for 'root@localhost'@'%'

I tried this way too;

root@localhost [mysql]> update user set authentication_string=password('password') where user='root';
Query OK, 0 rows affected, 1 (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

Both not working please help..

Solution

  • Try this (user and host have to be divided with ''):

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

    In a case you changed the privelages with DML:

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

    You have to run:

    FLUSH PRIVILEGES;