Access denied for user 'root'@'localhost' (using password: YES) after setting mysql_native_password

Question

I have a brand new installation of MySQL on Ubuntu 20.04.

During the installation I picked a password for root. After the installation, I did the following:

sudo mysql -u root

It logged me in without asking for password. Then:

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

I thought by doing this it would ask for my password on the next login. However, now when I try to log in it gives me:

user@machine:~/Work$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I type my correct password and cannot login.

How can I solve this and achieve what I trying to setup (login with root but having to type my password)?

Iḿ on version mysql Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Thanks!

Answer 1

Its recommend to stop copying off old blogs (and perhaps StackOverflow/DBAExchange answers too) to do any authentication relates changes in MySQL and MariaDB, the mechanisms are being updated and no longer apply. Always check the official documentation.

Use SET PASSWORD or ALTER USER to manage user authentication.

Also modifying a user/host component of the username will put triggers, events, plugins, grants, roles etc out of sync with the combined username (aka broken).

Evidently changing the plugin also has consequences that using SET PASSWORD and ALTER USER will prevent the creating of an invalid configuration.

Since you've stuffed it up already, the official documentation also describes the mechanism for how to reset the root password.