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



Question:

Oracle website seems to imply it only works on 15 and 14. http://dev.mysql.com/downloads/workbench/

Also two of my Ubuntu 16.04 machines don't seem to be able to connect (Access is Denied errors for root)

Picture

It installs okay. It opens okay. But it won't connect to localhost.

Anyone have any luck?


Solution:1

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.


Solution:2

Works fine for me and I did nothing special. Installed mysql-server-5.7 and workbench both from command line and set up a user with a password and set up the normal database permissions (also with the normal method).

enter image description here

And with a database and table:

enter image description here


Solution:3

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;  


Solution:4

MySQL server on AWS host running Ubuntu 16.04; mysql-workbench on Ubuntu 16.04 laptop; uses KeyPair connecction.

After setting up Test db connection was working. But the actual connection always failed with a message like the one posted by Jonathan Leaders. Checked the log at ~/.mysql/workbench/log and found a few "permission denied" messages.

Now I can get mysql-workbench to work with: sudo mysql-workbench

And later I can go and chmod the directories needing permission.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »