mysqlpasswordsroot

How to reset the root password in MySQL 8.0.11?

I have actually lost my root password and I need to change it. I follow these steps :

  1. Stop the MySQL server process.
  2. Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for a password.
  3. Connect to the MySQL server as the root user.

That can be found on these website : https://www.howtoforge.com/setting-changing-resetting-mysql-root-passwords#recover-mysql-root-password

mysql> use mysql;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD("TOOR");
mysql> flush privileges;
mysql> quit

First error, so I tried :

mysql> use mysql;
mysql> update user set password=PASSWORD("TOOR") where User='root';
mysql> flush privileges;
mysql> quit

Always the same error said :

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 '("TOO
R") WHERE User='root'' at line 1

How can I resolve this?

Solution

  • As this link says:

    “This function was removed in MySQL 8.0.11”

    1. If you in skip-grant-tables mode
      in mysqld_safe:

      UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

      And then, in the terminal:

      mysql -u root

      And then in MySQL:

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

    2. Not in skip-grant-tables mode just in MySQL:

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