`mysql -u root -p` ERROR 1698 (28000): Access denied for user 'root'@'localhost'

事象

ubuntu:~$  mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

原因

root ユーザが auth_socket 認証を利用している。

ubuntu:~$ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)
<snip>
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 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           |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

auth_socket 認証プラグインは、Unix ソケットファイル経由でローカルホストから接続するクライアントを認証します。(中略) このプラグインは、ユーザー名がサーバーへのクライアントプログラムで指定された MySQL ユーザー名と一致するかどうかをチェックし、その名前が一致する場合にのみ接続を許可します。
MySQL 5.6 リファレンスマニュアル

解決方法

root ユーザも mysql_native_password プラグインを利用する

ubuntu:~$ sudo -i
root:~# /usr/bin/mysql_secure_installation
<snip>
Press y|Y for Yes, any other key for No: no
Please set the password for root here.

New password:
Re-enter new password:
<snip>
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
<snip>
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
<snip>
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
<snip>
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
root:~# exit
logout

ログイン

ubuntu:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
<snip>
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 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 |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

mysql>

環境

ubuntu:~$ uname -a
Linux ubuntu 5.4.0-48-generic #52-Ubuntu SMP Thu Sep 10 10:58:49 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

ubuntu:~$ cat /etc/issue
Ubuntu 20.04.1 LTS \n \l

ubuntu:~$ mysql --version
mysql  Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu))