Unable to connect via MySQL Workbench to localhost in Ubuntu 16.04 (passwordless root)

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.

MySQL 5.7 and up don't support connecting as "root" in mysql-workbench so you must create a normal user and connect via that.

sudo mysql -u root -p

Create a user named "admin" and use that to connect in mysql-workbench.

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

This question might be two years old but my work with mysql-workbench tonight seems to have given me the answer to it.

root user now users auth_socket authentication by default. The only way root can gain access is by being logged in as root.

I found this out by running mysql -u root both as root user and standard user.

It would not work under standard user.

So my next question was - what user does mysql-workbench run as. Turns out it runs as standard user by default. To get it to run as root user it has to be run from root. So I went into root user CLI and type 'mysql-workbench'.

I then had to go into the settings for the root user as shown here. The file location is valid for an ubuntu 18.04 installation.

If that socket location is invalid then you'll need to go into the mysql CLI from root or sudo and run the following command.

After you have the correct settings test the connection. It should be successful and you're ready to go.