3

The hard data first:
Local machine: Xubuntu 17.10, with MySql-Workbench 6.3
Remote Machine: Ubuntu 16.04, with MySql-Client 5.7 and MySql-Server 5.7

The connection method is: Standard TCP/IP over SSH

The error:

Your connection attempt failed for user 'root' from your host to server at 127.0.0.1:3306: Can't connect to MySQL server on '127.0.0.1' (111)

Then it gives me a list with things to check.

1 Check that mysql is running on server 127.0.0.1

check

2 Check that mysql is running on port 3306

check

3 Check the root has rights to connect to 127.0.0.1 from your address

I checked the mysql.user table and it says that root has the host: localhost and since MySql-Workbench does an ssh connect to the remote machine and then connects to the MySql-Server, localhost should be fine.
Nonetheless I tried setting the host to % as a wildcard but that didn't change anything either so I switched back to default localhost. I also checked the my.cnf. That is the entire content of it:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

There is no bind-address or skip-networking directive set.

4 Make sure you are both providing a password if needed and using the correct password for 127.0.0.1 connecting from the host address you're connecting from

Password and stuff is all fine. Passwords are set, they are typed correctly when logging in.

When I do, what Workbench is supposed to do

  • ssh to the remote machine (ssh user@remotemachine)
  • login to mysql server (mysql -u root -p)

It works flawlessly I just can't do via MySql-Workbench

Help is much appreciated because I already tried everything I was able to research.

Improve this question
4
  • Welcome to the site. You are connecting the MySQL Server through "remotely" or "Native mode".
    – Md Haidar Ali Khan
    Apr 14, 2018 at 14:16
  • Try to bind IP "bind-address = 127.0.0.1" and also "skip-networking" in your "my.cnf" file. And after that run the command "sudo service mysql restart" then try to connect.
    – Md Haidar Ali Khan
    Apr 14, 2018 at 14:24
  • I add these two lines to my my.cnf bind-address=127.0.0.1 (line 23) and skip-networking (line 24). When I restart mysql it returns two errors in sudo journalctl -xe: mysqld: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 23! mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
    – antimatter
    Apr 28, 2018 at 16:54
  • I sorted the errors out when restarting mysql. I needed to precede the directives with [mysqld]. Now it restarts without errors but I still can't connect.
    – antimatter
    Apr 29, 2018 at 8:20

6 Answers 6

Reset to default
0

same issue i faced with mysql workbench 6.3.8 build 1228 CE (64-bits) community with kubuntu 18.04 version.

i set connection method standard tcp/ip over ssh and when i try to connect to remote server it asks for ssh password and after successful ssh tunnel it asks for remote mysql password ( which was expected as i did not store passwords in keychain -- option available there in connection window ) and when i entered it i got same above error.

after checking at server mysql log i found this line: Access denied for user 'root'@'localhost' (using password: NO)

but i already entered the password while workbench asks me.

finally i try edit connection and store mysql password there in keychain option and it works fine.

Improve this answer
1
  • I would prefer a solution where I don't have to store the password, but at least this is the first time after months where I was able to connect via mysql-workbench. Thanks
    – antimatter
    Jan 9, 2019 at 18:59
0

The error message says clearly the MySQL server could not be reached, hence check everything that could block access to it. This is not user related.

  1. Do you have a running network adaptor on the machine you run Workbench on? Does it allow TCP/IP connections?
  2. Can you reach the target machine your MySQL runs on?
  3. Does MySQL run on that target machine?
  4. Can you create an SSH tunnel to the target machine?
  5. Can you reach the MySQL server when you are logged in on the target machine (e.g. by means of an SSH)?

If you can answer all that with yes there should be no problem connecting to the MySQL server from MySQL Workbench. Point 6 can be a bit more complicated, if your MySQL server is not on the same machine as where the SSH endpoint is or if it has networking disabled or if it uses a different port than what you tried.

However, the general idea should be clear now. Try to isolate the actual problem (is it network related or a config problem?). Maybe this video can also help you: https://www.youtube.com/watch?v=DCgRF4KOYIY&t=76s

Improve this answer
1
  • Sorry for my late answer. 1. Yes, connecting from my machine at work, works flawlessly. 2. Yes. 3. Yes. 4. Yes. 5. Yes.
    – antimatter
    Apr 25, 2018 at 15:29
0

Go to /etc/mysql/mysql.conf.d/mysqld.cnf, and change:

bind-address = 127.0.0.1

to:

#bind-address = 127.0.0.1
Improve this answer
1
  • This is a very risky answer. If you remove this line MySQL changes from only listening on localhost to binding itself to all interfaces and listening publically (dev.mysql.com/doc/refman/8.0/en/…) and you could end up exposing your database to the world.
    – Nick
    May 16, 2020 at 15:49
-1

I am facing the same issue. As weird as it can get I found that I can only connect to the server using the "Topbar -> Database -> Connect to Database..."

enter image description here

Give it a try. This definitely seems a Workbench's bug. My version is 6.3.10

Improve this answer
1
  • Connecting via the menu is exactly the same as connecting using a tile.
    – Mike Lischke
    Apr 15, 2018 at 8:11
-1

I went head and changed my password here and workbench worked.

enter image description here

Improve this answer
-1

If you are in ubuntu, go to terminal and write: 1:

sudo mysql -u 'your_user_name' -p

2: After that, when you are are in mysql server type

show databases;

3: Then,you see there are many databases select mysql by type

use mysql;

4:Then, type select

user,host,plugin from mysql.server;

5: Then you see there is user,host and plugin. You have to change plugin for your user from "auth_socket" to "other_user_plugin" in my command i have plugin name of other user is "caching_sha2_passward".you have to type:

update user set plugin="caching_sha2_password" where User='your_user_name';

6:Then, type

flush privileges;

7:Then type exit to go back and type in terminal

sudo service mysql restart

After that you will be able to create databases with permission

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.