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)

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

Anyone have any luck?

Did you build it from source? What's the exact error given? Similar to when you specify an incorrect password? — , May 16 '16 at 23:39
Yes it is similar. But root has no password. And I can connect to root on command line correctly. And I double checked all priveleges and ran FLUSH PRIVILEGES;
At this point I'm curious if anyone can connect to localhost from 16, since the Oracle site explicitly says 14 and 15 only — Jonathan, May 16 '16 at 23:42
I didn't see Oracle make a claim it doesn't work, I just didn't see a binary for Xenial. I do however see mysql-workbench here, http://packages.ubuntu.com/xenial/mysql-workbench. That error doesn't seem like a software compatibility error. Turn logging on mysql and increase the logging level to 2 and see if the log shows your request. — , May 16 '16 at 23:50
@bc2946088 mysql-workbench is in the universe repository for 16.04 link — Videonauth, May 16 '16 at 23:58
did you check any of the 4 options? ;-) WB works perfectly fine for me (with 5.7 btw). Proof below. — Rinzwind, May 18 '16 at 20:02

score 51 · Answer 1 · answered Jun 03 '16 at 01:46

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.

This fixed it, but the next time I ran apt update it broke again. — Jonathan, Jul 01 '16 at 21:50
I didn't see it, because I didn't notice until next time I ran mysql, much later on — Jonathan, Jul 03 '16 at 01:07
I wonder if this is a Ubuntu 16.04 thing or a mysql 5.7 thing (was it Oracle or Canonical who made this change?) — Jonathan, May 04 '17 at 00:33
Thank, this solved my problem. I was able to login to mysql as sudo only. But doing this made me able to login as normal user — Surendra Shrestha, Apr 27 '18 at 06:51

Jonathan · Accepted Answer · 2017-02-22T00:14:59.373

47

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;

edited Feb 22 '17 at 00:14

answered Jul 01 '16 at 21:55

Jonathan

3,904

1

This answer was perfect for me, thanks Jonathan. – Chris Evans Jan 22 '19 at 07:54
4

This works for Ubuntu 18.04 – tadeubarbosa Apr 29 '19 at 21:44
1

Worked perfectly well. – manpikin May 10 '20 at 18:21
3

Worked for me in Ubuntu 20.04 – surya_hs Dec 17 '20 at 02:53
Wouldn't GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; fix the login problem with root on mysql-workbench – Goku Apr 14 '21 at 14:52
Works fin on Ubuntu 18.04 in 2021 – Omar Dulaimi Jul 26 '21 at 18:58

score 8 · Answer 3 · edited Sep 26 '18 at 15:28

8

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.

Root connection settings mysql-workbench.

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.

MySQL CLI command to find the socket.

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

edited Sep 26 '18 at 15:28

abu_bua

10,783

answered Sep 26 '18 at 14:36

George Tasker

81

Thank you for this! I shouldn't have to revert security-related changes to authentication to support an older tool, when I can just tell that tool to connect using the newer method. – Aaron Apr 25 '19 at 14:18
Do I achieve a local connection through this way? I don't understand why can't we connect through TCP/IP. – john gonidelis Dec 11 '19 at 22:09
hmm, so there is no way for tcp over ssh if the ssh user is not root then. Makes it hard. Sort of aligns with Postgres and the Postgres user. A pity we could not have allowed a MySQL user in the same way. – mckenzm Sep 19 '22 at 07:19

score 3 · Answer 4 · answered Aug 19 '18 at 04:31

Create an user account with appropriate administrative privileges that can connect via mysql workbench using the auth_socket plugin. Note that this does not work for root connections to the mysql server.

Logon to mysql from a terminal session:

$sudo mysql

If you are able to do this then the auth_socket plugin is enabled and the root account is authenticating using this plugin. Note that this is the default setup when installing mysql on ubuntu after having run the secure installation script.

First create a mysql user for your account 'valerie':

mysql> CREATE USER 'valerie'@'localhost' IDENTIFIED WITH auth_socket;

Enable administrative privileges for the account:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'valerie'@'localhost' WITH GRANT OPTION;

