MySQL認証エラー 1698 (28000): Access denied for user 'root'@'localhost'

Pythonのmysql.connectorで接続しようとしたところ、下記のエラーが出た。
MySQLのバージョンは 8.0.36-1

>>> conn = mysql.connector.connect(user="root", host="localhost", password="test", database="testdb")
...
mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'root'@'localhost'

まず、認証のpluginを確認する。

mysql> Use mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+

rootユーザは auth_socket pluginを使っている。

https://dev.mysql.com/doc/mysql-secure-deployment-guide/8.0/en/secure-deployment-configure-authentication.html

The auth_socket plugin checks whether the socket user name matches the MySQL user name specified by the client program to the server. If the names do not match, the plugin also checks whether the socket user name matches the name specified in the authentication_string column of the mysql.user table row. If a match is found, the plugin permits the connection.

UPDATE userによって、認証方式を native password に変更する

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

以下、確認。

mysql> Use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

この後、ユーザ名&パスワードでログインできるようになった。

参考にさせていただいたブログ

https://ryotatake.hatenablog.com/entry/2019/11/12/access_denied_for_user_rootlocalhost