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.
