This problem is explained in this post:
Even if you have setup password for MySQL root user, you still can't authenticate with the database server as root user with a password. Because, the MySQL root user is set to authenticate using the auth_socket plugin by default instead of the password in Ubuntu systems running MySQL 5.7 and newer versions. Hence, you can can't access the MySQL server with root user and its password.
The symptom is, if you try:
mysql -u root -p
and give the correct password, you still get:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
The solution is to change authentication method of the MySQL
root user from
auth_socket to something else, like
caching_sha2_password. The procedure to do so is explained in the above post. Thanks to Henrik Halbritter for the comment.
Use chmod to "setuid" the MySQL program
/usr/bin/mysql will eliminate the need to use
sudo in order to connect to my local server. However, this does not solve the problem that
mysql-workbench still cannot connect.
There might be other settings in
mysql-workbench, for example, select appropriate authentication method, but I didn't try.
Final remark: change authentication for the root user should only be used in development environment. In production, a non-root mysql user should be created and used.