Cannot connect with MySQL Workbench but can connect from shell with mysql command line client

Taken from https://askubuntu.com/questions/773446/unable-to-connect-via-mysql-workbench-to-localhost-in-ubuntu-16-04-passwordless/781136#781136 for my own reference:

The issue is likely due to socket authentication being enabled for the root user by default when no password is set, during the upgrade to 16.04. This important caveat is documented in the 16.04 release notes:

Password behaviour when the MySQL root password is empty has changed. Packaging now enables socket authentication when the MySQL root password is empty. This means that a non-root user can’t log in as the MySQL root user with an empty password.

For whatever reason, the MySQL Workbench that came with 16.04 doesn’t work out of the box with MySQL server, at least for me. I tried using “Local Socket/Pipe” to connect in a number of different ways but to no avail.

The solution is to revert back to native password authentication. You can do this by logging in to MySQL using socket authentication by doing:

sudo mysql -u root

Once logged in:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

which will revert back to the native (old default) password authentication. If you’ve attempted some other method to fix the issue, you’ll want to make sure the “plugin” field in mysql.user is set to “auth_token”, which may require using mysqld_safe to log in to MySQL in the event you’ve been tinketing with things, like I did.

Credit to Miguel Nieto’s blog post for this solution.