Exit the shell:

mysql> exit

Then in mysql workbench:

Create a new connection
Select the connection method as Local Socket/Pipe
Set the Socket/Pipe Path to: /var/run/mysqld/mysqld.sock (note that this is the path for a Debian / Ubuntu system and changes for other flavors of Linux)
Set the username ('valerie' in this example)

You should be able to then connect to the mysql server with this connection.

score 3 · Answer 5 · edited Jul 20 '21 at 13:36

3

Try this

select mysql database
use mysql;
UPDATE user set plugin='mysql_native_password' where User='root';
flush privileges;
exit;

Then

sudo service mysql restart

edited Jul 20 '21 at 13:36

zx485

2,426

answered Jul 21 '19 at 20:25

URI

31

Daxesh Prajapati · Answer 6 · 2022-06-20T17:45:25.407

3

Open terminal of your ubuntu system and enter below code,

In my ubuntu 20.04 os system it works fine, able to enter password and workbeanch working fine.

sudo snap connect mysql-workbench-community:password-manager-service :password-manager-service

edited Jun 20 '22 at 17:45

answered Jun 20 '22 at 17:45

Daxesh Prajapati

31
2

Working with ubuntu 22.04 & mysql workbench 8.0.29 – Nipun Tharuksha Jul 25 '22 at 10:57
@Daxesh Prajapati - Thanks. I used this command with Ubuntu 22.04.2 LTS and MySQL Workbench 8.0 and I was then able to connect with my password. – Victor Feb 25 '23 at 21:51

score 2 · Answer 7 · answered Apr 17 '20 at 16:52

If you are getting this error in Workbench then follow this steps.

First simply log in with your current password:

sudo mysql -u root -p

Then change your password because having low strength password gives error sometimes.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-strong-password';

FLUSH PRIVILEGES;

Then simply exit and again login with your new password:

quit

sudo mysql -u root -p

Once you successfully logged in type the command:

use mysql;

It should show a message like 'Database changed' then type:

UPDATE user SET plugin='mysql_native_password' WHERE User='root';

After that type:

UPDATE mysql.user set authentication_string=PASSWORD('new-strong-password') where user='root';

Then type:

FLUSH PRIVILEGES;

Then simply exit:

quit

Now try to log in with your new password in your WORKBENCH. Hope it will work. Thank you.

Not really an answer because now there is a password. How do we achieve the same result without a password? — mckenzm, Sep 19 '22 at 07:24

score 2 · Answer 8 · answered May 18 '16 at 20:01

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).

And with a database and table:

answered May 18 '16 at 20:01

Rinzwind

299,756

Interesting. Love the discworld reference. When I reconfigure the mysql packages, I do not get a prompt to change the password. Do you? I'm leaning towards thinking I have a corrupted install or some conflict. I think I'm supposed to get a prompt to change the root password. – Jonathan May 20 '16 at 00:59

score 1 · Answer 9 · edited Oct 16 '21 at 12:51

1

I had the same problem on newly installed 21.04 Ubuntu, after trying all these methods that didn't quite work.

I tried to go into settings and allowed all the permissions and it worked for some reason.

edited Oct 16 '21 at 12:51

Greenonline

2,081

answered Oct 12 '21 at 10:43

Keith John Smith

11

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – ChanganAuto Oct 12 '21 at 11:22
1

@ChanganAuto - The screenshot of all the settings enabled appears to be the answer. – Greenonline Oct 12 '21 at 11:24

score 0 · Answer 10 · answered Nov 16 '21 at 16:05

0

I ran this command and it fixed my issue:

sudo snap connect mysql-workbench-community:password-manager-service :password-manager-service

Thanks to Jos for his answer here

answered Nov 16 '21 at 16:05

Al Fahad

273

score 0 · Answer 11 · edited Jul 24 '19 at 12:07

0

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

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.

edited Jul 24 '19 at 12:07

Kevin Bowen

19,615
55
79
83

answered Mar 04 '17 at 07:22

Usman Suleman

1

3

Is this an answer to the question? – George Udosen Mar 04 '17 at 12:23

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

11 Answers11

Linked